Does EXPLAIN ANALYZE show a wrong plan for MIN/MAX? - Mailing list pgsql-hackers

Hi,

I've noticed that sometimes EXPLAIN ANALYZE is much slower than the 
plain query. After investigating I found that it happens when using MIN 
or MAX aggregates.

It seems that the plan outputted is not the optimized one (available 
since 8.1) that is really used when running the plain query.


I.e. this is about 14 times slower:


db=> SELECT min(t_stamp) FROM stats;          min
------------------------ 2005-01-14 17:43:59+01
(1 row)

Time: 2206.841 ms      ========

db=> EXPLAIN ANALYZE SELECT min(t_stamp) FROM stats;
QUERYPLAN
 

-----------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=65461.73..65461.74 rows=1 width=8) (actual 
 
time=30692.485..30692.488 rows=1 loops=1)   ->  Append  (cost=0.00..59648.38 rows=2325338 width=8) (actual 
time=0.043..22841.814 rows=2325018 loops=1)         ->  Seq Scan on stats  (cost=0.00..13.20 rows=320 width=8) 
(actual time=0.004..0.004 rows=0 loops=1)         ->  Seq Scan on stats_200501 stats  (cost=0.00..1.30 rows=30 
width=8) (actual time=0.030..0.132 rows=30 loops=1)         ->  Seq Scan on stats_200502 stats  (cost=0.00..117.81 
rows=4581 width=8) (actual time=0.055..16.635 rows=4581 loops=1)         ->  Seq Scan on stats_200503 stats
(cost=0.00..333.05
 
rows=12905 width=8) (actual time=0.108..46.866 rows=12905 loops=1)         ->  Seq Scan on stats_200504 stats
(cost=0.00..805.40
 
rows=31140 width=8) (actual time=0.212..113.868 rows=31140 loops=1)         ->  Seq Scan on stats_200505 stats
(cost=0.00..5432.80
 
rows=211580 width=8) (actual time=1.394..767.939 rows=211580 loops=1)         ->  Seq Scan on stats_200506 stats
(cost=0.00..9533.68
 
rows=371768 width=8) (actual time=2.870..1352.216 rows=371768 loops=1)         ->  Seq Scan on stats_200507 stats
(cost=0.00..9467.76
 
rows=369176 width=8) (actual time=2.761..1348.064 rows=369176 loops=1)         ->  Seq Scan on stats_200508 stats
(cost=0.00..6023.04
 
rows=234804 width=8) (actual time=1.537..853.712 rows=234804 loops=1)         ->  Seq Scan on stats_200509 stats
(cost=0.00..11600.68
 
rows=452568 width=8) (actual time=3.608..1644.433 rows=452568 loops=1)         ->  Seq Scan on stats_200510 stats
(cost=0.00..16318.62
 
rows=636462 width=8) (actual time=5.367..2329.015 rows=636462 loops=1)         ->  Seq Scan on stats_200511 stats
(cost=0.00..1.04rows=4 
 
width=8) (actual time=0.028..0.041 rows=4 loops=1) Total runtime: 30692.627 ms
(15 rows)

Time: 30694.357 ms      =========


Best regards
--
Matteo Beccati
http://phpadsnew.com
http://phppgads.com