On Fri, 3 Mar 2023 at 02:20, cen <cen.is.imba@xxxxxxxxx> 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
