Fw: [PHP] Fooling the query optimizer

On another list, someone posted this question. Are they correct, old
problem, etc.? I'll pass whatever info there is back to the originating
list.

Adam Lang
Systems Engineer
Rutgers Casualty Insurance Company
http://www.rutgersinsurance.com
----- Original Message -----
From: "Brent R.Matzelle" <bmatzelle(at)yahoo(dot)com>
To: "PostgreSQL PHP" <pgsql-php(at)postgresql(dot)org>
Sent: Thursday, February 08, 2001 10:41 AM
Subject: [PHP] Fooling the query optimizer

> Have any of you discovered a way to get around the current query optimizer
> limitation in Postgres? For example, I have a table that has three
columns
> that I want to index for frequent search duties. In Postgres I am forced
to
> create three indicies: one including all three columns, one for col2 and
> col3, and one for just col3. Databases like MySQL can use the first index
> for these types of queries "SELECT * WHERE col2 = x AND col3 = y" and
"SELECT
> * WHERE col3 = y". Postgres could only perform queries on indicies where
it
> looks like "SELECT * WHERE col1 = x AND col2 = y AND col3 = z" and "SELECT
*
> WHERE col1 = x AND col2 = y" etc. However adding extra indexes as above
> would decrease the write speed on that table because a simple insert would
> require an update on all three indicies.
>
> Is there a way to fool Postgres to use the first index by creating a query
> like "SELECT * WHERE col1 = * AND col3 = x"? I know I'm grasping for
straws
> here, but these issues can kill my database query performance.
>
> Brent