Re: Huge Performance Difference on Similar Query in Pg7.2 - Mailing list pgsql-general

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=1025405loops=1) 
              ->  Seq Scan on footnotes  (cost=100000000.00..100000001.01 rows=1 width=4) (actual time=0.01..0.03
rows=1loops=1) 
              ->  Materialize  (cost=3438258.19..3438258.19 rows=119480 width=99) (actual time=46157.41..48845.75
rows=1025405loops=1) 
                    ->  Hash Join  (cost=5403.12..3438258.19 rows=119480 width=99) (actual time=1344.17..43964.04
rows=1025405loops=1) 
                          ->  Merge Join  (cost=5392.13..3405513.72 rows=871449 width=77) (actual
time=1336.38..23857.31rows=1035128 loops=1) 
                                ->  Sort  (cost=5392.13..5392.13 rows=28443 width=65) (actual time=1323.65..1383.10
rows=31471loops=1) 
                                      ->  Hash Join  (cost=54.91..2615.14 rows=28443 width=65) (actual
time=21.62..901.24rows=33593 loops=1) 
                                            ->  Hash Join  (cost=50.15..2112.63 rows=28443 width=38) (actual
time=11.27..646.34rows=33593 loops=1) 
                                                  ->  Hash Join  (cost=43.48..1537.09 rows=28443 width=34) (actual
time=10.80..443.36rows=33593 loops=1) 
                                                        ->  Index Scan using datasets_setid_idx on datasets
(cost=0.00..969.14rows=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.08rows=0 loops=1) 
                                                              ->  Hash Join  (cost=6.68..42.53 rows=378 width=12)
(actualtime=0.53..9.39 rows=377 loops=1) 
                                                                    ->  Index Scan using reports_pkey on reports
(cost=0.00..28.29rows=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.38rows=0 loops=1) 
                                                                          ->  Index Scan using areas_pkey on areas
(cost=0.00..6.50rows=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=0loops=1) 
                                                        ->  Index Scan using areas_pkey on areas  (cost=0.00..6.50
rows=71width=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=0loops=1) 
                                                  ->  Index Scan using datatypes_pkey on datatypes  (cost=0.00..4.71
rows=18width=27) (actual time=10.15..10.23 rows=18 loops=1) 
                                ->  Index Scan using lhsecgriemis_setid_idx on lowhisectorgridemissions
(cost=0.00..3386569.53rows=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=109width=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)
(actualtime=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.41rows=1 loops=1) 
                          ->  Index Scan using areas_pkey on areas  (cost=0.00..4.52 rows=1 width=4) (actual
time=15.21..15.21rows=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=1loops=1) 
Total runtime: 61309.75 msec

EXPLAIN