Composite types let a single PostgreSQL column carry a structured value, and they are a common building block in server-side programming: stored procedures use them to bundle arguments and to shape return values. The catch is that how you call a function returning a composite type has a measurable effect on runtime.
A composite value can be decomposed back into its individual fields when needed:
|
1 2 3 4 5 |
test=# SELECT ('(10, 'hans', 500)'::person).*; id | name | income ----+------+-------- 10 | hans | 500 (1 row) |
Columns may also be declared with a composite type directly, just like any other data type:
|
1 2 3 4 5 6 7 8 |
test=# CREATE TABLE data (p person, gender char(1)); CREATE TABLE test=# d data Table 'public.data' Column | Type | Collation | Nullable | Default --------+--------------+-----------+----------+--------- p | person | | | gender | character(1) | | | |
Here the column type is person.
Setup: a three-million-row table
The pgstattuple extension is a good illustration. It exists to detect table bloat, and it reports its findings through a composite data type. Installing it is straightforward:
|
1 2 |
test=# CREATE EXTENSION pgstattuple; CREATE EXTENSION |
For the test below, a type holding three million entries is created first:
|
1 2 3 4 5 6 7 |
test=# CREATE TABLE x (id int); CREATE TABLE test=# INSERT INTO x SELECT * FROM generate_series(1, 3000000); INSERT 0 3000000 test=# vacuum ANALYZE ; VACUUM |
Inspecting all fields of x is then a matter of asking for the composite result:
|
1 2 3 4 5 6 7 |
test=# explain analyze SELECT (pgstattuple('x')).*; QUERY PLAN ------------------------------------------------------------------------------------------- Result (cost=0.00..0.03 rows=1 width=72) (actual time=1909.217..1909.219 rows=1 loops=1) Planning Time: 0.016 ms Execution Time: 1909.279 ms (3 rows) |
That took close to two seconds. Rewriting the call, however, produces a very different picture:
|
1 2 3 4 5 6 7 8 |
test=# explain analyze SELECT * FROM pgstattuple('x'); QUERY PLAN ---------------------------------------------------------------------------------------------- Function Scan on pgstattuple (cost=0.00..0.01 rows=1 width=72) (actual time=212.056..212.057 rows=1 loops=1) Planning Time: 0.019 ms Execution Time: 212.093 ms (3 rows) |
Moving the call into the FROM clause is substantially faster, and the same holds for a subselect:
|
1 2 3 4 5 6 7 |
test=# explain analyze SELECT (y).* FROM (SELECT pgstattuple('x') ) AS y; QUERY PLAN ----------------------------------------------------------------------------------------- Result (cost=0.00..0.01 rows=1 width=32) (actual time=209.666..209.666 rows=1 loops=1) Planning Time: 0.034 ms Execution Time: 209.698 ms (3 rows) |
Why the FROM clause is faster
The reason is that PostgreSQL expands the FROM clause. An expression of the form (pgstattuple('x')) is rewritten into something else:
|
1 2 3 4 5 6 |
… (pgstattuple('x')).table_len, (pgstattuple('x')).tuple_count, (pgstattuple('x')).tuple_len, (pgstattuple('x')).tuple_percent, … |
In the expanded form the function is invoked more often, which accounts for the runtime difference. Knowing what the planner does under the hood therefore pays off directly — the speedup between the two spellings can be dramatic, and support cases involving this pattern have been seen recently.
