0

My question refers to Postgresql 12.2.

I have two almost identical tables, structure- and contentwise, the main difference being that the primary key of the second table is a composite type, while the first table includes the components of the type directly.

CREATE TABLE first (
   a INTEGER,
   b SMALLINT,
   c SMALLINT,
   PRIMARY KEY (a,b,c),
   -- further fields: 3 SMALLINT, 6 INTEGER, 2 NUMERIC
);
CREATE TYPE abc_type AS (a INTEGER, b SMALLINT, c SMALLINT);
CREATE TABLE second (
   abc abc_type PRIMARY KEY,
   -- same further fields as above,
   extra BYTEA -- only 4 bytes long
);

Both tables contain the same number of records, roughly 2 billion. The size of table second is 1.25 times the size of table first, which may be partially attributed to the extra field.

But what's really surprising (to me) is the 2.5 times increase of index second_pkey compared to index first_pkey.

SELECT
  t.tablename,
  indexname,
  c.reltuples AS num_rows,
  pg_size_pretty(pg_relation_size(quote_ident(t.tablename)::text)) AS table_size,
  pg_size_pretty(pg_relation_size(quote_ident(indexrelname)::text)) AS index_size
FROM pg_tables t
  LEFT OUTER JOIN pg_class c ON t.tablename=c.relname
  LEFT OUTER JOIN
    ( SELECT c.relname AS ctablename, ipg.relname AS indexname, indexrelname FROM pg_index x
      JOIN pg_class c ON c.oid = x.indrelid
      JOIN pg_class ipg ON ipg.oid = x.indexrelid
      JOIN pg_stat_all_indexes psai ON x.indexreliddd = psai.indexrelid )
    AS foo
  ON t.tablename = foo.ctablename
WHERE indexname in ('first_pkey','second_pkey')
ORDER BY 1,2;
 tablename |  indexname  |   num_rows    | table_size | index_size 
-----------+-------------+---------------+------------+------------
 first     | first_pkey  | 1.9596214e+09 | 165 GB     | 62 GB
 second    | second_pkey | 1.9596214e+09 | 205 GB     | 160 GB

Is this difference in size a penalty for the composite type? Can it be avoided? The size might make a difference, not just regarding storage: 62 GB still fit into memory, while 160 GB is definitely beyond in my case.

2
  • I would stay away from custom (record) types like that. They are a nice idea, but don't really offer any benefits in my opinion. Jul 27 '20 at 10:29
  • The benefits are the same as for record types/objects in programming: In other tables, there are many fields of abc_type, and there are several functions handling abc_type. Not using abc_type means that queries become harder to read and harder to get right, since e.g. a comparison of two abc_types involves six fields and three comparisons in the first case and two fields and one comparison in the second case. I'd have expected that Postgresql is able to resolve internally the composite type as syntactic sugar and use it much like the separate fields.
    – gernot
    Jul 27 '20 at 10:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.