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

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.


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

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

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.


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

Attachment: signature.asc
Description: OpenPGP digital signature

Reply via email to