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
Last activity: Jul 18, 2025, 09:14 AM