VACUUM FULL ANALYZE;

Date: 2024-08-28 04:14:26
Duration: 46m7s

VACUUM FULL ANALYZE;

Date: 2024-08-28 03:28:19
Duration: 0ms

x Hide

CLUSTER pub2.TERM;

Date: 2024-08-28 03:27:41
Duration: 58s198ms

CLUSTER pub2.TERM;

Date: 2024-08-28 03:26:52
Duration: 0ms

x Hide

SELECT /* AllGDRelationsDAO */ ;

Date: 2024-08-28 13:03:18
Duration: 0ms

x Hide

CLUSTER pub2.TERM_LABEL;

Date: 2024-08-28 03:28:16
Duration: 35s257ms

CLUSTER pub2.TERM_LABEL;

Date: 2024-08-28 03:27:47
Duration: 0ms

x Hide

ALTER TABLE pub2.phenotype_term ADD CONSTRAINT phenotype_term_pk PRIMARY KEY (phenotype_id, term_id);

Date: 2024-08-28 00:01:16
Duration: 0ms

x Hide

ALTER TABLE pub2.gene_disease ADD CONSTRAINT gene_disease_pk PRIMARY KEY (gene_id, disease_id);

Date: 2024-08-28 00:00:47
Duration: 6s165ms

ALTER TABLE pub2.gene_disease ADD CONSTRAINT gene_disease_pk PRIMARY KEY (gene_id, disease_id);

Date: 2024-08-28 00:00:47
Duration: 0ms
Database: ctdprd51
User: pub2

x Hide

ALTER TABLE pub2.chem_disease ADD CONSTRAINT chem_disease_pk PRIMARY KEY (chem_id, disease_id);

Date: 2024-08-28 00:01:19
Duration: 0ms

x Hide

select pub2.maint_cached_value_refresh_data_metrics ();

Date: 2024-08-28 06:55:18
Duration: 33m21s

select pub2.maint_cached_value_refresh_data_metrics ();

Date: 2024-08-28 06:51:05
Duration: 0ms

x Hide

CREATE INDEX ix_gene_disease_network_score ON pub2.gene_disease USING btree (network_score);

Date: 2024-08-28 00:01:01
Duration: 13s561ms

CREATE INDEX ix_gene_disease_network_score ON pub2.gene_disease USING btree (network_score);

Date: 2024-08-28 00:01:01
Duration: 0ms

x Hide

CREATE INDEX ix_phenotype_term_phenotype_id ON pub2.phenotype_term USING btree (phenotype_id);

Date: 2024-08-28 00:01:17
Duration: 0ms

x Hide

CREATE INDEX ix_gene_disease_ind_chem_qty ON pub2.gene_disease USING btree (indirect_chem_qty) WHERE (indirect_chem_qty > 0);

Date: 2024-08-28 00:01:15
Duration: 6s771ms

CREATE INDEX ix_gene_disease_ind_chem_qty ON pub2.gene_disease USING btree (indirect_chem_qty) WHERE (indirect_chem_qty > 0);

Date: 2024-08-28 00:01:15
Duration: 0ms

x Hide

CREATE INDEX ix_gene_disease_exp_ref_qty ON pub2.gene_disease USING btree (exposure_reference_qty) WHERE (exposure_reference_qty > 0);

Date: 2024-08-28 00:01:15
Duration: 0ms

x Hide

CREATE INDEX ix_phenotype_term_term_id ON pub2.phenotype_term USING btree (term_id);

Date: 2024-08-28 00:01:17
Duration: 0ms

x Hide

CREATE INDEX ix_gene_disease_disease ON pub2.gene_disease USING btree (disease_id);

Date: 2024-08-28 00:01:07
Duration: 6s383ms

CREATE INDEX ix_gene_disease_disease ON pub2.gene_disease USING btree (disease_id);

Date: 2024-08-28 00:01:07
Duration: 0ms

x Hide

CREATE INDEX ix_gene_disease_cur_ref_qty ON pub2.gene_disease USING btree (curated_reference_qty) WHERE (curated_reference_qty > 0);

Date: 2024-08-28 00:01:08
Duration: 0ms

x Hide

CREATE INDEX ix_chem_disease_network_score ON pub2.chem_disease USING btree (network_score);

Date: 2024-08-28 00:01:21
Duration: 1s443ms

CREATE INDEX ix_chem_disease_network_score ON pub2.chem_disease USING btree (network_score);

Date: 2024-08-28 00:01:21
Duration: 0ms

x Hide

CREATE INDEX ix_chem_disease_cur_ref_qty ON pub2.chem_disease USING btree (curated_reference_qty) WHERE (curated_reference_qty > 0);

Date: 2024-08-28 00:01:21
Duration: 0ms

x Hide

CREATE INDEX ix_chem_disease_ind_gene_qty ON pub2.chem_disease USING btree (indirect_gene_qty) WHERE (indirect_gene_qty > 0);

Date: 2024-08-28 00:01:22
Duration: 0ms

x Hide

CREATE INDEX ix_chem_disease_disease ON pub2.chem_disease USING btree (disease_id);

Date: 2024-08-28 00:01:21
Duration: 0ms

x Hide

ALTER TABLE pub2.phenotype_term_axn ADD CONSTRAINT phenotype_term_axn_pk PRIMARY KEY (phenotype_id, term_id, action_type_nm, action_degree_type_nm);

Date: 2024-08-28 00:01:18
Duration: 0ms

x Hide

CREATE INDEX ix_chem_disease_exp_ref_qty ON pub2.chem_disease USING btree (exposure_reference_qty) WHERE (exposure_reference_qty > 0);

Date: 2024-08-28 00:01:22
Duration: 0ms

x Hide

CREATE INDEX ix_phenotype_term_axn_term_id ON pub2.phenotype_term_axn USING btree (term_id);

Date: 2024-08-28 00:01:19
Duration: 0ms

x Hide

CREATE INDEX ix_phenotype_term_axn_phenotype_id ON pub2.phenotype_term_axn USING btree (phenotype_id);

Date: 2024-08-28 00:01:18
Duration: 0ms

x Hide

select pub2.maint_phenotype_term_derive_data ( ) SELECT lc_cum_type_nm, 'gc_c_ixn_count', COUNT(*) -- Not distincted. FROM gene_chem_reference gcr UNION ALL SELECT lc_cum_type_nm, 'gc_c_gene_count', COUNT(DISTINCT gcr.gene_id) FROM gene_chem_reference gcr UNION ALL SELECT lc_cum_type_nm, 'gc_c_chem_count', COUNT(DISTINCT gcr.chem_id) FROM gene_chem_reference gcr UNION ALL SELECT lc_cum_type_nm, 'gc_taxon_count', COUNT(DISTINCT gcr.taxon_id) FROM gene_chem_reference gcr WHERE gcr.taxon_id IS NOT NULL UNION ALL SELECT lc_cum_type_nm, 'gc_c_ref_count', COUNT(DISTINCT gcr.reference_id) FROM gene_chem_reference gcr UNION ALL SELECT lc_cum_type_nm, 'gd_d_ixn_count', COUNT(*) -- Not distincted. FROM gene_disease_reference gdr WHERE gdr.source_cd IN ('C', 'O') UNION ALL SELECT lc_cum_type_nm, 'gd_d_gene_count', COUNT(DISTINCT gdr.gene_id) FROM gene_disease_reference gdr WHERE gdr.source_cd IN ('C', 'O') UNION ALL SELECT lc_cum_type_nm, 'gd_d_disease_count', COUNT(DISTINCT gdr.disease_id) FROM gene_disease_reference gdr WHERE gdr.source_cd IN ('C', 'O') UNION ALL SELECT lc_cum_type_nm, 'gd_i_ixn_count', COUNT(DISTINCT gdr.gene_id || '|' || gdr.disease_id) -- Inferreds must be distincted. FROM gene_disease_reference gdr WHERE gdr.source_cd = 'I' UNION ALL SELECT lc_cum_type_nm, 'gd_i_gene_count', COUNT(DISTINCT gdr.gene_id) FROM gene_disease_reference gdr WHERE gdr.source_cd = 'I' UNION ALL SELECT lc_cum_type_nm, 'gd_i_disease_count', COUNT(DISTINCT gdr.disease_id) FROM gene_disease_reference gdr WHERE gdr.source_cd = 'I' UNION ALL SELECT lc_cum_type_nm, 'cd_c_ixn_count', COUNT(*) -- Not distincted. FROM chem_disease_reference cdr WHERE cdr.source_cd = 'C' UNION ALL SELECT lc_cum_type_nm, 'cd_i_ixn_count', COUNT(DISTINCT cdr.chem_id || '|' || cdr.disease_id) -- Inferreds must be distincted. FROM chem_disease_reference cdr WHERE cdr.source_cd = 'I' UNION ALL SELECT lc_cum_type_nm, 'cd_' || LOWER(cdr.source_cd) || '_chem_count', COUNT(DISTINCT cdr.chem_id) "cdChemCount" FROM chem_disease_reference cdr WHERE cdr.source_cd IN ('C', 'I') GROUP BY cdr.source_cd UNION ALL SELECT lc_cum_type_nm, 'cd_' || LOWER(cdr.source_cd) || '_disease_count', COUNT(DISTINCT cdr.disease_id) FROM chem_disease_reference cdr WHERE cdr.source_cd IN ('C', 'I') GROUP BY cdr.source_cd UNION ALL -- direct gene-go SELECT lc_cum_type_nm, 'ggo_d_count', COUNT(*) FROM gene_go_annot gga UNION ALL -- direct gene_pathway SELECT lc_cum_type_nm, 'gp_d_count', COUNT(*) FROM term_pathway tp WHERE tp.object_type_id = ( SELECT ot.id FROM object_type ot WHERE ot.cd = 'gene') UNION ALL -- enriched chem-go SELECT lc_cum_type_nm, 'cgo_e_count', COUNT(*) FROM term_enrichment te WHERE te.object_type_id = ( SELECT ot.id FROM object_type ot WHERE ot.cd = 'chem') AND te.enriched_object_type_id = ( SELECT ot.id FROM object_type ot WHERE ot.cd = 'go') UNION ALL -- enriched chem-pathway SELECT lc_cum_type_nm, 'cp_e_count', COUNT(*) FROM term_enrichment te WHERE te.object_type_id = ( SELECT ot.id FROM object_type ot WHERE ot.cd = 'chem') AND te.enriched_object_type_id = ( SELECT ot.id FROM object_type ot WHERE ot.cd = 'pathway') UNION ALL -- inferred chem-pathway SELECT lc_cum_type_nm, 'cp_i_count', COUNT(*) FROM ( SELECT DISTINCT tp.pathway_id, gcr.chem_id FROM term_pathway tp INNER JOIN gene_chem_reference gcr on tp.term_id = gcr.gene_id) i UNION ALL -- gene-gene ixns SELECT lc_cum_type_nm, 'gg_c_ixn_count', COUNT(*) -- Not distincted. FROM gene_gene_reference ggr UNION ALL -- inferred disease_pathway SELECT lc_cum_type_nm, 'dp_i_count', COUNT(*) FROM ( SELECT DISTINCT tp.pathway_id, gd.disease_id FROM term_pathway tp INNER JOIN gene_disease gd on tp.term_id = gd.gene_id WHERE gd.curated_Reference_qty > 0) i UNION ALL -- inferred disease-go SELECT lc_cum_type_nm, 'dgo_i_count', COUNT(*) FROM ( SELECT DISTINCT phenotype_id, term_id FROM phenotype_term_reference WHERE term_object_type_id = ( select id from object_type where cd = 'disease')) i UNION ALL -- curated references SELECT lc_cum_type_nm, 'curated_ref_count', COUNT(*) FROM reference r WHERE r.has_ixns OR r.has_diseases OR r.has_phenotypes UNION ALL -- curated chemicals SELECT lc_cum_type_nm, 'curated_chem_count', COUNT(*) FROM ( SELECT gcr.chem_id FROM gene_chem_reference gcr UNION SELECT cdr.chem_id FROM chem_disease_reference cdr WHERE cdr.source_cd = 'C') i UNION ALL -- curated genes SELECT lc_cum_type_nm, 'curated_gene_count', COUNT(*) FROM ( SELECT gcr.gene_id FROM gene_chem_reference gcr UNION SELECT gdr.gene_id FROM gene_disease_reference gdr WHERE gdr.source_cd IN ('C', 'O')) i UNION ALL -- omim-curated genes SELECT lc_cum_type_nm, 'omim_gene_count', COUNT(DISTINCT gdr.gene_id) FROM gene_disease_reference gdr WHERE gdr.source_cd = 'O' UNION ALL -- curated diseases SELECT lc_cum_type_nm, 'curated_disease_count', COUNT(*) FROM ( SELECT cdr.disease_id FROM chem_disease_reference cdr WHERE cdr.source_cd = 'C' UNION SELECT gdr.disease_id FROM gene_disease_reference gdr WHERE gdr.source_cd IN ('C', 'O')) i UNION ALL -- omim-curated diseases SELECT lc_cum_type_nm, 'omim_disease_count', COUNT(DISTINCT gdr.disease_id) FROM gene_disease_reference gdr WHERE gdr.source_cd = 'O' -- ---------------------------------------- -- START: Exposure-related statistics -- ---------------------------------------- UNION ALL -- ---------------------------------------- SELECT lc_cum_type_nm, 'exp_c_evnt_count', count(*) from ( /* NOTE: The 'exp_c_evnt_count' is intended to match the Exposure statement download file - consequently, the queries between the report and this function must match. Changes to one necessitates changes to the other. */ SELECT DISTINCT /* AllExposuresDAO */ stressorTerm.nm ExposureStressorName, stressorTerm.acc_txt ExposureStressorID, ( SELECT STRING_AGG(distinct stressorSrcType.nm, '|')) StressorSourceCategory, stressor.src_details StressorSourceDetails, stressor.sample_qty NumberOfStressorSamples, stressor.note StressorNotes, receptor.qty NumberOfReceptors, concat(receptor.description || ' ', receptorTerm.nm) Receptors, receptor.note ReceptorNotes, ( SELECT STRING_AGG(distinct receptorTobaccoUse.tobacco_use_nm, ' | ')) SmokingStatus, receptor.age Age, receptor.age_uom_nm AgeUnitsOfMeasurement, receptor.age_qualifier_nm AgeQualifier, receptor.gender_nm Sex, ( SELECT STRING_AGG(DISTINCT receptorRace.race_nm || ' ' || receptorRace.pct, ' | ')) Race, ( SELECT STRING_AGG(DISTINCT eventAssayMethod.nm, ' | ')) Methods, event.detection_limit DetectionLimit, event.detection_limit_uom DetectionLimitUOM, event.detection_freq DetectionFrequency, event.medium_nm Medium, exposureMarkerTerm.nm ExposureMarker, exposureMarkerTerm.acc_txt ExposureMarkerID, event.exp_marker_lvl MarkerLevel, assay_uom MarkerUnitsOfMeasurement, assay_measurement_stat MarkerMeasurementStatistic, assay_note AssayNotes, ( SELECT STRING_AGG(distinct country.nm, ' | ')) StudyCountries, ( SELECT STRING_AGG(DISTINCT eventLocation.geographic_region_nm, ' | ')) StateOrProvince, ( SELECT STRING_AGG(DISTINCT eventLocation.locality_txt, ' | ')) CityTownRegionArea, event.note ExposureEventNotes, eiot.description OutcomeRelationship, diseaseTerm.nm DiseaseName, diseaseTerm.acc_txt DiseaseID, phenotypeTerm.nm PhenotypeName, phenotypeTerm.acc_txt PhenotypeID, outcome.phenotype_action_degree_type_nm PhenotypeActionDegreeType, ( SELECT STRING_AGG(DISTINCT anatomyTerm.nm, ' | ')) Anatomy, outcome.note ExposureOutcomeNotes, e.reference_acc_txt Reference, ( SELECT STRING_AGG(DISTINCT eventProject.project_nm, ' | ')) AssociatedStudyTitles, event.collection_start_yr EnrollmentStartYear, event.collection_end_yr EnrollmentEndYear, ( SELECT STRING_AGG(DISTINCT expStudyFactor.study_factor_nm, ' | ')) StudyFactors, eventLocation.exp_event_id ignorecolumneventID, COUNT(*) OVER () fullRowCount -- take this out of final report query FROM exposure e inner join exp_stressor stressor ON e.exp_stressor_id = stressor.id inner join term stressorTerm ON stressor.chem_id = stressorTerm.id left outer join exp_receptor receptor ON e.exp_receptor_id = receptor.id left outer join exp_event event ON e.exp_event_id = event.id left outer join term exposureMarkerTerm ON event.exp_marker_term_id = exposureMarkerTerm.id left outer join exp_outcome outcome ON e.exp_outcome_id = outcome.id left outer join exp_outcome_ixn_type eiot ON outcome.exp_outcome_ixn_type_id = eiot.id left outer join term diseaseTerm ON outcome.disease_id = diseaseTerm.id left outer join term phenotypeTerm ON outcome.phenotype_id = phenotypeTerm.id left outer join term receptorTerm ON receptor.term_id = receptorTerm.id inner join reference r ON e.reference_id = r.id left outer join exp_stressor_stressor_src esss ON stressor.id = esss.exp_stressor_id left outer join exp_stressor_src_type stressorSrcType ON esss.exp_stressor_src_type_id = stressorSrcType.id left outer join exp_receptor_tobacco_use receptorTobaccoUse ON receptor.id = receptorTobaccoUse.exp_receptor_id left outer join exp_receptor_race receptorRace ON receptor.id = receptorRace.exp_receptor_id left outer join exp_event_assay_method eventAssayMethod ON event.id = eventAssayMethod.exp_event_id left outer join exp_event_location eventLocation ON event.id = eventLocation.exp_event_id left outer join exp_anatomy expAnatomy ON outcome.id = expAnatomy.exp_outcome_id left outer join term anatomyTerm ON expAnatomy.anatomy_id = anatomyTerm.id left outer join country ON eventLocation.country_id = country.id left outer join exp_event_project eventProject ON event.id = eventProject.exp_event_id left outer join reference_exp referenceExp on e.reference_acc_txt = referenceExp.reference_acc_txt and e.reference_acc_db_id = referenceExp.reference_acc_db_id left outer join exp_study_factor expStudyFactor on referenceExp.id = expStudyFactor.reference_exp_id GROUP BY ExposureStressorName, ExposureStressorID, StressorSourceDetails, NumberOfStressorSamples, StressorNotes, NumberOfReceptors, Receptors, ReceptorNotes, Medium, ExposureMarker, ExposureMarkerID, MarkerLevel, MarkerUnitsOfMeasurement, MarkerMeasurementStatistic, AssayNotes, OutcomeRelationship, DiseaseName, DiseaseID, PhenotypeName, PhenotypeID, PhenotypeActionDegreeType, Reference, EnrollmentStartYear, EnrollmentEndYear, Age, AgeUnitsOfMeasurement, AgeQualifier, Sex, DetectionLimit, DetectionLimitUOM, DetectionFrequency, ExposureEventNotes, ExposureOutcomeNotes, ignorecolumneventID) as exp_c_evnt_count -- ---------------------------------------- -- ---------------------------------------- UNION ALL SELECT lc_cum_type_nm, 'exp_c_chem_count', COUNT(distinct chemTerm.id) FROM term chemTerm WHERE id IN ( select event.exp_marker_term_id from exp_event event where event.exp_marker_type_id in ( select id from exp_marker_type where nm like 'chem%') UNION SELECT chem_id FROM exp_stressor) UNION ALL SELECT lc_cum_type_nm, 'exp_c_gene_count', COUNT(distinct geneTerm.id) FROM term geneTerm WHERE id IN ( select event.exp_marker_term_id from exp_event event where event.exp_marker_type_id in ( select id from exp_marker_type where nm like 'gene%') union select distinct term_id from exp_receptor where object_type_id is not null and object_type_id = ( select id from object_type where cd = 'gene')) UNION ALL SELECT lc_cum_type_nm, 'exp_c_disease_count', COUNT(distinct diseaseTerm.id) FROM term diseaseTerm WHERE id IN ( SELECT disease_id FROM exp_outcome union select distinct term_id from exp_receptor where object_type_id is not null and object_type_id = ( select id from object_type where cd = 'disease')) UNION ALL SELECT lc_cum_type_nm, 'exp_c_phenotype_count', COUNT(distinct phenotypeTerm.id) FROM term phenotypeTerm WHERE id IN ( SELECT phenotype_id FROM exp_outcome union select distinct term_id from exp_receptor where object_type_id is not null and object_type_id = ( select id from object_type where cd = 'go')) union all SELECT lc_cum_type_nm, 'exp_reference_count', COUNT(distinct reference_id) FROM reference_exp -- ---------------------------------------- -- END: Exposure-related statistics -- ---------------------------------------- -- ---------------------------------------- -- START: Phenotype-related statistics -- ---------------------------------------- UNION ALL -- ---------------------------------------- SELECT lc_cum_type_nm, 'pheno_ixn_count', COUNT(*) -- Not distincted. FROM phenotype_term_reference ptr WHERE ptr.source_cd = 'C' and term_object_type_id = ( SELECT ot.id FROM object_type ot WHERE ot.cd = 'chem') UNION ALL -- unique phenotype gene count SELECT lc_cum_type_nm, 'pheno_c_gene_count', COUNT(DISTINCT ptr.term_id) FROM phenotype_term_reference ptr where ptr.source_cd IN ('C') and term_object_type_id = ( SELECT ot.id FROM object_type ot WHERE ot.cd = 'gene') UNION ALL -- unique phenotype chemical count SELECT lc_cum_type_nm, 'pheno_c_chem_count', COUNT(DISTINCT ptr.term_id) FROM phenotype_term_reference ptr where ptr.source_cd IN ('C') and term_object_type_id = ( SELECT ot.id FROM object_type ot WHERE ot.cd = 'chem') UNION ALL -- unique phenotype GO count SELECT lc_cum_type_nm, 'pheno_c_go_count', COUNT(DISTINCT ptr.phenotype_id) FROM phenotype_term_reference ptr where ptr.source_cd IN ('C') UNION ALL -- unique taxon count SELECT lc_cum_type_nm, 'pheno_c_taxon_count', COUNT(DISTINCT ptr.taxon_id) FROM phenotype_term_reference ptr where ptr.source_cd IN ('C') UNION ALL -- unique anatomy count SELECT lc_cum_type_nm, 'pheno_c_anatomy_count', COUNT(DISTINCT ia.anatomy_id) FROM ixn_anatomy ia ";

Date: 2024-08-28 06:51:05
Duration: 0ms

x Hide