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(at)wolff(dot)to>
To: "Vincent Hikida" <vhikida(at)inreach(dot)com>
Cc: <pgsql-general(at)postgresql(dot)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(at)inreach(dot)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(at)postgresql(dot)org)
>