Search
4 results for “query-performance”
Search results
Database Partitioning
How range, list, and hash partitioning each split one logical table into physical pieces, and why partition pruning depends entirely on the WHERE clause matching partition bounds directly, not on any index.
What is the difference between range, list, and hash partitioning, and when would you choose each?
Range partitioning divides rows by a value falling within a bounded, non-overlapping range (inclusive lower bound, exclusive upper bound), the natural fit for time-series data like logs partitioned by month. List partitioning explicitly assigns specific key values to specific partitions, a good fit when data naturally groups into a known, finite set of categories, like a specific list of counties or regions. Hash partitioning distributes rows by the hash of the partition key modulo a chosen number of partitions, useful specifically when there is no natural range or category to split on and you just need to spread rows roughly evenly across a fixed number of partitions.
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.
Why can't partition pruning work with a WHERE clause like WHERE logdate >= CURRENT_TIMESTAMP, and what does that tell you about writing partition-friendly queries?
Partition pruning at plan time requires the comparison value to be known and fixed when the plan is built, and `CURRENT_TIMESTAMP` is not immutable, its value depends on when the query actually executes, not when it's planned, so the planner can't statically prove which partitions it will or won't match. (Pruning can still happen at execution time for genuinely parameterized values, like a join parameter from an outer query, just not for volatile functions like this one.) This means writing partition-friendly queries means filtering on the partition key with values the planner can actually reason about, a literal, a bound parameter, or an immutable expression, not a function whose result varies by when the query runs.