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