Difference between pg_column_size(table.*) and pg_column_size(table.col1) + pg_column_size (table.col2)

Question

From PG DOC

pg_column_size(any) : number of bytes used to store a particular value (possibly compressed) pg_column_size shows the space used to store any individual data value

Example:

select pg_column_size(5::smallint);    -- 2 bytes 
select pg_column_size(5::int);         -- 4 bytes 

With input of pg_column_size, it can be a column or row, so I created a test to check it. Here's my test:

My table

CREATE TABLE index_test
(
  id integer NOT NULL,  -- 4  bytes 
  a integer,            -- 4  bytes 
  b integer,            -- 4  bytes 
  CONSTRAINT index_test_id PRIMARY KEY (id)
)

1/ First query: sum(pg_column_size(table.rows))

with abc as 
(
 select id,a,b
 from index_test where b > 100
)
select pg_size_pretty(sum(pg_column_size(abc.*))) from abc  -- "348 kB", abc.* = record

And query's explanation:

"Aggregate  (cost=427.55..427.56 rows=1 width=24) (actual time=9.171..9.171 rows=1 loops=1)"
"  CTE abc"
"    ->  Seq Scan on index_test  (cost=0.00..180.00 rows=9902 width=12) (actual time=0.039..2.882 rows=9902 loops=1)"
"          Filter: (b > 100)"
"  ->  CTE Scan on abc  (cost=0.00..198.04 rows=9902 width=24) (actual time=0.047..7.151 rows=9902 loops=1)"
"Total runtime: 9.376 ms"

2/ Second query: sum(pg_column_size(table.id)) + sum(pg_column_size(table.a)) + sum(pg_column_size(table.b))

with abc as 
(
 select id, a, b 
 from index_test where b > 100
)
select  pg_size_pretty((sum(pg_column_size(id)))  + (sum(pg_column_size(b))) + (sum(pg_column_size(a))))
from abc  -- "116 kB"

And query's explanation:

"Aggregate  (cost=526.57..526.59 rows=1 width=12) (actual time=10.959..10.959 rows=1 loops=1)"
"  CTE abc"
"    ->  Seq Scan on index_test  (cost=0.00..180.00 rows=9902 width=12) (actual time=0.035..2.780 rows=9902 loops=1)"
"          Filter: (b > 100)"
"  ->  CTE Scan on abc  (cost=0.00..198.04 rows=9902 width=12) (actual time=0.039..5.623 rows=9902 loops=1)"
"Total runtime: 11.173 ms"

3/ Results:

First query: 348 KB

Second query: 116 KB ( pg_column_size(id) = 39 KB ...)

I thought both query had to return the same result, but size of first query = 3 * size of second query, It made me confused. In first explanation, the "width = 24 bytes / row " (instead of 12), I wondered why it is increase, I think it is the clue to the question . Until now, I can not find clear answers for my question, please help me out .

Answer 1

The 24 byte/row difference rings a bell: it is the same size as the row header in tables and indexes. What I did not realize up until your question is that the same header size applies apparently to every row you return in a query:

SELECT pg_column_size((1::integer, 2::smallint));
 pg_column_size 
────────────────
             30

but

SELECT pg_column_size(1::integer) + pg_column_size(1::smallint) AS pg_column_size;
 pg_column_size 
────────────────
              6

When passing a row to pg_column_size(), it calculates the size of the whole row, including the header.

Now playing a bit further with this, there are some interesting facts you can find.

For example, one can check the difference caused by padding the columns up to the nearest 4 bytes (where k is a positive integer):

SELECT pg_column_size((1::smallint, 2::integer));
 pg_column_size 
────────────────
             32

Here we got two more bytes than in the first example, the remaining 2 bytes after the smallint got 'wasted'. This may be important when choosing the order of columns in huge tables - for example, we can squeeze something into that 'empty' space:

SELECT pg_column_size((1::smallint, FALSE, FALSE, 2::integer));
 pg_column_size 
────────────────
             32

The other important thing is that columns that have NULL as a value have zero size here:

SELECT pg_column_size((NULL::smallint, 2::integer));
 pg_column_size 
────────────────
             28

In contrast, you'll get NULL as a result when trying to add up the column individually:

SELECT pg_column_size(NULL::smallint) +  pg_column_size(2::integer) AS pg_column_size;
 pg_column_size 
────────────────
           NULL