Postgresql different queries Why the same explain?

Question

Query 1:

select c_bh,c_xzdm,c_twhbm,d_tjrq 
from (
   select c_bh,c_xzdm,c_twhbm,d_tjrq 
   from t_table 
   order by d_tjrq desc
) t1  
limit 10

Query 2:

select c_bh,c_xzdm,c_twhbm,d_tjrq 
from t_table 
order by d_tjrq desc 
limit 10

The same plan:

Limit  (cost=0.44..18.79 rows=10 width=59)
  ->  Index Scan using i_t_table_d_tjrq on t_table  (cost=0.44..32135085.58 rows=17507700 width=59)

any output of query (2) is a valid output of query (1) (the opposite is not true) and therefore Postgresql can choose to give the same output for (1) as for (2) and therefore the same execution plans. — Dan Getz, Dec 18 '17 at 08:33
Of course they use the same plan because they are doing the same thing. — , Dec 18 '17 at 09:09
SQL is ***declarative*** not ***imperative***. This means that SQL isn't executed, it is a language that allows you to express a problem space, the database engine the generates a plan from that problem declaration *(it chooses its own way to solve it, normally using cost estimations)*. In your examples both SQL statements amount to the same problem and so can be solved in the same way. — MatBailie, Dec 18 '17 at 09:12

score 0 · Answer 1 · answered Dec 18 '17 at 09:15

0

In the first case, PostgreSQL “flattens” the subquery so that it can be optimized better. The result is equivalent to the second query.

If you want to avoid that, put an optimization barrier like OFFSET 0 into the subquery.

answered Dec 18 '17 at 09:15

Laurenz Albe

1 Answers1