Re:450× Faster Joins with Index Condition Pushdown
by HDThoreaun on Saturday August 23, 2025 @05:56PM
(#44999453)
No, it is not a judgement call. The query planner should be storing the distributions of the values in every index. This makes it obvious which pushdown to do here. Again, basic stuff. Youre right though not quite simple as "push down as much as possible", it is one step past that.
Re:450× Faster Joins with Index Condition Pushdown
by jmalicki on Saturday August 23, 2025 @06:13PM
(#44999548)
Without storing the joint distribution of the values corresponding to the conditions that span multiple tables, it can be hard to know what's a win.
Re:450× Faster Joins with Index Condition Pushdown
by Seattle3503 on Saturday August 23, 2025 @10:53PM
(#45000902)
Postgres by default computes univariate stats for each column and uses those. If this is producing bad query plans, you can extend the statistics to be multivariate for select groups of columns manually. But to avoid combinatorially growth of stats related storage and work, you have to pick the columns by hand.
Re:450× Faster Joins with Index Condition Pushdown
by jmalicki on Sunday August 24, 2025 @12:29PM
(#45005538)
That still isn't multivariate stats across a join.
Re:450× Faster Joins with Index Condition Pushdown
by Jupe on Saturday August 23, 2025 @10:02PM
(#45000675)
Agreed. Isn't this precisely why key statistics (table statistics) are maintained in many DB systems? Essentially, always "push down" the predicate with the worst statistics and always execute (early) the predicates with high selectivity.
I'd be very surprised if virtually every RDBMS doesn't do this already.
But here they are deciding between "pushdown o.status==shipped" and "pushdown u.email==address@", in parallel both, then join (which they already did) or first doing "u.email==address@" then pushing down "u.id==o.user_id" mostly.
This is a judgment call. Their planner is pretty dumb to not know which one is better, but “push down as much as possible” doesn't cut it: you need to actually decide what to push down and why.