0

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)

See: https://explain.dalibo.com/plan/1918dgee6a473ba6

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)

4
  • Please consider reading this advice
    – mustaccio
    Commented Aug 29, 2023 at 12:17
  • thanks, added links to execution plans and added more precise cardinalities for the given example
    – MrSunday
    Commented Aug 29, 2023 at 13:33
  • PostgreSQL overestimates the number of rows found in p2 by almost a factor of 20. ANALYZE, perhaps with higher default_statistics_target, may improve the estimate. Commented Aug 29, 2023 at 15:42
  • had a look, default_statistics_target is already at 1000 and ANALYZE was performed, an even higher value makes, as far I understand, no sense. Looks like postgres has some issue with a long list in the IN statement for the estimation
    – MrSunday
    Commented Aug 30, 2023 at 6:39

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.