Обсуждение: Re: [PERFORM] 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@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828

Decibel! wrote:
>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:

Just out of curiosity, would predefining the order of join have solved
the issue, as in:

a. select * from t1 join t2 using(id) where t1.id > 158507;
vs.
b. select * from t2 join t1 using(id) where t1.id > 158507;

I'd expect a to be faster than b, is it?
-- 
Sincerely,                                                          srb@cuci.nl          Stephen R. van den Berg.
"Technology is stuff that doesn't work yet."   --  Bran Ferren
"We no longer think of chairs as technology."  --  Douglas Adams