jointest.sql

text/plain

Download

-- jointest.sql
 
-- 18.10.2019
 
drop table if exists tmaster;

create table tmaster (
id_t1 integer,
t1_season integer,
t1_id_t2 integer,
t1_value integer,
t1_cdescr varchar,
primary key (id_t1)
);

--


select setseed (0.34512);

insert into tmaster
select
 inum
,iseason
,row_number () over () as irow
,irandom
,'TXT: '||irandom::varchar
from (
select 
 inum::integer
,((inum>>20)+2)::integer as iseason
,inum::integer + (500000*random())::integer as irandom
from generate_series (1,(1<<21)) as inum
order by irandom
)qg
-- limit 50
;

alter table tmaster add constraint uk_master_season_id unique (t1_season,id_t1);



drop table if exists tfact;

create table tfact (
id_t2 integer,
t2_season integer,
t2_value integer,
t2_cdescr varchar,
primary key (id_t2)
);

--


select setseed (-0.76543);

insert into tfact
select
 qg.*
,'FKT: '||irandom::varchar
from (
select 
 inum::integer
,((inum>>20)+2)::integer as iseason
,inum::integer + (500000*random())::integer as irandom
from generate_series (1,(1<<21)) as inum
order by irandom
)qg
-- limit 50
;

alter table tfact add constraint uk_fact_season_id unique (t2_season,id_t2);

-----------------

-- slower:

explain (analyze, verbose, costs, settings, buffers)
select *
from tmaster
left join tfact on id_t2=t1_id_t2 and t2_season=t1_season
where t1_season=3
;

-- faster by setting a constant in left join on condition:

explain (analyze, verbose, costs, settings, buffers)
select *
from tmaster
left join tfact on id_t2=t1_id_t2 and t2_season=3 --t1_season
where t1_season=3
;