PostgreSQL (Version 15) does not find a (in my opinion) obvious optimization for the query plan, is there a way to help the optimizer with this problem?
I have a specific kind of query where I have to join one table with itself multiple times (for getting distances of points, but not of relevance here). This can be time consuming, as its a large table, but I can specify per query a subset of groups (one group matches many points) to reduce the searchspace.
A query looks something like
SELECT * FROM points AS p1, -- card(329_680_103) points AS p2, points AS p3 WHERE p2.group = p1.group AND p2.group = p3.group AND p2.property = 1 -- Card(56.394.737) AND p1.property = 1 AND p3.property = 2 -- Card(29.847) AND p2.group IN (1,2,3,4,5) -- card(4270) can be a long list
Th fastest way, tested with explicit joins, is to get the points for p3 (the IN + the property) and then fulfill the joins with the less specific points. As he does if:
AND p3.group IN (1,2,3,4,5)
However PostgreSQL does filter p2 with the IN and p3 with the property and only then is joining, causing bigger intermediate tables. ( See https://explain.dalibo.com/plan/220eccf9h2g2agbc) I know that this is specified in the query this way. But i hope that there is a way that the optimizer can handle this as these are set equal above.
So, is there a way to help postgreSQL to use the table first wich is, according to statistics smallest, together with the IN statement, without specifying it on creation of the query? (In reality the properties are a bit more compelx and on creation its not clear to the user which table would be the best to choose. This optimization should imo be handled on the server side)
As these lists for the groups can get very long, I don't want to duplicate it for each point. CTE and IN VALUES syntax did cause other performance issues on medium sized lists (~50.000 group-ids)