1
2
Details
38s468ms
17s669ms
20s799ms
19s234ms
select distinct
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 || ? || ? || ? || phenotypeterm.nm_html || ? || phenotypeterm.acc_txt || ? || phenotypeterm.acc_db_cd as phenotype,
phenotypeterm.id phenotypeid,
(
select
string_agg ( distinct taxonterm.nm || ? || ? || ? || 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
phenotypeterm.id = any ( array ( (
select
p.descendant_object_id
from
dag_path p
where
p.ancestor_object_id = ?) ) )
and associatedterm.object_type_id = ?
group by
associatedterm,
associatedtermnmsort,
phenotype,
casrn,
ixnid,
ixnprosehtml,
ixnprose,
ixnsort,
associatedtermid,
phenotypeid,
inferredcount
order by
associatedtermnmsort,
pt.indirect_term_qty desc
limit ?;
Times Reported Time consuming queries #1
Day
Hour
Count
Duration
Avg duration
Aug 01 11 2 38s468ms 19s234ms
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 38s468ms - Times executed: 2 ]
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
phenotypeTerm.id = ANY ( ARRAY ( (
SELECT
p.descendant_object_id
FROM
dag_path p
WHERE
p.ancestor_object_id = '1323369') ) )
and associatedTerm.object_type_id = 2
group by
associatedTerm,
associatedTermNmSort,
phenotype,
casRN,
ixnId,
ixnProseHtml,
ixnProse,
ixnSort,
associatedTermId,
phenotypeId,
inferredCount
ORDER BY
associatedTermNmSort,
pt.indirect_term_qty desc
LIMIT 50 ;
Date: 2026-08-01 11:58:22
Duration: 20s799ms
Database: ctdprd51
User: pubeu
Bind query: yes
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
phenotypeTerm.id = ANY ( ARRAY ( (
SELECT
p.descendant_object_id
FROM
dag_path p
WHERE
p.ancestor_object_id = '1323369') ) )
and associatedTerm.object_type_id = 2
group by
associatedTerm,
associatedTermNmSort,
phenotype,
casRN,
ixnId,
ixnProseHtml,
ixnProse,
ixnSort,
associatedTermId,
phenotypeId,
inferredCount
ORDER BY
associatedTermNmSort,
pt.indirect_term_qty desc
LIMIT 50 ;
Date: 2026-08-01 11:58:39
Duration: 17s669ms
Database: ctdprd51
User: pubeu
Bind query: yes
x Hide
2
2
Details
16s549ms
8s46ms
8s503ms
8s274ms
select
g.nm genesymbol,
g.id geneid,
g.acc_txt geneacc,
g.acc_db_cd geneaccdbcd,
c.nm chemnm,
c.nm_html chemnmhtml,
c.acc_txt chemacc,
c.secondary_nm casrn,
c.id chemid,
i.id ixnid,
i.ixn_prose_txt ixnprose,
i.ixn_prose_html ixnprosehtml,
i.actions_txt ixnactions,
count ( distinct gcr.reference_id) refcount,
count ( distinct gcr.taxon_id) taxoncount,
(
select
string_agg ( distinct taxonterm.nm || ? || ? || ? || taxonterm.nm_html || ? || taxonterm.acc_txt || ? || taxonterm.acc_db_cd || ? || coalesce ( taxonterm.secondary_nm, ?) , ?) ) as taxonterms,
(
select
string_agg ( distinct r.acc_txt, ?) ) as references,
count ( * ) over ( ) fullrowcount
from
gene_chem_reference gcr
inner join ixn i on gcr.ixn_id = i.id
inner join term g on gcr.gene_id = g.id
inner join term c on gcr.chem_id = c.id
inner join reference r on gcr.reference_id = r.id
left outer join term taxonterm on gcr.taxon_id = taxonterm.id
where
exists (
select
?
from
gene_chem_ref_gene_form gf
where
gf.gene_chem_reference_id = gcr.id
and gf.gene_id = gcr.gene_id
and gf.actor_form_type_nm in (
select
tc.nm
from
actor_form_type tp,
actor_form_type tc
where
tc.subset_left_no between tp.subset_left_no and tp.subset_right_no
and ( tp.nm = ?
or tp.nm = ?
or tp.nm = ?) ) )
and gcr.gene_id = any ( array ( (
select
gi.id gene_id
from
term gi
where
gi.object_type_id = ?
and upper ( gi.nm)
like ?) ) )
and exists (
select
?
from
gene_chem_reference_axn gcra
where
gcr.id = gcra.gene_chem_reference_id
and gcra.action_type_nm in (
select
ac.nm
from
action_type ap,
action_type ac
where
ac.subset_left_no between ap.subset_left_no and ap.subset_right_no
and ( ap.nm = ?) )
and ( gcra.action_degree_type_nm = ?) )
group by
g.nm,
g.nm_sort,
g.acc_txt,
g.acc_db_cd,
g.id,
c.nm,
c.nm_html,
c.nm_sort,
c.acc_txt,
c.secondary_nm,
c.id,
i.ixn_prose_txt,
i.ixn_prose_html,
i.sort_txt,
i.actions_txt,
i.id
order by
c.nm_sort,
g.nm_sort,
i.sort_txt
limit ? offset ?;
Times Reported Time consuming queries #2
Day
Hour
Count
Duration
Avg duration
Aug 01 02 2 16s549ms 8s274ms
x Hide
Examples
SELECT
/* AdvancedIxnQueryDAO.getData */
g.nm geneSymbol,
g.id geneId,
g.acc_txt geneAcc,
g.acc_db_cd geneAccDbCd,
c.nm chemNm,
c.nm_html chemNmhtml,
c.acc_txt chemAcc,
c.secondary_nm casRN,
c.id chemId,
i.id ixnId,
i.ixn_prose_txt ixnProse,
i.ixn_prose_html ixnProseHtml,
i.actions_txt ixnActions,
COUNT ( DISTINCT gcr.reference_id) refCount,
COUNT ( DISTINCT gcr.taxon_id) taxonCount,
(
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 r.acc_txt, '|') ) as references,
COUNT ( * ) OVER ( ) fullRowCount
FROM
gene_chem_reference gcr
INNER JOIN ixn i ON gcr.ixn_id = i.id
INNER JOIN term g ON gcr.gene_id = g.id
INNER JOIN term c ON gcr.chem_id = c.id
INNER JOIN reference r on gcr.reference_id = r.id
LEFT OUTER JOIN term taxonTerm on gcr.taxon_id = taxonTerm.id
WHERE
/* CIQH.getIxnWhereCore */
EXISTS (
SELECT
/* CIQH.getIxnGeneFormTypeWhere */
1
FROM
gene_chem_ref_gene_form gf
WHERE
gf.gene_chem_reference_id = gcr.id
AND gf.gene_id = gcr.gene_id
AND gf.actor_form_type_nm IN (
SELECT
tc.nm
FROM
actor_form_type tp,
actor_form_type tc
WHERE
tc.subset_left_no BETWEEN tp.subset_left_no AND tp.subset_right_no
AND ( tp.nm = 'protein'
OR tp.nm = 'gene'
OR tp.nm = 'mRNA') ) )
AND gcr.gene_id = ANY ( ARRAY ( (
SELECT
/* CIQH.getIxnGeneWhereEquals.Name */
gi.id gene_id
FROM
term gi
WHERE
gi.object_type_id = 4
AND UPPER ( gi.nm)
LIKE 'PTGS2') ) )
AND exists (
SELECT
1
FROM
gene_chem_reference_axn gcra
WHERE
gcr.id = gcra.gene_chem_reference_id
AND gcra.action_type_nm IN (
SELECT
ac.nm
FROM
action_type ap,
action_type ac
WHERE
ac.subset_left_no BETWEEN ap.subset_left_no AND ap.subset_right_no
AND ( ap.nm = 'expression') )
AND ( gcra.action_degree_type_nm = 'increases') )
GROUP BY
g.nm,
g.nm_sort,
g.acc_txt,
g.acc_db_cd,
g.id,
c.nm,
c.nm_html,
c.nm_sort,
c.acc_txt,
c.secondary_nm,
c.id,
i.ixn_prose_txt,
i.ixn_prose_html,
i.sort_txt,
i.actions_txt,
i.id
ORDER BY
c.nm_sort,
g.nm_sort,
i.sort_txt
LIMIT 50 OFFSET 6450 ;
Date: 2026-08-01 02:04:49
Duration: 8s503ms
Bind query: yes
SELECT
/* AdvancedIxnQueryDAO.getData */
g.nm geneSymbol,
g.id geneId,
g.acc_txt geneAcc,
g.acc_db_cd geneAccDbCd,
c.nm chemNm,
c.nm_html chemNmhtml,
c.acc_txt chemAcc,
c.secondary_nm casRN,
c.id chemId,
i.id ixnId,
i.ixn_prose_txt ixnProse,
i.ixn_prose_html ixnProseHtml,
i.actions_txt ixnActions,
COUNT ( DISTINCT gcr.reference_id) refCount,
COUNT ( DISTINCT gcr.taxon_id) taxonCount,
(
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 r.acc_txt, '|') ) as references,
COUNT ( * ) OVER ( ) fullRowCount
FROM
gene_chem_reference gcr
INNER JOIN ixn i ON gcr.ixn_id = i.id
INNER JOIN term g ON gcr.gene_id = g.id
INNER JOIN term c ON gcr.chem_id = c.id
INNER JOIN reference r on gcr.reference_id = r.id
LEFT OUTER JOIN term taxonTerm on gcr.taxon_id = taxonTerm.id
WHERE
/* CIQH.getIxnWhereCore */
EXISTS (
SELECT
/* CIQH.getIxnGeneFormTypeWhere */
1
FROM
gene_chem_ref_gene_form gf
WHERE
gf.gene_chem_reference_id = gcr.id
AND gf.gene_id = gcr.gene_id
AND gf.actor_form_type_nm IN (
SELECT
tc.nm
FROM
actor_form_type tp,
actor_form_type tc
WHERE
tc.subset_left_no BETWEEN tp.subset_left_no AND tp.subset_right_no
AND ( tp.nm = 'protein'
OR tp.nm = 'gene'
OR tp.nm = 'mRNA') ) )
AND gcr.gene_id = ANY ( ARRAY ( (
SELECT
/* CIQH.getIxnGeneWhereEquals.Name */
gi.id gene_id
FROM
term gi
WHERE
gi.object_type_id = 4
AND UPPER ( gi.nm)
LIKE 'PTGS2') ) )
AND exists (
SELECT
1
FROM
gene_chem_reference_axn gcra
WHERE
gcr.id = gcra.gene_chem_reference_id
AND gcra.action_type_nm IN (
SELECT
ac.nm
FROM
action_type ap,
action_type ac
WHERE
ac.subset_left_no BETWEEN ap.subset_left_no AND ap.subset_right_no
AND ( ap.nm = 'expression') )
AND ( gcra.action_degree_type_nm = 'increases') )
GROUP BY
g.nm,
g.nm_sort,
g.acc_txt,
g.acc_db_cd,
g.id,
c.nm,
c.nm_html,
c.nm_sort,
c.acc_txt,
c.secondary_nm,
c.id,
i.ixn_prose_txt,
i.ixn_prose_html,
i.sort_txt,
i.actions_txt,
i.id
ORDER BY
c.nm_sort,
g.nm_sort,
i.sort_txt
LIMIT 50 OFFSET 6450 ;
Date: 2026-08-01 02:04:42
Duration: 8s46ms
Bind query: yes
x Hide
3
2
Details
12s244ms
5s991ms
6s252ms
6s122ms
select
? "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 = ?) )
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 = ?
or d.id = ?)
order by
sq.chem_nm_sort,
sq.gene_symbol_sort,
d.nm,
gt.nm_sort) sqi;
Times Reported Time consuming queries #3
Day
Hour
Count
Duration
Avg duration
Aug 01 05 2 12s244ms 6s122ms
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 6s252ms - Times executed: 1 ]
[ User: qaeu - Total duration: 5s991ms - 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 = 1403103 ) )
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: 2026-08-01 05:48:45
Duration: 6s252ms
Database: ctdprd51
User: pubeu
Bind query: yes
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 = 1403103 ) )
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: 2026-08-01 05:43:37
Duration: 5s991ms
Database: ctdprd51
User: qaeu
Bind query: yes
x Hide
4
2
Details
11s2ms
5s282ms
5s719ms
5s501ms
select
? : ?| ?| ?| ?| ?| ')
from
gene_disease_axn a
where
a.gene_id = gdr.gene_id
and a.disease_id = gdr.disease_id)
else
null
end ,
c.nm,
gdr.network_score
order by
d.nm_sort,
g.nm,
"DirectEvidence",
c.nm;
Times Reported Time consuming queries #4
Day
Hour
Count
Duration
Avg duration
Aug 01 20 1 5s719ms 5s719ms 21 1 5s282ms 5s282ms
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 5s719ms - Times executed: 1 ]
x Hide
SELECT
/* BatchDiseaseGeneAssnsDAO */
'diabetes mellitus "Input",
d.nm "DiseaseName",
d.acc_db_cd || ':' || d.acc_txt "DiseaseID",
g.nm "GeneSymbol",
g.acc_txt "GeneID",
(
SELECT
STRING_AGG ( stm.slim_term_nm, '|' ORDER BY stm.slim_term_nm)
FROM
slim_term_mapping stm
WHERE
stm.mapped_term_id = d.id) "DiseaseCategories",
CASE WHEN gdr.via_chem_id IS NULL THEN
(
SELECT
STRING_AGG ( a.action_type_nm, '|')
FROM
gene_disease_axn a
WHERE
a.gene_id = gdr.gene_id
AND a.disease_id = gdr.disease_id)
ELSE
NULL
END "DirectEvidence",
c.nm "InferenceChemicalName",
gdr.network_score "InferenceScore",
STRING_AGG ( gdr.source_acc_txt, '|' ORDER BY gdr.source_acc_txt) "OmimIDs",
STRING_AGG ( DISTINCT r.acc_txt, '|') "PubMedIDs"
FROM
gene_disease_reference gdr
INNER JOIN term g ON gdr.gene_id = g.id
INNER JOIN term d ON gdr.disease_id = d.id
LEFT OUTER JOIN reference r ON gdr.reference_id = r.id
LEFT OUTER JOIN term c ON gdr.via_chem_id = c.id
WHERE ( d.id = 2197412 )
GROUP BY
g.nm,
g.acc_txt,
d.nm,
d.id,
d.acc_txt,
d.acc_db_cd,
d.nm_sort,
CASE WHEN gdr.via_chem_id IS NULL THEN
(
SELECT
STRING_AGG ( a.action_type_nm, '|')
FROM
gene_disease_axn a
WHERE
a.gene_id = gdr.gene_id
AND a.disease_id = gdr.disease_id)
ELSE
NULL
END ,
c.nm,
gdr.network_score
ORDER BY
d.nm_sort,
g.nm,
"DirectEvidence",
c.nm;
Date: 2026-08-01 20:54:53
Duration: 5s719ms
Database: ctdprd51
User: pubeu
Bind query: yes
SELECT
/* BatchDiseaseGeneAssnsDAO */
'diabetes mellitus "Input",
d.nm "DiseaseName",
d.acc_db_cd || ':' || d.acc_txt "DiseaseID",
g.nm "GeneSymbol",
g.acc_txt "GeneID",
(
SELECT
STRING_AGG ( stm.slim_term_nm, '|' ORDER BY stm.slim_term_nm)
FROM
slim_term_mapping stm
WHERE
stm.mapped_term_id = d.id) "DiseaseCategories",
CASE WHEN gdr.via_chem_id IS NULL THEN
(
SELECT
STRING_AGG ( a.action_type_nm, '|')
FROM
gene_disease_axn a
WHERE
a.gene_id = gdr.gene_id
AND a.disease_id = gdr.disease_id)
ELSE
NULL
END "DirectEvidence",
c.nm "InferenceChemicalName",
gdr.network_score "InferenceScore",
STRING_AGG ( gdr.source_acc_txt, '|' ORDER BY gdr.source_acc_txt) "OmimIDs",
STRING_AGG ( DISTINCT r.acc_txt, '|') "PubMedIDs"
FROM
gene_disease_reference gdr
INNER JOIN term g ON gdr.gene_id = g.id
INNER JOIN term d ON gdr.disease_id = d.id
LEFT OUTER JOIN reference r ON gdr.reference_id = r.id
LEFT OUTER JOIN term c ON gdr.via_chem_id = c.id
WHERE ( d.id = 2197412 )
GROUP BY
g.nm,
g.acc_txt,
d.nm,
d.id,
d.acc_txt,
d.acc_db_cd,
d.nm_sort,
CASE WHEN gdr.via_chem_id IS NULL THEN
(
SELECT
STRING_AGG ( a.action_type_nm, '|')
FROM
gene_disease_axn a
WHERE
a.gene_id = gdr.gene_id
AND a.disease_id = gdr.disease_id)
ELSE
NULL
END ,
c.nm,
gdr.network_score
ORDER BY
d.nm_sort,
g.nm,
"DirectEvidence",
c.nm;
Date: 2026-08-01 21:00:07
Duration: 5s282ms
Bind query: yes
x Hide
5
2
Details
10s938ms
5s290ms
5s648ms
5s469ms
select distinct
stressorterm.nm as chemnm,
stressorterm.nm_html as chemnmhtml,
stressorterm.nm_sort as chemnmsort,
stressorterm.acc_txt as chemacc,
(
select
string_agg ( distinct stressorsrctype.nm || ? || stressorsrctype.cd, ?) ) as stressorsrctypenm,
stressor.src_details as stressorsrcdetails,
stressor.sample_qty as stressorsampleqty,
stressor.note as stressornote,
receptor.qty as nbrreceptors,
receptor.description as receptors,
receptor.note as receptornotes,
receptorterm.nm || ? || (
select
cd
from
object_type
where
id = receptor.object_type_id) || ? || receptorterm.nm_html || ? || receptorterm.acc_txt || ? || receptorterm.acc_db_cd as receptorterms,
(
select
string_agg ( distinct receptortobaccouse.tobacco_use_nm || ? || receptortobaccouse.pct, ?) ) as smokerstatus,
receptor.age as agerange,
receptor.age_uom_nm as ageuomnm,
receptor.age_qualifier_nm as agequalifiernm,
receptor.gender_nm as gendernmsearch,
receptor.id receptorid,
(
select
string_agg ( pct || ? || gender_nm || ? || gender_nm_html, ?)
from
exp_receptor_gender
where
exp_receptor_id = receptor.id) as genderdetails,
(
select
string_agg ( distinct receptorrace.race_nm || ? || receptorrace.pct, ?) ) as receptorrace,
(
select
string_agg ( distinct eventassaymethod.nm, ?) ) as assaymethods,
event.medium_nm as medium,
event.medium_term_acc_txt as mediumacctxt,
(
select
string_agg ( distinct eventproject.project_nm, ?) ) as associatedstudytitles,
event.collection_start_yr || ? || event.collection_end_yr as collectionstartandendyr,
event.detection_limit as detectionlimit,
event.detection_limit_uom as detectionlimituom,
event.detection_freq as detectionfreq,
event.note as eventnote,
(
select
string_agg ( distinct eventlocation.geographic_region_nm, ?) ) as stateorprovince,
(
select
string_agg ( distinct eventlocation.locality_txt, ?) ) as localitytxt,
(
select
string_agg ( distinct country.nm, ?) ) as studycountries,
exposuremarkerterm.nm || ? || (
select
cd
from
object_type
where
id = exposuremarkerterm.object_type_id) || ? || exposuremarkerterm.nm_html || ? || exposuremarkerterm.acc_txt || ? || exposuremarkerterm.acc_db_cd as assayedmarkers,
event.exp_marker_lvl as assaylevel,
assay_uom as measurement,
assay_measurement_stat as measurementstat,
assay_note as assaynote,
eiot.description as outcomerltnp,
diseaseterm.nm || ? || ? || ? || diseaseterm.nm_html || ? || diseaseterm.acc_txt || ? || diseaseterm.acc_db_cd as diseasefield,
phenotypeterm.nm || ? || ? || ? || phenotypeterm.nm_html || ? || phenotypeterm.acc_txt || ? || phenotypeterm.acc_db_cd as phenotypefield,
outcome.phenotype_action_degree_type_nm as phenotypeactiondegreetypenm,
e.reference_acc_txt || ? || r.abbr_authors_txt || ? || r.pub_start_yr as ref,
r.abbr_authors_txt as abbrauthorstxt,
(
select
string_agg ( distinct expstudyfactor.study_factor_nm, ?) ) as studyfactornms,
(
select
string_agg ( distinct anatomyterm.nm_html || ? || anatomyterm.acc_txt || ? || anatomyterm.id || ? || anatomyterm.acc_db_cd || ? || anatomyterm.nm, ?) ) as anatomyterms,
outcome.note as outcomenote,
eventlocation.exp_event_id as eventid,
count ( * ) over ( ) fullrowcount
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
where
stressorterm.id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = ?)
or exposuremarkerterm.id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = ?)
group by
chemnm,
chemnmhtml,
chemnmsort,
chemacc,
stressorsrcdetails,
stressorsampleqty,
stressornote,
receptorterms,
medium,
mediumacctxt,
assayedmarkers,
assaylevel,
measurement,
measurementstat,
assaynote,
outcomerltnp,
diseasefield,
phenotypefield,
phenotypeactiondegreetypenm,
ref,
r.abbr_authors_txt,
collectionstartandendyr,
receptorid,
detectionlimit,
detectionlimituom,
detectionfreq,
eventnote,
outcomenote,
eventid
order by
chemnmsort
limit ?;
Times Reported Time consuming queries #5
Day
Hour
Count
Duration
Avg duration
Aug 01 07 2 10s938ms 5s469ms
x Hide
Examples User(s) involved
[ User: pubeu - Total duration: 10s938ms - Times executed: 2 ]
x Hide
SELECT DISTINCT
stressorTerm.nm as chemNm,
stressorTerm.nm_html as chemNmHtml,
stressorTerm.nm_sort as chemNmSort,
stressorTerm.acc_txt as chemAcc,
(
SELECT
STRING_AGG ( distinct stressorSrcType.nm || '^' || stressorSrcType.cd, '|') ) as stressorSrcTypeNm,
stressor.src_details as stressorSrcDetails,
stressor.sample_qty as stressorSampleQty,
stressor.note as stressorNote,
receptor.qty as nbrReceptors,
receptor.description as receptors,
receptor.note as receptorNotes,
receptorTerm.nm || '^' || (
select
cd
from
object_type
where
id = receptor.object_type_id) || '^' || receptorTerm.nm_html || '^' || receptorTerm.acc_txt || '^' || receptorTerm.acc_db_cd as receptorTerms,
(
SELECT
STRING_AGG ( distinct receptorTobaccoUse.tobacco_use_nm || '^' || receptorTobaccoUse.pct, ' | ') ) as smokerStatus,
receptor.age as ageRange,
receptor.age_uom_nm as ageUOMNm,
receptor.age_qualifier_nm as ageQualifierNm,
receptor.gender_nm as genderNmSearch,
receptor.id receptorID,
(
SELECT
STRING_AGG ( pct || '^' || gender_nm || '^' || gender_nm_html, '|')
from
exp_receptor_gender
where
exp_receptor_id = receptor.id) as genderDetails,
(
SELECT
STRING_AGG ( DISTINCT receptorRace.race_nm || '^' || receptorRace.pct, ' | ') ) as receptorRace,
(
SELECT
STRING_AGG ( DISTINCT eventAssayMethod.nm, ' | ') ) as assayMethods,
event.medium_nm as medium,
event.medium_term_acc_txt as mediumAccTxt,
(
SELECT
STRING_AGG ( DISTINCT eventProject.project_nm, ' | ') ) as associatedStudyTitles,
event.collection_start_yr || '-' || event.collection_end_yr as collectionStartAndEndYr,
event.detection_limit as detectionLimit,
event.detection_limit_uom as detectionLimitUOM,
event.detection_freq as detectionFreq,
event.note as eventNote,
(
SELECT
STRING_AGG ( DISTINCT eventLocation.geographic_region_nm, ' | ') ) as stateOrProvince,
(
SELECT
STRING_AGG ( DISTINCT eventLocation.locality_txt, ' | ') ) as localityTxt,
(
SELECT
STRING_AGG ( distinct country.nm, ' | ') ) as studyCountries,
exposureMarkerTerm.nm || '^' || (
select
cd
from
object_type
where
id = exposureMarkerTerm.object_type_id) || '^' || exposureMarkerTerm.nm_html || '^' || exposureMarkerTerm.acc_txt || '^' || exposureMarkerTerm.acc_db_cd as assayedMarkers,
event.exp_marker_lvl as assayLevel,
assay_uom as measurement,
assay_measurement_stat as measurementStat,
assay_note as assayNote,
eiot.description as outcomeRltnp,
diseaseTerm.nm || '^' || 'disease' || '^' || diseaseTerm.nm_html || '^' || diseaseTerm.acc_txt || '^' || diseaseTerm.acc_db_cd as diseaseField,
phenotypeTerm.nm || '^' || 'go' || '^' || phenotypeTerm.nm_html || '^' || phenotypeTerm.acc_txt || '^' || phenotypeTerm.acc_db_cd as phenotypeField,
outcome.phenotype_action_degree_type_nm as phenotypeActionDegreeTypeNm,
e.reference_acc_txt || '^' || r.abbr_authors_txt || '^' || r.pub_start_yr as ref,
r.abbr_authors_txt as abbrAuthorsTxt,
(
SELECT
STRING_AGG ( DISTINCT expStudyFactor.study_factor_nm, ' | ') ) as studyFactorNms,
(
SELECT
STRING_AGG ( distinct anatomyTerm.nm_html || '^' || anatomyTerm.acc_txt || '^' || anatomyTerm.id || '^' || anatomyTerm.acc_db_cd || '^' || anatomyTerm.nm, '|') ) as anatomyTerms,
outcome.note as outcomeNote,
eventLocation.exp_event_id as eventID,
COUNT ( * ) OVER ( ) fullRowCount
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
where
stressorTerm.id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = '1441693')
or exposureMarkerTerm.id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = '1441693')
GROUP BY
chemNm,
chemNmHtml,
chemNmSort,
chemAcc,
stressorSrcDetails,
stressorSampleQty,
stressorNote,
receptorTerms,
medium,
mediumAccTxt,
assayedMarkers,
assayLevel,
measurement,
measurementStat,
assayNote,
outcomeRltnp,
diseaseField,
phenotypeField,
phenotypeActionDegreeTypeNm,
ref,
r.abbr_authors_txt,
collectionStartAndEndYr,
receptorID,
detectionLimit,
detectionLimitUOM,
detectionFreq,
eventNote,
outcomeNote,
eventID
order by
chemNmSort
LIMIT 50 ;
Date: 2026-08-01 07:30:07
Duration: 5s648ms
Database: ctdprd51
User: pubeu
Bind query: yes
SELECT DISTINCT
stressorTerm.nm as chemNm,
stressorTerm.nm_html as chemNmHtml,
stressorTerm.nm_sort as chemNmSort,
stressorTerm.acc_txt as chemAcc,
(
SELECT
STRING_AGG ( distinct stressorSrcType.nm || '^' || stressorSrcType.cd, '|') ) as stressorSrcTypeNm,
stressor.src_details as stressorSrcDetails,
stressor.sample_qty as stressorSampleQty,
stressor.note as stressorNote,
receptor.qty as nbrReceptors,
receptor.description as receptors,
receptor.note as receptorNotes,
receptorTerm.nm || '^' || (
select
cd
from
object_type
where
id = receptor.object_type_id) || '^' || receptorTerm.nm_html || '^' || receptorTerm.acc_txt || '^' || receptorTerm.acc_db_cd as receptorTerms,
(
SELECT
STRING_AGG ( distinct receptorTobaccoUse.tobacco_use_nm || '^' || receptorTobaccoUse.pct, ' | ') ) as smokerStatus,
receptor.age as ageRange,
receptor.age_uom_nm as ageUOMNm,
receptor.age_qualifier_nm as ageQualifierNm,
receptor.gender_nm as genderNmSearch,
receptor.id receptorID,
(
SELECT
STRING_AGG ( pct || '^' || gender_nm || '^' || gender_nm_html, '|')
from
exp_receptor_gender
where
exp_receptor_id = receptor.id) as genderDetails,
(
SELECT
STRING_AGG ( DISTINCT receptorRace.race_nm || '^' || receptorRace.pct, ' | ') ) as receptorRace,
(
SELECT
STRING_AGG ( DISTINCT eventAssayMethod.nm, ' | ') ) as assayMethods,
event.medium_nm as medium,
event.medium_term_acc_txt as mediumAccTxt,
(
SELECT
STRING_AGG ( DISTINCT eventProject.project_nm, ' | ') ) as associatedStudyTitles,
event.collection_start_yr || '-' || event.collection_end_yr as collectionStartAndEndYr,
event.detection_limit as detectionLimit,
event.detection_limit_uom as detectionLimitUOM,
event.detection_freq as detectionFreq,
event.note as eventNote,
(
SELECT
STRING_AGG ( DISTINCT eventLocation.geographic_region_nm, ' | ') ) as stateOrProvince,
(
SELECT
STRING_AGG ( DISTINCT eventLocation.locality_txt, ' | ') ) as localityTxt,
(
SELECT
STRING_AGG ( distinct country.nm, ' | ') ) as studyCountries,
exposureMarkerTerm.nm || '^' || (
select
cd
from
object_type
where
id = exposureMarkerTerm.object_type_id) || '^' || exposureMarkerTerm.nm_html || '^' || exposureMarkerTerm.acc_txt || '^' || exposureMarkerTerm.acc_db_cd as assayedMarkers,
event.exp_marker_lvl as assayLevel,
assay_uom as measurement,
assay_measurement_stat as measurementStat,
assay_note as assayNote,
eiot.description as outcomeRltnp,
diseaseTerm.nm || '^' || 'disease' || '^' || diseaseTerm.nm_html || '^' || diseaseTerm.acc_txt || '^' || diseaseTerm.acc_db_cd as diseaseField,
phenotypeTerm.nm || '^' || 'go' || '^' || phenotypeTerm.nm_html || '^' || phenotypeTerm.acc_txt || '^' || phenotypeTerm.acc_db_cd as phenotypeField,
outcome.phenotype_action_degree_type_nm as phenotypeActionDegreeTypeNm,
e.reference_acc_txt || '^' || r.abbr_authors_txt || '^' || r.pub_start_yr as ref,
r.abbr_authors_txt as abbrAuthorsTxt,
(
SELECT
STRING_AGG ( DISTINCT expStudyFactor.study_factor_nm, ' | ') ) as studyFactorNms,
(
SELECT
STRING_AGG ( distinct anatomyTerm.nm_html || '^' || anatomyTerm.acc_txt || '^' || anatomyTerm.id || '^' || anatomyTerm.acc_db_cd || '^' || anatomyTerm.nm, '|') ) as anatomyTerms,
outcome.note as outcomeNote,
eventLocation.exp_event_id as eventID,
COUNT ( * ) OVER ( ) fullRowCount
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
where
stressorTerm.id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = '1441693')
or exposureMarkerTerm.id in (
select
descendant_object_id
from
dag_path
where
ancestor_object_id = '1441693')
GROUP BY
chemNm,
chemNmHtml,
chemNmSort,
chemAcc,
stressorSrcDetails,
stressorSampleQty,
stressorNote,
receptorTerms,
medium,
mediumAccTxt,
assayedMarkers,
assayLevel,
measurement,
measurementStat,
assayNote,
outcomeRltnp,
diseaseField,
phenotypeField,
phenotypeActionDegreeTypeNm,
ref,
r.abbr_authors_txt,
collectionStartAndEndYr,
receptorID,
detectionLimit,
detectionLimitUOM,
detectionFreq,
eventNote,
outcomeNote,
eventID
order by
chemNmSort
LIMIT 50 ;
Date: 2026-08-01 07:30:11
Duration: 5s290ms
Database: ctdprd51
User: pubeu
Bind query: yes
x Hide
6
1
Details
28m2s
28m2s
28m2s
28m2s
copy pub1.gene_disease_reference ( id, gene_id, disease_id, reference_id, source_acc_txt, source_acc_db_id, via_chem_id, ixn_id, network_score, source_cd, mod_tm)
to stdout ;
Times Reported Time consuming queries #6
Day
Hour
Count
Duration
Avg duration
Aug 01 18 1 28m2s 28m2s
x Hide
Examples
COPY pub1.gene_disease_reference ( id, gene_id, disease_id, reference_id, source_acc_txt, source_acc_db_id, via_chem_id, ixn_id, network_score, source_cd, mod_tm)
TO stdout ;
Date: 2026-08-01 18:46:01
Duration: 28m2s
x Hide
7
1
Details
27m53s
27m53s
27m53s
27m53s
copy pub2.gene_disease_reference ( id, gene_id, disease_id, reference_id, source_acc_txt, source_acc_db_id, via_chem_id, ixn_id, network_score, source_cd, mod_tm)
to stdout ;
Times Reported Time consuming queries #7
Day
Hour
Count
Duration
Avg duration
Aug 01 19 1 27m53s 27m53s
x Hide
Examples
COPY pub2.gene_disease_reference ( id, gene_id, disease_id, reference_id, source_acc_txt, source_acc_db_id, via_chem_id, ixn_id, network_score, source_cd, mod_tm)
TO stdout ;
Date: 2026-08-01 19:32:39
Duration: 27m53s
x Hide
8
1
Details
9m21s
9m21s
9m21s
9m21s
select
maint_query_logs_archive ( ) ;
Times Reported Time consuming queries #8
Day
Hour
Count
Duration
Avg duration
Aug 01 00 1 9m21s 9m21s
x Hide
Examples User(s) involved App(s) involved
[ User: pubc - Total duration: 9m21s - Times executed: 1 ]
x Hide
[ Application: psql - Total duration: 9m21s - Times executed: 1 ]
x Hide
/*
* Run daily to prune LOG_QUERY, archive old queries to LOG_QUERY_ARCHIVE
* and vacuum/analyze the tables.
*
* $Id: archive_query_logs.sql 10832 2012-03-19 15:27:11Z mcr $
*/
SELECT
maint_query_logs_archive ( ) ;
Date: 2026-08-01 00:09:23
Duration: 9m21s
Database: ctdprd51
User: pubc
Application: psql
x Hide
9
1
Details
6m56s
6m56s
6m56s
6m56s
copy pub2.term_enrichment_agent ( term_id, enriched_term_id, agent_term_id)
to stdout ;
Times Reported Time consuming queries #9
Day
Hour
Count
Duration
Avg duration
Aug 01 19 1 6m56s 6m56s
x Hide
Examples
COPY pub2.term_enrichment_agent ( term_id, enriched_term_id, agent_term_id)
TO stdout ;
Date: 2026-08-01 19:45:21
Duration: 6m56s
x Hide
10
1
Details
6m54s
6m54s
6m54s
6m54s
copy pub1.term_enrichment_agent ( term_id, enriched_term_id, agent_term_id)
to stdout ;
Times Reported Time consuming queries #10
Day
Hour
Count
Duration
Avg duration
Aug 01 18 1 6m54s 6m54s
x Hide
Examples
COPY pub1.term_enrichment_agent ( term_id, enriched_term_id, agent_term_id)
TO stdout ;
Date: 2026-08-01 18:58:43
Duration: 6m54s
x Hide
11
1
Details
1m52s
1m52s
1m52s
1m52s
copy pubc.log_query_archive ( id, type_cd, query_tm, submission_qty, session_id, remote_addr, http_user_agent, server_nm, node_nm, results_qty, execution_ms, basic_query_type, basic_query_txt, gene_query_type, gene_txt, gene_form_type_txt, taxon_query_type, taxon_txt, chem_query_type, chem_txt, party_query_type, party_nm_txt, acc_txt, go_query_type, go_txt, disease_query_type, disease_txt, action_type_txt, action_degree_type_txt, from_yr, through_yr, title_abstract_txt, has_marray, gene_set_txt, molecule_type_txt, volume_txt, first_page_txt, journal_query_type, journal_txt, is_review, pathway_query_type, pathway_txt, dag_txt, results_format_txt, batch_input_type_txt, gd_assn_type, p_val, p_val_type, input_term_qty, review_status)
to stdout ;
Times Reported Time consuming queries #11
Day
Hour
Count
Duration
Avg duration
Aug 01 19 1 1m52s 1m52s
x Hide
Examples
COPY pubc.log_query_archive ( id, type_cd, query_tm, submission_qty, session_id, remote_addr, http_user_agent, server_nm, node_nm, results_qty, execution_ms, basic_query_type, basic_query_txt, gene_query_type, gene_txt, gene_form_type_txt, taxon_query_type, taxon_txt, chem_query_type, chem_txt, party_query_type, party_nm_txt, acc_txt, go_query_type, go_txt, disease_query_type, disease_txt, action_type_txt, action_degree_type_txt, from_yr, through_yr, title_abstract_txt, has_marray, gene_set_txt, molecule_type_txt, volume_txt, first_page_txt, journal_query_type, journal_txt, is_review, pathway_query_type, pathway_txt, dag_txt, results_format_txt, batch_input_type_txt, gd_assn_type, p_val, p_val_type, input_term_qty, review_status)
TO stdout ;
Date: 2026-08-01 19:49:07
Duration: 1m52s
x Hide
12
1
Details
1m44s
1m44s
1m44s
1m44s
copy pub1.phenotype_term_reference ( id, phenotype_id, term_id, term_object_type_id, reference_id, taxon_id, ixn_id, evidence_cd, source_cd, source_acc_txt, source_acc_db_id, term_reference_id, via_term_id, via_term_object_type_id, network_score, mod_tm)
to stdout ;
Times Reported Time consuming queries #12
Day
Hour
Count
Duration
Avg duration
Aug 01 18 1 1m44s 1m44s
x Hide
Examples
COPY pub1.phenotype_term_reference ( id, phenotype_id, term_id, term_object_type_id, reference_id, taxon_id, ixn_id, evidence_cd, source_cd, source_acc_txt, source_acc_db_id, term_reference_id, via_term_id, via_term_object_type_id, network_score, mod_tm)
TO stdout ;
Date: 2026-08-01 18:49:42
Duration: 1m44s
x Hide
13
1
Details
1m43s
1m43s
1m43s
1m43s
copy pub2.phenotype_term_reference ( id, phenotype_id, term_id, term_object_type_id, reference_id, taxon_id, ixn_id, evidence_cd, source_cd, source_acc_txt, source_acc_db_id, term_reference_id, via_term_id, via_term_object_type_id, network_score, mod_tm)
to stdout ;
Times Reported Time consuming queries #13
Day
Hour
Count
Duration
Avg duration
Aug 01 19 1 1m43s 1m43s
x Hide
Examples
COPY pub2.phenotype_term_reference ( id, phenotype_id, term_id, term_object_type_id, reference_id, taxon_id, ixn_id, evidence_cd, source_cd, source_acc_txt, source_acc_db_id, term_reference_id, via_term_id, via_term_object_type_id, network_score, mod_tm)
TO stdout ;
Date: 2026-08-01 19:36:19
Duration: 1m43s
x Hide
14
1
Details
1m22s
1m22s
1m22s
1m22s
copy pub2.dag_path ( id, ancestor_dag_node_id, descendant_dag_node_id, ancestor_object_id, descendant_object_id, path_length, enumeration_txt)
to stdout ;
Times Reported Time consuming queries #14
Day
Hour
Count
Duration
Avg duration
Aug 01 19 1 1m22s 1m22s
x Hide
Examples
COPY pub2.dag_path ( id, ancestor_dag_node_id, descendant_dag_node_id, ancestor_object_id, descendant_object_id, path_length, enumeration_txt)
TO stdout ;
Date: 2026-08-01 19:02:21
Duration: 1m22s
x Hide
15
1
Details
1m22s
1m22s
1m22s
1m22s
copy pub1.dag_path ( id, ancestor_dag_node_id, descendant_dag_node_id, ancestor_object_id, descendant_object_id, path_length, enumeration_txt)
to stdout ;
Times Reported Time consuming queries #15
Day
Hour
Count
Duration
Avg duration
Aug 01 18 1 1m22s 1m22s
x Hide
Examples
COPY pub1.dag_path ( id, ancestor_dag_node_id, descendant_dag_node_id, ancestor_object_id, descendant_object_id, path_length, enumeration_txt)
TO stdout ;
Date: 2026-08-01 18:15:34
Duration: 1m22s
x Hide
16
1
Details
1m4s
1m4s
1m4s
1m4s
copy pub2.dag_path_step ( dag_path_id, step_no, dag_node_id, dag_edge_type_id)
to stdout ;
Times Reported Time consuming queries #16
Day
Hour
Count
Duration
Avg duration
Aug 01 19 1 1m4s 1m4s
x Hide
Examples
COPY pub2.dag_path_step ( dag_path_id, step_no, dag_node_id, dag_edge_type_id)
TO stdout ;
Date: 2026-08-01 19:03:26
Duration: 1m4s
x Hide
17
1
Details
1m4s
1m4s
1m4s
1m4s
copy pub1.dag_path_step ( dag_path_id, step_no, dag_node_id, dag_edge_type_id)
to stdout ;
Times Reported Time consuming queries #17
Day
Hour
Count
Duration
Avg duration
Aug 01 18 1 1m4s 1m4s
x Hide
Examples
COPY pub1.dag_path_step ( dag_path_id, step_no, dag_node_id, dag_edge_type_id)
TO stdout ;
Date: 2026-08-01 18:16:39
Duration: 1m4s
x Hide
18
1
Details
55s837ms
55s837ms
55s837ms
55s837ms
copy pub1.chem_disease_reference ( id, chem_id, disease_id, reference_id, source_acc_txt, source_acc_db_id, via_gene_id, ixn_id, network_score, source_cd, mod_tm)
to stdout ;
Times Reported Time consuming queries #18
Day
Hour
Count
Duration
Avg duration
Aug 01 18 1 55s837ms 55s837ms
x Hide
Examples
COPY pub1.chem_disease_reference ( id, chem_id, disease_id, reference_id, source_acc_txt, source_acc_db_id, via_gene_id, ixn_id, network_score, source_cd, mod_tm)
TO stdout ;
Date: 2026-08-01 18:14:02
Duration: 55s837ms
x Hide
19
1
Details
55s777ms
55s777ms
55s777ms
55s777ms
copy pub2.chem_disease_reference ( id, chem_id, disease_id, reference_id, source_acc_txt, source_acc_db_id, via_gene_id, ixn_id, network_score, source_cd, mod_tm)
to stdout ;
Times Reported Time consuming queries #19
Day
Hour
Count
Duration
Avg duration
Aug 01 19 1 55s777ms 55s777ms
x Hide
Examples
COPY pub2.chem_disease_reference ( id, chem_id, disease_id, reference_id, source_acc_txt, source_acc_db_id, via_gene_id, ixn_id, network_score, source_cd, mod_tm)
TO stdout ;
Date: 2026-08-01 19:00:50
Duration: 55s777ms
x Hide
20
1
Details
52s487ms
52s487ms
52s487ms
52s487ms
copy pub1.term ( id, object_type_id, acc_txt, acc_db_cd, nm, nm_sort, nm_html, secondary_nm, description, note, is_leaf, new_ixn_qty, ixn_qty, has_chems, has_diseases, has_genes, has_go, has_ixns, has_marrays, has_pathways, has_comps, has_references, has_exposures, has_phenotypes, has_ccc, curated_edge_qty, gene_edge_qty, nm_fts)
to stdout ;
Times Reported Time consuming queries #20
Day
Hour
Count
Duration
Avg duration
Aug 01 18 1 52s487ms 52s487ms
x Hide
Examples
COPY pub1.term ( id, object_type_id, acc_txt, acc_db_cd, nm, nm_sort, nm_html, secondary_nm, description, note, is_leaf, new_ixn_qty, ixn_qty, has_chems, has_diseases, has_genes, has_go, has_ixns, has_marrays, has_pathways, has_comps, has_references, has_exposures, has_phenotypes, has_ccc, curated_edge_qty, gene_edge_qty, nm_fts)
TO stdout ;
Date: 2026-08-01 18:51:08
Duration: 52s487ms
x Hide
pgBadger