postgresql 9.4 with nested "order by" - Mailing list pgsql-bugs

Hello,
I have a problem with postgresql and the "ORDER BY".

Centos 7 (new virtual container on proxmox) / Postgresql 9.4 / data import from Postgresql 8.3 without problems

Watch these three SELECT which gives the same result (the second make a list):

1) SELECT MAX(ref_id_trafic) FROM compteur_date WHERE ref_id_trafic IN (SELECT id FROM trafic WHERE ref_id_materiel=15
ORDERBY id); 
2) SELECT ref_id_trafic      FROM compteur_date WHERE ref_id_trafic IN (SELECT id FROM trafic WHERE ref_id_materiel=15
ORDERBY id); 
3) SELECT ref_id_trafic      FROM compteur_date WHERE ref_id_trafic IN (SELECT id FROM trafic WHERE ref_id_materiel=15)
           ORDER BY ref_id_trafic DESC LIMIT 1; 
4) SELECT ref_id_trafic      FROM compteur_date WHERE ref_id_trafic IN (SELECT id FROM trafic WHERE ref_id_materiel=15
ORDERBY id) ORDER BY ref_id_trafic DESC LIMIT 1; 

Indeed, we are not talking about the writing quality of last two
So the explain analyse shows :

1) explain analyse SELECT MAX(ref_id_trafic) FROM compteur_date WHERE ref_id_trafic IN (SELECT id FROM trafic WHERE
ref_id_materiel=15ORDER BY id); 
                                                                          QUERY PLAN
                                      

--------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=80650.71..80650.72 rows=1 width=4) (actual time=196.509..196.509 rows=1 loops=1)
   ->  Nested Loop  (cost=9506.15..80375.80 rows=109963 width=4) (actual time=149.288..192.147 rows=22334 loops=1)
         ->  HashAggregate  (cost=9505.72..9514.44 rows=872 width=4) (actual time=149.163..149.671 rows=859 loops=1)
               Group Key: trafic.id
               ->  Sort  (cost=9492.64..9494.82 rows=872 width=4) (actual time=148.576..148.705 rows=859 loops=1)
                     Sort Key: trafic.id
                     Sort Method: quicksort  Memory: 65kB
                     ->  Seq Scan on trafic  (cost=0.00..9450.05 rows=872 width=4) (actual time=0.065..147.899 rows=859
loops=1)
                           Filter: (ref_id_materiel = 15)
                           Rows Removed by Filter: 526805
         ->  Index Only Scan using ref_id_trafic_indexdate on compteur_date  (cost=0.43..80.00 rows=126 width=4)
(actualtime=0.027..0.041 rows=26 loops=859) 
               Index Cond: (ref_id_trafic = trafic.id)
               Heap Fetches: 22334
 Planning time: 1.812 ms
 Execution time: 196.688 ms
(15 rows)


2) explain analyse SELECT ref_id_trafic FROM compteur_date WHERE ref_id_trafic IN (SELECT id FROM trafic WHERE
ref_id_materiel=15ORDER BY id); 
                                                                       QUERY PLAN
                                

--------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=9506.15..80375.80 rows=109963 width=4) (actual time=133.948..167.970 rows=22334 loops=1)
   ->  HashAggregate  (cost=9505.72..9514.44 rows=872 width=4) (actual time=133.893..134.361 rows=859 loops=1)
         Group Key: trafic.id
         ->  Sort  (cost=9492.64..9494.82 rows=872 width=4) (actual time=133.274..133.416 rows=859 loops=1)
               Sort Key: trafic.id
               Sort Method: quicksort  Memory: 65kB
               ->  Seq Scan on trafic  (cost=0.00..9450.05 rows=872 width=4) (actual time=0.038..132.723 rows=859
loops=1)
                     Filter: (ref_id_materiel = 15)
                     Rows Removed by Filter: 526805
   ->  Index Only Scan using ref_id_trafic_indexdate on compteur_date  (cost=0.43..80.00 rows=126 width=4) (actual
time=0.018..0.031rows=26 loops=859) 
         Index Cond: (ref_id_trafic = trafic.id)
         Heap Fetches: 22334
 Planning time: 0.537 ms
 Execution time: 170.422 ms
(14 rows)


3) explain analyse SELECT ref_id_trafic FROM compteur_date WHERE ref_id_trafic IN (SELECT id FROM trafic WHERE
ref_id_materiel=15)ORDER BY ref_id_trafic DESC LIMIT 1; 
                                                                                      QUERY PLAN
                                                              

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.86..64.69 rows=1 width=4) (actual time=16510.462..16510.463 rows=1 loops=1)
   ->  Nested Loop Semi Join  (cost=0.86..7018606.22 rows=109963 width=4) (actual time=16510.457..16510.457 rows=1
loops=1)
         ->  Index Only Scan Backward using ref_id_trafic_indexdate on compteur_date  (cost=0.43..599666.29
rows=14303080width=4) (actual time=0.015..2205.771 rows=3659401 loops=1) 
               Heap Fetches: 3659401
         ->  Index Scan using trafic_pkey on trafic  (cost=0.42..0.45 rows=1 width=4) (actual time=0.003..0.003 rows=0
loops=3659401)
               Index Cond: (id = compteur_date.ref_id_trafic)
               Filter: (ref_id_materiel = 15)
               Rows Removed by Filter: 1
 Planning time: 13.384 ms
 Execution time: 16510.528 ms
(10 rows)


And the last :

4) explain analyse SELECT ref_id_trafic FROM compteur_date WHERE ref_id_trafic IN (SELECT id FROM trafic WHERE
ref_id_materiel=15ORDER BY id) ORDER BY ref_id_trafic DESC LIMIT 1; 
                                                                                      QUERY PLAN
                                                              

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=9483.08..11189.88 rows=1 width=4) (actual time=1723760.031..1723760.032 rows=1 loops=1)
   ->  Nested Loop Semi Join  (cost=9483.08..187457275.03 rows=109824 width=4) (actual time=1723760.028..1723760.028
rows=1loops=1) 
         Join Filter: (compteur_date.ref_id_trafic = trafic.id)
         Rows Removed by Join Filter: 3141947978
         ->  Index Only Scan Backward using ref_id_trafic_indexdate on compteur_date  (cost=0.43..599583.31
rows=14301431width=4) (actual time=0.012..5684.002 rows=3657681 loops=1) 
               Heap Fetches: 3657681
         ->  Materialize  (cost=9482.65..9497.89 rows=871 width=4) (actual time=0.000..0.170 rows=859 loops=3657681)
               ->  Sort  (cost=9482.65..9484.82 rows=871 width=4) (actual time=254.402..254.534 rows=859 loops=1)
                     Sort Key: trafic.id
                     Sort Method: quicksort  Memory: 65kB
                     ->  Seq Scan on trafic  (cost=0.00..9440.11 rows=871 width=4) (actual time=0.026..253.805 rows=859
loops=1)
                           Filter: (ref_id_materiel = 15)
                           Rows Removed by Filter: 526330
 Planning time: 0.627 ms
 Execution time: 1723760.361 ms
(15 rows)

it seems that the double nesting ORDER BY is not properly managed
On the old Postgresql 8.3 no problem detected

Many greetings
Marc Hamelin