Question

[Databases] Compare and why the two below queries plans are different after executing them?

postgres=# EXPLAIN ANALYZE SELECT sorted , rndm FROM table1 where sorted > 1999231 and rndm = 1005; QUERY PLAN Index Scan using table1-sorted-idx on table1 (cost=0.43.. 166.44 rows-1 width-8) (actual time-e . 801..0.801 rows=0 loops=1) Index Cond: (sorted 1999231) Filter: (rndm= 1005) Rows Removed by Filter: 3840 Planning Time: 0.114 ms Execution Time:0.816 ms (6 rows) postgres=# EXPLAIN ANALYZE SELECT sorted , rndm FROM table1 where sorted < 1999231 and rndm = 1005; QUERY PLAN Seq Scan on table1 (cost-6.00. .253093 . 32 rows=103 width-8) Filter: ((sorted < 1999231) AND (rndm = 1005)) Rows Removed by Filter: 9999886 Planning Time: 0.099 ms Execution Time: 865.186 ms (5 rows) (actual loops=1) time« 13.513..865. 164 rows-114

Answer 1

In 1st query table contains indexing column for sorted. Data 1st will be searched index column with out scanning entire table and the filter out data using rndm on table using table scan.

Where as in 2nd query table doesn't have any indexing column, so data directly will get filtered instead of index with help of where clause columns. Entire table scan will happen unlike 1st query.