Re: Indexes?

Perhaps I'm missing something but let's say that the index has the
following:

toDate           fromDate
1992-03-02  1991-01-23
1992-04-03  1990-06-13
1993-05-03  1991-01-22
...
...
...
2004-12-01  2003-02-22
2005-03-04  2003-02-22          (a)
2005-03-05  2004-12-15          (b)
2005-03-05  2004-06-18          (c)
2007-04-12  2005-06-18          (d)

Let's say that there are a million entries where the toDate is less than
today 2004-12-02. That is less than (a) in the index. From the index then
only a, b, c, and d should be scanned further. a and c would be picked based
on the index values because 2004-12-02 is between the from and end date.
However, b and d would be excluded immediately because the the from date is
greater than 2004-12-02 and would save the optimizer from even reading the
table for these index entries because the fromDate is in the index.

This may be a somewhat extreme example but my experience is in most systems
old historical data makes up the bulk of the data and newer data is a much
smaller amount. In addition most people are interested in data from the most
recent month.

Of course I may be mistaken about the data distribution.

Vincent
----- Original Message -----
From: "Bruno Wolff III" <bruno@wolff.to>
To: "Vincent Hikida" <vhikida@inreach.com>
Cc: <pgsql-general@postgresql.org>
Sent: Thursday, December 02, 2004 12:10 PM
Subject: Re: [GENERAL] Indexes?


> On Wed, Dec 01, 2004 at 23:16:48 -0800,
>  Vincent Hikida <vhikida@inreach.com> wrote:
>> 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.
>
> I combined index won't be very useful for the kind of search he is doing.
> And not having an index on FromDate could hurt in some cases depending
> on the distribution of values.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>