Search
5 results for “query-planner”
Search results
SQL Joins & Query Planning
Why the same JOIN can execute as a nested loop, a hash join, or a merge join depending entirely on table size and available sort order, and why "it's impossible to suppress nested loops entirely" is a real, documented planner constraint.
What are the three join algorithms PostgreSQL's planner can choose between, and what does each one actually do?
A nested loop join takes each row from one table (the outer side) and scans the other table (the inner side) for matches, the simplest algorithm, and cheap specifically when the inner side is small or has a usable index so that scan is fast per outer row. A hash join builds an in-memory hash table from one input keyed on the join column, then probes it with rows from the other input, efficient for large inputs with no useful sort order or index. A merge join requires both inputs already sorted on the join key (or sorts them first) and then walks both sorted streams in lockstep, efficient specifically when that sorted order already exists or is useful for something else in the query anyway.
Why would a hash join beat a nested loop join for two large, unindexed tables, but lose to a nested loop join when one table is tiny?
A nested loop join's cost scales with (outer rows) × (cost of an inner-side lookup per row), so with two large unindexed tables, the inner-side scan is expensive and gets repeated for every single outer row, making the total cost grow multiplicatively. A hash join instead pays a roughly one-time cost to build a hash table from one side, then does a cheap lookup per row from the other side, additive rather than multiplicative, which wins decisively at scale. But when one table is tiny, the nested loop's "repeat the inner scan per outer row" cost is trivial regardless, and it avoids the hash table's build overhead entirely, so the simpler algorithm wins for small inputs specifically.
PostgreSQL's documentation says it's "impossible to suppress nested-loop joins entirely" even with enable_nestloop off. What does that tell you about relying on planner hints to force a specific join algorithm?
That specific guarantee is documented only for `enable_nestloop`: turning it off only discourages the planner by making nested loops look artificially expensive in cost estimation, it can't hard-disable them, because for some queries a nested loop is the only viable plan at all (for example, certain correlated subquery shapes), so the planner will still use one if it must. `enable_hashjoin` and `enable_mergejoin` don't carry that same caveat, disabling either one actually can prevent the planner from choosing that join type, since a nested loop (or the other remaining method) is always available as a fallback plan. In practice, though, all three settings are best treated as a debugging/diagnostic tool for understanding planner behavior, not a reliable production mechanism for forcing a specific join algorithm, the actual fix for a bad plan is almost always better statistics (via `ANALYZE`) or a better index, not overriding the planner's method choice.
A query filters WHERE logdate >= 2008-01-01 against a table range-partitioned by logdate across dozens of monthly partitions. What does partition pruning actually do, and what does it depend on?
Partition pruning lets the planner prove, from the query's WHERE clause and each partition's declared bounds, that some partitions cannot possibly contain a matching row, and it excludes them from the plan entirely rather than scanning and filtering every partition. In this example, `logdate >= 2008-01-01` has no upper bound, so it prunes only the partitions entirely before 2008-01, decades of older monthly partitions are eliminated before execution, while every partition from 2008-01 onward, including all of them up to the present, is still considered and scanned. Pruning down to a single partition would need a bounded predicate on both ends, for example `logdate >= 2008-01-01 AND logdate < 2008-02-01`. This depends entirely on the partition bounds themselves, not on any index, a partitioned table with no indexes at all still benefits from pruning, and pruning specifically requires the WHERE clause to reference the partition key directly with values (or parameters) the planner can actually compare against those bounds.