From: | Clifford Wolf <clifford(dot)wolf(at)linbit(dot)com> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: Bug in query planer ? |
Date: | 2006-02-01 16:44:10 |
Message-ID: | 200602011744.10897.clifford.wolf@linbit.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
Hi,
On Tuesday 31 January 2006 18:59, you wrote:
> Shouldn't the query planner be able to do the right thing without the
> index? Where does the magic 'rows=459' come from?
ok - I've spend some time reading the postgres sources now. qesel() is using
a selectivity of DEFAULT_EQ_SEL (0.005) for all expressions with functions.
Since our query has three such equals AND'ed this gives a selectivity of
0.000000125 instead of 0.9. That's causing postgres to create a query plan
which runs aprox. 8 hours instead of less then a second.
I've now created a combined expression index for my case so the query planner
can check the selectivity. This is a huge overkill and there is a lot of
space for improvements..
As a last resort for such cases it would be good to be able to hardcode
selectivities in the SQL statements. Something like:
SELECT ...
FROM ...
WHERE con.ccu_objid IN (...)
AND cel.isActiv = 'Y'
AND ( upper(coalesce(dev.isActiv,'Y')) = 'Y'
AND upper(coalesce(dev.IsCommittedSP,'Y')) = 'Y'
AND upper(coalesce(dev.IsCommittedCust,'Y')) = 'Y'
) WITH SELECTIVITY 0.9
AND loc.shortName = '5195'
However, it would be great to have get_restriction_variable() and
get_attstatsslot() extended so they can pass the most common values
from the statistics cache thru expressions, as described in my earlier
mail.
yours,
- clifford
--
: Clifford Wolf Tel +43-1-8178292-00 :
: LINBIT Information Technologies GmbH Fax +43-1-8178292-82 :
: Schönbrunnerstr 244, 1120 Vienna, Austria http://www.linbit.com :
From | Date | Subject | |
---|---|---|---|
Next Message | Patrick Rotsaert | 2006-02-01 16:57:50 | Re: BUG #2225: Backend crash -- BIG table |
Previous Message | Garoso, Fernando | 2006-02-01 16:23:23 | BUG #2231: Incorrect Order By |