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@xxxxxxxxxxxxxx
[mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Alan Hodgson
Sent: Tuesday, June 06, 2006 11:05 AM
To: pgsql-general@xxxxxxxxxxxxxx
Subject: Re: [GENERAL] Backwards index scan
On June 6, 2006 07:59 am, "Carlos Oliva" <carlos@xxxxxxxxxxx> 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.
