Re: Posible planner improvement?

Moving to -hackers...

On May 21, 2008, at 9:09 AM, Richard Huxton wrote:
> Luke Lonergan wrote:
>> The problem is that the implied join predicate is not being
>> propagated. This is definitely a planner deficiency.
>
> IIRC only equality conditions are propagated and gt, lt, between
> aren't. I seem to remember that the argument given was that the
> cost of checking for the ability to propagate was too high for the
> frequency when it ocurred.
>
> Of course, what was true for code and machines of 5 years ago might
> not be so today.

Definitely...

How hard would it be to propagate all conditions (except maybe
functions, though perhaps the new function cost estimates make that
more practical) in cases of equality?

For reference, the original query as posted to -performance:

select * from t1, t2 where t1.id > 158507 and t1.id = t2.id;

That took > 84 minutes (the query was a bit longer but this is the
part that made the difference) after a little change the query took
~1 second:

select * from t1, t2 where t1.id > 158507 and t2.id > 158507 and
t1.id = t2.id;

--
Decibel!, aka Jim C. Nasby, Database Architect decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828