Re: Fw: [PHP] Fooling the query optimizer

"Adam Lang" <aalang(at)rutgersinsurance(dot)com> forwards:
>> In Postgres I am forced to create three indicies: one including all
>> three columns, one for col2 and col3, and one for just col3.

Depending on what his queries actually are, perhaps it's sufficient
to create one index on (col3,col2,col1), rather than on (col1,col2,col3)
as I presume his first index currently is. As Mike Ansley points out,
Postgres can use the first N columns of an index if all N are
constrained by a query's WHERE clause; but there is no point in looking
at index columns beyond an unconstrained column, because if you did
you'd be fighting the index order instead of being helped by it.

I think that the planner used to have some bugs that might interfere
with recognition of these partial-index-match cases, but it's been okay
with them since 7.0 for sure. To say more, we'd need to know exactly
which PG version he's running and exactly what his queries look like.

regards, tom lane