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;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
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.04 rows=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
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
