Tom,
Yes, the distribution must be what's doing it. I guess I knew that
subconciously, but was looking for something like hints to force the planner to
do what I wanted. Instead it looks like I'll have to do a bit of tweaking with
my indexes. Probably a partial index on the 'not_displayed_id' column. It'll
be very small and shouldn't cause much overhead. I was trying to keep my index
count down, and have had a dual-column index on (replaced_by_id,
not_displayed_id) to this point.
Thanks once again for your help!
________________________________________
From: Tom Lane [EMAIL PROTECTED]
Sent: Tuesday, December 18, 2007 10:36 AM
To: Weber, Geoffrey M.
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Problem with index not being chosen inside PL/PgSQL
function...
"Weber, Geoffrey M." <[EMAIL PROTECTED]> writes:
> Hmm - good question! However, it is - both the id and
> not_displayed_id are INTEGERs.
Well, in that case it must be a statistics issue --- does the indexed
column have a badly skewed distribution?
You could investigate how many rows the planner thinks will be fetched
via
PREPARE foo(int) AS
SELECT ah.* FROM alert ah where ( (ah.replaced_by_id = '0') AND
(not_displayed_id = $1 ) ) ORDER BY replaced_by_id, not_displayed_id;
EXPLAIN EXECUTE foo(42);
which will set up exactly the same planning situation as occurs in the
plpgsql function: no knowledge of the exact value being compared to.
regards, tom lane
______________________________________________________________________
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email
______________________________________________________________________
NOTICE: This electronic mail transmission may contain confidential information
and is intended only for the person(s) named. Any use, copying or disclosure
by any other person is strictly prohibited. If you have received this
transmission in error, please notify the sender via e-mail.
NOTICE: This electronic mail transmission may contain confidential information
and is intended only for the person(s) named. Any use, copying, or disclosure
by any other person is strictly prohibited. If you have received this
transmission in error, please notify the sender via e-mail.
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
