Re: Postgres undeterministically uses a bad plan, how to convince it otherwise?

On Fri, 3 Mar 2023 at 02:20, cen <cen(dot)is(dot)imba(at)gmail(dot)com> wrote:
> I understand that even though both colums are indexed, the indexes are
> completely different but the point is, how would one know in advance
> which one will be faster when designing the query?

Likely to be safe, you'd just include both. The problem is that the
query planner makes use of equivalence classes to deduce equivalence
in quals.

If you have a query such as:

select * from t1 inner join t2 on t1.x = t2.y where t1.x = 3;

then the planner can deduce that t2.y must also be 3 and that qual can
be pushed down to the scan level. If t2.y = 3 is quite selective and
there's an index on that column, then this deduction is likely going
to be a very good win, as the alternative of not using it requires
looking at all rows in t2.

The problem is that the equivalence class code only can deduce
equality. If we had written:

select * from t1 inner join t2 on t1.x = t2.y where t1.x > 2 and t1.x < 4;

then we'd not have gotten quite as optimal a plan.

Providing we're doing an inner join, then we could just write both
sets of quals to force the planner's hand:

select * from t1 inner join t2 on t1.x = t2.y where t1.x > 2 and t1.x
< 4 and t2.y > 2 and t2.y < 4;

you could likely do this.

I still hope to improve this in the planner one day. A few other
things are getting closer which sets the bar a bit lower on getting
something like this committed. There's some relevant discussion in
[1].

David

[1] https://postgr.es/m/CAKJS1f9FK_X_5HKcPcSeimy16Owe3EmPmmGsGWLcKkj_rW9s6A%40mail.gmail.com