pg.ddx.io  pgsql-php@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Adam Lang <aalang@rutgersinsurance.com>
To: pgsql-php@postgresql.org
Subject: Fw: Fooling the query optimizer
Date: Thu, 8 Feb 2001 14:15:01 -0500
Message-ID: <026a01c09203$6f863f00$330a0a0a@rutgersinsurance.com> (raw)

Here is another response.  Again, if you really want to pursue this line of
questions more, the general pgsql list will serrve you a lot better.

"Adam Lang" <aalang@rutgersinsurance.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

Adam Lang
Systems Engineer
Rutgers Casualty Insurance Company
http://www.rutgersinsurance.com
----- Original Message -----
From: "Adam Lang" <aalang@rutgersinsurance.com>
To: "PostgreSQL PHP" <pgsql-php@postgresql.org>
Sent: Thursday, February 08, 2001 11:44 AM
Subject: Re: [PHP] Fooling the query optimizer


> Also, what version of postgres are you running?
>
> Adam Lang
> Systems Engineer
> Rutgers Casualty Insurance Company
> http://www.rutgersinsurance.com
> ----- Original Message -----
> From: "Brent R.Matzelle" <bmatzelle@yahoo.com>
> To: "PostgreSQL PHP" <pgsql-php@postgresql.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
>




view thread (6+ messages)

Message-ID: <026a01c09203$6f863f00$330a0a0a@rutgersinsurance.com>
Permalink:  ../026a01c09203$6f863f00$330a0a0a@rutgersinsurance.com/
Also on:    postgresql.org/message-id/026a01c09203$6f863f00$330a0a0a@rutgersinsurance.com

 · 

reply

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-php@postgresql.org
  Cc: aalang@rutgersinsurance.com
  Subject: Re: Fw: Fooling the query optimizer
  In-Reply-To: <026a01c09203$6f863f00$330a0a0a@rutgersinsurance.com>
  References: <026a01c09203$6f863f00$330a0a0a@rutgersinsurance.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