more voodoo planner bs :)

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(at)backpack(dot)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!"
-----------------------------------------------------------------------------