Thread: 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
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
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
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
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