Re: Posible planner improvement?

A Dimecres 21 Maig 2008, Mark Mielke va escriure:
> A Dimecres 21 Maig 2008, Richard Huxton va escriure:
> >> Albert Cervera Areny wrote:
> >>> I've got a query similar to this:
> >>>
> >>> 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;
> >>
> >> Try posting EXPLAIN ANALYSE SELECT ... for both of those queries and
> >> we'll see why it's better at the second one.
>
> Even if the estimates were off (they look a bit off for the first
> table), the above two queries are logically identical, and I would
> expect the planner to make the same decision for both.
>
> I am curious - what is the result of:
>
> select * from t1, t2 where t2.id > 158507 and t1.id = t2.id;
>
> Is it the same speed as the first or second, or is a third speed entirely?

Attached the same file with the third result at the end. The result is worst
than the other two cases. Note that I've analyzed both tables but results are
the same. One order of magnitude between the two first queries.

>
> If t1.id = t2.id, I would expect the planner to substitute them freely
> in terms of identities?
>
> Cheers,
> mark