Postgres Misc.

000-postgres-misc.md

Benchmarks, tricky queries, etc.

20220106_benchmark_any_uuid_indexes.sql

-- 20220106_benchmark_any_uuid_indexes.sql
-- Benchmark: what's the fastest index for checking if a uuid column equals any of the uuids in an array of uuids.
-- Seems to be order fastest to slowest: btree, gin, gist. 
-- Run on Postgres 11.5.0

create extension btree_gist;
create extension btree_gin;

drop table if exists uuid_index_benchmark;
create table uuid_index_benchmark (
    id serial primary key,
    uuid1 uuid not null default gen_random_uuid(),
    uuid2 uuid not null default gen_random_uuid()
);
insert into uuid_index_benchmark (id)
select i from generate_series(0, 500000) as i;
select * from uuid_index_benchmark;

drop table if exists selected_uuids;
create temp table selected_uuids as (
select array_agg(x.uuid1) as uuid1 from (select uuid1 from uuid_index_benchmark order by random() limit 100) x
);

-- No indexes
drop index if exists uuid_index_benchmark_uuid1_idx_btree;
drop index if exists uuid_index_benchmark_uuid1_idx_gist;
drop index if exists uuid_index_benchmark_uuid1_idx_gin;
vacuum (analyze, full) uuid_index_benchmark;

-- ~360ms
explain analyze
select b.id, b.uuid1
from uuid_index_benchmark b
where b.uuid1 = any((select selected_uuids.uuid1 from selected_uuids limit 1)::uuid[]);


-- btree index.
drop index if exists uuid_index_benchmark_uuid1_idx_btree;
drop index if exists uuid_index_benchmark_uuid1_idx_gist;
drop index if exists uuid_index_benchmark_uuid1_idx_gin;
create index uuid_index_benchmark_uuid1_idx_btree on uuid_index_benchmark using btree(uuid1);
vacuum (analyze, full) uuid_index_benchmark;

-- ~2.25ms
explain analyze
select b.id, b.uuid1
from uuid_index_benchmark b
where b.uuid1 = any((select selected_uuids.uuid1 from selected_uuids limit 1)::uuid[]);


-- gist index.
drop index if exists uuid_index_benchmark_uuid1_idx_btree;
drop index if exists uuid_index_benchmark_uuid1_idx_gist;
drop index if exists uuid_index_benchmark_uuid1_idx_gin;
create index uuid_index_benchmark_uuid1_idx_gist on uuid_index_benchmark using gist(uuid1);
vacuum (analyze, full) uuid_index_benchmark;

-- ~4.25ms
explain analyze
select b.id, b.uuid1
from uuid_index_benchmark b
where b.uuid1 = any((select selected_uuids.uuid1 from selected_uuids limit 1)::uuid[]);

-- gin index.
drop index if exists uuid_index_benchmark_uuid1_idx_btree;
drop index if exists uuid_index_benchmark_uuid1_idx_gist;
drop index if exists uuid_index_benchmark_uuid1_idx_gin;
create index uuid_index_benchmark_uuid1_idx_gin on uuid_index_benchmark using gin(uuid1);
vacuum (analyze, full) uuid_index_benchmark;

-- ~2.25
explain analyze
select b.id, b.uuid1
from uuid_index_benchmark b
where b.uuid1 = any((select selected_uuids.uuid1 from selected_uuids limit 1)::uuid[]);