I believe that it is better to have a concatenated key of (toDate,FromDate).
The reason the toDate should come first is that for more "recent" records,
finding curDates less than toDate is much more selective than finding
curDates greater than fromDate. Actually I'm not sure if fromDate is that
helpful either as part of the concatenated key (it probably depends) but
definitely not by itself.
If your usual query is someEarlyHistoricalDate between toDate and fromDate,
then the concatenated key should be (fromDate,toDate) instead.
If toDate is sometimes not known, I would use some fixed date far in the
future rather than a null.
Vincent
----- Original Message -----
From: "Bjørn T Johansen" <btj@havleik.no>
To: <pgsql-general@postgresql.org>
Sent: Wednesday, December 01, 2004 10:11 PM
Subject: [GENERAL] Indexes?
>I have a table where I need to use "..where curdate between fromDate and
>toDate".
> Is it best to have two indexes, one for FromDate and one for toDate or
> just one index for both the fields?
>
>
> Regards,
>
> BTJ
>
> --
> -----------------------------------------------------------------------------------------------
> Bjørn T Johansen
>
> btj@havleik.no
> -----------------------------------------------------------------------------------------------
> Someone wrote:
> "I understand that if you play a Windows CD backwards you hear strange
> Satanic messages"
> To which someone replied:
> "It's even worse than that; play it forwards and it installs Windows"
> -----------------------------------------------------------------------------------------------
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>