Times Reported Time consuming queries #1

x Hide

[ User: load - Total duration: 9m18s - Times executed: 3 ]

x Hide

select distinct primaryGeneTerm.acc_txt, taxonTerm.acc_txt, gt.gene_acc_txt from pub1.GENE_TAXON gt, pub1.TERM primaryGeneTerm, pub1.TERM taxonTerm where gt.gene_id = primaryGeneTerm.id and gt.taxon_id = taxonTerm.id;

Date: 2024-05-10 16:19:42
Duration: 3m14s
Database: ctddev51
User: load
Bind query: yes

select distinct primaryGeneTerm.acc_txt, taxonTerm.acc_txt, gt.gene_acc_txt from pub1.GENE_TAXON gt, pub1.TERM primaryGeneTerm, pub1.TERM taxonTerm where gt.gene_id = primaryGeneTerm.id and gt.taxon_id = taxonTerm.id;

Date: 2024-05-10 15:56:46
Duration: 3m10s
Database: ctddev51
User: load
Bind query: yes

select distinct primaryGeneTerm.acc_txt, taxonTerm.acc_txt, gt.gene_acc_txt from pub1.GENE_TAXON gt, pub1.TERM primaryGeneTerm, pub1.TERM taxonTerm where gt.gene_id = primaryGeneTerm.id and gt.taxon_id = taxonTerm.id;

Date: 2024-05-10 13:32:27
Duration: 2m53s
Database: ctddev51
User: load
Bind query: yes

x Hide

Times Reported Time consuming queries #2

x Hide

select ID, OBJECT_TYPE_ID, ACC_TXT, t.ACC_DB_CD, NM, NM_SORT, SECONDARY_NM, DESCRIPTION, NOTE from pub1.term t where object_type_id = 1;

Date: 2024-05-10 13:33:53
Duration: 1m19s
Bind query: yes

select ID, OBJECT_TYPE_ID, ACC_TXT, t.ACC_DB_CD, NM, NM_SORT, SECONDARY_NM, DESCRIPTION, NOTE from pub1.term t where object_type_id = 1;

Date: 2024-05-10 15:57:59
Duration: 1m6s
Bind query: yes

select ID, OBJECT_TYPE_ID, ACC_TXT, t.ACC_DB_CD, NM, NM_SORT, SECONDARY_NM, DESCRIPTION, NOTE from pub1.term t where object_type_id = 1;

Date: 2024-05-10 16:20:53
Duration: 1m5s
Bind query: yes

x Hide

Times Reported Time consuming queries #3

x Hide

[ User: edit - Total duration: 4s365ms - Times executed: 1 ]

x Hide

[ Application: pgAdmin 4 - CONN:9310068 - Total duration: 4s365ms - Times executed: 1 ]

x Hide

select * from tm_reference tr, tm_reference_term trt where tr.id = trt.tm_reference_id and tr.acc_txt in ('33677314', '34170130', '34262720', '34228652', '34014027', '33965509', '33958549', '33955514', '33789219', '33757839', '33715114', '34155207', '34218561', '33592467', '34118929', '33894333', '34200939', '34207749', '34107000', '33994527', '33960999', '34090504', '33774344', '34083730', '34075131', '33913518', '33837838', '33837044', '33771511', '33751676', '33713393', '33617373', '33523378', '33515409', '34045544', '34084152', '34064677', '34045954', '33607168', '34968297', '33620701', '33611093', '32860006', '33910586', '33925709', '33877299', '33616673', '33563863', '33891610', '33849548', '33487623', '33540251', '33213296', '33663497', '33278335', '33222378', '33221087', '33108535', '33574433', '33484186', '33385678', '33326828', '33297752', '33044583', '33015978', '33510276', '33500442', '33499329', '33495402', '33159990', '33402167', '34802403', '34308371', '34038317', '33872129', '33682710', '33551103', '33437219', '33301878', '33285409', '33069733', '33047383', '33035625', '33030406', '32734364') order by tr.acc_txt, trt.term_text;

Date: 2024-05-10 12:45:03
Duration: 4s365ms
Database: ctddev51
User: edit
Application: pgAdmin 4 - CONN:9310068

select * from tm_reference tr, tm_reference_term trt where tr.id = trt.tm_reference_id and tr.acc_txt in ('33677314', '34170130', '34262720', '34228652', '34014027', '33965509', '33958549', '33955514', '33789219', '33757839', '33715114', '34155207', '34218561', '33592467', '34118929', '33894333', '34200939', '34207749', '34107000', '33994527', '33960999', '34090504', '33774344', '34083730', '34075131', '33913518', '33837838', '33837044', '33771511', '33751676', '33713393', '33617373', '33523378', '33515409', '34045544', '34084152', '34064677', '34045954', '33607168', '34968297', '33620701', '33611093', '32860006', '33910586', '33925709', '33877299', '33616673', '33563863', '33891610', '33849548', '33487623', '33540251', '33213296', '33663497', '33278335', '33222378', '33221087', '33108535', '33574433', '33484186', '33385678', '33326828', '33297752', '33044583', '33015978', '33510276', '33500442', '33499329', '33495402', '33159990', '33402167', '34802403', '34308371', '34038317', '33872129', '33682710', '33551103', '33437219', '33301878', '33285409', '33069733', '33047383', '33035625', '33030406', '32734364') order by tr.acc_txt, trt.term_text;

Date: 2024-05-10 15:51:48
Duration: 3s567ms

select * from tm_reference tr, tm_reference_term trt where tr.id = trt.tm_reference_id and tr.acc_txt in ('33677314', '34170130', '34262720', '34228652', '34014027', '33965509', '33958549', '33955514', '33789219', '33757839', '33715114', '34155207', '34218561', '33592467', '34118929', '33894333', '34200939', '34207749', '34107000', '33994527', '33960999', '34090504', '33774344', '34083730', '34075131', '33913518', '33837838', '33837044', '33771511', '33751676', '33713393', '33617373', '33523378', '33515409', '34045544', '34084152', '34064677', '34045954', '33607168', '34968297', '33620701', '33611093', '32860006', '33910586', '33925709', '33877299', '33616673', '33563863', '33891610', '33849548', '33487623', '33540251', '33213296', '33663497', '33278335', '33222378', '33221087', '33108535', '33574433', '33484186', '33385678', '33326828', '33297752', '33044583', '33015978', '33510276', '33500442', '33499329', '33495402', '33159990', '33402167', '34802403', '34308371', '34038317', '33872129', '33682710', '33551103', '33437219', '33301878', '33285409', '33069733', '33047383', '33035625', '33030406', '32734364') order by tr.acc_txt, trt.term_text;

Date: 2024-05-10 13:50:05
Duration: 3s30ms

x Hide

Times Reported Time consuming queries #4

x Hide

[ User: pubeu - Total duration: 3s540ms - Times executed: 1 ]

x Hide

WITH recursive sub_node ( object_id, id, path, lvl ) AS ( SELECT n.object_id, n.id, ARRAY[n.nm_sort], 1 FROM dag_node n WHERE n.object_id = '588473' UNION ALL SELECT n.object_id, n.id, CAST(path || n.nm_sort AS varchar(600)[]), sn.lvl + 1 FROM dag_node n INNER JOIN sub_node sn ON (n.parent_id = sn.id)) SELECT /* TreeTermBasicsDAO.getDescendants */ DISTINCT t.nm priNm, t.nm_html priNmHtml, t.secondary_nm secondaryNm, t.acc_db_cd accDbCd, t.acc_txt termAcc, t.is_leaf isLeaf, t.has_chems hasChems, t.has_diseases hasDiseases, t.has_exposures hasExposures, t.has_genes hasGenes, sn.lvl, sn.path, MAX(sn.lvl) OVER () maxLvl, t.has_phenotypes hasPhenotypes FROM sub_node sn INNER JOIN term t ON sn.object_id = t.id WHERE sn.lvl <= 2 ORDER BY sn.path;

Date: 2024-05-10 05:45:12
Duration: 3s540ms
Database: ctddev51
User: pubeu
Bind query: yes

WITH recursive sub_node ( object_id, id, path, lvl ) AS ( SELECT n.object_id, n.id, ARRAY[n.nm_sort], 1 FROM dag_node n WHERE n.object_id = '1424916' UNION ALL SELECT n.object_id, n.id, CAST(path || n.nm_sort AS varchar(600)[]), sn.lvl + 1 FROM dag_node n INNER JOIN sub_node sn ON (n.parent_id = sn.id)) SELECT /* TreeTermBasicsDAO.getDescendants */ DISTINCT t.nm priNm, t.nm_html priNmHtml, t.secondary_nm secondaryNm, t.acc_db_cd accDbCd, t.acc_txt termAcc, t.is_leaf isLeaf, t.has_chems hasChems, t.has_diseases hasDiseases, t.has_exposures hasExposures, t.has_genes hasGenes, sn.lvl, sn.path, MAX(sn.lvl) OVER () maxLvl, t.has_phenotypes hasPhenotypes FROM sub_node sn INNER JOIN term t ON sn.object_id = t.id WHERE sn.lvl <= 2 ORDER BY sn.path;

Date: 2024-05-10 05:45:13
Duration: 1s76ms
Bind query: yes

x Hide

Times Reported Time consuming queries #5

x Hide

[ User: pubeu - Total duration: 4s155ms - Times executed: 1 ]

x Hide

select distinct /* ChemPhenotypesAssnsDAO */ associatedTerm.nm || '^' || o.cd || '^' || associatedTerm.nm_html || '^' || associatedTerm.acc_txt || '^' || associatedTerm.acc_db_cd as associatedTerm, associatedTerm.id associatedTermId, ptr.ixn_id ixnId, associatedTerm.object_type_id || '|' || associatedTerm.nm_sort associatedTermNmSort, COALESCE(associatedTerm.secondary_nm, '') casRN, phenotypeTerm.nm || '^' || 'go' || '^' || phenotypeTerm.nm_html || '^' || phenotypeTerm.acc_txt || '^' || phenotypeTerm.acc_db_cd as phenotype, phenotypeTerm.id phenotypeId, ( SELECT STRING_AGG(distinct taxonTerm.nm || '^' || 'taxon' || '^' || taxonTerm.nm_html || '^' || taxonTerm.acc_txt || '^' || taxonTerm.acc_db_cd || '^' || COALESCE(taxonTerm.secondary_nm, ''), '|')) as taxonTerms, ( SELECT STRING_AGG(distinct anatomyTerm.nm_html || '^' || anatomyTerm.acc_txt || '^' || ia.level_seq || '^' || anatomyTerm.acc_db_cd || '^' || anatomyTerm.nm, '|')) as anatomyTerms, COUNT(DISTINCT taxonTerm.nm) taxonCount, i.ixn_prose_html ixnProseHtml, i.ixn_prose_txt ixnProse, i.sort_txt ixnSort, ( SELECT STRING_AGG(distinct r.acc_txt, '|')) as references, COUNT(DISTINCT ptr.reference_id) refCount, pt.indirect_term_qty inferredCount, COUNT(*) OVER () fullRowCount from phenotype_term_reference ptr inner join phenotype_term pt on ptr.term_id = pt.term_id and ptr.phenotype_id = pt.phenotype_id inner join term associatedTerm on ptr.term_id = associatedTerm.id inner join term phenotypeTerm on ptr.phenotype_id = phenotypeTerm.id left outer join term taxonTerm on ptr.taxon_id = taxonTerm.id inner join reference r on ptr.reference_id = r.id inner join ixn i on ptr.ixn_id = i.id inner join object_type o on associatedTerm.object_type_id = o.id left outer join ixn_anatomy ia on ptr.ixn_id = ia.ixn_id left outer join term anatomyTerm on ia.anatomy_id = anatomyTerm.id where ptr.term_id IN ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 2 and upper(baseTerm.nm) LIKE 'ACETYLCYSTEINE')) and ptr.term_object_type_id = 2 and ptr.phenotype_id IN ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 5 and baseTerm.id in ( select object_id from db_link l where l.acc_txt = 'GO:0006979' AND l.type_cd = 'A' AND l.object_type_id = 5))) and i.id in ( select ixn_id from ixn_anatomy where anatomy_id IN ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 10 and upper(baseTerm.nm) LIKE 'CARDIOVASCULAR SYSTEM'))) and taxonTerm.id in ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 1 and baseTerm.id in ( select object_id from db_link l where l.acc_txt = '9605' AND l.type_cd = 'A' AND l.object_type_id = 1))) and i.id in ( select ixn_id from ixn_axn where action_type_nm = 'phenotype' and action_degree_type_nm in ('increases')) group by associatedTerm, associatedTermNmSort, phenotype, casRN, ixnId, ixnProseHtml, ixnProse, ixnSort, associatedTermId, phenotypeId, inferredCount ORDER BY associatedTermNmSort LIMIT 50;

Date: 2024-05-10 05:48:46
Duration: 4s155ms
Database: ctddev51
User: pubeu
Bind query: yes

x Hide

Times Reported Time consuming queries #6

x Hide

select distinct /* ChemPhenotypesAssnsDAO */ associatedTerm.nm || '^' || o.cd || '^' || associatedTerm.nm_html || '^' || associatedTerm.acc_txt || '^' || associatedTerm.acc_db_cd as associatedTerm, associatedTerm.id associatedTermId, ptr.ixn_id ixnId, associatedTerm.object_type_id || '|' || associatedTerm.nm_sort associatedTermNmSort, COALESCE(associatedTerm.secondary_nm, '') casRN, phenotypeTerm.nm || '^' || 'go' || '^' || phenotypeTerm.nm_html || '^' || phenotypeTerm.acc_txt || '^' || phenotypeTerm.acc_db_cd as phenotype, phenotypeTerm.id phenotypeId, ( SELECT STRING_AGG(distinct taxonTerm.nm || '^' || 'taxon' || '^' || taxonTerm.nm_html || '^' || taxonTerm.acc_txt || '^' || taxonTerm.acc_db_cd || '^' || COALESCE(taxonTerm.secondary_nm, ''), '|')) as taxonTerms, ( SELECT STRING_AGG(distinct anatomyTerm.nm_html || '^' || anatomyTerm.acc_txt || '^' || ia.level_seq || '^' || anatomyTerm.acc_db_cd || '^' || anatomyTerm.nm, '|')) as anatomyTerms, COUNT(DISTINCT taxonTerm.nm) taxonCount, i.ixn_prose_html ixnProseHtml, i.ixn_prose_txt ixnProse, i.sort_txt ixnSort, ( SELECT STRING_AGG(distinct r.acc_txt, '|')) as references, COUNT(DISTINCT ptr.reference_id) refCount, pt.indirect_term_qty inferredCount, COUNT(*) OVER () fullRowCount from phenotype_term_reference ptr inner join phenotype_term pt on ptr.term_id = pt.term_id and ptr.phenotype_id = pt.phenotype_id inner join term associatedTerm on ptr.term_id = associatedTerm.id inner join term phenotypeTerm on ptr.phenotype_id = phenotypeTerm.id left outer join term taxonTerm on ptr.taxon_id = taxonTerm.id inner join reference r on ptr.reference_id = r.id inner join ixn i on ptr.ixn_id = i.id inner join object_type o on associatedTerm.object_type_id = o.id left outer join ixn_anatomy ia on ptr.ixn_id = ia.ixn_id left outer join term anatomyTerm on ia.anatomy_id = anatomyTerm.id where ptr.term_id IN ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 2 and upper(baseTerm.nm) LIKE 'ZINC')) and ptr.term_object_type_id = 2 and ptr.phenotype_id IN ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 5 and baseTerm.id in ( select object_id from db_link l where l.acc_txt = 'GO:0006915' AND l.type_cd = 'A' AND l.object_type_id = 5))) and taxonTerm.id in ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 1 and baseTerm.id in ( select object_id from db_link l where l.acc_txt = '9606' AND l.type_cd = 'A' AND l.object_type_id = 1))) and i.id in ( select ixn_id from ixn_axn where action_type_nm = 'phenotype' and action_degree_type_nm in ('increases')) group by associatedTerm, associatedTermNmSort, phenotype, casRN, ixnId, ixnProseHtml, ixnProse, ixnSort, associatedTermId, phenotypeId, inferredCount ORDER BY associatedTermNmSort LIMIT 50;

Date: 2024-05-10 05:48:42
Duration: 4s146ms
Bind query: yes

x Hide

Times Reported Time consuming queries #7

x Hide

[ User: pubeu - Total duration: 3s935ms - Times executed: 1 ]

x Hide

SELECT /* CIQH.getIxnCacheQuery */ gcr.ixn_id, NULL, NULL, NULL FROM gene_chem_reference gcr WHERE /* CIQH.getIxnWhereCore */ gcr.gene_id = ANY (ARRAY (( SELECT /* IQH.getMasterDiseaseWhereEquals.Name.Gene */ gd.gene_id FROM term t INNER JOIN dag_path dp ON t.id = dp.ancestor_object_id INNER JOIN gene_disease gd ON dp.descendant_object_id = gd.disease_id WHERE UPPER(t.nm) LIKE 'ASTHMA' AND t.object_type_id = 3))) AND gcr.id IN ( SELECT gcra.gene_chem_reference_id FROM gene_chem_reference_axn gcra WHERE (gcra.action_degree_type_nm = 'increases'));

Date: 2024-05-10 05:47:19
Duration: 3s935ms
Database: ctddev51
User: pubeu
Bind query: yes

x Hide

Times Reported Time consuming queries #8

x Hide

[ User: pubeu - Total duration: 3s839ms - Times executed: 1 ]

x Hide

SELECT /* BatchChemGODAO */ 'ddt' "Input", sqi.chem_nm "ChemicalName", sqi.chem_acc_txt "ChemicalID", sqi.casRN "CasRN", sqi.gene_symbol "GeneSymbol", sqi.gene_acc_txt "GeneID", sqi.ontology_nm "Ontology", sqi.go_term_nm "GoTermName", sqi.go_acc_txt "GoTermID" FROM ( WITH sq AS ( SELECT DISTINCT c.id chem_id, c.nm chem_nm, c.acc_txt chem_acc_txt, c.secondary_nm casRN, c.nm_sort chem_nm_sort, gcr.gene_id, g.nm gene_symbol, g.acc_txt gene_acc_txt, g.nm_sort gene_symbol_sort FROM term c INNER JOIN gene_chem_reference gcr ON c.id = gcr.chem_id INNER JOIN term g ON gcr.gene_id = g.id WHERE (c.id = 1285538)) SELECT DISTINCT sq.chem_nm, sq.chem_acc_txt, sq.casRN, sq.gene_symbol, sq.gene_acc_txt, gt.nm go_term_nm, gt.acc_txt go_acc_txt, sq.chem_nm_sort, sq.gene_symbol_sort, gt.nm_sort, d.nm ontology_nm FROM sq INNER JOIN gene_go_annot gga ON sq.gene_id = gga.gene_id INNER JOIN dag_node gt ON gga.go_term_id = gt.object_id INNER JOIN dag d ON gt.dag_id = d.id WHERE gga.is_not = false AND (d.id = 5 OR d.id = 4) ORDER BY sq.chem_nm_sort, sq.gene_symbol_sort, d.nm, gt.nm_sort) sqi;

Date: 2024-05-10 05:48:20
Duration: 3s839ms
Database: ctddev51
User: pubeu
Bind query: yes

x Hide

Times Reported Time consuming queries #9

x Hide

SELECT /* AdvancedGeneQueryDAO.getData */ g.id geneId, g.acc_txt acc, g.nm nm, g.nm nmHtml, g.secondary_nm secondaryNm, g.has_chems hasChems, g.has_diseases hasDiseases, g.has_exposures hasExposures, g.has_phenotypes hasPhenotypes, COUNT(*) OVER () fullRowCount FROM term g WHERE g.id IN ( SELECT gcr.gene_id FROM gene_chem_reference gcr WHERE /* CIQH.getIxnWhereCore */ gcr.gene_id = ANY (ARRAY (( SELECT /* IQH.getMasterDiseaseWhereEquals.Name.Gene */ gd.gene_id FROM term t INNER JOIN dag_path dp ON t.id = dp.ancestor_object_id INNER JOIN gene_disease gd ON dp.descendant_object_id = gd.disease_id WHERE UPPER(t.nm) LIKE 'ASTHMA' AND t.object_type_id = 3))) AND gcr.id IN ( SELECT gcra.gene_chem_reference_id FROM gene_chem_reference_axn gcra WHERE (gcra.action_degree_type_nm = 'increases'))) ORDER BY g.nm_sort, g.id LIMIT 50;

Date: 2024-05-10 05:47:24
Duration: 3s758ms
Bind query: yes

x Hide