In this lesson, you will learn how the abstraction of custom types influences query planner decisions and raw execution speed compared to native PostgreSQL types.
The Cost of Abstraction
When you define a custom type—whether it’s a DOMAIN with constraints, a COMPOSITE type, or a custom base type via C—you add a layer of complexity between the storage engine and the query executor. PostgreSQL is highly optimized for native types like INTEGER, BIGINT, and TEXT. These types have well-understood size constraints and efficient, hard-coded comparison functions.
When you use a custom type, the database must invoke the type’s associated support functions (input, output, receive, send, and—most importantly—comparison functions) every time it processes a row.
Profiling the Overhead
To visualize the performance impact, you can compare a query using a native type against one using a custom domain. Consider a simple DOMAIN used for age validation:
-- Native integer
EXPLAIN ANALYZE SELECT * FROM users WHERE age > 21;
-- Domain-wrapped integer
CREATE DOMAIN valid_age AS INTEGER CHECK (VALUE > 0);
EXPLAIN ANALYZE SELECT * FROM users_with_domain WHERE age_column > 21;
When you run EXPLAIN ANALYZE, you might notice a slight increase in "actual time" for the custom type. This is because the planner often has less visibility into the statistical distribution of data stored in custom types. While the ANALYZE command gathers statistics, custom types often lack the complex internal logic that allows the planner to estimate selectivity as accurately as it does for native types.
If your custom type requires a function call for every comparison (e.g., in an ORDER BY clause), that overhead adds up. In high-throughput systems, the accumulation of these function calls—known as "function call overhead"—can significantly slow down sorting and grouping operations. Furthermore, because custom types may prevent the use of certain "index-only scans" if the index opclass isn't perfectly optimized, you might see the database reverting to a full table scan where it would have used an index for a native INTEGER.
Key Takeaway: While custom types improve data integrity and code readability, they introduce performance overhead by necessitating additional function calls and potentially reducing the query planner's ability to optimize selectivity and index usage.