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.
signature.asc
Description: OpenPGP digital signature
