Re: When are index scans used over seq scans?

From: John A Meinel <john(at)arbash-meinel(dot)com>
To: Richard van den Berg <richard(dot)vandenberg(at)trust-factory(dot)com>
Cc: pgsql-perform <pgsql-performance(at)postgresql(dot)org>
Subject: Re: When are index scans used over seq scans?
Date: 2005-04-20 15:37:34
Message-ID: 4266773E.405@arbash-meinel.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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.

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Vivek Khera 2005-04-20 15:38:06 Re: Opteron vs Xeon (Was: What to do with 6 disks?)
Previous Message Vivek Khera 2005-04-20 15:36:29 Re: What to do with 6 disks?