Rough comparison of query performance of int/text columns with hash index and partitioned tables

index.sql

-- surrogate & text keys
create table ni (
  key serial primary key,
  id text UNIQUE,
  dataset_key int,
  name text
);

create table ti (
  key serial primary key,
  id text UNIQUE,
  dataset_key int,
  name_key int
);

-- text keys
create table nt (
  id text primary key,
  dataset_key int,
  name text
);

create table tt (
  id text primary key,
  dataset_key int,
  name_id text
);

-- hash index
create table nth (
  id text not null,
  dataset_key int,
  name text
);


-- partitions
create table ntp (
  id text not null,
  dataset_key int,
  name text
) PARTITION BY LIST (dataset_key);

create table ttp (
  id text not null,
  dataset_key int,
  name_id text
) PARTITION BY LIST (dataset_key);


-- insert 10 million test records per table
INSERT INTO ni (key, id, dataset_key, name)
SELECT x.key, '#n' || key, floor(log(x.key)), 'Abies alba sp #' || x.key
  FROM generate_series(1,10000000) AS x(key);

INSERT INTO ti (key, id, dataset_key, name_key)
SELECT x.key, '#t' || key, floor(log(x.key)), x.key
  FROM generate_series(1,10000000) AS x(key);


INSERT INTO nt (id, dataset_key, name)
SELECT x.key::text, floor(log(x.key)), 'Abies alba sp #' || x.key
  FROM generate_series(1,10000000) AS x(key);

INSERT INTO tt (id, dataset_key, name_id)
SELECT x.key::text, floor(log(x.key)), x.key::text
  FROM generate_series(1,10000000) AS x(key);


INSERT INTO nth (id, dataset_key, name)
SELECT x.key::text, floor(log(x.key)), 'Abies alba sp #' || x.key
  FROM generate_series(1,10000000) AS x(key);
CREATE INDEX ON nth USING hash (id);


CREATE TABLE ntp_0 PARTITION OF ntp FOR VALUES IN ( 0 );
CREATE TABLE ntp_1 PARTITION OF ntp FOR VALUES IN ( 1 );
CREATE TABLE ntp_2 PARTITION OF ntp FOR VALUES IN ( 2 );
CREATE TABLE ntp_3 PARTITION OF ntp FOR VALUES IN ( 3 );
CREATE TABLE ntp_4 PARTITION OF ntp FOR VALUES IN ( 4 );
CREATE TABLE ntp_5 PARTITION OF ntp FOR VALUES IN ( 5 );
CREATE TABLE ntp_6 PARTITION OF ntp FOR VALUES IN ( 6 );
CREATE TABLE ntp_7 PARTITION OF ntp FOR VALUES IN ( 7 );
CREATE TABLE ntp_8 PARTITION OF ntp FOR VALUES IN ( 8 );
insert into ntp select * from nt;
ALTER TABLE ntp_0 ADD PRIMARY KEY(id);
ALTER TABLE ntp_1 ADD PRIMARY KEY(id);
ALTER TABLE ntp_2 ADD PRIMARY KEY(id);
ALTER TABLE ntp_3 ADD PRIMARY KEY(id);
ALTER TABLE ntp_4 ADD PRIMARY KEY(id);
ALTER TABLE ntp_5 ADD PRIMARY KEY(id);
ALTER TABLE ntp_6 ADD PRIMARY KEY(id);
ALTER TABLE ntp_7 ADD PRIMARY KEY(id);
ALTER TABLE ntp_8 ADD PRIMARY KEY(id);

CREATE TABLE ttp_0 PARTITION OF ttp FOR VALUES IN ( 0 );
CREATE TABLE ttp_1 PARTITION OF ttp FOR VALUES IN ( 1 );
CREATE TABLE ttp_2 PARTITION OF ttp FOR VALUES IN ( 2 );
CREATE TABLE ttp_3 PARTITION OF ttp FOR VALUES IN ( 3 );
CREATE TABLE ttp_4 PARTITION OF ttp FOR VALUES IN ( 4 );
CREATE TABLE ttp_5 PARTITION OF ttp FOR VALUES IN ( 5 );
CREATE TABLE ttp_6 PARTITION OF ttp FOR VALUES IN ( 6 );
CREATE TABLE ttp_7 PARTITION OF ttp FOR VALUES IN ( 7 );
CREATE TABLE ttp_8 PARTITION OF ttp FOR VALUES IN ( 8 );
insert into ttp select * from tt;
ALTER TABLE ttp_0 ADD PRIMARY KEY(id);
ALTER TABLE ttp_1 ADD PRIMARY KEY(id);
ALTER TABLE ttp_2 ADD PRIMARY KEY(id);
ALTER TABLE ttp_3 ADD PRIMARY KEY(id);
ALTER TABLE ttp_4 ADD PRIMARY KEY(id);
ALTER TABLE ttp_5 ADD PRIMARY KEY(id);
ALTER TABLE ttp_6 ADD PRIMARY KEY(id);
ALTER TABLE ttp_7 ADD PRIMARY KEY(id);
ALTER TABLE ttp_8 ADD PRIMARY KEY(id);


ANALYZE VERBOSE;


EXPLAIN ANALYZE  SELECT * from ti JOIN ni ON ti.name_key = ni.key WHERE ti.key = 356278;
-- Planning time: 0.315 ms
-- Execution time: 0.081 ms
EXPLAIN ANALYZE  SELECT * from tt JOIN nt ON tt.name_id = nt.id WHERE tt.id = '356278';
-- Planning time: 0.352 ms
-- Execution time: 0.112 ms
EXPLAIN ANALYZE  SELECT * from tt JOIN nth ON tt.name_id = nth.id WHERE tt.id = '356278';
-- Planning time: 0.335 ms
-- Execution time: 0.126 ms
EXPLAIN ANALYZE  SELECT * from ttp JOIN ntp ON ttp.name_id = ntp.id WHERE ttp.id = '356278';
-- Planning time: 1.510 ms
-- Execution time: 0.650 ms

EXPLAIN ANALYZE  SELECT * from ti JOIN ni ON ti.name_key = ni.key WHERE ti.key = 356278 AND ti.dataset_key=5;
-- Planning time: 0.418 ms
-- Execution time: 0.083 ms
EXPLAIN ANALYZE  SELECT * from tt JOIN nt ON tt.name_id = nt.id WHERE tt.id = '356278' AND tt.dataset_key=5;
-- Planning time: 0.405 ms
-- Execution time: 0.123 ms
EXPLAIN ANALYZE  SELECT * from tt JOIN nth ON tt.name_id = nth.id WHERE tt.id = '356278' AND tt.dataset_key=5;
-- Planning time: 0.367 ms
-- Execution time: 0.126 ms
EXPLAIN ANALYZE  SELECT * from ttp JOIN ntp ON ttp.name_id = ntp.id AND ttp.dataset_key=ntp.dataset_key WHERE ttp.id = '356278' AND ttp.dataset_key=5;
-- Planning time: 1.144 ms
-- Execution time: 0.126 ms

Hopefully much much better with pg11 partition pruning