Thread: 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@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!"
-----------------------------------------------------------------------------
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!"
-----------------------------------------------------------------------------
"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