Re: When are index scans used over seq scans?

Richard van den Berg wrote:

>John A Meinel wrote:
>
>
>>I believe the problem is that postgres doesn't recognize how restrictive
>>a date-range is unless it uses constants.
>>
>>
>
>And it does when using BETWEEN with int for example? Impressive. :-)
>
>
>
>>select blah from du WHERE time between '2004-10-10' and '2004-10-15';
>>Will properly use the index, because it realizes it only returns a few
>>rows.
>>
>>
>
>Correct, it does.
>
>
>
>>Probably you should try to find out the status of multi-table
>>selectivity. It was discussed in the last couple of months.
>>
>>
>
>I can't find the posts you are refering to. What is the priciple of
>multi-table selectivity?
>
>Your explanation sounds very plausible.. I don't mind changing the
>cpu_tuple_cost before running BETWEEN with timestamps, they are easy
>enough to spot.
>
>Thanks,
>
>
>
Well, there was a thread titled "date - range"
There is also "recognizing range constraints" which started with "plan
for relatively simple query seems to be very inefficient".

Sorry that I gave you poor search terms.

Anyway, "date - range" gives an interesting workaround. Basically you
store date ranges with a different structure, which allows fast index
lookups.

The other threads are just discussing the possibility of improving the
planner so that it recognizes WHERE a > b AND a < c, is generally more
restrictive.

There was a discussion about how to estimate selectivity, but I think it
mostly boils down that except for pathological cases, a > b AND a < c is
always more restrictive than just a > b, or  a < c.

Some of it may be also be found in pgsql-hackers, rather than
pgsql-performance, but I'm not subscribed to -hackers, so most of it
should be in -performance.

John
=:->

caveat, I'm not a developer, I just read a lot of the list.