Optimizer Parameters - Mailing list pgsql-novice

Upon testing queries with EXPLAIN ANALYSE, I started to notice that the
planner would avoid using indexes when available.   Instead it would
jump to sequence scans, ignoring the index and increasing overall time
it took to get results.

I have been looking up documentation and noticed that you can somewhat
force Postgres into using Indexes when available.    So I changed the
following two lines in the .conf file:

    enable_seqscan = false
    enable_nestloop = false

This was recommended in the documentation, and to say the least things
have really changed in performance.   Queries have halved the time
needed to execute even if the estimates are insanely high compared.

I also increased this value, which apparently helps when running ANALYSE
on tables:
    default_statistics_target = 1000

Now how sane is it to keep those options turned off?   And what side
effects can I expect from changing default_statistics_target?   And any
way to have the planner quiet guessing tens of thousands of rows will be
return when there are at most hundred?

I included the EXPLAIN ALALYSE results in an attachment to maintain
formatting of the output. Thanks in advance!

    Martin Foster
    Creator/Designer Ethereal Realms
    martin@ethereal-realms.org

EXPLAIN ANALYSE SELECT
    Po.PuppetName     AS PuppetName,
    Po.PuppeteerLogin AS PuppeteerLogin,
    Po.RealmName      AS RealmName,
    Re.RealmPublic    AS RealmPublic,
    Re.RealmVerified  AS RealmVerified
  FROM ethereal.Post Po, ethereal.Puppet Ch, ethereal.Realm Re
  WHERE Po.PuppeteerLogin = Ch.PuppeteerLogin
    AND Po.RealmName = Re.RealmName
    AND Po.PostTimestamp > (LOCALTIMESTAMP - INTERVAL '10 minutes')
    AND Po.PuppetName IS NOT NULL
  GROUP BY Po.PuppeteerLogin, Po.PuppetName, Po.RealmName, Re.RealmPublic, Re.RealmVerified;

 -- Pre changes
 Group  (cost=298025.66..322310.42 rows=161898 width=77) (actual time=4241.28..4329.68 rows=74 loops=1)
   ->  Sort  (cost=298025.66..302073.12 rows=1618985 width=77) (actual time=4241.23..4253.68 rows=14420 loops=1)
         Sort Key: po.puppeteerlogin, po.puppetname, po.realmname, re.realmpublic, re.realmverified
         ->  Merge Join  (cost=24174.23..44794.94 rows=1618985 width=77) (actual time=3199.66..3891.09 rows=14420
loops=1)
               Merge Cond: ("outer".puppeteerlogin = "inner".puppeteerlogin)
               ->  Sort  (cost=5964.67..6056.93 rows=36906 width=10) (actual time=366.18..427.94 rows=36318 loops=1)
                     Sort Key: ch.puppeteerlogin
                     ->  Seq Scan on puppet ch  (cost=0.00..3165.06 rows=36906 width=10) (actual time=0.02..133.52
rows=36456loops=1) 
               ->  Sort  (cost=18209.57..18310.85 rows=40513 width=67) (actual time=2832.70..2945.77 rows=14411
loops=1)
                     Sort Key: po.puppeteerlogin
                     ->  Hash Join  (cost=30.30..15109.11 rows=40513 width=67) (actual time=2822.23..2830.93 rows=392
loops=1)
                           Hash Cond: ("outer".realmname = "inner".realmname)
                           ->  Seq Scan on post po  (cost=0.00..14369.84 rows=40513 width=41) (actual
time=2820.88..2826.30rows=392 loops=1) 
                                 Filter: ((posttimestamp > (('now'::text)::timestamp(6) without time zone -
'00:10'::interval))AND (puppetname IS NOT NULL)) 
                           ->  Hash  (cost=29.84..29.84 rows=184 width=26) (actual time=1.25..1.25 rows=0 loops=1)
                                 ->  Seq Scan on realm re  (cost=0.00..29.84 rows=184 width=26) (actual time=0.02..0.90
rows=179loops=1) 
 Total runtime: 4419.10 msec
(17 rows)

 -- Post changes
 Group  (cost=100293106.79..100316406.96 rows=155334 width=77) (actual time=1029.10..1159.25 rows=99 loops=1)
   ->  Sort  (cost=100293106.79..100296990.15 rows=1553344 width=77) (actual time=1025.98..1047.32 rows=24730 loops=1)
         Sort Key: po.puppeteerlogin, po.puppetname, po.realmname, re.realmpublic, re.realmverified
         ->  Merge Join  (cost=100031106.45..100050913.48 rows=1553344 width=77) (actual time=453.60..839.30 rows=24730
loops=1)
               Merge Cond: ("outer".puppeteerlogin = "inner".puppeteerlogin)
               ->  Sort  (cost=25124.25..25229.58 rows=42133 width=67) (actual time=17.07..17.67 rows=631 loops=1)
                     Sort Key: po.puppeteerlogin
                     ->  Hash Join  (cost=120.06..21887.85 rows=42133 width=67) (actual time=2.32..14.25 rows=631
loops=1)
                           Hash Cond: ("outer".realmname = "inner".realmname)
                           ->  Index Scan using idxpost_timestamp on post po  (cost=0.00..21030.46 rows=42133 width=41)
(actualtime=0.15..7.05 rows=631 loops=1) 
                                 Index Cond: (posttimestamp > (('now'::text)::timestamp(6) without time zone -
'00:10'::interval))
                                 Filter: (puppetname IS NOT NULL)
                           ->  Hash  (cost=119.61..119.61 rows=181 width=26) (actual time=2.01..2.01 rows=0 loops=1)
                                 ->  Index Scan using pkrealm on realm re  (cost=0.00..119.61 rows=181 width=26)
(actualtime=0.05..1.55 rows=181 loops=1) 
               ->  Sort  (cost=100005982.20..100006073.27 rows=36428 width=10) (actual time=435.39..506.81 rows=58859
loops=1)
                     Sort Key: ch.puppeteerlogin
                     ->  Seq Scan on puppet ch  (cost=100000000.00..100003222.28 rows=36428 width=10) (actual
time=0.03..184.55rows=36428 loops=1) 
 Total runtime: 1175.33 msec
(21 rows)


EXPLAIN ANALYSE SELECT
    Po.PuppetName     AS PuppetName,
    Po.PuppeteerLogin AS PuppeteerLogin,
    Po.RealmName      AS RealmName,
    Re.RealmPublic    AS RealmPublic,
    Re.RealmVerified  AS RealmVerified
  FROM ethereal.Post Po, ethereal.Puppet Ch, ethereal.Realm Re
  WHERE Po.PostIDNumber > (SELECT MIN(PostIDNumber)
       FROM ethereal.Post
       WHERE PostTimeStamp > (LOCALTIMESTAMP - INTERVAL '10 minutes'))
    AND Po.PuppeteerLogin = Ch.PuppeteerLogin
    AND Po.RealmName = Re.RealmName
    AND Po.PuppetName IS NOT NULL
  GROUP BY Po.PuppeteerLogin, Po.PuppetName, Po.RealmName, Re.RealmPublic, Re.RealmVerified;

 -- Pre changes
 Group  (cost=297096.47..321381.23 rows=161898 width=77) (actual time=2108.70..2179.12 rows=76 loops=1)
   InitPlan
     ->  Aggregate  (cost=14473.08..14473.08 rows=1 width=4) (actual time=1141.58..1141.58 rows=1 loops=1)
           ->  Seq Scan on post  (cost=0.00..14369.84 rows=41297 width=4) (actual time=1136.50..1140.95 rows=413
loops=1)
                 Filter: (posttimestamp > (('now'::text)::timestamp(6) without time zone - '00:10'::interval))
   ->  Sort  (cost=297096.47..301143.93 rows=1618985 width=77) (actual time=2108.55..2121.85 rows=15302 loops=1)
         Sort Key: po.puppeteerlogin, po.puppetname, po.realmname, re.realmpublic, re.realmverified
         ->  Merge Join  (cost=23245.04..43865.75 rows=1618985 width=77) (actual time=1782.43..2003.58 rows=15302
loops=1)
               Merge Cond: ("outer".puppeteerlogin = "inner".puppeteerlogin)
               ->  Sort  (cost=5964.67..6056.93 rows=36906 width=10) (actual time=415.56..449.62 rows=36318 loops=1)
                     Sort Key: ch.puppeteerlogin
                     ->  Seq Scan on puppet ch  (cost=0.00..3165.06 rows=36906 width=10) (actual time=0.02..135.58
rows=36456loops=1) 
               ->  Sort  (cost=17280.38..17381.66 rows=40513 width=67) (actual time=1362.15..1376.53 rows=15293
loops=1)
                     Sort Key: po.puppeteerlogin
                     ->  Hash Join  (cost=30.30..14179.92 rows=40513 width=67) (actual time=1354.41..1360.42 rows=411
loops=1)
                           Hash Cond: ("outer".realmname = "inner".realmname)
                           ->  Seq Scan on post po  (cost=0.00..13440.65 rows=40513 width=41) (actual
time=1353.11..1355.96rows=411 loops=1) 
                                 Filter: ((postidnumber > $0) AND (puppetname IS NOT NULL))
                           ->  Hash  (cost=29.84..29.84 rows=184 width=26) (actual time=1.20..1.20 rows=0 loops=1)
                                 ->  Seq Scan on realm re  (cost=0.00..29.84 rows=184 width=26) (actual time=0.02..0.88
rows=179loops=1) 
 Total runtime: 2192.72 msec
(21 rows)

 -- Post changes
 Group  (cost=100292759.45..100316059.61 rows=155334 width=77) (actual time=876.30..969.77 rows=84 loops=1)
   InitPlan
     ->  Aggregate  (cost=21137.57..21137.57 rows=1 width=4) (actual time=3.98..3.98 rows=1 loops=1)
           ->  Index Scan using idxpost_timestamp on post  (cost=0.00..21030.46 rows=42846 width=4) (actual
time=0.14..3.26rows=538 loops=1) 
                 Index Cond: (posttimestamp > (('now'::text)::timestamp(6) without time zone - '00:10'::interval))
   ->  Sort  (cost=100292759.45..100296642.81 rows=1553344 width=77) (actual time=875.32..893.11 rows=20378 loops=1)
         Sort Key: po.puppeteerlogin, po.puppetname, po.realmname, re.realmpublic, re.realmverified
         ->  Merge Join  (cost=100030759.11..100050566.13 rows=1553344 width=77) (actual time=436.22..708.88 rows=20378
loops=1)
               Merge Cond: ("outer".puppeteerlogin = "inner".puppeteerlogin)
               ->  Sort  (cost=24776.90..24882.24 rows=42133 width=67) (actual time=26.54..27.01 rows=524 loops=1)
                     Sort Key: po.puppeteerlogin
                     ->  Hash Join  (cost=120.06..21540.50 rows=42133 width=67) (actual time=5.93..24.30 rows=524
loops=1)
                           Hash Cond: ("outer".realmname = "inner".realmname)
                           ->  Index Scan using pkpost on post po  (cost=0.00..20683.11 rows=42133 width=41) (actual
time=4.08..17.10rows=524 loops=1) 
                                 Index Cond: (postidnumber > $0)
                                 Filter: (puppetname IS NOT NULL)
                           ->  Hash  (cost=119.61..119.61 rows=181 width=26) (actual time=1.68..1.68 rows=0 loops=1)
                                 ->  Index Scan using pkrealm on realm re  (cost=0.00..119.61 rows=181 width=26)
(actualtime=0.04..1.28 rows=181 loops=1) 
               ->  Sort  (cost=100005982.20..100006073.27 rows=36428 width=10) (actual time=408.56..456.91 rows=54202
loops=1)
                     Sort Key: ch.puppeteerlogin
                     ->  Seq Scan on puppet ch  (cost=100000000.00..100003222.28 rows=36428 width=10) (actual
time=0.01..162.22rows=36428 loops=1) 
 Total runtime: 984.75 msec
(22 rows)