In this lesson, you will learn how to inform the PostgreSQL query planner about custom functional dependencies to enable smarter, more efficient execution plans.
The Problem: Planner Blindness
When you create complex custom types or utilize domain types, the PostgreSQL optimizer often treats these columns as independent variables. If you have a custom type representing a Coordinate and a related RegionID, the optimizer might assume that any coordinate could exist in any region. However, in your specific application, a Coordinate might strictly belong to one RegionID. Without this metadata, the planner might choose a nested loop join when a hash join would be significantly faster, or it might incorrectly estimate the number of rows returned.
Defining the Dependency
A "Functional Dependency" is a formal rule stating that the value of one column (or set of columns) determines the value of another. While PostgreSQL doesn't have a declarative CREATE DEPENDENCY statement for arbitrary types, we communicate these relationships to the optimizer using Statistics Extensions.
By creating a STATISTICS object, we tell the planner to perform a multivariate analysis. This allows the database to "see" the correlation between your custom types:
CREATE STATISTICS custom_type_dependency
ON coord_col, region_col
FROM my_complex_table;
Once this is created, you must run ANALYZE on the table. PostgreSQL will collect cross-column statistics, effectively learning that coord_col -> region_col.
Why This Matters
When the optimizer knows that coord_col functionally determines region_col, it can drastically improve its cardinality estimates. If you execute a query filtering by both columns, the planner will no longer underestimate the selectivity of the combined filter. Instead of assuming the two conditions are independent (which often leads to a massive underestimation of the result set size), it uses the functional dependency to realize that filtering by coord_col already implies the value for region_col. This prevents the "multiply the selectivities" trap, leading the planner to choose index scans over sequential scans or more efficient join strategies.
Key Takeaway: By creating multivariate statistics objects, you bridge the gap between your custom type logic and the query planner's decision-making process, ensuring the optimizer accounts for hidden relationships between your data fields.