Sample Header Ad - 728x90

Postgres query planner join selectivity greater than 1?

1 vote
1 answer
48 views
I am using PostgreSQL 14.17. I am trying to debug a query planner failure in a bigger query, but I think I've narrowed down the problem to a self-join on a join table:
SELECT t2.item_id
  FROM item_sessions t1
  JOIN item_sessions t2
       ON t1.session_key = t2.session_key
 WHERE t1.item_id = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx';
After running ANALYZE on the table, EXPLAIN gives this plan (which matches the subplan in the larger query):
Nested Loop  (cost=1.12..119.60 rows=7398 width=16)
   ->  Index Only Scan using item_sessions_item_id_session_key_uniq on item_sessions t1  (cost=0.56..8.58 rows=1 width=33)
         Index Cond: (item_id = 'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx'::uuid)
   ->  Index Only Scan using item_sessions_session_idx on item_sessions t2  (cost=0.56..110.11 rows=91 width=49)
         Index Cond: (session_key = (t1.session_key)::text)
**Why is the loop estimating 7398 rows when the two child nodes estimate 1 and 91 respectively?** I would have expected the loop total to be less than 1 * 91 FWIW, the child estimates seem correct. item_id has n_distinct at -0.77649677, so the expected row count is 1.3, and session_key has n_distinct at 149555 out of an estimated 1.36e+07 tuples, which gives 90.9 expected tuples per session_key. The indexes referenced in the plan are: - item_sessions_session_idx btree (session_key, item_id) - item_sessions_item_id_session_key_uniq UNIQUE CONSTRAINT, btree (item_id, session_key) ETA: I created a minimal reproduction [here](https://github.com/felipeochoa/pg-plan-selectivity-gt1) . The failure is visible [in the job logs](https://github.com/felipeochoa/pg-plan-selectivity-gt1/actions/runs/16359411460/job/46224463766) on 17.5, 16.9, and 15.13
Asked by Felipe (317 rep)
Jul 17, 2025, 04:41 AM
Last activity: Jul 18, 2025, 09:14 AM