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

Luis Alberto Amigo Navarro writes:
> first of all the difference between expected times and real times may be
> reduced tuning your postgresql.conf
> try to set enable_seqscan to off and then re explain analyze to see if there
> is another possible solution
> Regards

About the times, how do I adjust those? Does it really makes such a huge
difference or is it just for better display.

I set enable_seqscan to off, and it changed to the Seq scan on emissions
to a index scan on emissions, but time didn't change, still about a
factor 50 between both queries:

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

Aggregate (cost=103440665.94..103440665.94 rows=1 width=131) (actual time=61151.81..61151.81 rows=1 loops=1)
-> Hash Join (cost=100005420.21..103440665.93 rows=4 width=131) (actual time=49892.37..61150.12 rows=1606 loops=1)
-> Nested Loop (cost=100005403.12..103440051.40 rows=119480 width=103) (actual time=46157.46..59840.49 rows=1025405 loops=1)
-> Seq Scan on footnotes (cost=100000000.00..100000001.01 rows=1 width=4) (actual time=0.01..0.03 rows=1 loops=1)
-> Materialize (cost=3438258.19..3438258.19 rows=119480 width=99) (actual time=46157.41..48845.75 rows=1025405 loops=1)
-> Hash Join (cost=5403.12..3438258.19 rows=119480 width=99) (actual time=1344.17..43964.04 rows=1025405 loops=1)
-> Merge Join (cost=5392.13..3405513.72 rows=871449 width=77) (actual time=1336.38..23857.31 rows=1035128 loops=1)
-> Sort (cost=5392.13..5392.13 rows=28443 width=65) (actual time=1323.65..1383.10 rows=31471 loops=1)
-> Hash Join (cost=54.91..2615.14 rows=28443 width=65) (actual time=21.62..901.24 rows=33593 loops=1)
-> Hash Join (cost=50.15..2112.63 rows=28443 width=38) (actual time=11.27..646.34 rows=33593 loops=1)
-> Hash Join (cost=43.48..1537.09 rows=28443 width=34) (actual time=10.80..443.36 rows=33593 loops=1)
-> Index Scan using datasets_setid_idx on datasets (cost=0.00..969.14 rows=33786 width=22) (actual time=0.64..249.20 rows=33786 loops=1)
-> Hash (cost=42.53..42.53 rows=378 width=12) (actual time=10.08..10.08 rows=0 loops=1)
-> Hash Join (cost=6.68..42.53 rows=378 width=12) (actual time=0.53..9.39 rows=377 loops=1)
-> Index Scan using reports_pkey on reports (cost=0.00..28.29 rows=378 width=8) (actual time=0.07..7.25 rows=378 loops=1)
-> Hash (cost=6.50..6.50 rows=71 width=4) (actual time=0.38..0.38 rows=0 loops=1)
-> Index Scan using areas_pkey on areas (cost=0.00..6.50 rows=71 width=4) (actual time=0.01..0.24 rows=71 loops=1)
-> Hash (cost=6.50..6.50 rows=71 width=4) (actual time=0.40..0.40 rows=0 loops=1)
-> Index Scan using areas_pkey on areas (cost=0.00..6.50 rows=71 width=4) (actual time=0.02..0.27 rows=71 loops=1)
-> Hash (cost=4.71..4.71 rows=18 width=27) (actual time=10.26..10.26 rows=0 loops=1)
-> Index Scan using datatypes_pkey on datatypes (cost=0.00..4.71 rows=18 width=27) (actual time=10.15..10.23 rows=18 loops=1)
-> Index Scan using lhsecgriemis_setid_idx on lowhisectorgridemissions (cost=0.00..3386569.53 rows=1035134 width=12) (actual time=12.67..13841.30 rows=1035134 loops=1)
-> Hash (cost=10.72..10.72 rows=109 width=22) (actual time=7.68..7.68 rows=0 loops=1)
-> Index Scan using sectordefinitions_pkey on sectordefinitions (cost=0.00..10.72 rows=109 width=22) (actual time=6.74..7.46 rows=109 loops=1)
-> Hash (cost=17.09..17.09 rows=1 width=28) (actual time=27.32..27.32 rows=0 loops=1)
-> Nested Loop (cost=0.00..17.09 rows=1 width=28) (actual time=27.29..27.31 rows=1 loops=1)
-> Nested Loop (cost=0.00..12.55 rows=1 width=24) (actual time=27.26..27.28 rows=1 loops=1)
-> Nested Loop (cost=0.00..8.01 rows=1 width=20) (actual time=12.04..12.05 rows=1 loops=1)
-> Index Scan using datasets_setid_idx on datasets (cost=0.00..3.36 rows=1 width=12) (actual time=4.61..4.62 rows=1 loops=1)
-> Index Scan using reports_pkey on reports (cost=0.00..4.64 rows=1 width=8) (actual time=7.41..7.41 rows=1 loops=1)
-> Index Scan using areas_pkey on areas (cost=0.00..4.52 rows=1 width=4) (actual time=15.21..15.21 rows=1 loops=1)
-> Index Scan using areas_pkey on areas (cost=0.00..4.52 rows=1 width=4) (actual time=0.02..0.02 rows=1 loops=1)
Total runtime: 61309.75 msec

EXPLAIN