Times Reported Time consuming queries #1

x Hide

[ User: zbx_monitor - Total duration: 57s533ms - Times executed: 30 ]

x Hide

SELECT pg_database_size(datname::text) FROM pg_catalog.pg_database WHERE datistemplate = false AND datname = 'postgres';

Date: 2024-08-23 01:39:05
Duration: 3s44ms
Bind query: yes

SELECT pg_database_size(datname::text) FROM pg_catalog.pg_database WHERE datistemplate = false AND datname = 'postgres';

Date: 2024-08-23 00:19:05
Duration: 2s980ms
Database: postgres
User: zbx_monitor
Bind query: yes

SELECT pg_database_size(datname::text) FROM pg_catalog.pg_database WHERE datistemplate = false AND datname = 'ctddev51';

Date: 2024-08-23 04:04:24
Duration: 2s894ms
Database: ctddev51
User: zbx_monitor
Bind query: yes

x Hide

Times Reported Time consuming queries #2

x Hide

[ User: pubeu - Total duration: 30s66ms - Times executed: 8 ]

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 = '589967' 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-08-23 05:45:17
Duration: 4s421ms
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 = '589967' 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-08-23 10:24:09
Duration: 3s827ms
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 = '589967' 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-08-23 17:07:46
Duration: 3s740ms
Database: ctddev51
User: pubeu
Bind query: yes

x Hide

Times Reported Time consuming queries #3

x Hide

[ User: zbx_monitor - Total duration: 14s536ms - Times executed: 8 ]

x Hide

SELECT row_to_json(T) FROM ( SELECT CASE WHEN pg_is_in_recovery() THEN 0 ELSE pg_wal_lsn_diff (pg_current_wal_lsn (), '0/00000000') END AS WRITE, CASE WHEN NOT pg_is_in_recovery() THEN 0 ELSE pg_wal_lsn_diff (pg_last_wal_receive_lsn (), '0/00000000') END AS RECEIVE, count(*) FROM pg_ls_waldir () AS COUNT) T;

Date: 2024-08-23 05:22:44
Duration: 2s701ms
Bind query: yes

SELECT row_to_json(T) FROM ( SELECT CASE WHEN pg_is_in_recovery() THEN 0 ELSE pg_wal_lsn_diff (pg_current_wal_lsn (), '0/00000000') END AS WRITE, CASE WHEN NOT pg_is_in_recovery() THEN 0 ELSE pg_wal_lsn_diff (pg_last_wal_receive_lsn (), '0/00000000') END AS RECEIVE, count(*) FROM pg_ls_waldir () AS COUNT) T;

Date: 2024-08-23 04:52:44
Duration: 2s440ms
Database: postgres
User: zbx_monitor
Bind query: yes

SELECT row_to_json(T) FROM ( SELECT CASE WHEN pg_is_in_recovery() THEN 0 ELSE pg_wal_lsn_diff (pg_current_wal_lsn (), '0/00000000') END AS WRITE, CASE WHEN NOT pg_is_in_recovery() THEN 0 ELSE pg_wal_lsn_diff (pg_last_wal_receive_lsn (), '0/00000000') END AS RECEIVE, count(*) FROM pg_ls_waldir () AS COUNT) T;

Date: 2024-08-23 00:27:43
Duration: 2s295ms
Database: postgres
User: zbx_monitor
Bind query: yes

x Hide

Times Reported Time consuming queries #4

x Hide

[ User: pubeu - Total duration: 4s245ms - Times executed: 2 ]

x Hide

SELECT /* DiseaseGeneAssnsDAO.rowCount */ COUNT(*) FROM gene_disease gd WHERE gd.disease_id IN ( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '2071900');

Date: 2024-08-23 05:48:59
Duration: 4s145ms
Bind query: yes

SELECT /* DiseaseGeneAssnsDAO.rowCount */ COUNT(*) FROM gene_disease gd WHERE gd.disease_id IN ( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '2071900');

Date: 2024-08-23 05:49:10
Duration: 2s748ms
Bind query: yes

SELECT /* DiseaseGeneAssnsDAO.rowCount */ COUNT(*) FROM gene_disease gd WHERE gd.disease_id IN ( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '2071900');

Date: 2024-08-23 05:49:07
Duration: 2s381ms
Bind query: yes

x Hide

Times Reported Time consuming queries #5

x Hide

[ User: zbx_monitor - Total duration: 7s114ms - Times executed: 3 ]

x Hide

SELECT row_to_json(T) FROM ( SELECT checkpoints_timed, checkpoints_req, checkpoint_write_time, checkpoint_sync_time, buffers_checkpoint, buffers_clean, maxwritten_clean, buffers_backend, buffers_backend_fsync, buffers_alloc FROM pg_catalog.pg_stat_bgwriter) T;

Date: 2024-08-23 04:31:48
Duration: 2s903ms
Database: postgres
User: zbx_monitor
Bind query: yes

SELECT row_to_json(T) FROM ( SELECT checkpoints_timed, checkpoints_req, checkpoint_write_time, checkpoint_sync_time, buffers_checkpoint, buffers_clean, maxwritten_clean, buffers_backend, buffers_backend_fsync, buffers_alloc FROM pg_catalog.pg_stat_bgwriter) T;

Date: 2024-08-23 04:55:48
Duration: 2s678ms
Database: postgres
User: zbx_monitor
Bind query: yes

SELECT row_to_json(T) FROM ( SELECT checkpoints_timed, checkpoints_req, checkpoint_write_time, checkpoint_sync_time, buffers_checkpoint, buffers_clean, maxwritten_clean, buffers_backend, buffers_backend_fsync, buffers_alloc FROM pg_catalog.pg_stat_bgwriter) T;

Date: 2024-08-23 04:10:28
Duration: 1s667ms
Bind query: yes

x Hide

Times Reported Time consuming queries #6

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.taxon_id = ANY (ARRAY ( SELECT /* CIQH.getIxnTaxonWhereEquals.Name */ dp.descendant_object_id FROM term t INNER JOIN dag_path dp ON t.id = dp.ancestor_object_id WHERE UPPER(t.nm) LIKE 'FELIS CATUS' AND t.object_type_id = 1)) 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-08-23 05:48:01
Duration: 8s274ms
Bind query: yes

x Hide

Times Reported Time consuming queries #7

x Hide

SELECT /* ReferenceGeneGeneIxnsDAO */ fg.nm fromGeneSymbol, fg.acc_txt fromGeneAcc, tg.nm toGeneSymbol, tg.acc_txt toGeneAcc, ft.nm fromTaxonNm, ft.secondary_nm fromTaxonCommonNm, ft.acc_txt fromTaxonAcc, tt.nm toTaxonNm, tt.secondary_nm toTaxonCommonNm, tt.acc_txt toTaxonAcc, ggr.experimental_sys_nm, ggr.experimental_sys_type, ( SELECT STRING_AGG(ggt.throughput_txt, ', ' ORDER BY ggt.throughput_txt) FROM gene_gene_ref_throughput ggt WHERE ggt.gene_gene_reference_id = ggr.id) throughput, COUNT(*) OVER () fullRowCount FROM gene_gene_reference ggr INNER JOIN term fg ON ggr.from_gene_id = fg.id INNER JOIN term tg ON ggr.to_gene_id = tg.id INNER JOIN term ft ON ggr.from_taxon_id = ft.id INNER JOIN term tt ON ggr.to_taxon_id = tt.id WHERE ggr.reference_id = '111363' ORDER BY fg.nm_sort, tg.nm_sort LIMIT 50;

Date: 2024-08-23 05:49:32
Duration: 4s117ms
Bind query: yes

SELECT /* ReferenceGeneGeneIxnsDAO */ fg.nm fromGeneSymbol, fg.acc_txt fromGeneAcc, tg.nm toGeneSymbol, tg.acc_txt toGeneAcc, ft.nm fromTaxonNm, ft.secondary_nm fromTaxonCommonNm, ft.acc_txt fromTaxonAcc, tt.nm toTaxonNm, tt.secondary_nm toTaxonCommonNm, tt.acc_txt toTaxonAcc, ggr.experimental_sys_nm, ggr.experimental_sys_type, ( SELECT STRING_AGG(ggt.throughput_txt, ', ' ORDER BY ggt.throughput_txt) FROM gene_gene_ref_throughput ggt WHERE ggt.gene_gene_reference_id = ggr.id) throughput, COUNT(*) OVER () fullRowCount FROM gene_gene_reference ggr INNER JOIN term fg ON ggr.from_gene_id = fg.id INNER JOIN term tg ON ggr.to_gene_id = tg.id INNER JOIN term ft ON ggr.from_taxon_id = ft.id INNER JOIN term tt ON ggr.to_taxon_id = tt.id WHERE ggr.reference_id = '111363' ORDER BY fg.nm_sort, tg.nm_sort LIMIT 50;

Date: 2024-08-23 05:49:36
Duration: 3s489ms
Bind query: yes

x Hide

Times Reported Time consuming queries #8

x Hide

[ User: zbx_monitor - Total duration: 1s403ms - Times executed: 1 ]

x Hide

SELECT count(*) FROM pg_catalog.pg_stat_all_tables WHERE (n_dead_tup / (n_live_tup + n_dead_tup)::float8) > 0.2 AND (n_live_tup + n_dead_tup) > 50;

Date: 2024-08-23 00:16:06
Duration: 1s501ms
Bind query: yes

SELECT count(*) FROM pg_catalog.pg_stat_all_tables WHERE (n_dead_tup / (n_live_tup + n_dead_tup)::float8) > 0.2 AND (n_live_tup + n_dead_tup) > 50;

Date: 2024-08-23 00:06:06
Duration: 1s456ms
Bind query: yes

SELECT count(*) FROM pg_catalog.pg_stat_all_tables WHERE (n_dead_tup / (n_live_tup + n_dead_tup)::float8) > 0.2 AND (n_live_tup + n_dead_tup) > 50;

Date: 2024-08-23 04:58:06
Duration: 1s450ms
Bind query: yes

x Hide