Re: Huge Performance Difference on Similar Query in Pg7.2

Luis Alberto Amigo Navarro writes:
> can you send "explain analyze" instead of "explain" results in order to view
> real time?
> Regards
>
Here it is:
1)
emep=> explain analyze select count(*) from EmissionsView , DataSetsView where DataSetsView.setid = EmissionsView.setid and EmissionsView.setid = '4614' ;
NOTICE: QUERY PLAN:

Aggregate (cost=5975.66..5975.66 rows=1 width=131) (actual time=1264.12..1264.13 rows=1 loops=1)
-> Hash Join (cost=5958.18..5975.58 rows=35 width=131) (actual time=1205.90..1262.46 rows=1606 loops=1)
-> Hash Join (cost=5956.30..5972.99 rows=35 width=127) (actual time=1197.85..1248.93 rows=1606 loops=1)
-> Hash Join (cost=5954.41..5970.40 rows=35 width=123) (actual time=1197.50..1224.92 rows=1606 loops=1)
-> Seq Scan on reports (cost=0.00..11.78 rows=378 width=8) (actual time=0.02..1.03 rows=378 loops=1)
-> Hash (cost=5954.30..5954.30 rows=42 width=115) (actual time=1193.55..1193.55 rows=0 loops=1)
-> Nested Loop (cost=4375.32..5954.30 rows=42 width=115) (actual time=1121.44..1188.23 rows=1606 loops=1)
-> Nested Loop (cost=4375.32..5812.95 rows=42 width=103) (actual time=1115.41..1134.58 rows=1606 loops=1)
-> Seq Scan on footnotes (cost=0.00..1.01 rows=1 width=4) (actual time=0.01..0.02 rows=1 loops=1)
-> Materialize (cost=5811.31..5811.31 rows=42 width=99) (actual time=1115.36..1116.33 rows=1606 loops=1)
-> Hash Join (cost=4375.32..5811.31 rows=42 width=99) (actual time=1047.90..1112.31 rows=1606 loops=1)
-> Hash Join (cost=4371.95..5796.47 rows=307 width=77) (actual time=1042.42..1076.85 rows=1606 loops=1)
-> Merge Join (cost=4370.73..5789.88 rows=307 width=50) (actual time=1039.84..1059.42 rows=1606 loops=1)
-> Sort (cost=4370.73..4370.73 rows=28192 width=38) (actual time=996.25..1001.69 rows=3830 loops=1)
-> Hash Join (cost=24.06..1834.10 rows=28192 width=38) (actual time=7.50..507.66 rows=33593 loops=1)
-> Hash Join (cost=22.17..1268.37 rows=28192 width=34) (actual time=7.15..303.78 rows=33593 loops=1)
-> Seq Scan on datasets (cost=0.00..724.86 rows=33786 width=22) (actual time=0.36..122.78 rows=33786 loops=1)
-> Hash (cost=21.23..21.23 rows=378 width=12) (actual time=6.73..6.73 rows=0 loops=1)
-> Hash Join (cost=1.89..21.23 rows=378 width=12) (actual time=3.17..6.03 rows=377 loops=1)
-> Seq Scan on reports (cost=0.00..11.78 rows=378 width=8) (actual time=2.83..4.05 rows=378 loops=1)
-> Hash (cost=1.71..1.71 rows=71 width=4) (actual time=0.26..0.26 rows=0 loops=1)
-> Seq Scan on areas (cost=0.00..1.71 rows=71 width=4) (actual time=0.01..0.14 rows=71 loops=1)
-> Hash (cost=1.71..1.71 rows=71 width=4) (actual time=0.27..0.27 rows=0 loops=1)
-> Seq Scan on areas (cost=0.00..1.71 rows=71 width=4) (actual time=0.01..0.15 rows=71 loops=1)
-> Index Scan using lhsecgriemis_setid_idx on emissions (cost=0.00..1343.91 rows=368 width=12) (actual time=33.77..42.61 rows=1606 loops=1)
-> Hash (cost=1.18..1.18 rows=18 width=27) (actual time=2.49..2.49 rows=0 loops=1)
-> Seq Scan on datatypes (cost=0.00..1.18 rows=18 width=27) (actual time=2.40..2.44 rows=18 loops=1)
-> Hash (cost=3.09..3.09 rows=109 width=22) (actual time=5.41..5.41 rows=0 loops=1)
-> Seq Scan on sectordefinitions (cost=0.00..3.09 rows=109 width=22) (actual time=4.89..5.19 rows=109 loops=1)
-> Index Scan using datasets_setid_idx on datasets (cost=0.00..3.37 rows=1 width=12) (actual time=0.02..0.02 rows=1 loops=1606)
-> Hash (cost=1.71..1.71 rows=71 width=4) (actual time=0.25..0.25 rows=0 loops=1)
-> Seq Scan on areas (cost=0.00..1.71 rows=71 width=4) (actual time=0.01..0.13 rows=71 loops=1)
-> Hash (cost=1.71..1.71 rows=71 width=4) (actual time=7.95..7.95 rows=0 loops=1)
-> Seq Scan on areas (cost=0.00..1.71 rows=71 width=4) (actual time=7.69..7.83 rows=71 loops=1)
Total runtime: 1361.51 msec

EXPLAIN

2)
emep=> explain analyze select count(*) from EmissionsView , DataSetsView where DataSetsView.setid = EmissionsView.setid and DataSetsView.setid = '4614' ;
NOTICE: QUERY PLAN:

Aggregate (cost=91660.36..91660.36 rows=1 width=131) (actual time=64414.80..64414.80 rows=1 loops=1)
-> Hash Join (cost=2732.23..91660.35 rows=3 width=131) (actual time=58428.47..64413.14 rows=1606 loops=1)
-> Nested Loop (cost=2719.53..91058.61 rows=117798 width=103) (actual time=49523.50..63005.67 rows=1025405 loops=1)
-> Seq Scan on footnotes (cost=0.00..1.01 rows=1 width=4) (actual time=0.01..0.03 rows=1 loops=1)
-> Materialize (cost=89290.63..89290.63 rows=117798 width=99) (actual time=49523.43..51974.76 rows=1025405 loops=1)
-> Hash Join (cost=2719.53..89290.63 rows=117798 width=99) (actual time=1619.56..47188.00 rows=1025405 loops=1)
-> Hash Join (cost=2716.17..56957.45 rows=863754 width=77) (actual time=1617.06..27358.00 rows=1035128 loops=1)
-> Seq Scan on emissions (cost=0.00..18502.34 rows=1035134 width=12) (actual time=6.42..5027.94 rows=1035134 loops=1)
-> Hash (cost=2328.69..2328.69 rows=28192 width=65) (actual time=848.29..848.29 rows=0 loops=1)
-> Hash Join (cost=25.29..2328.69 rows=28192 width=65) (actual time=12.33..762.21 rows=33593 loops=1)
-> Hash Join (cost=24.06..1834.10 rows=28192 width=38) (actual time=10.57..519.08 rows=33593 loops=1)
-> Hash Join (cost=22.17..1268.37 rows=28192 width=34) (actual time=10.22..315.37 rows=33593 loops=1)
-> Seq Scan on datasets (cost=0.00..724.86 rows=33786 width=22) (actual time=0.44..129.13 rows=33786 loops=1)
-> Hash (cost=21.23..21.23 rows=378 width=12) (actual time=9.71..9.71 rows=0 loops=1)
-> Hash Join (cost=1.89..21.23 rows=378 width=12) (actual time=5.23..8.98 rows=377 loops=1)
-> Seq Scan on reports (cost=0.00..11.78 rows=378 width=8) (actual time=4.90..7.04 rows=378 loops=1)
-> Hash (cost=1.71..1.71 rows=71 width=4) (actual time=0.26..0.26 rows=0 loops=1)
-> Seq Scan on areas (cost=0.00..1.71 rows=71 width=4) (actual time=0.01..0.14 rows=71 loops=1)
-> Hash (cost=1.71..1.71 rows=71 width=4) (actual time=0.27..0.27 rows=0 loops=1)
-> Seq Scan on areas (cost=0.00..1.71 rows=71 width=4) (actual time=0.01..0.15 rows=71 loops=1)
-> Hash (cost=1.18..1.18 rows=18 width=27) (actual time=1.69..1.69 rows=0 loops=1)
-> Seq Scan on datatypes (cost=0.00..1.18 rows=18 width=27) (actual time=1.60..1.64 rows=18 loops=1)
-> Hash (cost=3.09..3.09 rows=109 width=22) (actual time=2.44..2.44 rows=0 loops=1)
-> Seq Scan on sectordefinitions (cost=0.00..3.09 rows=109 width=22) (actual time=1.91..2.20 rows=109 loops=1)
-> Hash (cost=12.70..12.70 rows=1 width=28) (actual time=25.72..25.72 rows=0 loops=1)
-> Hash Join (cost=10.62..12.70 rows=1 width=28) (actual time=25.53..25.71 rows=1 loops=1)
-> Seq Scan on areas (cost=0.00..1.71 rows=71 width=4) (actual time=0.01..0.14 rows=71 loops=1)
-> Hash (cost=10.62..10.62 rows=1 width=24) (actual time=25.44..25.44 rows=0 loops=1)
-> Nested Loop (cost=0.00..10.62 rows=1 width=24) (actual time=25.24..25.43 rows=1 loops=1)
-> Nested Loop (cost=0.00..5.96 rows=1 width=16) (actual time=21.79..21.98 rows=1 loops=1)
-> Index Scan using datasets_setid_idx on datasets (cost=0.00..3.37 rows=1 width=12) (actual time=15.48..15.48 rows=1 loops=1)
-> Seq Scan on areas (cost=0.00..1.71 rows=71 width=4) (actual time=6.23..6.38 rows=71 loops=1)
-> Index Scan using reports_pkey on reports (cost=0.00..4.64 rows=1 width=8) (actual time=3.44..3.44 rows=1 loops=1)
Total runtime: 64568.82 msec

EXPLAIN