Thread

1. more voodoo planner bs :)

   D. Duccini <duccini@backpack.com> — 2002-05-08T00:31:03Z

   Can anyone tell me why the planner is penalizing me for changing ONE
   value?
   
   explain SELECT l.idsubaccount,1 as
   status,l.type,l.date,l.units,l.rate,l.total,l.text
   FROM lineitemsnext l, accounts a, subaccounts s
   WHERE
       a.billdate = 1
   and a.id = s.idaccount
   and s.id = l.idsubaccount
   and l.text <> ''
   and l.date <= '2002-05-30'
   
   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
   
   here is what i get if a.billdate = 1
   
   Hash Join  (cost=46.55..241.82 rows=242 width=48)
     ->  Hash Join  (cost=11.49..185.31 rows=242 width=44)
           ->  Seq Scan on subaccounts s  (cost=0.00..57.36 rows=2836 width=8)
           ->  Hash  (cost=10.88..10.88 rows=242 width=36)
                 ->  Seq Scan on lineitemsnext l  (cost=0.00..10.88 rows=242 width=36)
     ->  Hash  (cost=24.76..24.76 rows=940 width=4)
           ->  Seq Scan on accounts a  (cost=0.00..24.76 rows=940 width=4)
   
   EXPLAIN
   
   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
   
   here is what i get if a.billdate = 2
   
   Nested Loop  (cost=0.00..123.69 rows=1 width=48)
     ->  Nested Loop  (cost=0.00..117.57 rows=3 width=12)
           ->  Seq Scan on accounts a  (cost=0.00..24.76 rows=1 width=4)
           ->  Seq Scan on subaccounts s  (cost=0.00..57.36 rows=2836 width=8)
     ->  Index Scan using idxlineitemsnextbysub on lineitemsnext l (cost=0.00..2.02 rows=1 width=36)
   
   EXPLAIN
   
   
   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
   
   when billdate = 1, its blazing fast
   
   when its any other value, it never really completes (ie, i stop it because
   its taking too long)
   
   
   
   -----------------------------------------------------------------------------
   david@backpack.com            BackPack Software, Inc.        www.backpack.com
   +1 651.645.7550 voice       "Life is an Adventure.    
   +1 651.645.9798 fax            Don't forget your BackPack!"   
   -----------------------------------------------------------------------------
   
   
   

2. voodoo planner update

   D. Duccini <duccini@backpack.com> — 2002-05-08T01:27:46Z

   Here's where things get interesting:
   
   now things have flipped around!
   
   billdate = 1 is SLOWER than billdate = any other value
   
   # explain select s.id from subaccounts s, accounts a where a.id = s.idaccount and a.billdate = 2;
   NOTICE:  QUERY PLAN:
   
   Nested Loop  (cost=0.00..16.42 rows=2 width=12)
     ->  Index Scan using idxaccountbilldate on accounts a  (cost=0.00..2.02 rows=1 width=4)
     ->  Index Scan using idxsubprimary on subaccounts s  (cost=0.00..14.22 rows=15 width=8)
   
   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
   
   # explain select s.id from subaccounts s, accounts a where a.id = s.idaccount and a.billdate = 1;
   NOTICE:  QUERY PLAN:
   
   Hash Join  (cost=79.83..310.33 rows=3506 width=12)
     ->  Seq Scan on subaccounts s  (cost=0.00..71.10 rows=3510 width=8)
     ->  Hash  (cost=76.03..76.03 rows=1520 width=4)
           ->  Seq Scan on accounts a  (cost=0.00..76.03 rows=1520 width=4)
   
   
   
   
   
   -----------------------------------------------------------------------------
   david@backpack.com            BackPack Software, Inc.        www.backpack.com
   +1 651.645.7550 voice       "Life is an Adventure.    
   +1 651.645.9798 fax            Don't forget your BackPack!"   
   -----------------------------------------------------------------------------
   
   
   

3. Re: more voodoo planner bs :)

   Tom Lane <tgl@sss.pgh.pa.us> — 2002-05-08T04:44:18Z

   "D. Duccini" <duccini@backpack.com> writes:
   > Can anyone tell me why the planner is penalizing me for changing ONE
   > value?
   
   I'm guessing that one or the other value matches the "most common value"
   recorded in pg_statistic?
   
   I'm also betting you are not on 7.2.  Older versions have coarser
   statistics that are more likely to make foolish decisions for
   not-quite-the-most-common values ...
   
   			regards, tom lane