Update performance docs to take TPC into account

[roji]

We have an inheritance mapping section in the perf docs which compares TPH and TPT. Aside from adding TPC into the mix, we should consider moving that to the modeling page on inheritance (and linking from the perf docs) - perf should be an essential factor in the decision of which strategy to use.

Also point sparse columns to our own docs rather than to the SQL Server ones.

[roji]

Following is a perf investigation that compares TPH and TPC scenarios, both for disk space used and for query performance.

Some key findings:

• Overall, I'm less convinced TPH is a better general choice than TPC.
  • UNION ALL is generally cheap, making multi-type TPC queries reasonable (different from TPT's JOIN).
  • Users may define an index for faster filtering on the discriminator which TPC doesn't need.
    • In some scenarios this can a source of query slowdown compared to a table scan. Note that SQL Server avoids using an index if it isn't highly selective: for 5 discriminator values over 1 million rows, SQL server prefers a table scan (but not PostgreSQL).
    • If the application wants to filter by another column ("get me all dogs with name X"), then an index is needed anyway (composite in the TPH case, with the discriminator), so this advantage of TPC probably disappears.
    • The additional index also slows down updates.
• SQL Server sparse columns are really useful for wide-table TPH scenarios with lots of nulls.
• There's considerable database variance here (between SQL Server and PostgreSQL).

Table size

All scenarios have a total of 1 million rows, split across 5 different "types".

SQL Server

Scenario name	reserved	data	index_size	unused	description
TPH_Wide	179344 KB	121800 KB	57472 KB	72 KB	1 table with 50 columns, only 5 actually ever populated (45 contain only nulls)
TPH_Sparse_Wide	121104 KB	63912 KB	57128 KB	64 KB	1 table with 50 sparse columns, only 5 actually ever populated (45 contain only nulls)
TPH_Narrow	118416 KB	60048 KB	58280 KB	88 KB	1 table with 5 columns
TPH_Sparse_Narrow	121872 KB	63912 KB	57896 KB	64 KB	1 table with 5 sparse columns
TPC (all tables)	34848 KB	34816 KB	8 KB	0 KB	5 tables with 1 column each

Table size data above was obtained with sp_spaceused.

PostgreSQL

Scenario name	table size	indexes size	description
TPH_Wide	54 MB	6320 kB	1 table with 50 columns, only 5 actually ever populated (45 contain only nulls)
TPH_Narrow	46 MB	6320 kB	1 table with 5 columns
TPC (all tables)	42 MB	0 bytes	5 tables with 1 column each

Query performance

SQL Server

Select single type

This queries for a single type by filtering on the Discriminator in TPH, or simply selecting from the appropriate table in TPC.

Scenario name	Total subtree cost	Query
TPH_Wide	11.634537	SELECT id FROM TPH_Wide WHERE Discriminator='Type3'
TPH_Sparse_Wide	6.274537	SELECT id FROM TPH_Sparse_Wide WHERE Discriminator='Type3'
TPH_Narrow	5.916759	SELECT id FROM TPH_Narrow WHERE Discriminator='Type3'
TPH_Sparse_Narrow	6.274537	SELECT id FROM TPH_Sparse_Narrow WHERE Discriminator='Type3'
TPC	1.0284672	SELECT id FROM Type3

Plan for TPH_Wide:

StmtText	StmtId	NodeId	Parent	PhysicalOp	LogicalOp	Argument	DefinedValues	EstimateRows	EstimateIO	EstimateCPU	AvgRowSize	TotalSubtreeCost	OutputList	Warnings	Type	Parallel	EstimateExecutions
SELECT id FROM TPH_Sparse_Narrow WHERE Discriminator='Type3'	1	1	0	NULL	NULL	1	NULL	200000	NULL	NULL	NULL	6.274537	NULL	NULL	SELECT	false	NULL
|--Parallelism(Gather Streams)	1	2	1	Parallelism	Gather Streams	NULL	NULL	200000	0	0.09095	11	6.274537	[test].[dbo].[TPH_Sparse_Narrow].[Id]	NULL	PLAN_ROW	true	1
|--Table Scan(OBJECT:([test].[dbo].[TPH_Sparse_Narrow]), WHERE:([test].[dbo].[TPH_Sparse_Narrow].[Discriminator]=N'Type3'))	1	3	2	Table Scan	Table Scan	OBJECT:([test].[dbo].[TPH_Sparse_Narrow]), WHERE:([test].[dbo].[TPH_Sparse_Narrow].[Discriminator]=N'Type3')	[test].[dbo].[TPH_Sparse_Narrow].[Id]	200000	5.9202404	0.18334642	25	6.103587	[test].[dbo].[TPH_Sparse_Narrow].[Id]	NULL	PLAN_ROW	true	1

Note that SQL Server chooses to do a table scan rather than use the index (see this SO); a different composition distribution of discriminator values would change this.

Select all types

This queries for all types by not filtering on the Discriminator in TPH, or using UNION WITH on all 5 tables in TPC.

Scenario name	Total subtree cost	Query
TPH_Wide	12.380319	SELECT id FROM TPH_Wide
TPH_Sparse_Wide	7.020319	SELECT id FROM TPH_Sparse_Wide
TPH_Narrow	6.6625414	SELECT id FROM TPH_Narrow
TPH_Sparse_Narrow	7.020319	SELECT id FROM TPH_Sparse_Narrow
TPC	5.242336	SELECT id FROM Type1 UNION ALL SELECT id FROM Type 2 UNION WITH ...

Note sure how TPC can be faster than the others...

PostgreSQL

Select single type

This queries for a single type by filtering on the Discriminator in TPH, or simply selecting from the appropriate table in TPC.

Scenario name	Cost	Query
TPH_Wide	2162.15..11497.90	SELECT id FROM TPH_Wide WHERE Discriminator='Type3'
TPH_Narrow	2149.88..10498.29	SELECT id FROM TPH_Narrow WHERE Discriminator='Type3'
TPC	0.00..3081.00	SELECT id FROM Type3

Plan for TPH_Wide:

QUERY PLAN
Bitmap Heap Scan on tph_wide (cost=2162.15..11497.90 rows=197900 width=4)
Recheck Cond: (discriminator = 'Type3'::text)
-> Bitmap Index Scan on ix_discriminator_wide (cost=0.00..2112.68 rows=197900 width=0)
Index Cond: (discriminator = 'Type3'::text)

Select all types

This queries for all types by not filtering on the Discriminator in TPH, or using UNION WITH on all 5 tables in TPC.

Scenario name	Cost	Query
TPH_Wide	0.00..16862.00	SELECT id FROM TPH_Wide
TPH_Narrow	0.00..15888.00	SELECT id FROM TPH_Narrow
TPC	0.00..20405	SELECT id FROM Type1 UNION ALL SELECT id FROM Type 2 UNION WITH ...

SQL used

SQL Server

------ Set up TPH
DROP TABLE IF EXISTS TPH_Wide;
CREATE TABLE TPH_Wide (
    Id int NOT NULL IDENTITY,
    Discriminator nvarchar(10) NOT NULL,
    Col1 nvarchar(max) NULL,
    Col2 nvarchar(max) NULL,
    Col3 nvarchar(max) NULL,
    Col4 nvarchar(max) NULL,
    Col5 nvarchar(max) NULL,
    Col6 nvarchar(max) NULL,
    Col7 nvarchar(max) NULL,
    Col8 nvarchar(max) NULL,
    Col9 nvarchar(max) NULL,
    Col10 nvarchar(max) NULL,
    Col11 nvarchar(max) NULL,
    Col12 nvarchar(max) NULL,
    Col13 nvarchar(max) NULL,
    Col14 nvarchar(max) NULL,
    Col15 nvarchar(max) NULL,
    Col16 nvarchar(max) NULL,
    Col17 nvarchar(max) NULL,
    Col18 nvarchar(max) NULL,
    Col19 nvarchar(max) NULL,
    Col20 nvarchar(max) NULL,
    Col21 nvarchar(max) NULL,
    Col22 nvarchar(max) NULL,
    Col23 nvarchar(max) NULL,
    Col24 nvarchar(max) NULL,
    Col25 nvarchar(max) NULL,
    Col26 nvarchar(max) NULL,
    Col27 nvarchar(max) NULL,
    Col28 nvarchar(max) NULL,
    Col29 nvarchar(max) NULL,
    Col30 nvarchar(max) NULL,
    Col31 nvarchar(max) NULL,
    Col32 nvarchar(max) NULL,
    Col33 nvarchar(max) NULL,
    Col34 nvarchar(max) NULL,
    Col35 nvarchar(max) NULL,
    Col36 nvarchar(max) NULL,
    Col37 nvarchar(max) NULL,
    Col38 nvarchar(max) NULL,
    Col39 nvarchar(max) NULL,
    Col40 nvarchar(max) NULL,
    Col41 nvarchar(max) NULL,
    Col42 nvarchar(max) NULL,
    Col43 nvarchar(max) NULL,
    Col44 nvarchar(max) NULL,
    Col45 nvarchar(max) NULL,
    Col46 nvarchar(max) NULL,
    Col47 nvarchar(max) NULL,
    Col48 nvarchar(max) NULL,
    Col49 nvarchar(max) NULL,
    Col50 nvarchar(max) NULL
);
CREATE INDEX IX_Discriminator ON TPH_Wide(Discriminator);

DROP TABLE IF EXISTS TPH_Sparse_Wide;
CREATE TABLE TPH_Sparse_Wide (
    Id int NOT NULL IDENTITY,
    Discriminator nvarchar(10) NOT NULL,
    Col1 nvarchar(max) SPARSE NULL,
    Col2 nvarchar(max) SPARSE NULL,
    Col3 nvarchar(max) SPARSE NULL,
    Col4 nvarchar(max) SPARSE NULL,
    Col5 nvarchar(max) SPARSE NULL,
    Col6 nvarchar(max) SPARSE NULL,
    Col7 nvarchar(max) SPARSE NULL,
    Col8 nvarchar(max) SPARSE NULL,
    Col9 nvarchar(max) SPARSE NULL,
    Col10 nvarchar(max) SPARSE NULL,
    Col11 nvarchar(max) SPARSE NULL,
    Col12 nvarchar(max) SPARSE NULL,
    Col13 nvarchar(max) SPARSE NULL,
    Col14 nvarchar(max) SPARSE NULL,
    Col15 nvarchar(max) SPARSE NULL,
    Col16 nvarchar(max) SPARSE NULL,
    Col17 nvarchar(max) SPARSE NULL,
    Col18 nvarchar(max) SPARSE NULL,
    Col19 nvarchar(max) SPARSE NULL,
    Col20 nvarchar(max) SPARSE NULL,
    Col21 nvarchar(max) SPARSE NULL,
    Col22 nvarchar(max) SPARSE NULL,
    Col23 nvarchar(max) SPARSE NULL,
    Col24 nvarchar(max) SPARSE NULL,
    Col25 nvarchar(max) SPARSE NULL,
    Col26 nvarchar(max) SPARSE NULL,
    Col27 nvarchar(max) SPARSE NULL,
    Col28 nvarchar(max) SPARSE NULL,
    Col29 nvarchar(max) SPARSE NULL,
    Col30 nvarchar(max) SPARSE NULL,
    Col31 nvarchar(max) SPARSE NULL,
    Col32 nvarchar(max) SPARSE NULL,
    Col33 nvarchar(max) SPARSE NULL,
    Col34 nvarchar(max) SPARSE NULL,
    Col35 nvarchar(max) SPARSE NULL,
    Col36 nvarchar(max) SPARSE NULL,
    Col37 nvarchar(max) SPARSE NULL,
    Col38 nvarchar(max) SPARSE NULL,
    Col39 nvarchar(max) SPARSE NULL,
    Col40 nvarchar(max) SPARSE NULL,
    Col41 nvarchar(max) SPARSE NULL,
    Col42 nvarchar(max) SPARSE NULL,
    Col43 nvarchar(max) SPARSE NULL,
    Col44 nvarchar(max) SPARSE NULL,
    Col45 nvarchar(max) SPARSE NULL,
    Col46 nvarchar(max) SPARSE NULL,
    Col47 nvarchar(max) SPARSE NULL,
    Col48 nvarchar(max) SPARSE NULL,
    Col49 nvarchar(max) SPARSE NULL,
    Col50 nvarchar(max) SPARSE NULL
);
CREATE INDEX IX_Discriminator ON TPH_Sparse_Wide(Discriminator);

DROP TABLE IF EXISTS TPH_Narrow;
CREATE TABLE TPH_Narrow (
                     Id int NOT NULL IDENTITY,
                     Discriminator nvarchar(10) NOT NULL,
                     Col10 nvarchar(max) NULL,
                     Col20 nvarchar(max) NULL,
                     Col30 nvarchar(max) NULL,
                     Col40 nvarchar(max) NULL,
                     Col50 nvarchar(max) NULL
);
CREATE INDEX IX_Discriminator ON TPH_Narrow(Discriminator);

DROP TABLE IF EXISTS TPH_Sparse_Narrow;
CREATE TABLE TPH_Sparse_Narrow (
                            Id int NOT NULL IDENTITY,
                            Discriminator nvarchar(10) NOT NULL,
                            Col10 nvarchar(max) SPARSE NULL,
                            Col20 nvarchar(max) SPARSE NULL,
                            Col30 nvarchar(max) SPARSE NULL,
                            Col40 nvarchar(max) SPARSE NULL,
                            Col50 nvarchar(max) SPARSE NULL
);
CREATE INDEX IX_Discriminator ON TPH_Sparse_Narrow(Discriminator);

BEGIN TRANSACTION;

DECLARE @i INT = 0;
WHILE @i < 200000
BEGIN
    INSERT INTO TPH_Wide (Discriminator, Col10) VALUES ('Type1', CAST(@i AS NVARCHAR(MAX)));
    INSERT INTO TPH_Wide (Discriminator, Col20) VALUES ('Type2', CAST(@i AS NVARCHAR(MAX)));
    INSERT INTO TPH_Wide (Discriminator, Col30) VALUES ('Type3', CAST(@i AS NVARCHAR(MAX)));
    INSERT INTO TPH_Wide (Discriminator, Col40) VALUES ('Type4', CAST(@i AS NVARCHAR(MAX)));
    INSERT INTO TPH_Wide (Discriminator, Col50) VALUES ('Type5', CAST(@i AS NVARCHAR(MAX)));

    INSERT INTO TPH_Sparse_Wide (Discriminator, Col10) VALUES ('Type1', CAST(@i AS NVARCHAR(MAX)));
    INSERT INTO TPH_Sparse_Wide (Discriminator, Col20) VALUES ('Type2', CAST(@i AS NVARCHAR(MAX)));
    INSERT INTO TPH_Sparse_Wide (Discriminator, Col30) VALUES ('Type3', CAST(@i AS NVARCHAR(MAX)));
    INSERT INTO TPH_Sparse_Wide (Discriminator, Col40) VALUES ('Type4', CAST(@i AS NVARCHAR(MAX)));
    INSERT INTO TPH_Sparse_Wide (Discriminator, Col50) VALUES ('Type5', CAST(@i AS NVARCHAR(MAX)));

    INSERT INTO TPH_Narrow (Discriminator, Col10) VALUES ('Type1', CAST(@i AS NVARCHAR(MAX)));
    INSERT INTO TPH_Narrow (Discriminator, Col20) VALUES ('Type2', CAST(@i AS NVARCHAR(MAX)));
    INSERT INTO TPH_Narrow (Discriminator, Col30) VALUES ('Type3', CAST(@i AS NVARCHAR(MAX)));
    INSERT INTO TPH_Narrow (Discriminator, Col40) VALUES ('Type4', CAST(@i AS NVARCHAR(MAX)));
    INSERT INTO TPH_Narrow (Discriminator, Col50) VALUES ('Type5', CAST(@i AS NVARCHAR(MAX)));

    INSERT INTO TPH_Sparse_Narrow (Discriminator, Col10) VALUES ('Type1', CAST(@i AS NVARCHAR(MAX)));
    INSERT INTO TPH_Sparse_Narrow (Discriminator, Col20) VALUES ('Type2', CAST(@i AS NVARCHAR(MAX)));
    INSERT INTO TPH_Sparse_Narrow (Discriminator, Col30) VALUES ('Type3', CAST(@i AS NVARCHAR(MAX)));
    INSERT INTO TPH_Sparse_Narrow (Discriminator, Col40) VALUES ('Type4', CAST(@i AS NVARCHAR(MAX)));
    INSERT INTO TPH_Sparse_Narrow (Discriminator, Col50) VALUES ('Type5', CAST(@i AS NVARCHAR(MAX)));

    SET @i = @i + 1
END;

COMMIT;

------ Set up TPC
DROP TABLE IF EXISTS Type1;
DROP TABLE IF EXISTS Type2;
DROP TABLE IF EXISTS Type3;
DROP TABLE IF EXISTS Type4;
DROP TABLE IF EXISTS Type5;

CREATE TABLE Type1 (
    Id int NOT NULL IDENTITY,
    Col10 nvarchar(max) NULL
);

CREATE TABLE Type2 (
    Id int NOT NULL IDENTITY,
    Col20 nvarchar(max) NULL
);

CREATE TABLE Type3 (
    Id int NOT NULL IDENTITY,
    Col30 nvarchar(max) NULL
);

CREATE TABLE Type4 (
    Id int NOT NULL IDENTITY,
    Col40 nvarchar(max) NULL
);

CREATE TABLE Type5 (
     Id int NOT NULL IDENTITY,
     Col50 nvarchar(max) NULL
);

-- Seed 1 million rows
BEGIN TRANSACTION;

DECLARE @i INT = 0;
WHILE @i < 200000
BEGIN
    INSERT INTO Type1 (Col10) VALUES (CAST(@i AS NVARCHAR(MAX)));
    INSERT INTO Type2 (Col20) VALUES (CAST(@i AS NVARCHAR(MAX)));
    INSERT INTO Type3 (Col30) VALUES (CAST(@i AS NVARCHAR(MAX)));
    INSERT INTO Type4 (Col40) VALUES (CAST(@i AS NVARCHAR(MAX)));
    INSERT INTO Type5 (Col50) VALUES (CAST(@i AS NVARCHAR(MAX)));
    SET @i = @i + 1
END;

COMMIT;

-- Space
sp_spaceused TPH_Wide;
sp_spaceused TPH_Sparse_Wide;
sp_spaceused TPH_Narrow;
sp_spaceused TPH_Sparse_Narrow;

sp_spaceused Type1;
sp_spaceused Type2;
sp_spaceused Type3;
sp_spaceused Type4;
sp_spaceused Type5;

SET SHOWPLAN_ALL ON;

-- Query for single entity type
SELECT id FROM TPH_Wide WHERE Discriminator='Type3';
SELECT id FROM TPH_Sparse_Wide WHERE Discriminator='Type3';
SELECT id FROM TPH_Narrow WHERE Discriminator='Type3';
SELECT id FROM TPH_Sparse_Narrow WHERE Discriminator='Type3';

SELECT id FROM Type3;

-- Query for all entity types
SELECT id FROM TPH_Wide;
SELECT id FROM TPH_Sparse_Wide;
SELECT id FROM TPH_Narrow;
SELECT id FROM TPH_Sparse_Narrow;

SELECT id FROM Type1
UNION ALL
SELECT id FROM Type2
UNION ALL
SELECT id FROM Type3
UNION ALL
SELECT id FROM Type4
UNION ALL
SELECT id FROM Type5;

SET SHOWPLAN_ALL OFF;

PostgreSQL

------ Set up TPH
DROP TABLE IF EXISTS TPH_Wide;
CREATE TABLE TPH_Wide (
    Id int NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    Discriminator text NOT NULL,
    Col1 text NULL,
    Col2 text NULL,
    Col3 text NULL,
    Col4 text NULL,
    Col5 text NULL,
    Col6 text NULL,
    Col7 text NULL,
    Col8 text NULL,
    Col9 text NULL,
    Col10 text NULL,
    Col11 text NULL,
    Col12 text NULL,
    Col13 text NULL,
    Col14 text NULL,
    Col15 text NULL,
    Col16 text NULL,
    Col17 text NULL,
    Col18 text NULL,
    Col19 text NULL,
    Col20 text NULL,
    Col21 text NULL,
    Col22 text NULL,
    Col23 text NULL,
    Col24 text NULL,
    Col25 text NULL,
    Col26 text NULL,
    Col27 text NULL,
    Col28 text NULL,
    Col29 text NULL,
    Col30 text NULL,
    Col31 text NULL,
    Col32 text NULL,
    Col33 text NULL,
    Col34 text NULL,
    Col35 text NULL,
    Col36 text NULL,
    Col37 text NULL,
    Col38 text NULL,
    Col39 text NULL,
    Col40 text NULL,
    Col41 text NULL,
    Col42 text NULL,
    Col43 text NULL,
    Col44 text NULL,
    Col45 text NULL,
    Col46 text NULL,
    Col47 text NULL,
    Col48 text NULL,
    Col49 text NULL,
    Col50 text NULL
);
CREATE INDEX IX_Discriminator_Wide ON TPH_Wide(Discriminator);

DROP TABLE IF EXISTS TPH_Narrow;
CREATE TABLE TPH_Narrow (
    Id int NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    Discriminator text NOT NULL,
    Col10 text NULL,
    Col20 text NULL,
    Col30 text NULL,
    Col40 text NULL,
    Col50 text NULL
);
CREATE INDEX IX_Discriminator_Narrow ON TPH_Narrow(Discriminator);

------ Set up TPC
DROP TABLE IF EXISTS Type1;
DROP TABLE IF EXISTS Type2;
DROP TABLE IF EXISTS Type3;
DROP TABLE IF EXISTS Type4;
DROP TABLE IF EXISTS Type5;

CREATE TABLE Type1 (
    Id int NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    Col10 text NULL
);

CREATE TABLE Type2 (
    Id int NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    Col20 text NULL
);

CREATE TABLE Type3 (
    Id int NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    Col30 text NULL
);

CREATE TABLE Type4 (
    Id int NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    Col40 text NULL
);

CREATE TABLE Type5 (
    Id int NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    Col50 text NULL
);

-- Seed 1 million rows
DO $$BEGIN
FOR i IN 1..200000 LOOP
    INSERT INTO TPH_Wide (Discriminator, Col10) VALUES ('Type1', i::text);
    INSERT INTO TPH_Wide (Discriminator, Col20) VALUES ('Type2', i::text);
    INSERT INTO TPH_Wide (Discriminator, Col30) VALUES ('Type3', i::text);
    INSERT INTO TPH_Wide (Discriminator, Col40) VALUES ('Type4', i::text);
    INSERT INTO TPH_Wide (Discriminator, Col50) VALUES ('Type5', i::text);

    INSERT INTO TPH_Narrow (Discriminator, Col10) VALUES ('Type1', i::text);
    INSERT INTO TPH_Narrow (Discriminator, Col20) VALUES ('Type2', i::text);
    INSERT INTO TPH_Narrow (Discriminator, Col30) VALUES ('Type3', i::text);
    INSERT INTO TPH_Narrow (Discriminator, Col40) VALUES ('Type4', i::text);
    INSERT INTO TPH_Narrow (Discriminator, Col50) VALUES ('Type5', i::text);

    INSERT INTO Type1 (Col10) VALUES (i::text);
    INSERT INTO Type2 (Col20) VALUES (i::text);
    INSERT INTO Type3 (Col30) VALUES (i::text);
    INSERT INTO Type4 (Col40) VALUES (i::text);
    INSERT INTO Type5 (Col50) VALUES (i::text);
END LOOP;
END$$;

VACUUM ANALYZE TPH_Wide;
VACUUM ANALYZE TPH_Narrow;
VACUUM ANALYZE Type1;
VACUUM ANALYZE Type2;
VACUUM ANALYZE Type3;
VACUUM ANALYZE Type4;
VACUUM ANALYZE Type5;

-- Space
SELECT pg_size_pretty(pg_total_relation_size('TPH_Wide')); -- 60 MB
SELECT pg_size_pretty(pg_total_relation_size('TPH_Narrow')); -- 52 MB

SELECT pg_size_pretty(pg_table_size('TPH_Wide')) AS table_size, pg_size_pretty(pg_indexes_size('TPH_Wide')) AS indexes_size; -- 54 MB, 6320 kB
SELECT pg_size_pretty(pg_table_size('TPH_Narrow')) AS table_size, pg_size_pretty(pg_indexes_size('TPH_Narrow')) AS indexes_size; -- 46 MB, 6320 kB

SELECT
    pg_size_pretty(
        pg_table_size('Type1') +
        pg_table_size('Type2') +
        pg_table_size('Type3') +
        pg_table_size('Type4') +
        pg_table_size('Type5')) AS tables_size, -- 42 MB
    pg_size_pretty(
        pg_indexes_size('Type1') +
        pg_indexes_size('Type2') +
        pg_indexes_size('Type3') +
        pg_indexes_size('Type4') +
        pg_indexes_size('Type5')) AS indexes_size; -- 0 bytes

-- Query for single entity type
EXPLAIN SELECT id FROM TPH_Wide WHERE Discriminator='Type3';
EXPLAIN SELECT id FROM TPH_Narrow WHERE Discriminator='Type3';

EXPLAIN SELECT id FROM Type3;

-- Query for all entity types
EXPLAIN SELECT id FROM TPH_Wide;
EXPLAIN SELECT id FROM TPH_Narrow;

EXPLAIN SELECT id FROM Type1
UNION ALL
SELECT id FROM Type2
UNION ALL
SELECT id FROM Type3
UNION ALL
SELECT id FROM Type4
UNION ALL
SELECT id FROM Type5;

[roji]

My personal guidance would be:

• If your app will mostly query a single type, use TPC over TPH.
  • Table size is smaller: no null columns, no discriminator.
  • No index ever needed on the discriminator column, which would slow down updates and possibly also queries. An index may not be needed on TPH either, but that depends on various factors.
• If your app will mostly query multiple types (e.g. the base type), lean towards TPH.
  • If using TPH on SQL Server, definitely consider using sparse columns for columns that will be rarely populated (consult the SQL Server docs).
• Use TPT only if constrained to do so by an existing database schema or an external data normalization constraint.