pg.ddx.io  pgsql-php@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Fooling the query optimizer
6+ messages / 2 participants
[nested] [flat]

* Fooling the query optimizer
@ 2001-02-08 15:41  Brent R.Matzelle <bmatzelle@yahoo.com>
  0 siblings, 3 replies; 6+ messages in thread

From: Brent R.Matzelle @ 2001-02-08 15:41 UTC (permalink / raw)
  To: pgsql-php

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




^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Fooling the query optimizer
@ 2001-02-08 16:26  Adam Lang <aalang@rutgersinsurance.com>
  parent: Brent R.Matzelle <bmatzelle@yahoo.com>
  2 siblings, 0 replies; 6+ messages in thread

From: Adam Lang @ 2001-02-08 16:26 UTC (permalink / raw)
  To: pgsql-php

Let me look into this.

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




^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Fooling the query optimizer
@ 2001-02-08 16:44  Adam Lang <aalang@rutgersinsurance.com>
  parent: Brent R.Matzelle <bmatzelle@yahoo.com>
  2 siblings, 0 replies; 6+ messages in thread

From: Adam Lang @ 2001-02-08 16:44 UTC (permalink / raw)
  To: pgsql-php

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




^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Fooling the query optimizer
@ 2001-02-08 17:22  Brent R.Matzelle <bmatzelle@yahoo.com>
  0 siblings, 0 replies; 6+ messages in thread

From: Brent R.Matzelle @ 2001-02-08 17:22 UTC (permalink / raw)
  To: pgsql-php

I'm running PostgreSQL 7.0.3 RPMs on RedHat 7.  Thanks for your help.  

Brent

>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





^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Fooling the query optimizer
@ 2001-02-08 18:07  Adam Lang <aalang@rutgersinsurance.com>
  parent: Brent R.Matzelle <bmatzelle@yahoo.com>
  2 siblings, 0 replies; 6+ messages in thread

From: Adam Lang @ 2001-02-08 18:07 UTC (permalink / raw)
  To: pgsql-php

Here is a reply I got (I am subscribed to pgsql-general list.  Subscription
info can be gotten from www.postgresql.org):

Running this query:

"SELECT * WHERE col3 = x"

with a btree index on (col1, col2, col3) cannot be performed in an efficient
manner, in any database, because you have specified the column order to be
col1, col2, col3.  If somebody claims that MySQL can do this, they're
misunderstanding the problem, and/or solution, or there's some fudging going
on by somebody.

Imagine the index to look like this:

col1    col2    col3
1       1       1
1       1       2
1       1       3
1       1       4
1       2       1
1       2       2
1       2       3
1       2       4

and a query which says "SELECT * WHERE col3 = 4".  Now what order are you
going to traverse the index in?  Remember that you can only use col3, and
have to binary search (btree index).  If you binary split the index, then
you have one 4 in one half, and one four in another, i.e.: it's not going to
work.

If MySQL claims it can do this, then the only way that I can think that they
are doing this is by creating extra or separate indices behind the scenes,
which is inefficient, and not particularly user friendly.  Of course, they
may have used GiST to create a special index for this ;-) and gotten it
working, but I doubt it.  Btree indices are by far the most common with
simple data.

For any btree index, the index can be used to the point where the index
columns and the filter columns diverge, IN ORDER, e.g.: if your index is
over columns a, b, c, d, and you filter on a, b, d, e, then the index can be
used, only over columns a and b, though (not d!!).  In the example cited,
the first column in the index is not used in the filter, and so the complete
index has to be ignored, i.e.: seq scan.

Cheers...



MikeA



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




^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Fw: Fooling the query optimizer
@ 2001-02-08 19:15  Adam Lang <aalang@rutgersinsurance.com>
  0 siblings, 0 replies; 6+ messages in thread

From: Adam Lang @ 2001-02-08 19:15 UTC (permalink / raw)
  To: pgsql-php

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
>




^ permalink  raw  reply  [nested|flat] 6+ messages in thread


end of thread, other threads:[~2001-02-08 19:15 UTC | newest]

Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-02-08 15:41 Fooling the query optimizer Brent R.Matzelle <bmatzelle@yahoo.com>
2001-02-08 16:26 ` Adam Lang <aalang@rutgersinsurance.com>
2001-02-08 16:44 ` Adam Lang <aalang@rutgersinsurance.com>
2001-02-08 18:07 ` Adam Lang <aalang@rutgersinsurance.com>
2001-02-08 17:22 Re: Fooling the query optimizer Brent R.Matzelle <bmatzelle@yahoo.com>
2001-02-08 19:15 Fw: Fooling the query optimizer Adam Lang <aalang@rutgersinsurance.com>

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