Postgres Query Planner expects 1 row for a join doing a Nested Loop Join

32 views Asked by At

I'm having a performance issue when joining between 2 tables because the query planner estimates 1 row, instead of potentially thousands, and chooses to do a nested join. Some background on the query:

On Version 15.3

Two tables:

An items table:
id | company_id | name | ...

A linking table:
id | parent_item_id | child_item_id | link_type | company_id | ...

The query (A simplified version of the query):

Select ... from Items i
Join Links l on i.id = l.parent_item_id and l.link_type = 'foo'
join Items i_child on l.child_item_id = i_child.id
....
where ....

The items table has millions of records, and each item can have hundreds of links to other items through the links table.

The issue seems to be that the query planner expects there to be a single row per item -> child join, but in reality a parent item can link to many child items.

Here's an image of what the query planner is showing.

enter image description here

Questions:

  1. Is there a way to force the query planner to have a better row estimate, or force it to try a hash join instead of a nested join? I've tried just to test SET LOCAL enable_nestloop = False; and it still chooses a nested join.
  2. Is there a way to write a query like that in a different way that would be more performant?
0

There are 0 answers