Skip to content

Instantly share code, notes, and snippets.

@maxp
Created October 13, 2020 04:24
Show Gist options
  • Save maxp/480ed919a9ad96ef8fe95dfa7853e954 to your computer and use it in GitHub Desktop.
Save maxp/480ed919a9ad96ef8fe95dfa7853e954 to your computer and use it in GitHub Desktop.
-- postgresql 13 default
-- ubuntu, core i5, ssd
create table t1 (i bigint, c varchar(20), t text);
create table t2 (i bigint, c varchar(20), t text);
insert into t1 (i,c,t)
select random() * 1000000, '', '12345678912345678912345678900'
from generate_series(0,1000000);
insert into t2 (i,c,t)
select random() * 1000000, '', '12345678912345678912345678900'
from generate_series(0,1000000);
update t1 set c=i::varchar;
update t2 set c=i::varchar;
create index t1_i on t1(i);
create index t1_c on t1(c);
create index t2_i on t2(i);
create index t2_c on t2(c);
analyze t1;
analyze t2;
select count(*) from (
select distinct i from t1
) as d;
-- 164 ms
select count(*) from (
select distinct c from t1
) as d;
-- 186 ms
select count(*) from (
select distinct t1.c from t1 join t2 on t1.c = t2.c
) as d;
-- 825 ms
select count(*) from (
select distinct t1.i from t1 join t2 on t1.i = t2.i
) as d;
-- 526 ms
select count(*) from (
select t1.c from t1 join t2 on t1.c = t2.c
) as d;
-- 450 ms
select count(*) from (
select t1.i from t1 join t2 on t1.i = t2.i
) as d;
-- 420 ms
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment