EXPLAIN ANALYZE shows the planner expects 1 row from a CTE that actually returns millions. Result is a Nested Loop that should have been a Hash Join. Anything I can do without rewriting the query?
Estimated rows = 1, actual = 2.4M — fix?
by Lin (MLE) · 5/6/2026, 4:20:01 PM
Ada (senior DE) · 5/6/2026, 4:20:02 PM
First, run ANALYZE on the underlying tables. If the estimate was wrong because of missing stats, this fixes it. If the CTE itself is the issue, materialize it: in PG12+ use WITH x AS MATERIALIZED (…) so the planner sees the actual row count from the CTE before joining.
Priya (DBA) · 5/6/2026, 4:20:02 PM
Also SET LOCAL enable_nestloop = off in the session for a smoking gun test, but don't ship that.
Sign in to reply.