On Tue, 2025-02-04 at 15:00 +0100, Tomas Vondra wrote:
> This is a surprisingly common query pattern in OLTP applications,
> thanks
> to normalization.
+1. Creating a small lookup table should be encouraged rather than
penalized.
Your test data includes a fact table with 10k rows and no index on the
filter condition. In OLTP applications the fact table might often fit
in memory, but I'd still expect it to have an index on the filter
condition. That might not change your overall point, but I'm curious
why you constructed the test that way?
> There's a lot of stuff that could / should be improved on the current
> patch. For (1) we might add support for more complex cases with
> snowflake schemas [3] or with multiple fact tables. At the same time
> (1)
> needs to be very cheap, so that it does not regress every non-
> starjoin
> query.
The patch only considers the largest table as the fact table, which is
a good heuristic of course. However, I'm curious if other approaches
might work. For instance, could we consider the table involved in the
most join conditions to be the fact table?
If you base it on the join conditions rather than the size of the
table, then detection of the star join would be based purely on the
query structure (not stats), which would be nice for predictability.
> But the bigger question is whether it makes sense to have such fast-
> path
> modes for certain query shapes.
We should explore what kinds of surprising cases it might create, or
what maintenance headaches might come up with future planner changes.
But the performance numbers you posted suggest that we should do
something here.
Regards,
Jeff Davis
