Times Reported Time consuming queries #1

x Hide

[ User: pubc - Total duration: 1m34s - Times executed: 1 ]

x Hide

[ Application: pgAdmin 4 - CONN:1610913 - Total duration: 1m34s - Times executed: 1 ]

x Hide

--begin transaction INSERT INTO log_query_bots (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, 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) SELECT q.id, q.type_cd, q.query_tm, q.submission_qty, q.session_id, q.remote_addr, q.http_user_agent, q.server_nm, q.node_nm, q.results_qty, q.execution_ms, q.basic_query_type, q.basic_query_txt, q.gene_query_type, q.gene_txt, q.gene_form_type_txt, q.taxon_query_type, q.taxon_txt, q.chem_query_type, q.chem_txt, q.party_query_type, q.party_nm_txt, q.acc_txt, q.go_query_type, q.go_txt, q.disease_query_type, q.disease_txt, q.action_type_txt, q.action_degree_type_txt, q.from_yr, q.through_yr, q.title_abstract_txt, q.has_marray, q.pathway_query_type, q.pathway_txt, q.dag_txt, q.results_format_txt, q.batch_input_type_txt, q.gd_assn_type, q.p_val, q.p_val_type, q.input_term_qty, q.review_status FROM log_query_archive q WHERE upper(q.http_user_agent) LIKE upper('%ARCHIVE%') OR upper(q.http_user_agent) LIKE upper('%SPIDER%') OR upper(q.http_user_agent) LIKE upper('%FACEBOOK%') OR upper(q.http_user_agent) LIKE upper('%NUTCH%') OR upper(q.http_user_agent) LIKE upper('%TRACK%') OR upper(q.http_user_agent) LIKE upper('%SITESUCKER%') OR upper(q.http_user_agent) LIKE upper('%KNOWLEDGE AI%') OR upper(q.http_user_agent) LIKE upper('%MEGAINDEX%') OR upper(q.http_user_agent) LIKE upper('%YANDEX%') OR upper(q.http_user_agent) LIKE upper('%PANSCIENT%') AND q.id NOT IN ( SELECT a.id FROM log_query_bots a);

Date: 2025-03-26 11:49:38
Duration: 1m34s
Database: ctddev51
User: pubc
Application: pgAdmin 4 - CONN:1610913

x Hide

Times Reported Time consuming queries #2

x Hide

[ User: pubeu - Total duration: 7s673ms - Times executed: 2 ]

x Hide

SELECT /* CompsPrepDAO.cg */ DISTINCT gcr.chem_id, gcr.gene_id, gcra.action_degree_type_nm, gcra.action_type_cd FROM gene_chem_reference gcr INNER JOIN gene_chem_reference_axn gcra ON gcr.id = gcra.gene_chem_reference_id WHERE gcra.action_type_nm IN ('activity', 'binding', 'expression');

Date: 2025-03-26 10:31:38
Duration: 4s463ms
Bind query: yes

SELECT /* CompsPrepDAO.cg */ DISTINCT gcr.chem_id, gcr.gene_id, gcra.action_degree_type_nm, gcra.action_type_cd FROM gene_chem_reference gcr INNER JOIN gene_chem_reference_axn gcra ON gcr.id = gcra.gene_chem_reference_id WHERE gcra.action_type_nm IN ('activity', 'binding', 'expression');

Date: 2025-03-26 10:18:48
Duration: 4s257ms
Bind query: yes

SELECT /* CompsPrepDAO.cg */ DISTINCT gcr.chem_id, gcr.gene_id, gcra.action_degree_type_nm, gcra.action_type_cd FROM gene_chem_reference gcr INNER JOIN gene_chem_reference_axn gcra ON gcr.id = gcra.gene_chem_reference_id WHERE gcra.action_type_nm IN ('activity', 'binding', 'expression');

Date: 2025-03-26 10:31:46
Duration: 4s135ms
Database: ctddev51
User: pubeu
Bind query: yes

x Hide

Times Reported Time consuming queries #3

x Hide

[ User: pubeu - Total duration: 19s163ms - Times executed: 3 ]

x Hide

select p.ancestor_object_id, p.descendant_object_id from DAG_PATH p where p.descendant_object_id in ( select go_term_id from GENE_GO_ANNOT gga where gga.taxon_id = ( select id from TERM where acc_txt = '9606' and object_type_id = ( select id from OBJECT_TYPE where cd = 'taxon')) AND gga.is_not = 'f') and p.ancestor_object_id NOT in ( SELECT c.id FROM TERM c WHERE c.acc_txt in ('ALL') AND c.object_type_id = ( select id from OBJECT_TYPE where cd = 'go'));

Date: 2025-03-26 10:18:29
Duration: 6s592ms
Database: ctddev51
User: pubeu
Bind query: yes

select p.ancestor_object_id, p.descendant_object_id from DAG_PATH p where p.descendant_object_id in ( select go_term_id from GENE_GO_ANNOT gga where gga.taxon_id = ( select id from TERM where acc_txt = '9606' and object_type_id = ( select id from OBJECT_TYPE where cd = 'taxon')) AND gga.is_not = 'f') and p.ancestor_object_id NOT in ( SELECT c.id FROM TERM c WHERE c.acc_txt in ('ALL') AND c.object_type_id = ( select id from OBJECT_TYPE where cd = 'go'));

Date: 2025-03-26 10:31:17
Duration: 6s527ms
Database: ctddev51
User: pubeu
Bind query: yes

select p.ancestor_object_id, p.descendant_object_id from DAG_PATH p where p.descendant_object_id in ( select go_term_id from GENE_GO_ANNOT gga where gga.taxon_id = ( select id from TERM where acc_txt = '9606' and object_type_id = ( select id from OBJECT_TYPE where cd = 'taxon')) AND gga.is_not = 'f') and p.ancestor_object_id NOT in ( SELECT c.id FROM TERM c WHERE c.acc_txt in ('ALL') AND c.object_type_id = ( select id from OBJECT_TYPE where cd = 'go'));

Date: 2025-03-26 10:41:53
Duration: 6s44ms
Database: ctddev51
User: pubeu
Bind query: yes

x Hide

Times Reported Time consuming queries #4

x Hide

[ User: pubeu - Total duration: 14s359ms - Times executed: 4 ]

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 = '597156' 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: 2025-03-26 10:18:25
Duration: 3s802ms
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 = '597156' 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: 2025-03-26 10:31:13
Duration: 3s728ms
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 = '597156' 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: 2025-03-26 10:41:49
Duration: 3s437ms
Database: ctddev51
User: pubeu
Bind query: yes

x Hide

Times Reported Time consuming queries #5

x Hide

select count(*) FROM log_query_archive q WHERE upper(q.http_user_agent) LIKE upper('%ARCHIVE%') OR upper(q.http_user_agent) LIKE upper('%SPIDER%') OR upper(q.http_user_agent) LIKE upper('%FACEBOOK%') OR upper(q.http_user_agent) LIKE upper('%NUTCH%') OR upper(q.http_user_agent) LIKE upper('%TRACK%') OR upper(q.http_user_agent) LIKE upper('%SITESUCKER%') OR upper(q.http_user_agent) LIKE upper('%KNOWLEDGE AI%') OR upper(q.http_user_agent) LIKE upper('%MEGAINDEX%') OR upper(q.http_user_agent) LIKE upper('%YANDEX%') OR upper(q.http_user_agent) LIKE upper('%PANSCIENT%');

Date: 2025-03-26 11:53:48
Duration: 13s81ms

x Hide

Times Reported Time consuming queries #6

x Hide

[ User: pubc - Total duration: 9s277ms - Times executed: 1 ]

x Hide

[ Application: pgAdmin 4 - CONN:9220482 - Total duration: 9s277ms - Times executed: 1 ]

x Hide

SELECT count(*) FROM log_query_bots AS l, excluded_user_agent2 AS ua WHERE UPPER(l.http_user_agent) LIKE UPPER(ua.user_agent_pattern);

Date: 2025-03-26 10:51:22
Duration: 9s277ms
Database: ctddev51
User: pubc
Application: pgAdmin 4 - CONN:9220482

x Hide

Times Reported Time consuming queries #7

x Hide

select gene_id, chem_id from GENE_CHEM_REFERENCE r where gene_id in ( select gene_id from GENE_TAXON where taxon_id = ( select id from TERM where acc_txt = '9606' and object_type_id = ( select id from OBJECT_TYPE where cd = 'taxon'))) and gene_id in ( select gene_id from GENE_GO_ANNOT where taxon_id = ( select id from TERM where acc_txt = '9606' and object_type_id = ( select id from OBJECT_TYPE where cd = 'taxon')) AND is_not = 'f' and go_term_id not in ( SELECT c.id FROM TERM c WHERE c.acc_txt in ('ALL') AND c.object_type_id = ( select id from OBJECT_TYPE where cd = 'go'))) and r.id NOT in ( SELECT gene_chem_reference_id FROM GENE_CHEM_REFERENCE_AXN WHERE action_degree_type_nm = 'does not affect');

Date: 2025-03-26 10:18:38
Duration: 2s221ms
Bind query: yes

select gene_id, chem_id from GENE_CHEM_REFERENCE r where gene_id in ( select gene_id from GENE_TAXON where taxon_id = ( select id from TERM where acc_txt = '9606' and object_type_id = ( select id from OBJECT_TYPE where cd = 'taxon'))) and gene_id in ( select gene_id from GENE_GO_ANNOT where taxon_id = ( select id from TERM where acc_txt = '9606' and object_type_id = ( select id from OBJECT_TYPE where cd = 'taxon')) AND is_not = 'f' and go_term_id not in ( SELECT c.id FROM TERM c WHERE c.acc_txt in ('ALL') AND c.object_type_id = ( select id from OBJECT_TYPE where cd = 'go'))) and r.id NOT in ( SELECT gene_chem_reference_id FROM GENE_CHEM_REFERENCE_AXN WHERE action_degree_type_nm = 'does not affect');

Date: 2025-03-26 10:42:03
Duration: 1s991ms
Bind query: yes

select gene_id, chem_id from GENE_CHEM_REFERENCE r where gene_id in ( select gene_id from GENE_TAXON where taxon_id = ( select id from TERM where acc_txt = '9606' and object_type_id = ( select id from OBJECT_TYPE where cd = 'taxon'))) and gene_id in ( select gene_id from GENE_GO_ANNOT where taxon_id = ( select id from TERM where acc_txt = '9606' and object_type_id = ( select id from OBJECT_TYPE where cd = 'taxon')) AND is_not = 'f' and go_term_id not in ( SELECT c.id FROM TERM c WHERE c.acc_txt in ('ALL') AND c.object_type_id = ( select id from OBJECT_TYPE where cd = 'go'))) and r.id NOT in ( SELECT gene_chem_reference_id FROM GENE_CHEM_REFERENCE_AXN WHERE action_degree_type_nm = 'does not affect');

Date: 2025-03-26 10:31:28
Duration: 1s889ms
Bind query: yes

x Hide

Times Reported Time consuming queries #8

x Hide

select p.ancestor_object_id, p.descendant_object_id from DAG_PATH p where p.descendant_object_id in ( select chem_id from GENE_CHEM_REFERENCE gcr where gcr.gene_id in ( select gene_id from GENE_TAXON where taxon_id = ( select id from TERM where acc_txt = '9606' and object_type_id = ( select id from OBJECT_TYPE where cd = 'taxon')))) AND p.ancestor_object_id != ( SELECT c.id FROM TERM c WHERE c.acc_txt = 'D' AND c.object_type_id = ( select id from OBJECT_TYPE where cd = 'chem'));

Date: 2025-03-26 10:31:26
Duration: 1s256ms
Bind query: yes

select p.ancestor_object_id, p.descendant_object_id from DAG_PATH p where p.descendant_object_id in ( select chem_id from GENE_CHEM_REFERENCE gcr where gcr.gene_id in ( select gene_id from GENE_TAXON where taxon_id = ( select id from TERM where acc_txt = '9606' and object_type_id = ( select id from OBJECT_TYPE where cd = 'taxon')))) AND p.ancestor_object_id != ( SELECT c.id FROM TERM c WHERE c.acc_txt = 'D' AND c.object_type_id = ( select id from OBJECT_TYPE where cd = 'chem'));

Date: 2025-03-26 10:18:41
Duration: 1s251ms
Bind query: yes

select p.ancestor_object_id, p.descendant_object_id from DAG_PATH p where p.descendant_object_id in ( select chem_id from GENE_CHEM_REFERENCE gcr where gcr.gene_id in ( select gene_id from GENE_TAXON where taxon_id = ( select id from TERM where acc_txt = '9606' and object_type_id = ( select id from OBJECT_TYPE where cd = 'taxon')))) AND p.ancestor_object_id != ( SELECT c.id FROM TERM c WHERE c.acc_txt = 'D' AND c.object_type_id = ( select id from OBJECT_TYPE where cd = 'chem'));

Date: 2025-03-26 10:18:36
Duration: 1s71ms
Bind query: yes

x Hide

Times Reported Time consuming queries #9

x Hide

select gene_id, chem_id from GENE_CHEM_REFERENCE r where gene_id in ( select gene_id from GENE_TAXON where taxon_id = ( select id from TERM where acc_txt = '9606' and object_type_id = ( select id from OBJECT_TYPE where cd = 'taxon'))) and gene_id in ( select term_id from TERM_PATHWAY p where p.object_type_id = ( select id from OBJECT_TYPE where cd = 'gene') and term_id in ( select gene_id from GENE_TAXON where taxon_id = ( select id from TERM where acc_txt = '9606' and object_type_id = ( select id from OBJECT_TYPE where cd = 'taxon')))) and r.id NOT in ( SELECT gene_chem_reference_id FROM GENE_CHEM_REFERENCE_AXN WHERE action_degree_type_nm = 'does not affect');

Date: 2025-03-26 10:18:43
Duration: 1s682ms
Bind query: yes

select gene_id, chem_id from GENE_CHEM_REFERENCE r where gene_id in ( select gene_id from GENE_TAXON where taxon_id = ( select id from TERM where acc_txt = '9606' and object_type_id = ( select id from OBJECT_TYPE where cd = 'taxon'))) and gene_id in ( select term_id from TERM_PATHWAY p where p.object_type_id = ( select id from OBJECT_TYPE where cd = 'gene') and term_id in ( select gene_id from GENE_TAXON where taxon_id = ( select id from TERM where acc_txt = '9606' and object_type_id = ( select id from OBJECT_TYPE where cd = 'taxon')))) and r.id NOT in ( SELECT gene_chem_reference_id FROM GENE_CHEM_REFERENCE_AXN WHERE action_degree_type_nm = 'does not affect');

Date: 2025-03-26 10:42:07
Duration: 1s461ms
Bind query: yes

select gene_id, chem_id from GENE_CHEM_REFERENCE r where gene_id in ( select gene_id from GENE_TAXON where taxon_id = ( select id from TERM where acc_txt = '9606' and object_type_id = ( select id from OBJECT_TYPE where cd = 'taxon'))) and gene_id in ( select term_id from TERM_PATHWAY p where p.object_type_id = ( select id from OBJECT_TYPE where cd = 'gene') and term_id in ( select gene_id from GENE_TAXON where taxon_id = ( select id from TERM where acc_txt = '9606' and object_type_id = ( select id from OBJECT_TYPE where cd = 'taxon')))) and r.id NOT in ( SELECT gene_chem_reference_id FROM GENE_CHEM_REFERENCE_AXN WHERE action_degree_type_nm = 'does not affect');

Date: 2025-03-26 10:31:32
Duration: 1s418ms
Bind query: yes