DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com.au;
	h=Message-ID:Received:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding;
	b=MiTcPB5oHkUh0q4mapCJ3qsjyhpzfJPm4AKTCGlbk8h8vfupxeBKSnRtJ3rB8W3Vs7ZOPJ2ASTYexpNsSPMeytaT7m8Yw35kbC4f01B9JE9os1Gz7gpEwpcoDaMIcvAbsVqiEN3FeFnkT7eFG8X/yrrQQ3xCp6Q5eGV2+i5Ppzk=
	;
Message-ID: <20060407064108.49532.qmail@web52315.mail.yahoo.com>
Date: Fri, 7 Apr 2006 16:41:08 +1000 (EST)
From: Brian Herlihy <btherl@yahoo.com.au>
Subject: Re: Query planner is using wrong index. 
To: pgsql-performance@postgresql.org
In-Reply-To: <3544.1144379836@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

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