clear.sql

\timing
TRUNCATE variants_smallvariant;
ALTER SEQUENCE variants_smallvariant_id_seq RESTART WITH 1;

fill1.sql

\set AUTOCOMMIT 'off'
\copy "variants_smallvariant" ("release", "chromosome", "chromosome_no", "start", "end", "bin", "reference", "alternative", "var_type", "case_id", "set_id", "genotype", "num_hom_alt", "num_hom_ref", "num_het", "num_hemi_alt", "num_hemi_ref", "in_clinvar", "exac_frequency", "exac_homozygous", "exac_heterozygous", "exac_hemizygous", "thousand_genomes_frequency", "thousand_genomes_homozygous", "thousand_genomes_heterozygous", "thousand_genomes_hemizygous", "gnomad_exomes_frequency", "gnomad_exomes_homozygous", "gnomad_exomes_heterozygous", "gnomad_exomes_hemizygous", "gnomad_genomes_frequency", "gnomad_genomes_homozygous", "gnomad_genomes_heterozygous", "gnomad_genomes_hemizygous", "refseq_gene_id", "refseq_transcript_id", "refseq_transcript_coding", "refseq_hgvs_c", "refseq_hgvs_p", "refseq_effect", "ensembl_gene_id", "ensembl_transcript_id", "ensembl_transcript_coding", "ensembl_hgvs_c", "ensembl_hgvs_p", "ensembl_effect") FROM 'ISDBM322015_1.tsv' WITH CSV HEADER  DELIMITER E'\t' NULL '.';

fill2.sql

\set AUTOCOMMIT 'off'
\copy "variants_smallvariant" ("release", "chromosome", "chromosome_no", "start", "end", "bin", "reference", "alternative", "var_type", "case_id", "set_id", "genotype", "num_hom_alt", "num_hom_ref", "num_het", "num_hemi_alt", "num_hemi_ref", "in_clinvar", "exac_frequency", "exac_homozygous", "exac_heterozygous", "exac_hemizygous", "thousand_genomes_frequency", "thousand_genomes_homozygous", "thousand_genomes_heterozygous", "thousand_genomes_hemizygous", "gnomad_exomes_frequency", "gnomad_exomes_homozygous", "gnomad_exomes_heterozygous", "gnomad_exomes_hemizygous", "gnomad_genomes_frequency", "gnomad_genomes_homozygous", "gnomad_genomes_heterozygous", "gnomad_genomes_hemizygous", "refseq_gene_id", "refseq_transcript_id", "refseq_transcript_coding", "refseq_hgvs_c", "refseq_hgvs_p", "refseq_effect", "ensembl_gene_id", "ensembl_transcript_id", "ensembl_transcript_coding", "ensembl_hgvs_c", "ensembl_hgvs_p", "ensembl_effect") FROM 'ISDBM322015_2.tsv' WITH CSV HEADER  DELIMITER E'\t' NULL '.';

fill3.sql

\set AUTOCOMMIT 'off'
\copy "variants_smallvariant" ("release", "chromosome", "chromosome_no", "start", "end", "bin", "reference", "alternative", "var_type", "case_id", "set_id", "genotype", "num_hom_alt", "num_hom_ref", "num_het", "num_hemi_alt", "num_hemi_ref", "in_clinvar", "exac_frequency", "exac_homozygous", "exac_heterozygous", "exac_hemizygous", "thousand_genomes_frequency", "thousand_genomes_homozygous", "thousand_genomes_heterozygous", "thousand_genomes_hemizygous", "gnomad_exomes_frequency", "gnomad_exomes_homozygous", "gnomad_exomes_heterozygous", "gnomad_exomes_hemizygous", "gnomad_genomes_frequency", "gnomad_genomes_homozygous", "gnomad_genomes_heterozygous", "gnomad_genomes_hemizygous", "refseq_gene_id", "refseq_transcript_id", "refseq_transcript_coding", "refseq_hgvs_c", "refseq_hgvs_p", "refseq_effect", "ensembl_gene_id", "ensembl_transcript_id", "ensembl_transcript_coding", "ensembl_hgvs_c", "ensembl_hgvs_p", "ensembl_effect") FROM 'ISDBM322015_3.tsv' WITH CSV HEADER  DELIMITER E'\t' NULL '.';

fill4.sql

\set AUTOCOMMIT 'off'
\copy "variants_smallvariant" ("release", "chromosome", "chromosome_no", "start", "end", "bin", "reference", "alternative", "var_type", "case_id", "set_id", "genotype", "num_hom_alt", "num_hom_ref", "num_het", "num_hemi_alt", "num_hemi_ref", "in_clinvar", "exac_frequency", "exac_homozygous", "exac_heterozygous", "exac_hemizygous", "thousand_genomes_frequency", "thousand_genomes_homozygous", "thousand_genomes_heterozygous", "thousand_genomes_hemizygous", "gnomad_exomes_frequency", "gnomad_exomes_homozygous", "gnomad_exomes_heterozygous", "gnomad_exomes_hemizygous", "gnomad_genomes_frequency", "gnomad_genomes_homozygous", "gnomad_genomes_heterozygous", "gnomad_genomes_hemizygous", "refseq_gene_id", "refseq_transcript_id", "refseq_transcript_coding", "refseq_hgvs_c", "refseq_hgvs_p", "refseq_effect", "ensembl_gene_id", "ensembl_transcript_id", "ensembl_transcript_coding", "ensembl_hgvs_c", "ensembl_hgvs_p", "ensembl_effect") FROM 'ISDBM322015_4.tsv' WITH CSV HEADER  DELIMITER E'\t' NULL '.';

fill_table.sql

\copy "temp_variants_smallvariant_1" ("release", "chromosome", "chromosome_no", "start", "end", "bin", "reference", "alternative", "var_type", "case_id", "set_id", "genotype", "num_hom_alt", "num_hom_ref", "num_het", "num_hemi_alt", "num_hemi_ref", "in_clinvar", "exac_frequency", "exac_homozygous", "exac_heterozygous", "exac_hemizygous", "thousand_genomes_frequency", "thousand_genomes_homozygous", "thousand_genomes_heterozygous", "thousand_genomes_hemizygous", "gnomad_exomes_frequency", "gnomad_exomes_homozygous", "gnomad_exomes_heterozygous", "gnomad_exomes_hemizygous", "gnomad_genomes_frequency", "gnomad_genomes_homozygous", "gnomad_genomes_heterozygous", "gnomad_genomes_hemizygous", "refseq_gene_id", "refseq_transcript_id", "refseq_transcript_coding", "refseq_hgvs_c", "refseq_hgvs_p", "refseq_effect", "ensembl_gene_id", "ensembl_transcript_id", "ensembl_transcript_coding", "ensembl_hgvs_c", "ensembl_hgvs_p", "ensembl_effect") FROM 'ISDBM322015_1.tsv' WITH CSV HEADER  DELIMITER E'\t' NULL '.';
\copy "temp_variants_smallvariant_2" ("release", "chromosome", "chromosome_no", "start", "end", "bin", "reference", "alternative", "var_type", "case_id", "set_id", "genotype", "num_hom_alt", "num_hom_ref", "num_het", "num_hemi_alt", "num_hemi_ref", "in_clinvar", "exac_frequency", "exac_homozygous", "exac_heterozygous", "exac_hemizygous", "thousand_genomes_frequency", "thousand_genomes_homozygous", "thousand_genomes_heterozygous", "thousand_genomes_hemizygous", "gnomad_exomes_frequency", "gnomad_exomes_homozygous", "gnomad_exomes_heterozygous", "gnomad_exomes_hemizygous", "gnomad_genomes_frequency", "gnomad_genomes_homozygous", "gnomad_genomes_heterozygous", "gnomad_genomes_hemizygous", "refseq_gene_id", "refseq_transcript_id", "refseq_transcript_coding", "refseq_hgvs_c", "refseq_hgvs_p", "refseq_effect", "ensembl_gene_id", "ensembl_transcript_id", "ensembl_transcript_coding", "ensembl_hgvs_c", "ensembl_hgvs_p", "ensembl_effect") FROM 'ISDBM322015_2.tsv' WITH CSV HEADER  DELIMITER E'\t' NULL '.';
\copy "temp_variants_smallvariant_3" ("release", "chromosome", "chromosome_no", "start", "end", "bin", "reference", "alternative", "var_type", "case_id", "set_id", "genotype", "num_hom_alt", "num_hom_ref", "num_het", "num_hemi_alt", "num_hemi_ref", "in_clinvar", "exac_frequency", "exac_homozygous", "exac_heterozygous", "exac_hemizygous", "thousand_genomes_frequency", "thousand_genomes_homozygous", "thousand_genomes_heterozygous", "thousand_genomes_hemizygous", "gnomad_exomes_frequency", "gnomad_exomes_homozygous", "gnomad_exomes_heterozygous", "gnomad_exomes_hemizygous", "gnomad_genomes_frequency", "gnomad_genomes_homozygous", "gnomad_genomes_heterozygous", "gnomad_genomes_hemizygous", "refseq_gene_id", "refseq_transcript_id", "refseq_transcript_coding", "refseq_hgvs_c", "refseq_hgvs_p", "refseq_effect", "ensembl_gene_id", "ensembl_transcript_id", "ensembl_transcript_coding", "ensembl_hgvs_c", "ensembl_hgvs_p", "ensembl_effect") FROM 'ISDBM322015_3.tsv' WITH CSV HEADER  DELIMITER E'\t' NULL '.';
\copy "temp_variants_smallvariant_4" ("release", "chromosome", "chromosome_no", "start", "end", "bin", "reference", "alternative", "var_type", "case_id", "set_id", "genotype", "num_hom_alt", "num_hom_ref", "num_het", "num_hemi_alt", "num_hemi_ref", "in_clinvar", "exac_frequency", "exac_homozygous", "exac_heterozygous", "exac_hemizygous", "thousand_genomes_frequency", "thousand_genomes_homozygous", "thousand_genomes_heterozygous", "thousand_genomes_hemizygous", "gnomad_exomes_frequency", "gnomad_exomes_homozygous", "gnomad_exomes_heterozygous", "gnomad_exomes_hemizygous", "gnomad_genomes_frequency", "gnomad_genomes_homozygous", "gnomad_genomes_heterozygous", "gnomad_genomes_hemizygous", "refseq_gene_id", "refseq_transcript_id", "refseq_transcript_coding", "refseq_hgvs_c", "refseq_hgvs_p", "refseq_effect", "ensembl_gene_id", "ensembl_transcript_id", "ensembl_transcript_coding", "ensembl_hgvs_c", "ensembl_hgvs_p", "ensembl_effect") FROM 'ISDBM322015_4.tsv' WITH CSV HEADER  DELIMITER E'\t' NULL '.';

from_scratch.sql

CREATE EXTENSION btree_gin;
CREATE TABLE temp_variants_smallvariant_1 (
  id text, 
  release text, 
  chromosome text, 
  chromosome_no text, 
  start text, 
  "end" text, 
  bin text, 
  reference text, 
  alternative text, 
  var_type text, 
  case_id text, 
  set_id text, 
  genotype text, 
  in_clinvar text, 
  exac_frequency text, 
  exac_homozygous text, 
  exac_heterozygous text, 
  exac_hemizygous text, 
  thousand_genomes_frequency text, 
  thousand_genomes_homozygous text, 
  thousand_genomes_heterozygous text, 
  thousand_genomes_hemizygous text, 
  gnomad_exomes_frequency text, 
  gnomad_exomes_homozygous text, 
  gnomad_exomes_heterozygous text, 
  gnomad_exomes_hemizygous text, 
  gnomad_genomes_frequency text, 
  gnomad_genomes_homozygous text, 
  gnomad_genomes_heterozygous text, 
  gnomad_genomes_hemizygous text, 
  refseq_gene_id text, 
  refseq_transcript_id text, 
  refseq_transcript_coding text, 
  refseq_hgvs_c text, 
  refseq_hgvs_p text, 
  refseq_effect text, 
  ensembl_gene_id text, 
  ensembl_transcript_id text, 
  ensembl_transcript_coding text, 
  ensembl_hgvs_c text, 
  ensembl_hgvs_p text, 
  ensembl_effect text, 
  num_hemi_alt text, 
  num_hemi_ref text, 
  num_het text, 
  num_hom_alt text, 
  num_hom_ref text
);
CREATE TABLE temp_variants_smallvariant_2 AS SELECT * FROM temp_variants_smallvariant_1;
CREATE TABLE temp_variants_smallvariant_3 AS SELECT * FROM temp_variants_smallvariant_1;
CREATE TABLE temp_variants_smallvariant_4 AS SELECT * FROM temp_variants_smallvariant_1;
CREATE TABLE variants_smallvariant (
  id bigint NOT NULL, 
  release character varying(32) NOT NULL, 
  chromosome character varying(32) NOT NULL, 
  chromosome_no integer NOT NULL, 
  start integer NOT NULL, 
  "end" integer NOT NULL, 
  bin integer NOT NULL, 
  reference character varying(512) NOT NULL, 
  alternative character varying(512) NOT NULL, 
  var_type character varying(8) NOT NULL, 
  case_id integer NOT NULL, 
  set_id integer NOT NULL, 
  genotype jsonb NOT NULL, 
  in_clinvar boolean, 
  exac_frequency double precision, 
  exac_homozygous integer, 
  exac_heterozygous integer, 
  exac_hemizygous integer, 
  thousand_genomes_frequency double precision, 
  thousand_genomes_homozygous integer, 
  thousand_genomes_heterozygous integer, 
  thousand_genomes_hemizygous integer, 
  gnomad_exomes_frequency double precision, 
  gnomad_exomes_homozygous integer, 
  gnomad_exomes_heterozygous integer, 
  gnomad_exomes_hemizygous integer, 
  gnomad_genomes_frequency double precision, 
  gnomad_genomes_homozygous integer, 
  gnomad_genomes_heterozygous integer, 
  gnomad_genomes_hemizygous integer, 
  refseq_gene_id character varying(32), 
  refseq_transcript_id character varying(32), 
  refseq_transcript_coding boolean, 
  refseq_hgvs_c character varying(512), 
  refseq_hgvs_p character varying(512), 
  refseq_effect character varying(64) [], 
  ensembl_gene_id character varying(32), 
  ensembl_transcript_id character varying(32), 
  ensembl_transcript_coding boolean, 
  ensembl_hgvs_c character varying(512), 
  ensembl_hgvs_p character varying(512), 
  ensembl_effect character varying(64) [] NOT NULL, 
  num_hemi_alt integer NOT NULL, 
  num_hemi_ref integer NOT NULL, 
  num_het integer NOT NULL, 
  num_hom_alt integer NOT NULL, 
  num_hom_ref integer NOT NULL
) PARTITION BY HASH (case_id);
ALTER TABLE 
  ONLY variants_smallvariant 
ADD 
  CONSTRAINT variants_smallvariant_pkey PRIMARY KEY (id, case_id) DEFERRABLE;
--CREATE INDEX variants_sm_case_id_071d6b_gin ON variants_smallvariant USING gin (case_id, ensembl_effect);
--CREATE INDEX variants_sm_case_id_1f4f31_idx ON variants_smallvariant USING btree (case_id, refseq_gene_id);
--CREATE INDEX variants_sm_case_id_3efbb1_idx ON variants_smallvariant USING btree (case_id, chromosome, bin);
--CREATE INDEX variants_sm_case_id_423a80_idx ON variants_smallvariant USING btree (case_id, in_clinvar);
--CREATE INDEX variants_sm_case_id_5d52f6_idx ON variants_smallvariant USING btree (case_id, ensembl_gene_id);
--CREATE INDEX variants_sm_case_id_6f9d8c_idx ON variants_smallvariant USING btree (case_id);
--CREATE INDEX variants_sm_case_id_a529e8_gin ON variants_smallvariant USING gin (case_id, refseq_effect);
--CREATE INDEX variants_sm_coordinates ON variants_smallvariant USING btree (release, chromosome, start);
CREATE SEQUENCE variants_smallvariant_id_seq AS integer START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
ALTER SEQUENCE variants_smallvariant_id_seq OWNED BY variants_smallvariant.id;
ALTER TABLE 
  ONLY variants_smallvariant ALTER COLUMN id 
SET 
  DEFAULT nextval(
    'variants_smallvariant_id_seq' :: regclass
  );
CREATE TABLE variants_smallvariant_0 PARTITION OF variants_smallvariant FOR 
VALUES 
  WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE variants_smallvariant_1 PARTITION OF variants_smallvariant FOR 
VALUES 
  WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE variants_smallvariant_2 PARTITION OF variants_smallvariant FOR 
VALUES 
  WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE variants_smallvariant_3 PARTITION OF variants_smallvariant FOR 
VALUES 
  WITH (MODULUS 4, REMAINDER 3);

postgresql.conf

listen_addresses = '0.0.0.0'		
max_connections = 500			
shared_buffers = 8GB			
maintenance_work_mem = 2GB		
dynamic_shared_memory_type = posix	
shared_preload_libraries = 'pg_stat_statements'  
effective_io_concurrency = 16		
max_worker_processes = 8		
full_page_writes = off 
wal_compression = off			
commit_delay = 100000			
checkpoint_timeout = 4h		
max_wal_size = 100GB
min_wal_size = 1GB
checkpoint_completion_target = 0.8	
log_destination = 'stderr'		
logging_collector = on			
log_directory = 'pg_log'		
log_filename = 'postgresql-%a.log'	
log_truncate_on_rotation = on		
log_rotation_age = 1d			
log_rotation_size = 0			
log_checkpoints = on
log_duration = on
log_line_prefix = '< %m > '			
log_statement = 'all'
log_timezone = 'Europe/Berlin'
track_activity_query_size = 102400	
pg_stat_statements.track = all
datestyle = 'iso, mdy'
timezone = 'Europe/Berlin'
lc_messages = 'en_US.UTF-8'			
lc_monetary = 'en_US.UTF-8'			
lc_numeric = 'en_US.UTF-8'			
lc_time = 'en_US.UTF-8'				
default_text_search_config = 'pg_catalog.english'
max_locks_per_transaction = 64		

query1.sql

\timing
\set autocommit 'off'
explain analyze INSERT INTO "variants_smallvariant" ("release", "chromosome", "chromosome_no", "start", "end", "bin", "reference", "alternative", "var_type", "case_id", "set_id", "genotype", "num_hom_alt", "num_hom_ref", "num_het", "num_hemi_alt", "num_hemi_ref", "in_clinvar", "exac_frequency", "exac_homozygous", "exac_heterozygous", "exac_hemizygous", "thousand_genomes_frequency", "thousand_genomes_homozygous", "thousand_genomes_heterozygous", "thousand_genomes_hemizygous", "gnomad_exomes_frequency", "gnomad_exomes_homozygous", "gnomad_exomes_heterozygous", "gnomad_exomes_hemizygous", "gnomad_genomes_frequency", "gnomad_genomes_homozygous", "gnomad_genomes_heterozygous", "gnomad_genomes_hemizygous", "refseq_gene_id", "refseq_transcript_id", "refseq_transcript_coding", "refseq_hgvs_c", "refseq_hgvs_p", "refseq_effect", "ensembl_gene_id", "ensembl_transcript_id", "ensembl_transcript_coding", "ensembl_hgvs_c", "ensembl_hgvs_p", "ensembl_effect") (SELECT cast("release" as varchar(32)), cast("chromosome" as varchar(32)), cast("chromosome_no" as integer), cast("start" as integer), cast("end" as integer), cast("bin" as integer), cast("reference" as varchar(512)), cast("alternative" as varchar(512)), cast("var_type" as varchar(8)), cast("case_id" as integer), cast("set_id" as integer), cast("genotype" as jsonb), cast("num_hom_alt" as integer), cast("num_hom_ref" as integer), cast("num_het" as integer), cast("num_hemi_alt" as integer), cast("num_hemi_ref" as integer), cast("in_clinvar" as boolean), cast("exac_frequency" as double precision), cast("exac_homozygous" as integer), cast("exac_heterozygous" as integer), cast("exac_hemizygous" as integer), cast("thousand_genomes_frequency" as double precision), cast("thousand_genomes_homozygous" as integer), cast("thousand_genomes_heterozygous" as integer), cast("thousand_genomes_hemizygous" as integer), cast("gnomad_exomes_frequency" as double precision), cast("gnomad_exomes_homozygous" as integer), cast("gnomad_exomes_heterozygous" as integer), cast("gnomad_exomes_hemizygous" as integer), cast("gnomad_genomes_frequency" as double precision), cast("gnomad_genomes_homozygous" as integer), cast("gnomad_genomes_heterozygous" as integer), cast("gnomad_genomes_hemizygous" as integer), cast("refseq_gene_id" as varchar(16)), cast("refseq_transcript_id" as varchar(16)), cast("refseq_transcript_coding" as boolean), cast("refseq_hgvs_c" as varchar(512)), cast("refseq_hgvs_p" as varchar(512)), cast("refseq_effect" as varchar(64)[]), cast("ensembl_gene_id" as varchar(16)), cast("ensembl_transcript_id" as varchar(32)), cast("ensembl_transcript_coding" as boolean), cast("ensembl_hgvs_c" as varchar(512)), cast("ensembl_hgvs_p" as varchar(512)), cast("ensembl_effect" as varchar(64)[]) FROM "temp_variants_smallvariant_1");-- ON CONFLICT DO NOTHING;

query2.sql

\timing
\set autocommit 'off'
explain analyze INSERT INTO "variants_smallvariant" ("release", "chromosome", "chromosome_no", "start", "end", "bin", "reference", "alternative", "var_type", "case_id", "set_id", "genotype", "num_hom_alt", "num_hom_ref", "num_het", "num_hemi_alt", "num_hemi_ref", "in_clinvar", "exac_frequency", "exac_homozygous", "exac_heterozygous", "exac_hemizygous", "thousand_genomes_frequency", "thousand_genomes_homozygous", "thousand_genomes_heterozygous", "thousand_genomes_hemizygous", "gnomad_exomes_frequency", "gnomad_exomes_homozygous", "gnomad_exomes_heterozygous", "gnomad_exomes_hemizygous", "gnomad_genomes_frequency", "gnomad_genomes_homozygous", "gnomad_genomes_heterozygous", "gnomad_genomes_hemizygous", "refseq_gene_id", "refseq_transcript_id", "refseq_transcript_coding", "refseq_hgvs_c", "refseq_hgvs_p", "refseq_effect", "ensembl_gene_id", "ensembl_transcript_id", "ensembl_transcript_coding", "ensembl_hgvs_c", "ensembl_hgvs_p", "ensembl_effect") (SELECT cast("release" as varchar(32)), cast("chromosome" as varchar(32)), cast("chromosome_no" as integer), cast("start" as integer), cast("end" as integer), cast("bin" as integer), cast("reference" as varchar(512)), cast("alternative" as varchar(512)), cast("var_type" as varchar(8)), cast("case_id" as integer), cast("set_id" as integer), cast("genotype" as jsonb), cast("num_hom_alt" as integer), cast("num_hom_ref" as integer), cast("num_het" as integer), cast("num_hemi_alt" as integer), cast("num_hemi_ref" as integer), cast("in_clinvar" as boolean), cast("exac_frequency" as double precision), cast("exac_homozygous" as integer), cast("exac_heterozygous" as integer), cast("exac_hemizygous" as integer), cast("thousand_genomes_frequency" as double precision), cast("thousand_genomes_homozygous" as integer), cast("thousand_genomes_heterozygous" as integer), cast("thousand_genomes_hemizygous" as integer), cast("gnomad_exomes_frequency" as double precision), cast("gnomad_exomes_homozygous" as integer), cast("gnomad_exomes_heterozygous" as integer), cast("gnomad_exomes_hemizygous" as integer), cast("gnomad_genomes_frequency" as double precision), cast("gnomad_genomes_homozygous" as integer), cast("gnomad_genomes_heterozygous" as integer), cast("gnomad_genomes_hemizygous" as integer), cast("refseq_gene_id" as varchar(16)), cast("refseq_transcript_id" as varchar(16)), cast("refseq_transcript_coding" as boolean), cast("refseq_hgvs_c" as varchar(512)), cast("refseq_hgvs_p" as varchar(512)), cast("refseq_effect" as varchar(64)[]), cast("ensembl_gene_id" as varchar(16)), cast("ensembl_transcript_id" as varchar(32)), cast("ensembl_transcript_coding" as boolean), cast("ensembl_hgvs_c" as varchar(512)), cast("ensembl_hgvs_p" as varchar(512)), cast("ensembl_effect" as varchar(64)[]) FROM "temp_variants_smallvariant_2");-- ON CONFLICT DO NOTHING;

query3.sql

\timing
\set autocommit 'off'
explain analyze INSERT INTO "variants_smallvariant" ("release", "chromosome", "chromosome_no", "start", "end", "bin", "reference", "alternative", "var_type", "case_id", "set_id", "genotype", "num_hom_alt", "num_hom_ref", "num_het", "num_hemi_alt", "num_hemi_ref", "in_clinvar", "exac_frequency", "exac_homozygous", "exac_heterozygous", "exac_hemizygous", "thousand_genomes_frequency", "thousand_genomes_homozygous", "thousand_genomes_heterozygous", "thousand_genomes_hemizygous", "gnomad_exomes_frequency", "gnomad_exomes_homozygous", "gnomad_exomes_heterozygous", "gnomad_exomes_hemizygous", "gnomad_genomes_frequency", "gnomad_genomes_homozygous", "gnomad_genomes_heterozygous", "gnomad_genomes_hemizygous", "refseq_gene_id", "refseq_transcript_id", "refseq_transcript_coding", "refseq_hgvs_c", "refseq_hgvs_p", "refseq_effect", "ensembl_gene_id", "ensembl_transcript_id", "ensembl_transcript_coding", "ensembl_hgvs_c", "ensembl_hgvs_p", "ensembl_effect") (SELECT cast("release" as varchar(32)), cast("chromosome" as varchar(32)), cast("chromosome_no" as integer), cast("start" as integer), cast("end" as integer), cast("bin" as integer), cast("reference" as varchar(512)), cast("alternative" as varchar(512)), cast("var_type" as varchar(8)), cast("case_id" as integer), cast("set_id" as integer), cast("genotype" as jsonb), cast("num_hom_alt" as integer), cast("num_hom_ref" as integer), cast("num_het" as integer), cast("num_hemi_alt" as integer), cast("num_hemi_ref" as integer), cast("in_clinvar" as boolean), cast("exac_frequency" as double precision), cast("exac_homozygous" as integer), cast("exac_heterozygous" as integer), cast("exac_hemizygous" as integer), cast("thousand_genomes_frequency" as double precision), cast("thousand_genomes_homozygous" as integer), cast("thousand_genomes_heterozygous" as integer), cast("thousand_genomes_hemizygous" as integer), cast("gnomad_exomes_frequency" as double precision), cast("gnomad_exomes_homozygous" as integer), cast("gnomad_exomes_heterozygous" as integer), cast("gnomad_exomes_hemizygous" as integer), cast("gnomad_genomes_frequency" as double precision), cast("gnomad_genomes_homozygous" as integer), cast("gnomad_genomes_heterozygous" as integer), cast("gnomad_genomes_hemizygous" as integer), cast("refseq_gene_id" as varchar(16)), cast("refseq_transcript_id" as varchar(16)), cast("refseq_transcript_coding" as boolean), cast("refseq_hgvs_c" as varchar(512)), cast("refseq_hgvs_p" as varchar(512)), cast("refseq_effect" as varchar(64)[]), cast("ensembl_gene_id" as varchar(16)), cast("ensembl_transcript_id" as varchar(32)), cast("ensembl_transcript_coding" as boolean), cast("ensembl_hgvs_c" as varchar(512)), cast("ensembl_hgvs_p" as varchar(512)), cast("ensembl_effect" as varchar(64)[]) FROM "temp_variants_smallvariant_3");-- ON CONFLICT DO NOTHING;

query4.sql

\timing
\set autocommit 'off'
explain analyze INSERT INTO "variants_smallvariant" ("release", "chromosome", "chromosome_no", "start", "end", "bin", "reference", "alternative", "var_type", "case_id", "set_id", "genotype", "num_hom_alt", "num_hom_ref", "num_het", "num_hemi_alt", "num_hemi_ref", "in_clinvar", "exac_frequency", "exac_homozygous", "exac_heterozygous", "exac_hemizygous", "thousand_genomes_frequency", "thousand_genomes_homozygous", "thousand_genomes_heterozygous", "thousand_genomes_hemizygous", "gnomad_exomes_frequency", "gnomad_exomes_homozygous", "gnomad_exomes_heterozygous", "gnomad_exomes_hemizygous", "gnomad_genomes_frequency", "gnomad_genomes_homozygous", "gnomad_genomes_heterozygous", "gnomad_genomes_hemizygous", "refseq_gene_id", "refseq_transcript_id", "refseq_transcript_coding", "refseq_hgvs_c", "refseq_hgvs_p", "refseq_effect", "ensembl_gene_id", "ensembl_transcript_id", "ensembl_transcript_coding", "ensembl_hgvs_c", "ensembl_hgvs_p", "ensembl_effect") (SELECT cast("release" as varchar(32)), cast("chromosome" as varchar(32)), cast("chromosome_no" as integer), cast("start" as integer), cast("end" as integer), cast("bin" as integer), cast("reference" as varchar(512)), cast("alternative" as varchar(512)), cast("var_type" as varchar(8)), cast("case_id" as integer), cast("set_id" as integer), cast("genotype" as jsonb), cast("num_hom_alt" as integer), cast("num_hom_ref" as integer), cast("num_het" as integer), cast("num_hemi_alt" as integer), cast("num_hemi_ref" as integer), cast("in_clinvar" as boolean), cast("exac_frequency" as double precision), cast("exac_homozygous" as integer), cast("exac_heterozygous" as integer), cast("exac_hemizygous" as integer), cast("thousand_genomes_frequency" as double precision), cast("thousand_genomes_homozygous" as integer), cast("thousand_genomes_heterozygous" as integer), cast("thousand_genomes_hemizygous" as integer), cast("gnomad_exomes_frequency" as double precision), cast("gnomad_exomes_homozygous" as integer), cast("gnomad_exomes_heterozygous" as integer), cast("gnomad_exomes_hemizygous" as integer), cast("gnomad_genomes_frequency" as double precision), cast("gnomad_genomes_homozygous" as integer), cast("gnomad_genomes_heterozygous" as integer), cast("gnomad_genomes_hemizygous" as integer), cast("refseq_gene_id" as varchar(16)), cast("refseq_transcript_id" as varchar(16)), cast("refseq_transcript_coding" as boolean), cast("refseq_hgvs_c" as varchar(512)), cast("refseq_hgvs_p" as varchar(512)), cast("refseq_effect" as varchar(64)[]), cast("ensembl_gene_id" as varchar(16)), cast("ensembl_transcript_id" as varchar(32)), cast("ensembl_transcript_coding" as boolean), cast("ensembl_hgvs_c" as varchar(512)), cast("ensembl_hgvs_p" as varchar(512)), cast("ensembl_effect" as varchar(64)[]) FROM "temp_variants_smallvariant_4");-- ON CONFLICT DO NOTHING;