From: John A Meinel <john@arbash-meinel.com>
To: Richard van den Berg <richard.vandenberg@trust-factory.com>
Cc: pgsql-perform <pgsql-performance@postgresql.org>
Subject: Re: When are index scans used over seq scans?
Date: Wed, 20 Apr 2005 10:37:34 -0500
Message-ID: <4266773E.405@arbash-meinel.com> (raw)
In-Reply-To: <42667219.7050907@trust-factory.com>
References: <426665A0.1080102@trust-factory.com>
<42666890.1000401@arbash-meinel.com>
<42667219.7050907@trust-factory.com>
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.
Attachments:
signature.asc (application/pgp-signature, 252B) download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: john@arbash-meinel.com, richard.vandenberg@trust-factory.com
Subject: Re: When are index scans used over seq scans?
In-Reply-To: <4266773E.405@arbash-meinel.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox