more voodoo planner bs :) - Mailing list pgsql-novice

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!"
-----------------------------------------------------------------------------