Benchmarks, tricky queries, etc.
Created
January 6, 2022 22:42
-
-
Save alexklibisz/0b354e3c11aa179a16b10c2ab8cea285 to your computer and use it in GitHub Desktop.
Postgres Misc.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 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[]); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment