Thread: Backwards index scan
Are there any configurations/flags that we should re-set for the database (v 7.4.x) in order to enable a backwards scan on an index? We are trying to query a table in descending order. We added an index that we were hoping would be scanned backwards but EXPLAIN never indicates that the optimizer will carry out a backwards scan on the index that we added to the table. EXPLAIN indicates that the optimizer will always use a sequential scan if we order the query in descending order.
OUR TESTS
We are conducting a simple test to asses if the optimizer ever uses the index. The table has several columns and the select statement is as follows: select * from ord0007 order by prtnbr, ordschdte desc. The index that we added is "ord0007_k" btree (prtnbr, ordschdte). Prtnbr is numeric(10,0) not null, and ordschdte is date.
We find that the optimizer uses the index for the query if we set enable_sort to off and the query uses ordschdte in ascending order as follows: select * from ord0007 order by prtnbr, ordschdte. For this query, EXPLAIN returns the following output:
QUERY PLAN
--------------------------------------------------------------------------------
Index Scan using ord0007_k on ord0007 (cost=0.00..426.03 rows=232 width=1816)
(1 row)
However the optimizer uses a sequential scan if we order by a descending ordschdte as follows: select * from ord0007 order by prtnbr, ordschdte desc. For this query, whether we set the enable_sort to on or off, EXPLAIN returns the following output:
QUERY PLAN
--------------------------------------------------------------------
Sort (cost=100000155.44..100000156.02 rows=232 width=1816)
Sort Key: prtnbr, ordschdte
-> Seq Scan on ord0007 (cost=0.00..146.32 rows=232 width=1816)
(3 rows)
On June 6, 2006 07:59 am, "Carlos Oliva" <carlos@pbsinet.com> wrote: > We are conducting a simple test to asses if the optimizer ever uses the > index. The table has several columns and the select statement is as > follows: select * from ord0007 order by prtnbr, ordschdte desc. The > index that we added is "ord0007_k" btree (prtnbr, ordschdte). Prtnbr is > numeric(10,0) not null, and ordschdte is date. You have to "order by prtnbr desc, ordschdte desc" to have the index used the way you want. You can re-order in an outer query if you need to. -- Alan
Thank for your response Alan. This indeed corrects the problem as long as
we configure the database to enable_seqscan=false.
Perhaps, you can help me with a side effect of using this index: Rows with
null dates seem to fall off the index. When the ordschdte is null, the
query fails the rows of the data for which the ordschdte is null. We had to
resort to a second query that uses a sequential scan to retrieve the rows
that have a null ordschdte. Is there any kind of index that we can create
that would allow us to order by ordshcdte and which would retrieve rows with
null dates?
Thanks in advance for your response.
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Alan Hodgson
Sent: Tuesday, June 06, 2006 11:05 AM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Backwards index scan
On June 6, 2006 07:59 am, "Carlos Oliva" <carlos@pbsinet.com> wrote:
> We are conducting a simple test to asses if the optimizer ever uses the
> index. The table has several columns and the select statement is as
> follows: select * from ord0007 order by prtnbr, ordschdte desc. The
> index that we added is "ord0007_k" btree (prtnbr, ordschdte). Prtnbr is
> numeric(10,0) not null, and ordschdte is date.
You have to "order by prtnbr desc, ordschdte desc" to have the index used
the way you want. You can re-order in an outer query if you need to.
--
Alan
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
I don't think that null values are indexed - you'll probably need to coalesce your null data value to some value if you want it indexed. You can coalesce those value back to null when you retrieve the data from the query. John Carlos Oliva wrote: > Thank for your response Alan. This indeed corrects the problem as long as > we configure the database to enable_seqscan=false. > > Perhaps, you can help me with a side effect of using this index: Rows with > null dates seem to fall off the index. When the ordschdte is null, the > query fails the rows of the data for which the ordschdte is null. We had to > resort to a second query that uses a sequential scan to retrieve the rows > that have a null ordschdte. Is there any kind of index that we can create > that would allow us to order by ordshcdte and which would retrieve rows with > null dates? > > Thanks in advance for your response. > > -----Original Message----- > From: pgsql-general-owner@postgresql.org > [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Alan Hodgson > Sent: Tuesday, June 06, 2006 11:05 AM > To: pgsql-general@postgresql.org > Subject: Re: [GENERAL] Backwards index scan > > On June 6, 2006 07:59 am, "Carlos Oliva" <carlos@pbsinet.com> wrote: > >>We are conducting a simple test to asses if the optimizer ever uses the >>index. The table has several columns and the select statement is as >>follows: select * from ord0007 order by prtnbr, ordschdte desc. The >>index that we added is "ord0007_k" btree (prtnbr, ordschdte). Prtnbr is >>numeric(10,0) not null, and ordschdte is date. > > > You have to "order by prtnbr desc, ordschdte desc" to have the index used > the way you want. You can re-order in an outer query if you need to. >
On Tue, Jun 06, 2006 at 12:27:33PM -0400, Carlos Oliva wrote:
> Thank for your response Alan. This indeed corrects the problem as long as
> we configure the database to enable_seqscan=false.
If you have to do that, something is still wrong. Do you have
accurate statistics? Is the planner mistaken about something?
A
--
Andrew Sullivan | ajs@crankycanuck.ca
A certain description of men are for getting out of debt, yet are
against all taxes for raising money to pay it off.
--Alexander Hamilton
John Sidney-Woollett <johnsw@wardbrook.com> writes: > I don't think that null values are indexed - you'll probably need to coalesce > your null data value to some value if you want it indexed. That is most definitely not true for Postgres. NULL values are included in the index. However NULLs sort as greater than all values in Postgres. So when you sort descending they'll appear *first*. If you sort ascending they'll appear last. If you have any clauses like 'WHERE col > foo' then it will not be true for NULL values of col regardless of what foo is and those records will be dropped. This is true regardless of whether there's an index. -- greg
Thanks for putting me straight - I thought I remembered a previous post from Tom about nulls not being indexed but it was probably referring to partial indexes not indexing values that are null... Coalescing null values might still be helpful to ensure that they are ordered in the index at a specific location (either the beginning or the end depending on your substitution value). John Greg Stark wrote: > John Sidney-Woollett <johnsw@wardbrook.com> writes: > > >>I don't think that null values are indexed - you'll probably need to coalesce >>your null data value to some value if you want it indexed. > > > That is most definitely not true for Postgres. NULL values are included in the > index. > > However NULLs sort as greater than all values in Postgres. So when you sort > descending they'll appear *first*. If you sort ascending they'll appear last. > > If you have any clauses like 'WHERE col > foo' then it will not be true for > NULL values of col regardless of what foo is and those records will be > dropped. This is true regardless of whether there's an index. > >