--- Tom Lane <tgl@xxxxxxxxxxxxx> wrote: > Brian Herlihy <btherl@xxxxxxxxxxxx> 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
