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
