From: Brian Herlihy <btherl@yahoo.com.au>
To: pgsql-performance@postgresql.org
Subject: Re: Query planner is using wrong index.
Date: Fri, 7 Apr 2006 16:41:08 +1000 (EST)
Message-ID: <20060407064108.49532.qmail@web52315.mail.yahoo.com> (raw)
In-Reply-To: <3544.1144379836@sss.pgh.pa.us>
--- Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Brian Herlihy <btherl@yahoo.com.au> writes:
> > My options seem to be
> > - Fudge the analysis results so that the selectivity estimate changes. I
> > have tested reducing n_distinct, but this doesn't seem to help.
> > - Combine the columns into one column, allowing postgres to calculate the
> > combined selectivity.
> > - Drop the (p2, p3) index. But I need this for other queries.
>
> Have you considered reordering the pkey to be (p2,p3,p1) and then
> dropping the (p2,p3) index?
>
> regards, tom lane
Hi Tom,
I've considered it. Unfortunately I need to do lookups on (p1) and (p1,p2) as
well as (p1, p2, p3).
The solution I've gone with is to create an index on (p2 || '/' || p3). This
is unique for each p2/p3 combination, because p2 cannot contain the '/'
character. I'm assuming that this index will be no slower to generate than one
on (p2, p3), as concatenation is very cheap. Having the index on an expression
"hides" it from the optimizer, which is then forced to use the primary key
instead.
It works perfectly now! There were only 2 queries in the system which need
this index, so it was no problem to change them.
Thankyou very much for all your time and patience!
Before I go, I have a question - From discussions on the Postgresql irc
channel, and from reading the TODO list on the website, I am under the
impression that there are no plans to allow optimizer hints, such as "use index
table_pkey". Is this really true? Such a feature would make life inestimably
easier for your end-users, particularly me :)
Thanks,
Brian
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: btherl@yahoo.com.au
Subject: Re: Query planner is using wrong index.
In-Reply-To: <20060407064108.49532.qmail@web52315.mail.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox