pgBadger-
Global information
- Generated on Wed Jul 22 04:15:03 2026
- Log file: /project/archive/log/postgres/dbprd51/postgresql.log-20260721
- Parsed 45,425 log entries in 2s
- Log start from 2026-07-21 00:00:01 to 2026-07-21 23:57:58
-
Overview
Global Stats
- 19 Number of unique normalized queries
- 56 Number of queries
- 44m Total query duration
- 2026-07-21 00:09:18 First query
- 2026-07-21 23:52:33 Last query
- 2 queries/s at 2026-07-21 16:55:53 Query peak
- 44m Total query duration
- 0ms Prepare/parse total duration
- 0ms Bind total duration
- 44m Execute total duration
- 0 Number of events
- 0 Number of unique normalized events
- 0 Max number of times the same event was reported
- 0 Number of cancellation
- 3 Total number of automatic vacuums
- 13 Total number of automatic analyzes
- 0 Number temporary file
- 0 Max size of temporary file
- 0.00 B Average size of temporary file
- 5,629 Total number of sessions
- 58 sessions at 2026-07-21 06:22:54 Session peak
- 40d20m47s Total duration of sessions
- 10m14s Average duration of sessions
- 0 Average queries per session
- 469ms Average queries duration per session
- 10m13s Average idle time per session
- 5,629 Total number of connections
- 33 connections/s at 2026-07-21 06:55:30 Connection peak
- 2 Total number of databases
SQL Traffic
Key values
- 2 queries/s Query Peak
- 2026-07-21 16:55:53 Date
SELECT Traffic
Key values
- 2 queries/s Query Peak
- 2026-07-21 16:55:53 Date
INSERT/UPDATE/DELETE Traffic
Key values
- 1 queries/s Query Peak
- 2026-07-21 14:07:19 Date
Queries duration
Key values
- 44m Total query duration
Prepared queries ratio
Key values
- 0.00 Ratio of bind vs prepare
- 0.00 % Ratio between prepared and "usual" statements
General Activity
↑ Back to the top of the General Activity tableDay Hour Count Min duration Max duration Avg duration Latency Percentile(90) Latency Percentile(95) Latency Percentile(99) Jul 21 00 4 0ms 9m16s 2m43s 0ms 46s429ms 9m23s 01 2 0ms 47s643ms 47s63ms 0ms 46s483ms 47s643ms 02 0 0ms 0ms 0ms 0ms 0ms 0ms 03 1 0ms 2m22s 2m22s 0ms 0ms 2m22s 04 0 0ms 0ms 0ms 0ms 0ms 0ms 05 1 0ms 5s413ms 5s413ms 0ms 0ms 5s413ms 06 11 0ms 1m52s 21s191ms 6s485ms 39s391ms 1m52s 07 3 0ms 5s548ms 5s270ms 0ms 0ms 15s812ms 08 0 0ms 0ms 0ms 0ms 0ms 0ms 09 0 0ms 0ms 0ms 0ms 0ms 0ms 10 9 0ms 1m52s 24s673ms 21s13ms 48s480ms 1m52s 11 0 0ms 0ms 0ms 0ms 0ms 0ms 12 0 0ms 0ms 0ms 0ms 0ms 0ms 13 0 0ms 0ms 0ms 0ms 0ms 0ms 14 9 0ms 1m52s 24s675ms 21s42ms 48s478ms 1m52s 15 0 0ms 0ms 0ms 0ms 0ms 0ms 16 2 0ms 5s67ms 5s64ms 0ms 0ms 10s128ms 17 0 0ms 0ms 0ms 0ms 0ms 0ms 18 9 0ms 1m53s 24s736ms 21s80ms 48s649ms 1m53s 19 0 0ms 0ms 0ms 0ms 0ms 0ms 20 0 0ms 0ms 0ms 0ms 0ms 0ms 21 2 0ms 5s614ms 5s454ms 0ms 0ms 10s909ms 22 2 0ms 8s927ms 8s858ms 0ms 0ms 17s717ms 23 1 0ms 13m8s 13m8s 0ms 0ms 13m8s Day Hour SELECT COPY TO Average Duration Latency Percentile(90) Latency Percentile(95) Latency Percentile(99) Jul 21 00 3 0 3m36s 0ms 0ms 9m16s 01 2 0 47s63ms 0ms 0ms 47s643ms 02 0 0 0ms 0ms 0ms 0ms 03 1 0 2m22s 0ms 0ms 2m22s 04 0 0 0ms 0ms 0ms 0ms 05 1 0 5s413ms 0ms 0ms 5s413ms 06 2 9 21s191ms 0ms 6s485ms 1m52s 07 3 0 5s270ms 0ms 0ms 15s812ms 08 0 0 0ms 0ms 0ms 0ms 09 0 0 0ms 0ms 0ms 0ms 10 0 9 24s673ms 0ms 21s13ms 1m52s 11 0 0 0ms 0ms 0ms 0ms 12 0 0 0ms 0ms 0ms 0ms 13 0 0 0ms 0ms 0ms 0ms 14 0 9 24s675ms 0ms 21s42ms 1m52s 15 0 0 0ms 0ms 0ms 0ms 16 2 0 5s64ms 0ms 0ms 10s128ms 17 0 0 0ms 0ms 0ms 0ms 18 0 9 24s736ms 0ms 21s80ms 1m53s 19 0 0 0ms 0ms 0ms 0ms 20 0 0 0ms 0ms 0ms 0ms 21 2 0 5s454ms 0ms 0ms 10s909ms 22 2 0 8s858ms 0ms 0ms 17s717ms 23 1 0 13m8s 0ms 0ms 13m8s Day Hour INSERT UPDATE DELETE COPY FROM Average Duration Latency Percentile(90) Latency Percentile(95) Latency Percentile(99) Jul 21 00 0 0 0 0 0ms 0ms 0ms 0ms 01 0 0 0 0 0ms 0ms 0ms 0ms 02 0 0 0 0 0ms 0ms 0ms 0ms 03 0 0 0 0 0ms 0ms 0ms 0ms 04 0 0 0 0 0ms 0ms 0ms 0ms 05 0 0 0 0 0ms 0ms 0ms 0ms 06 0 0 0 0 0ms 0ms 0ms 0ms 07 0 0 0 0 0ms 0ms 0ms 0ms 08 0 0 0 0 0ms 0ms 0ms 0ms 09 0 0 0 0 0ms 0ms 0ms 0ms 10 0 0 0 0 0ms 0ms 0ms 0ms 11 0 0 0 0 0ms 0ms 0ms 0ms 12 0 0 0 0 0ms 0ms 0ms 0ms 13 0 0 0 0 0ms 0ms 0ms 0ms 14 0 0 0 0 0ms 0ms 0ms 0ms 15 0 0 0 0 0ms 0ms 0ms 0ms 16 0 0 0 0 0ms 0ms 0ms 0ms 17 0 0 0 0 0ms 0ms 0ms 0ms 18 0 0 0 0 0ms 0ms 0ms 0ms 19 0 0 0 0 0ms 0ms 0ms 0ms 20 0 0 0 0 0ms 0ms 0ms 0ms 21 0 0 0 0 0ms 0ms 0ms 0ms 22 0 0 0 0 0ms 0ms 0ms 0ms 23 0 0 0 0 0ms 0ms 0ms 0ms Day Hour Prepare Bind Bind/Prepare Percentage of prepare Jul 21 00 0 2 2.00 0.00% 01 0 2 2.00 0.00% 02 0 0 0.00 0.00% 03 0 1 1.00 0.00% 04 0 0 0.00 0.00% 05 0 1 1.00 0.00% 06 0 2 2.00 0.00% 07 0 3 3.00 0.00% 08 0 0 0.00 0.00% 09 0 0 0.00 0.00% 10 0 0 0.00 0.00% 11 0 0 0.00 0.00% 12 0 0 0.00 0.00% 13 0 0 0.00 0.00% 14 0 0 0.00 0.00% 15 0 0 0.00 0.00% 16 0 2 2.00 0.00% 17 0 0 0.00 0.00% 18 0 0 0.00 0.00% 19 0 0 0.00 0.00% 20 0 0 0.00 0.00% 21 0 2 2.00 0.00% 22 0 2 2.00 0.00% 23 0 1 1.00 0.00% Day Hour Count Average / Second Jul 21 00 81 0.02/s 01 87 0.02/s 02 80 0.02/s 03 75 0.02/s 04 75 0.02/s 05 94 0.03/s 06 3,552 0.99/s 07 350 0.10/s 08 76 0.02/s 09 78 0.02/s 10 79 0.02/s 11 74 0.02/s 12 74 0.02/s 13 78 0.02/s 14 80 0.02/s 15 78 0.02/s 16 79 0.02/s 17 72 0.02/s 18 75 0.02/s 19 76 0.02/s 20 77 0.02/s 21 77 0.02/s 22 80 0.02/s 23 82 0.02/s Day Hour Count Average Duration Average idle time Jul 21 00 81 30m24s 30m15s 01 87 27m50s 27m49s 02 80 30m2s 30m2s 03 75 31m30s 31m28s 04 75 31m21s 31m21s 05 94 25m48s 25m48s 06 3,552 42s765ms 42s700ms 07 350 6m36s 6m36s 08 76 31m 31m 09 78 32m32s 32m32s 10 79 30m25s 30m23s 11 74 32m18s 32m18s 12 74 31m17s 31m17s 13 78 32m 32m 14 80 30m27s 30m24s 15 78 31m34s 31m34s 16 79 30m21s 30m21s 17 72 32m29s 32m29s 18 75 30m16s 30m13s 19 76 31m52s 31m52s 20 77 30m54s 30m54s 21 77 29m56s 29m56s 22 80 29m35s 29m35s 23 82 30m 29m51s -
Connections
Established Connections
Key values
- 33 connections Connection Peak
- 2026-07-21 06:55:30 Date
Connections per database
Key values
- ctdprd51 Main Database
- 5,629 connections Total
Connections per user
Key values
- pubeu Main User
- 5,629 connections Total
-
Sessions
Simultaneous sessions
Key values
- 58 sessions Session Peak
- 2026-07-21 06:22:54 Date
Histogram of session times
Key values
- 2,162 0-500ms duration
Sessions per database
Key values
- ctdprd51 Main Database
- 5,629 sessions Total
Sessions per user
Key values
- pubeu Main User
- 5,629 sessions Total
Sessions per host
Key values
- 10.12.5.53 Main Host
- 5,629 sessions Total
-
Checkpoints / Restartpoints
Checkpoints Buffers
Key values
- 131,512 buffers Checkpoint Peak
- 2026-07-21 07:41:02 Date
- 1619.211 seconds Highest write time
- 0.001 seconds Sync time
Checkpoints Wal files
Key values
- 38 files Wal files usage Peak
- 2026-07-21 07:41:02 Date
Checkpoints distance
Key values
- 1,218.32 Mo Distance Peak
- 2026-07-21 07:41:02 Date
Checkpoints Activity
↑ Back to the top of the Checkpoint Activity tableDay Hour Written buffers Write time Sync time Total time Jul 21 00 292 29.44s 0.002s 29.452s 01 2,124 212.823s 0.002s 212.868s 02 515 51.768s 0.002s 51.776s 03 105 10.705s 0.002s 10.713s 04 354 35.639s 0.002s 35.649s 05 385 38.807s 0.002s 38.854s 06 263 26.529s 0.002s 26.537s 07 131,579 1,626.025s 0.002s 1,626.137s 08 98 9.998s 0.002s 10.006s 09 277 27.976s 0.002s 27.986s 10 67 6.895s 0.002s 6.903s 11 85 8.697s 0.002s 8.705s 12 46 4.84s 0.002s 4.868s 13 22 2.377s 0.002s 2.385s 14 8 0.887s 0.001s 0.891s 15 20 2.175s 0.002s 2.183s 16 17 1.873s 0.002s 1.881s 17 9 0.986s 0.001s 0.99s 18 23 2.478s 0.002s 2.486s 19 9 0.887s 0.001s 0.892s 20 81 8.291s 0.002s 8.299s 21 33 3.501s 0.002s 3.509s 22 460 46.28s 0.002s 46.288s 23 115 11.707s 0.002s 11.715s Day Hour Added Removed Recycled Synced files Longest sync Average sync Jul 21 00 0 0 0 63 0.001s 0.002s 01 0 0 1 33 0.001s 0.002s 02 0 0 0 28 0.001s 0.002s 03 0 0 0 24 0.001s 0.002s 04 0 0 0 35 0.001s 0.002s 05 0 0 1 38 0.001s 0.002s 06 0 0 0 40 0.001s 0.002s 07 0 0 38 30 0.001s 0.002s 08 0 0 0 20 0.001s 0.002s 09 0 0 0 108 0.001s 0.002s 10 0 0 0 20 0.001s 0.002s 11 0 0 0 22 0.001s 0.002s 12 0 0 0 15 0.001s 0.002s 13 0 0 0 13 0.001s 0.002s 14 0 0 0 7 0.001s 0.001s 15 0 0 0 17 0.001s 0.002s 16 0 0 0 13 0.001s 0.002s 17 0 0 0 7 0.001s 0.001s 18 0 0 0 15 0.001s 0.002s 19 0 0 0 7 0.001s 0.001s 20 0 0 0 23 0.001s 0.002s 21 0 0 0 17 0.001s 0.002s 22 0 0 0 36 0.001s 0.002s 23 0 0 0 25 0.001s 0.002s Day Hour Count Avg time (sec) Jul 21 00 0 0s 01 0 0s 02 0 0s 03 0 0s 04 0 0s 05 0 0s 06 0 0s 07 0 0s 08 0 0s 09 0 0s 10 0 0s 11 0 0s 12 0 0s 13 0 0s 14 0 0s 15 0 0s 16 0 0s 17 0 0s 18 0 0s 19 0 0s 20 0 0s 21 0 0s 22 0 0s 23 0 0s Day Hour Mean distance Mean estimate Jul 21 00 1,193.00 kB 399,663.00 kB 01 8,409.00 kB 324,570.00 kB 02 175.00 kB 263,678.00 kB 03 173.00 kB 213,622.00 kB 04 639.50 kB 173,147.00 kB 05 844.50 kB 140,404.00 kB 06 676.00 kB 113,869.50 kB 07 311,922.50 kB 592,592.00 kB 08 202.50 kB 480,036.50 kB 09 797.00 kB 388,942.50 kB 10 117.00 kB 315,104.00 kB 11 209.00 kB 255,262.50 kB 12 72.00 kB 206,789.50 kB 13 44.50 kB 167,509.00 kB 14 26.00 kB 142,828.00 kB 15 41.00 kB 122,123.50 kB 16 40.00 kB 98,927.00 kB 17 31.00 kB 84,354.00 kB 18 32.50 kB 72,128.00 kB 19 27.00 kB 61,502.00 kB 20 52.00 kB 52,591.50 kB 21 49.00 kB 42,608.00 kB 22 1,254.50 kB 34,659.50 kB 23 263.50 kB 28,201.00 kB -
Temporary Files
Size of temporary files
Key values
- 0 Temp Files size Peak
- Date
Size of temporary files (5 minutes period)
NO DATASET
Number of temporary files
Key values
- 0 per second Temp Files Peak
- Date
Number of temporary files (5 minutes period)
NO DATASET
Temporary Files Activity
↑ Back to the top of the Temporary Files Activity tableDay Hour Count Total size Average size Jul 21 00 0 0 0 01 0 0 0 02 0 0 0 03 0 0 0 04 0 0 0 05 0 0 0 06 0 0 0 07 0 0 0 08 0 0 0 09 0 0 0 10 0 0 0 11 0 0 0 12 0 0 0 13 0 0 0 14 0 0 0 15 0 0 0 16 0 0 0 17 0 0 0 18 0 0 0 19 0 0 0 20 0 0 0 21 0 0 0 22 0 0 0 23 0 0 0 -
Vacuums
Vacuums / Analyzes Distribution
Key values
- 18.04 sec Highest CPU-cost vacuum
Table pub2.term_set_enrichment_agent
Database ctdprd51 - 2026-07-21 07:09:10 Date
- 0 sec Highest CPU-cost analyze
Table
Database ctdprd51 - Date
Average Autovacuum Duration
Key values
- 18.04 sec Highest CPU-cost vacuum
Table pub2.term_set_enrichment_agent
Database ctdprd51 - 2026-07-21 07:09:10 Date
Analyzes per table
Key values
- pubc.log_query (13) Main table analyzed (database ctdprd51)
- 13 analyzes Total
Vacuums per table
Key values
- pg_catalog.pg_statistic (1) Main table vacuumed on database ctdprd51
- 3 vacuums Total
Index Buffer usage Skipped WAL usage Table Vacuums scans hits misses dirtied pins frozen records full page bytes ctdprd51.pg_catalog.pg_statistic 1 1 773 0 191 0 124 519 178 732,086 ctdprd51.pub2.term_set_enrichment_agent 1 0 197,362 0 75,705 0 0 98,602 5 5,855,036 ctdprd51.pubc.log_query 1 1 190 0 21 0 0 43 17 124,431 Total 3 2 198,325 81 75,917 0 124 99,164 200 6,711,553 Tuples removed per table
Key values
- pg_catalog.pg_statistic (594) Main table with removed tuples on database ctdprd51
- 601 tuples Total removed
Pages removed per table
Key values
- unknown (0) Main table with removed pages on database unknown
- 0 pages Total removed
Autovacuum Activity
↑ Back to the top of the Autovacuum Activity tableDay Hour VACUUMs ANALYZEs Jul 21 00 0 0 01 0 1 02 0 1 03 0 1 04 1 2 05 1 3 06 0 0 07 1 1 08 0 0 09 0 1 10 0 0 11 0 1 12 0 0 13 0 0 14 0 0 15 0 0 16 0 0 17 0 0 18 0 0 19 0 0 20 0 0 21 0 1 22 0 0 23 0 1 - 18.04 sec Highest CPU-cost vacuum
-
Locks
Locks by types
Key values
- unknown Main Lock Type
- 0 locks Total
Most frequent waiting queries (N)
Rank Count Total time Min time Max time Avg duration Query NO DATASET
Queries that waited the most
Rank Wait time Query NO DATASET
-
Queries
Queries by type
Key values
- 19 Total read queries
- 36 Total write queries
Queries by database
Key values
- unknown Main database
- 30 Requests
- 37m47s (ctdprd51)
- Main time consuming database
Queries by user
Key values
- unknown Main user
- 60 Requests
User Request type Count Duration postgres Total 16 17m40s copy to 16 17m40s pubc Total 1 9m16s select 1 9m16s pubeu Total 46 24m14s select 46 24m14s unknown Total 60 12m30s copy to 56 11m53s others 1 6s734ms select 3 31s146ms Duration by user
Key values
- 24m14s (pubeu) Main time consuming user
User Request type Count Duration postgres Total 16 17m40s copy to 16 17m40s pubc Total 1 9m16s select 1 9m16s pubeu Total 46 24m14s select 46 24m14s unknown Total 60 12m30s copy to 56 11m53s others 1 6s734ms select 3 31s146ms Queries by host
Key values
- unknown Main host
- 123 Requests
- 1h3m41s (unknown)
- Main time consuming host
Queries by application
Key values
- unknown Main application
- 47 Requests
- 25m52s (unknown)
- Main time consuming application
Number of cancelled queries
Key values
- 0 per second Cancelled query Peak
- 2026-07-21 06:50:59 Date
Number of cancelled queries (5 minutes period)
NO DATASET
-
Top Queries
Histogram of query times
Key values
- 31 > 10000ms duration
Slowest individual queries
Rank Duration Query 1 13m8s 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 = 'gene'))) 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 'VTG1') INTERSECT ( SELECT /* IQH.getMasterGoWhereEquals.Gene */ ai.gene_id FROM dag_path pi INNER JOIN gene_go_annot ai ON pi.descendant_object_id = ai.go_term_id INNER JOIN db_link dbli ON dbli.object_id = pi.ancestor_object_id WHERE dbli.acc_txt = 'GO:0008150' AND dbli.type_cd = 'A' AND dbli.object_type_id = 5) INTERSECT ( SELECT /* IQH.getMasterPathwayWhereEquals.Acc */ tp.term_id FROM term_pathway tp WHERE tp.object_type_id = 4 AND UPPER(tp.pathway_acc_txt) = 'R-HSA-2161541' AND tp.pathway_acc_db_cd = 'REACT'))) AND gcr.chem_id = ANY (ARRAY ( SELECT /* CIQH.getIxnChemWhereEquals.Name */ dp.descendant_object_id FROM dag_path dp INNER JOIN term t ON t.id = dp.ancestor_object_id WHERE UPPER(t.nm) LIKE 'TRIPHENYL PHOSPHATE' AND t.object_type_id = 2)) AND gcr.taxon_id = ANY (ARRAY ( SELECT /* CIQH.getIxnTaxonWhereEquals.Acc */ dp.descendant_object_id FROM dag_path dp INNER JOIN dag_node n ON n.id = dp.ancestor_dag_node_id WHERE n.acc_txt = '9615' AND n.dag_id = 7)) 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;[ Date: 2026-07-21 23:52:33 - Database: ctdprd51 - User: pubeu - Bind query: yes ]
2 9m16s /* * 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-07-21 00:09:18 - Database: ctdprd51 - User: pubc - Application: psql ]
3 2m22s SELECT /* BasicCountsDAO gen */ ii.cd, COUNT(ii.id) cnt FROM ( SELECT ot.cd, tl.term_id id FROM object_type ot INNER JOIN term_label tl ON ot.id = tl.object_type_id WHERE tl.nm_fts @@ to_tsquery('common.english_nostops', '(11,14-EICOSADIENOIC | 11,14EICOSADIENOIC) & ACID & DELPHIN_QT & DELTOIN & DEMETHYLDELAVAINE & A & DEMETHYLDELAVAINE & B & DEOXYANDROGRAPHOLIDE & KARAKOLINE & KARANJIN & NEOKADSURANIC & ACID & B & (2,7-DIDEACETYL-2,7-DIBENZOYL-TAXAYUNNANINE | 2,7DIDEACETYL2,7DIBENZOYLTAXAYUNNANINE) & F & BENZOYLNAPELLINE & (6-DEMETHYLDESOLINE | 6DEMETHYLDESOLINE) & DEOXYACONITINE & R & (-NORCOCLAURINE | NORCOCLAURINE) & IGNAVINE & ISOTALATIZIDINE & JESACONITINE & 3R,8S,9R,10R,13R,14S,17R & (-3-HYDROXY-4,4,9,13,14-PENTAMETHYL-17- | 3HYDROXY4,4,9,13,14PENTAMETHYL17) & E,2R & (-6-METHYL-7- | 6METHYL7) & 2R,3R,4S,5S,6R & (-3,4,5-TRIHYDROXY-6- | 3,4,5TRIHYDROXY6) & 2R,3R,4S,5S,6R & (-3,4,5-TRIHYDROXY-6- | 3,4,5TRIHYDROXY6) & HYDROXYMETHYL & (OXAN-2-YL | OXAN2YL) & OXYMETHYL & (OXAN-2-YL | OXAN2YL) & (OXYHEPT-5-E | OXYHEPT5E)') UNION SELECT 'reference', r.id FROM reference r WHERE r.title_abstract_fts @@ to_tsquery('pg_catalog.english', '(11,14-EICOSADIENOIC | 11,14EICOSADIENOIC) & ACID & DELPHIN_QT & DELTOIN & DEMETHYLDELAVAINE & A & DEMETHYLDELAVAINE & B & DEOXYANDROGRAPHOLIDE & KARAKOLINE & KARANJIN & NEOKADSURANIC & ACID & B & (2,7-DIDEACETYL-2,7-DIBENZOYL-TAXAYUNNANINE | 2,7DIDEACETYL2,7DIBENZOYLTAXAYUNNANINE) & F & BENZOYLNAPELLINE & (6-DEMETHYLDESOLINE | 6DEMETHYLDESOLINE) & DEOXYACONITINE & R & (-NORCOCLAURINE | NORCOCLAURINE) & IGNAVINE & ISOTALATIZIDINE & JESACONITINE & 3R,8S,9R,10R,13R,14S,17R & (-3-HYDROXY-4,4,9,13,14-PENTAMETHYL-17- | 3HYDROXY4,4,9,13,14PENTAMETHYL17) & E,2R & (-6-METHYL-7- | 6METHYL7) & 2R,3R,4S,5S,6R & (-3,4,5-TRIHYDROXY-6- | 3,4,5TRIHYDROXY6) & 2R,3R,4S,5S,6R & (-3,4,5-TRIHYDROXY-6- | 3,4,5TRIHYDROXY6) & HYDROXYMETHYL & (OXAN-2-YL | OXAN2YL) & OXYMETHYL & (OXAN-2-YL | OXAN2YL) & (OXYHEPT-5-E | OXYHEPT5E)') OR r.id IN ( SELECT rpr.reference_id FROM reference_party_role rpr INNER JOIN reference_party rp ON rpr.reference_party_id = rp.id WHERE (SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE '(11,14-EICOSADIENOIC' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE '11,14EICOSADIENOIC)' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'ACID' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'DELPHIN_QT' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'DELTOIN' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'DEMETHYLDELAVAINE' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'A' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'DEMETHYLDELAVAINE' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'B' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'DEOXYANDROGRAPHOLIDE' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'KARAKOLINE' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'KARANJIN' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'NEOKADSURANIC' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'ACID' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'B' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE '(2,7-DIDEACETYL-2,7-DIBENZOYL-TAXAYUNNANINE' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE '2,7DIDEACETYL2,7DIBENZOYLTAXAYUNNANINE)' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'F' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'BENZOYLNAPELLINE' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE '(6-DEMETHYLDESOLINE' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE '6DEMETHYLDESOLINE)' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'DEOXYACONITINE' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'R' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE '(-NORCOCLAURINE' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'NORCOCLAURINE)' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'IGNAVINE' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'ISOTALATIZIDINE' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'JESACONITINE' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE '3R,8S,9R,10R,13R,14S,17R' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE '(-3-HYDROXY-4,4,9,13,14-PENTAMETHYL-17-' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE '3HYDROXY4,4,9,13,14PENTAMETHYL17)' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'E,2R' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE '(-6-METHYL-7-' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE '6METHYL7)' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE '2R,3R,4S,5S,6R' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE '(-3,4,5-TRIHYDROXY-6-' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE '3,4,5TRIHYDROXY6)' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE '2R,3R,4S,5S,6R' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE '(-3,4,5-TRIHYDROXY-6-' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE '3,4,5TRIHYDROXY6)' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'HYDROXYMETHYL' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE '(OXAN-2-YL' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'OXAN2YL)' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'OXYMETHYL' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE '(OXAN-2-YL' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'OXAN2YL)' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE '(OXYHEPT-5-E' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'OXYHEPT5E)')) UNION SELECT ot.cd, l.object_id FROM db_link l INNER JOIN object_type ot on l.object_type_id = ot.id WHERE l.type_cd = 'A' AND (upper(l.acc_txt) LIKE '11,14-EICOSADIENOIC' OR upper(l.acc_txt) LIKE 'ACID' OR upper(l.acc_txt) LIKE 'DELPHIN_QT' OR upper(l.acc_txt) LIKE 'DELTOIN' OR upper(l.acc_txt) LIKE 'DEMETHYLDELAVAINE' OR upper(l.acc_txt) LIKE 'A' OR upper(l.acc_txt) LIKE 'DEMETHYLDELAVAINE' OR upper(l.acc_txt) LIKE 'B' OR upper(l.acc_txt) LIKE 'DEOXYANDROGRAPHOLIDE' OR upper(l.acc_txt) LIKE 'KARAKOLINE' OR upper(l.acc_txt) LIKE 'KARANJIN' OR upper(l.acc_txt) LIKE 'NEOKADSURANIC' OR upper(l.acc_txt) LIKE 'ACID' OR upper(l.acc_txt) LIKE 'B' OR upper(l.acc_txt) LIKE '2,7-DIDEACETYL-2,7-DIBENZOYL-TAXAYUNNANINE' OR upper(l.acc_txt) LIKE 'F' OR upper(l.acc_txt) LIKE 'BENZOYLNAPELLINE' OR upper(l.acc_txt) LIKE '6-DEMETHYLDESOLINE' OR upper(l.acc_txt) LIKE 'DEOXYACONITINE' OR upper(l.acc_txt) LIKE 'R' OR upper(l.acc_txt) LIKE '-NORCOCLAURINE' OR upper(l.acc_txt) LIKE 'IGNAVINE' OR upper(l.acc_txt) LIKE 'ISOTALATIZIDINE' OR upper(l.acc_txt) LIKE 'JESACONITINE' OR upper(l.acc_txt) LIKE '3R,8S,9R,10R,13R,14S,17R' OR upper(l.acc_txt) LIKE '-3-HYDROXY-4,4,9,13,14-PENTAMETHYL-17-' OR upper(l.acc_txt) LIKE 'E,2R' OR upper(l.acc_txt) LIKE '-6-METHYL-7-' OR upper(l.acc_txt) LIKE '2R,3R,4S,5S,6R' OR upper(l.acc_txt) LIKE '-3,4,5-TRIHYDROXY-6-' OR upper(l.acc_txt) LIKE '2R,3R,4S,5S,6R' OR upper(l.acc_txt) LIKE '-3,4,5-TRIHYDROXY-6-' OR upper(l.acc_txt) LIKE 'HYDROXYMETHYL' OR upper(l.acc_txt) LIKE 'OXAN-2-YL' OR upper(l.acc_txt) LIKE 'OXYMETHYL' OR upper(l.acc_txt) LIKE 'OXAN-2-YL' OR upper(l.acc_txt) LIKE 'OXYHEPT-5-E')) ii GROUP BY ii.cd;[ Date: 2026-07-21 03:47:54 - Database: ctdprd51 - User: pubeu - Bind query: yes ]
4 1m53s 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-07-21 18:06:54 - Database: ctdprd51 - User: postgres - Application: pg_dump ]
5 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;[ Date: 2026-07-21 10:06:55 - Database: ctdprd51 - User: postgres - Application: pg_dump ]
6 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;[ Date: 2026-07-21 14:06:55 - Database: ctdprd51 - User: postgres - Application: pg_dump ]
7 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;[ Date: 2026-07-21 06:06:54 - Database: ctdprd51 - User: postgres - Application: pg_dump ]
8 47s643ms SELECT /* DiseaseGeneAssnsDAO */ d.nm diseaseNm, d.acc_txt diseaseAcc, d.acc_db_cd diseaseAccDbCd, d.id diseaseId, g.nm geneSymbol, g.acc_txt geneAcc, g.acc_db_cd geneAccDbCd, g.id geneId, gd.network_score networkScore, gd.indirect_chem_qty inferredCount, gd.reference_qty referenceCount, gd.exposure_reference_qty exposureReferenceCount, CASE WHEN gd.curated_reference_qty > 0 THEN ( SELECT STRING_AGG(a.action_type_cd || '^' || a.action_type_nm, '|') FROM gene_disease_axn a WHERE a.gene_id = gd.gene_id AND a.disease_id = gd.disease_id) ELSE NULL END actionTypes FROM gene_disease gd INNER JOIN term g ON gd.gene_id = g.id INNER JOIN term d ON gd.disease_id = d.id WHERE gd.disease_id IN ( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '2194442') ORDER BY actionTypes, gd.network_score DESC NULLS LAST, g.nm_sort, d.nm_sort;[ Date: 2026-07-21 01:06:20 - Database: ctdprd51 - User: pubeu - Bind query: yes ]
9 46s483ms SELECT /* DiseaseGeneAssnsDAO */ d.nm diseaseNm, d.acc_txt diseaseAcc, d.acc_db_cd diseaseAccDbCd, d.id diseaseId, g.nm geneSymbol, g.acc_txt geneAcc, g.acc_db_cd geneAccDbCd, g.id geneId, gd.network_score networkScore, gd.indirect_chem_qty inferredCount, gd.reference_qty referenceCount, gd.exposure_reference_qty exposureReferenceCount, CASE WHEN gd.curated_reference_qty > 0 THEN ( SELECT STRING_AGG(a.action_type_cd || '^' || a.action_type_nm, '|') FROM gene_disease_axn a WHERE a.gene_id = gd.gene_id AND a.disease_id = gd.disease_id) ELSE NULL END actionTypes FROM gene_disease gd INNER JOIN term g ON gd.gene_id = g.id INNER JOIN term d ON gd.disease_id = d.id WHERE gd.disease_id IN ( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '2194442') ORDER BY actionTypes, gd.network_score DESC NULLS LAST, g.nm_sort, d.nm_sort;[ Date: 2026-07-21 01:08:49 - Database: ctdprd51 - User: pubeu - Bind query: yes ]
10 46s429ms SELECT /* DiseaseGeneAssnsDAO */ d.nm diseaseNm, d.acc_txt diseaseAcc, d.acc_db_cd diseaseAccDbCd, d.id diseaseId, g.nm geneSymbol, g.acc_txt geneAcc, g.acc_db_cd geneAccDbCd, g.id geneId, gd.network_score networkScore, gd.indirect_chem_qty inferredCount, gd.reference_qty referenceCount, gd.exposure_reference_qty exposureReferenceCount, CASE WHEN gd.curated_reference_qty > 0 THEN ( SELECT STRING_AGG(a.action_type_cd || '^' || a.action_type_nm, '|') FROM gene_disease_axn a WHERE a.gene_id = gd.gene_id AND a.disease_id = gd.disease_id) ELSE NULL END actionTypes FROM gene_disease gd INNER JOIN term g ON gd.gene_id = g.id INNER JOIN term d ON gd.disease_id = d.id WHERE gd.disease_id IN ( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '2194442') ORDER BY actionTypes, gd.network_score DESC NULLS LAST, g.nm_sort, d.nm_sort;[ Date: 2026-07-21 00:48:44 - Database: ctdprd51 - User: pubeu - Bind query: yes ]
11 45s647ms SELECT /* DiseaseGeneAssnsDAO */ d.nm diseaseNm, d.acc_txt diseaseAcc, d.acc_db_cd diseaseAccDbCd, d.id diseaseId, g.nm geneSymbol, g.acc_txt geneAcc, g.acc_db_cd geneAccDbCd, g.id geneId, gd.network_score networkScore, gd.indirect_chem_qty inferredCount, gd.reference_qty referenceCount, gd.exposure_reference_qty exposureReferenceCount, CASE WHEN gd.curated_reference_qty > 0 THEN ( SELECT STRING_AGG(a.action_type_cd || '^' || a.action_type_nm, '|') FROM gene_disease_axn a WHERE a.gene_id = gd.gene_id AND a.disease_id = gd.disease_id) ELSE NULL END actionTypes FROM gene_disease gd INNER JOIN term g ON gd.gene_id = g.id INNER JOIN term d ON gd.disease_id = d.id WHERE gd.disease_id IN ( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '2194442') ORDER BY actionTypes, gd.network_score DESC NULLS LAST, g.nm_sort, d.nm_sort;[ Date: 2026-07-21 00:46:24 - Database: ctdprd51 - User: pubeu - Bind query: yes ]
12 24s294ms COPY pubc.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) TO stdout;[ Date: 2026-07-21 14:07:19 ]
13 24s269ms COPY pubc.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) TO stdout;[ Date: 2026-07-21 18:07:19 ]
14 24s153ms COPY pubc.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) TO stdout;[ Date: 2026-07-21 10:07:19 ]
15 24s23ms COPY pubc.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) TO stdout;[ Date: 2026-07-21 06:07:18 ]
16 20s56ms COPY edit.db_link (object_id, object_type_id, acc_txt, db_id, type_cd, is_primary, create_by, create_tm, mod_by, mod_tm) TO stdout;[ Date: 2026-07-21 14:00:22 - Database: ctdprd51 - User: postgres - Application: pg_dump ]
17 19s993ms COPY edit.db_link (object_id, object_type_id, acc_txt, db_id, type_cd, is_primary, create_by, create_tm, mod_by, mod_tm) TO stdout;[ Date: 2026-07-21 18:00:21 - Database: ctdprd51 - User: postgres - Application: pg_dump ]
18 19s961ms COPY edit.db_link (object_id, object_type_id, acc_txt, db_id, type_cd, is_primary, create_by, create_tm, mod_by, mod_tm) TO stdout;[ Date: 2026-07-21 10:00:22 - Database: ctdprd51 - User: postgres - Application: pg_dump ]
19 19s960ms COPY edit.db_link (object_id, object_type_id, acc_txt, db_id, type_cd, is_primary, create_by, create_tm, mod_by, mod_tm) TO stdout;[ Date: 2026-07-21 06:00:22 - Database: ctdprd51 - User: postgres - Application: pg_dump ]
20 15s510ms COPY pubc.log_query_bots_original (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, user_agent_pattern) TO stdout;[ Date: 2026-07-21 18:07:34 ]
Time consuming queries (N)
Rank Total duration Times executed Min duration Max duration Avg duration Query 1 13m8s 1 13m8s 13m8s 13m8s 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 = ?))) and gcr.gene_id = any (array (( select gi.id gene_id from term gi where gi.object_type_id = ? and upper(gi.nm) like ?) intersect ( select ai.gene_id from dag_path pi inner join gene_go_annot ai on pi.descendant_object_id = ai.go_term_id inner join db_link dbli on dbli.object_id = pi.ancestor_object_id where dbli.acc_txt = ? and dbli.type_cd = ? and dbli.object_type_id = ?) intersect ( select tp.term_id from term_pathway tp where tp.object_type_id = ? and upper(tp.pathway_acc_txt) = ? and tp.pathway_acc_db_cd = ?))) and gcr.chem_id = any (array ( select dp.descendant_object_id from dag_path dp inner join term t on t.id = dp.ancestor_object_id where upper(t.nm) like ? and t.object_type_id = ?)) and gcr.taxon_id = any (array ( select dp.descendant_object_id from dag_path dp inner join dag_node n on n.id = dp.ancestor_dag_node_id where n.acc_txt = ? and n.dag_id = ?)) 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 ?;Times Reported Time consuming queries #1
Day Hour Count Duration Avg duration Jul 21 23 1 13m8s 13m8s [ User: pubeu - Total duration: 13m8s - Times executed: 1 ]
-
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 = 'gene'))) 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 'VTG1') INTERSECT ( SELECT /* IQH.getMasterGoWhereEquals.Gene */ ai.gene_id FROM dag_path pi INNER JOIN gene_go_annot ai ON pi.descendant_object_id = ai.go_term_id INNER JOIN db_link dbli ON dbli.object_id = pi.ancestor_object_id WHERE dbli.acc_txt = 'GO:0008150' AND dbli.type_cd = 'A' AND dbli.object_type_id = 5) INTERSECT ( SELECT /* IQH.getMasterPathwayWhereEquals.Acc */ tp.term_id FROM term_pathway tp WHERE tp.object_type_id = 4 AND UPPER(tp.pathway_acc_txt) = 'R-HSA-2161541' AND tp.pathway_acc_db_cd = 'REACT'))) AND gcr.chem_id = ANY (ARRAY ( SELECT /* CIQH.getIxnChemWhereEquals.Name */ dp.descendant_object_id FROM dag_path dp INNER JOIN term t ON t.id = dp.ancestor_object_id WHERE UPPER(t.nm) LIKE 'TRIPHENYL PHOSPHATE' AND t.object_type_id = 2)) AND gcr.taxon_id = ANY (ARRAY ( SELECT /* CIQH.getIxnTaxonWhereEquals.Acc */ dp.descendant_object_id FROM dag_path dp INNER JOIN dag_node n ON n.id = dp.ancestor_dag_node_id WHERE n.acc_txt = '9615' AND n.dag_id = 7)) 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;
Date: 2026-07-21 23:52:33 Duration: 13m8s Database: ctdprd51 User: pubeu Bind query: yes
2 9m16s 1 9m16s 9m16s 9m16s select maint_query_logs_archive ();Times Reported Time consuming queries #2
Day Hour Count Duration Avg duration Jul 21 00 1 9m16s 9m16s [ User: pubc - Total duration: 9m16s - Times executed: 1 ]
[ Application: psql - Total duration: 9m16s - Times executed: 1 ]
-
/* * 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-07-21 00:09:18 Duration: 9m16s Database: ctdprd51 User: pubc Application: psql
3 7m31s 4 1m52s 1m53s 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 #3
Day Hour Count Duration Avg duration Jul 21 06 1 1m52s 1m52s 10 1 1m52s 1m52s 14 1 1m52s 1m52s 18 1 1m53s 1m53s [ User: postgres - Total duration: 7m31s - Times executed: 4 ]
[ Application: pg_dump - Total duration: 7m31s - Times executed: 4 ]
-
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-07-21 18:06:54 Duration: 1m53s Database: ctdprd51 User: postgres Application: pg_dump
-
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-07-21 10:06:55 Duration: 1m52s Database: ctdprd51 User: postgres Application: pg_dump
-
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-07-21 14:06:55 Duration: 1m52s Database: ctdprd51 User: postgres Application: pg_dump
4 3m6s 4 45s647ms 47s643ms 46s550ms select d.nm diseasenm, d.acc_txt diseaseacc, d.acc_db_cd diseaseaccdbcd, d.id diseaseid, g.nm genesymbol, g.acc_txt geneacc, g.acc_db_cd geneaccdbcd, g.id geneid, gd.network_score networkscore, gd.indirect_chem_qty inferredcount, gd.reference_qty referencecount, gd.exposure_reference_qty exposurereferencecount, case when gd.curated_reference_qty > ? then ( select string_agg(a.action_type_cd || ? || a.action_type_nm, ?) from gene_disease_axn a where a.gene_id = gd.gene_id and a.disease_id = gd.disease_id) else null end actiontypes from gene_disease gd inner join term g on gd.gene_id = g.id inner join term d on gd.disease_id = d.id where gd.disease_id in ( select p.descendant_object_id from dag_path p where p.ancestor_object_id = ?) order by actiontypes, gd.network_score desc nulls last, g.nm_sort, d.nm_sort;Times Reported Time consuming queries #4
Day Hour Count Duration Avg duration Jul 21 00 2 1m32s 46s38ms 01 2 1m34s 47s63ms [ User: pubeu - Total duration: 3m6s - Times executed: 4 ]
-
SELECT /* DiseaseGeneAssnsDAO */ d.nm diseaseNm, d.acc_txt diseaseAcc, d.acc_db_cd diseaseAccDbCd, d.id diseaseId, g.nm geneSymbol, g.acc_txt geneAcc, g.acc_db_cd geneAccDbCd, g.id geneId, gd.network_score networkScore, gd.indirect_chem_qty inferredCount, gd.reference_qty referenceCount, gd.exposure_reference_qty exposureReferenceCount, CASE WHEN gd.curated_reference_qty > 0 THEN ( SELECT STRING_AGG(a.action_type_cd || '^' || a.action_type_nm, '|') FROM gene_disease_axn a WHERE a.gene_id = gd.gene_id AND a.disease_id = gd.disease_id) ELSE NULL END actionTypes FROM gene_disease gd INNER JOIN term g ON gd.gene_id = g.id INNER JOIN term d ON gd.disease_id = d.id WHERE gd.disease_id IN ( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '2194442') ORDER BY actionTypes, gd.network_score DESC NULLS LAST, g.nm_sort, d.nm_sort;
Date: 2026-07-21 01:06:20 Duration: 47s643ms Database: ctdprd51 User: pubeu Bind query: yes
-
SELECT /* DiseaseGeneAssnsDAO */ d.nm diseaseNm, d.acc_txt diseaseAcc, d.acc_db_cd diseaseAccDbCd, d.id diseaseId, g.nm geneSymbol, g.acc_txt geneAcc, g.acc_db_cd geneAccDbCd, g.id geneId, gd.network_score networkScore, gd.indirect_chem_qty inferredCount, gd.reference_qty referenceCount, gd.exposure_reference_qty exposureReferenceCount, CASE WHEN gd.curated_reference_qty > 0 THEN ( SELECT STRING_AGG(a.action_type_cd || '^' || a.action_type_nm, '|') FROM gene_disease_axn a WHERE a.gene_id = gd.gene_id AND a.disease_id = gd.disease_id) ELSE NULL END actionTypes FROM gene_disease gd INNER JOIN term g ON gd.gene_id = g.id INNER JOIN term d ON gd.disease_id = d.id WHERE gd.disease_id IN ( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '2194442') ORDER BY actionTypes, gd.network_score DESC NULLS LAST, g.nm_sort, d.nm_sort;
Date: 2026-07-21 01:08:49 Duration: 46s483ms Database: ctdprd51 User: pubeu Bind query: yes
-
SELECT /* DiseaseGeneAssnsDAO */ d.nm diseaseNm, d.acc_txt diseaseAcc, d.acc_db_cd diseaseAccDbCd, d.id diseaseId, g.nm geneSymbol, g.acc_txt geneAcc, g.acc_db_cd geneAccDbCd, g.id geneId, gd.network_score networkScore, gd.indirect_chem_qty inferredCount, gd.reference_qty referenceCount, gd.exposure_reference_qty exposureReferenceCount, CASE WHEN gd.curated_reference_qty > 0 THEN ( SELECT STRING_AGG(a.action_type_cd || '^' || a.action_type_nm, '|') FROM gene_disease_axn a WHERE a.gene_id = gd.gene_id AND a.disease_id = gd.disease_id) ELSE NULL END actionTypes FROM gene_disease gd INNER JOIN term g ON gd.gene_id = g.id INNER JOIN term d ON gd.disease_id = d.id WHERE gd.disease_id IN ( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '2194442') ORDER BY actionTypes, gd.network_score DESC NULLS LAST, g.nm_sort, d.nm_sort;
Date: 2026-07-21 00:48:44 Duration: 46s429ms Database: ctdprd51 User: pubeu Bind query: yes
5 2m22s 1 2m22s 2m22s 2m22s select ii.cd, count(ii.id) cnt from ( select ot.cd, tl.term_id id from object_type ot inner join term_label tl on ot.id = tl.object_type_id where tl.nm_fts @@ to_tsquery(?, ?) union select ?, r.id from reference r where r.title_abstract_fts @@ to_tsquery(?, ?) or r.id in ( select rpr.reference_id from reference_party_role rpr inner join reference_party rp on rpr.reference_party_id = rp.id where (substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ?)) union select ot.cd, l.object_id from db_link l inner join object_type ot on l.object_type_id = ot.id where l.type_cd = ? and (upper(l.acc_txt) like ? or upper(l.acc_txt) like ? or upper(l.acc_txt) like ? or upper(l.acc_txt) like ? or upper(l.acc_txt) like ? or upper(l.acc_txt) like ? or upper(l.acc_txt) like ? or upper(l.acc_txt) like ? or upper(l.acc_txt) like ? or upper(l.acc_txt) like ? or upper(l.acc_txt) like ? or upper(l.acc_txt) like ? or upper(l.acc_txt) like ? or upper(l.acc_txt) like ? or upper(l.acc_txt) like ? or upper(l.acc_txt) like ? or upper(l.acc_txt) like ? or upper(l.acc_txt) like ? or upper(l.acc_txt) like ? or upper(l.acc_txt) like ? or upper(l.acc_txt) like ? or upper(l.acc_txt) like ? or upper(l.acc_txt) like ? or upper(l.acc_txt) like ? or upper(l.acc_txt) like ? or upper(l.acc_txt) like ? or upper(l.acc_txt) like ? or upper(l.acc_txt) like ? or upper(l.acc_txt) like ? or upper(l.acc_txt) like ? or upper(l.acc_txt) like ? or upper(l.acc_txt) like ? or upper(l.acc_txt) like ? or upper(l.acc_txt) like ? or upper(l.acc_txt) like ? or upper(l.acc_txt) like ? or upper(l.acc_txt) like ?)) ii group by ii.cd;Times Reported Time consuming queries #5
Day Hour Count Duration Avg duration Jul 21 03 1 2m22s 2m22s [ User: pubeu - Total duration: 2m22s - Times executed: 1 ]
-
SELECT /* BasicCountsDAO gen */ ii.cd, COUNT(ii.id) cnt FROM ( SELECT ot.cd, tl.term_id id FROM object_type ot INNER JOIN term_label tl ON ot.id = tl.object_type_id WHERE tl.nm_fts @@ to_tsquery('common.english_nostops', '(11,14-EICOSADIENOIC | 11,14EICOSADIENOIC) & ACID & DELPHIN_QT & DELTOIN & DEMETHYLDELAVAINE & A & DEMETHYLDELAVAINE & B & DEOXYANDROGRAPHOLIDE & KARAKOLINE & KARANJIN & NEOKADSURANIC & ACID & B & (2,7-DIDEACETYL-2,7-DIBENZOYL-TAXAYUNNANINE | 2,7DIDEACETYL2,7DIBENZOYLTAXAYUNNANINE) & F & BENZOYLNAPELLINE & (6-DEMETHYLDESOLINE | 6DEMETHYLDESOLINE) & DEOXYACONITINE & R & (-NORCOCLAURINE | NORCOCLAURINE) & IGNAVINE & ISOTALATIZIDINE & JESACONITINE & 3R,8S,9R,10R,13R,14S,17R & (-3-HYDROXY-4,4,9,13,14-PENTAMETHYL-17- | 3HYDROXY4,4,9,13,14PENTAMETHYL17) & E,2R & (-6-METHYL-7- | 6METHYL7) & 2R,3R,4S,5S,6R & (-3,4,5-TRIHYDROXY-6- | 3,4,5TRIHYDROXY6) & 2R,3R,4S,5S,6R & (-3,4,5-TRIHYDROXY-6- | 3,4,5TRIHYDROXY6) & HYDROXYMETHYL & (OXAN-2-YL | OXAN2YL) & OXYMETHYL & (OXAN-2-YL | OXAN2YL) & (OXYHEPT-5-E | OXYHEPT5E)') UNION SELECT 'reference', r.id FROM reference r WHERE r.title_abstract_fts @@ to_tsquery('pg_catalog.english', '(11,14-EICOSADIENOIC | 11,14EICOSADIENOIC) & ACID & DELPHIN_QT & DELTOIN & DEMETHYLDELAVAINE & A & DEMETHYLDELAVAINE & B & DEOXYANDROGRAPHOLIDE & KARAKOLINE & KARANJIN & NEOKADSURANIC & ACID & B & (2,7-DIDEACETYL-2,7-DIBENZOYL-TAXAYUNNANINE | 2,7DIDEACETYL2,7DIBENZOYLTAXAYUNNANINE) & F & BENZOYLNAPELLINE & (6-DEMETHYLDESOLINE | 6DEMETHYLDESOLINE) & DEOXYACONITINE & R & (-NORCOCLAURINE | NORCOCLAURINE) & IGNAVINE & ISOTALATIZIDINE & JESACONITINE & 3R,8S,9R,10R,13R,14S,17R & (-3-HYDROXY-4,4,9,13,14-PENTAMETHYL-17- | 3HYDROXY4,4,9,13,14PENTAMETHYL17) & E,2R & (-6-METHYL-7- | 6METHYL7) & 2R,3R,4S,5S,6R & (-3,4,5-TRIHYDROXY-6- | 3,4,5TRIHYDROXY6) & 2R,3R,4S,5S,6R & (-3,4,5-TRIHYDROXY-6- | 3,4,5TRIHYDROXY6) & HYDROXYMETHYL & (OXAN-2-YL | OXAN2YL) & OXYMETHYL & (OXAN-2-YL | OXAN2YL) & (OXYHEPT-5-E | OXYHEPT5E)') OR r.id IN ( SELECT rpr.reference_id FROM reference_party_role rpr INNER JOIN reference_party rp ON rpr.reference_party_id = rp.id WHERE (SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE '(11,14-EICOSADIENOIC' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE '11,14EICOSADIENOIC)' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'ACID' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'DELPHIN_QT' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'DELTOIN' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'DEMETHYLDELAVAINE' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'A' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'DEMETHYLDELAVAINE' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'B' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'DEOXYANDROGRAPHOLIDE' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'KARAKOLINE' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'KARANJIN' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'NEOKADSURANIC' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'ACID' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'B' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE '(2,7-DIDEACETYL-2,7-DIBENZOYL-TAXAYUNNANINE' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE '2,7DIDEACETYL2,7DIBENZOYLTAXAYUNNANINE)' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'F' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'BENZOYLNAPELLINE' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE '(6-DEMETHYLDESOLINE' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE '6DEMETHYLDESOLINE)' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'DEOXYACONITINE' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'R' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE '(-NORCOCLAURINE' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'NORCOCLAURINE)' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'IGNAVINE' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'ISOTALATIZIDINE' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'JESACONITINE' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE '3R,8S,9R,10R,13R,14S,17R' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE '(-3-HYDROXY-4,4,9,13,14-PENTAMETHYL-17-' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE '3HYDROXY4,4,9,13,14PENTAMETHYL17)' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'E,2R' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE '(-6-METHYL-7-' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE '6METHYL7)' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE '2R,3R,4S,5S,6R' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE '(-3,4,5-TRIHYDROXY-6-' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE '3,4,5TRIHYDROXY6)' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE '2R,3R,4S,5S,6R' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE '(-3,4,5-TRIHYDROXY-6-' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE '3,4,5TRIHYDROXY6)' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'HYDROXYMETHYL' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE '(OXAN-2-YL' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'OXAN2YL)' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'OXYMETHYL' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE '(OXAN-2-YL' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'OXAN2YL)' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE '(OXYHEPT-5-E' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'OXYHEPT5E)')) UNION SELECT ot.cd, l.object_id FROM db_link l INNER JOIN object_type ot on l.object_type_id = ot.id WHERE l.type_cd = 'A' AND (upper(l.acc_txt) LIKE '11,14-EICOSADIENOIC' OR upper(l.acc_txt) LIKE 'ACID' OR upper(l.acc_txt) LIKE 'DELPHIN_QT' OR upper(l.acc_txt) LIKE 'DELTOIN' OR upper(l.acc_txt) LIKE 'DEMETHYLDELAVAINE' OR upper(l.acc_txt) LIKE 'A' OR upper(l.acc_txt) LIKE 'DEMETHYLDELAVAINE' OR upper(l.acc_txt) LIKE 'B' OR upper(l.acc_txt) LIKE 'DEOXYANDROGRAPHOLIDE' OR upper(l.acc_txt) LIKE 'KARAKOLINE' OR upper(l.acc_txt) LIKE 'KARANJIN' OR upper(l.acc_txt) LIKE 'NEOKADSURANIC' OR upper(l.acc_txt) LIKE 'ACID' OR upper(l.acc_txt) LIKE 'B' OR upper(l.acc_txt) LIKE '2,7-DIDEACETYL-2,7-DIBENZOYL-TAXAYUNNANINE' OR upper(l.acc_txt) LIKE 'F' OR upper(l.acc_txt) LIKE 'BENZOYLNAPELLINE' OR upper(l.acc_txt) LIKE '6-DEMETHYLDESOLINE' OR upper(l.acc_txt) LIKE 'DEOXYACONITINE' OR upper(l.acc_txt) LIKE 'R' OR upper(l.acc_txt) LIKE '-NORCOCLAURINE' OR upper(l.acc_txt) LIKE 'IGNAVINE' OR upper(l.acc_txt) LIKE 'ISOTALATIZIDINE' OR upper(l.acc_txt) LIKE 'JESACONITINE' OR upper(l.acc_txt) LIKE '3R,8S,9R,10R,13R,14S,17R' OR upper(l.acc_txt) LIKE '-3-HYDROXY-4,4,9,13,14-PENTAMETHYL-17-' OR upper(l.acc_txt) LIKE 'E,2R' OR upper(l.acc_txt) LIKE '-6-METHYL-7-' OR upper(l.acc_txt) LIKE '2R,3R,4S,5S,6R' OR upper(l.acc_txt) LIKE '-3,4,5-TRIHYDROXY-6-' OR upper(l.acc_txt) LIKE '2R,3R,4S,5S,6R' OR upper(l.acc_txt) LIKE '-3,4,5-TRIHYDROXY-6-' OR upper(l.acc_txt) LIKE 'HYDROXYMETHYL' OR upper(l.acc_txt) LIKE 'OXAN-2-YL' OR upper(l.acc_txt) LIKE 'OXYMETHYL' OR upper(l.acc_txt) LIKE 'OXAN-2-YL' OR upper(l.acc_txt) LIKE 'OXYHEPT-5-E')) ii GROUP BY ii.cd;
Date: 2026-07-21 03:47:54 Duration: 2m22s Database: ctdprd51 User: pubeu Bind query: yes
6 1m36s 4 24s23ms 24s294ms 24s185ms copy pubc.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) to stdout;Times Reported Time consuming queries #6
Day Hour Count Duration Avg duration Jul 21 06 1 24s23ms 24s23ms 10 1 24s153ms 24s153ms 14 1 24s294ms 24s294ms 18 1 24s269ms 24s269ms -
COPY pubc.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) TO stdout;
Date: 2026-07-21 14:07:19 Duration: 24s294ms
-
COPY pubc.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) TO stdout;
Date: 2026-07-21 18:07:19 Duration: 24s269ms
-
COPY pubc.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) TO stdout;
Date: 2026-07-21 10:07:19 Duration: 24s153ms
7 1m19s 4 19s960ms 20s56ms 19s993ms copy edit.db_link (object_id, object_type_id, acc_txt, db_id, type_cd, is_primary, create_by, create_tm, mod_by, mod_tm) to stdout;Times Reported Time consuming queries #7
Day Hour Count Duration Avg duration Jul 21 06 1 19s960ms 19s960ms 10 1 19s961ms 19s961ms 14 1 20s56ms 20s56ms 18 1 19s993ms 19s993ms [ User: postgres - Total duration: 1m19s - Times executed: 4 ]
[ Application: pg_dump - Total duration: 1m19s - Times executed: 4 ]
-
COPY edit.db_link (object_id, object_type_id, acc_txt, db_id, type_cd, is_primary, create_by, create_tm, mod_by, mod_tm) TO stdout;
Date: 2026-07-21 14:00:22 Duration: 20s56ms Database: ctdprd51 User: postgres Application: pg_dump
-
COPY edit.db_link (object_id, object_type_id, acc_txt, db_id, type_cd, is_primary, create_by, create_tm, mod_by, mod_tm) TO stdout;
Date: 2026-07-21 18:00:21 Duration: 19s993ms Database: ctdprd51 User: postgres Application: pg_dump
-
COPY edit.db_link (object_id, object_type_id, acc_txt, db_id, type_cd, is_primary, create_by, create_tm, mod_by, mod_tm) TO stdout;
Date: 2026-07-21 10:00:22 Duration: 19s961ms Database: ctdprd51 User: postgres Application: pg_dump
8 1m1s 4 15s368ms 15s510ms 15s440ms copy pubc.log_query_bots_original (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, user_agent_pattern) to stdout;Times Reported Time consuming queries #8
Day Hour Count Duration Avg duration Jul 21 06 1 15s368ms 15s368ms 10 1 15s491ms 15s491ms 14 1 15s390ms 15s390ms 18 1 15s510ms 15s510ms -
COPY pubc.log_query_bots_original (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, user_agent_pattern) TO stdout;
Date: 2026-07-21 18:07:34 Duration: 15s510ms
-
COPY pubc.log_query_bots_original (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, user_agent_pattern) TO stdout;
Date: 2026-07-21 10:07:34 Duration: 15s491ms
-
COPY pubc.log_query_bots_original (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, user_agent_pattern) TO stdout;
Date: 2026-07-21 14:07:34 Duration: 15s390ms
9 59s541ms 4 14s819ms 14s942ms 14s885ms copy edit.ixn_actor (ixn_id, position_seq, object_type_id, acc_txt, acc_db_id, object_nm, actor_form_type_id, qual_actor_form_type_id, seq_acc_txt, create_by, create_tm, mod_by, mod_tm) to stdout;Times Reported Time consuming queries #9
Day Hour Count Duration Avg duration Jul 21 06 1 14s894ms 14s894ms 10 1 14s885ms 14s885ms 14 1 14s819ms 14s819ms 18 1 14s942ms 14s942ms -
COPY edit.ixn_actor (ixn_id, position_seq, object_type_id, acc_txt, acc_db_id, object_nm, actor_form_type_id, qual_actor_form_type_id, seq_acc_txt, create_by, create_tm, mod_by, mod_tm) TO stdout;
Date: 2026-07-21 18:00:52 Duration: 14s942ms
-
COPY edit.ixn_actor (ixn_id, position_seq, object_type_id, acc_txt, acc_db_id, object_nm, actor_form_type_id, qual_actor_form_type_id, seq_acc_txt, create_by, create_tm, mod_by, mod_tm) TO stdout;
Date: 2026-07-21 06:00:53 Duration: 14s894ms
-
COPY edit.ixn_actor (ixn_id, position_seq, object_type_id, acc_txt, acc_db_id, object_nm, actor_form_type_id, qual_actor_form_type_id, seq_acc_txt, create_by, create_tm, mod_by, mod_tm) TO stdout;
Date: 2026-07-21 10:00:53 Duration: 14s885ms
10 58s208ms 4 14s510ms 14s587ms 14s552ms copy edit.reference (id, title, journal_nm, issue, pages_txt, page_position_seq, volume, pub_dt_format_mask, pub_start_dt, pub_end_dt, pub_start_season_nm, pub_end_season_nm, is_review, is_author_list_complete, affiliation_txt, abstract_txt, create_by, create_tm, mod_by, mod_tm) to stdout;Times Reported Time consuming queries #10
Day Hour Count Duration Avg duration Jul 21 06 1 14s587ms 14s587ms 10 1 14s539ms 14s539ms 14 1 14s510ms 14s510ms 18 1 14s570ms 14s570ms -
COPY edit.reference (id, title, journal_nm, issue, pages_txt, page_position_seq, volume, pub_dt_format_mask, pub_start_dt, pub_end_dt, pub_start_season_nm, pub_end_season_nm, is_review, is_author_list_complete, affiliation_txt, abstract_txt, create_by, create_tm, mod_by, mod_tm) TO stdout;
Date: 2026-07-21 06:01:07 Duration: 14s587ms
-
COPY edit.reference (id, title, journal_nm, issue, pages_txt, page_position_seq, volume, pub_dt_format_mask, pub_start_dt, pub_end_dt, pub_start_season_nm, pub_end_season_nm, is_review, is_author_list_complete, affiliation_txt, abstract_txt, create_by, create_tm, mod_by, mod_tm) TO stdout;
Date: 2026-07-21 18:01:07 Duration: 14s570ms
-
COPY edit.reference (id, title, journal_nm, issue, pages_txt, page_position_seq, volume, pub_dt_format_mask, pub_start_dt, pub_end_dt, pub_start_season_nm, pub_end_season_nm, is_review, is_author_list_complete, affiliation_txt, abstract_txt, create_by, create_tm, mod_by, mod_tm) TO stdout;
Date: 2026-07-21 10:01:08 Duration: 14s539ms
11 29s888ms 4 7s431ms 7s510ms 7s472ms copy edit.ixn (id, ixn_type_id, parent_id, position_seq, root_id, create_by, create_tm, mod_by, mod_tm) to stdout;Times Reported Time consuming queries #11
Day Hour Count Duration Avg duration Jul 21 06 1 7s466ms 7s466ms 10 1 7s481ms 7s481ms 14 1 7s431ms 7s431ms 18 1 7s510ms 7s510ms -
COPY edit.ixn (id, ixn_type_id, parent_id, position_seq, root_id, create_by, create_tm, mod_by, mod_tm) TO stdout;
Date: 2026-07-21 18:00:31 Duration: 7s510ms
-
COPY edit.ixn (id, ixn_type_id, parent_id, position_seq, root_id, create_by, create_tm, mod_by, mod_tm) TO stdout;
Date: 2026-07-21 10:00:32 Duration: 7s481ms
-
COPY edit.ixn (id, ixn_type_id, parent_id, position_seq, root_id, create_by, create_tm, mod_by, mod_tm) TO stdout;
Date: 2026-07-21 06:00:31 Duration: 7s466ms
12 27s582ms 5 5s9ms 6s485ms 5s516ms select d.abbr dagabbr, d.nm dagnm, gt.level_min_no daglevelmin, gt.nm gonm, gt.nm_html gonmhtml, gt.acc_txt goacc, gt.object_id goid, te.corrected_p_val pvalcorrected, te.raw_p_val pvalraw, te.target_match_qty targetmatchqty, te.target_total_qty targettotalqty, te.background_match_qty backgroundmatchqty, te.background_total_qty backgroundtotalqty, count(*) over () fullrowcount from term_enrichment te inner join dag_node gt on te.enriched_term_id = gt.object_id inner join dag d on gt.dag_id = d.id where te.term_id = ? and te.enriched_object_type_id = ? order by te.corrected_p_val, d.abbr, gt.nm_sort limit ?;Times Reported Time consuming queries #12
Day Hour Count Duration Avg duration Jul 21 06 2 11s769ms 5s884ms 07 3 15s812ms 5s270ms [ User: pubeu - Total duration: 27s582ms - Times executed: 5 ]
-
SELECT /* ChemGODAO */ d.abbr dagAbbr, d.nm dagNm, gt.level_min_no dagLevelMin, gt.nm gonm, gt.nm_html gonmhtml, gt.acc_txt goacc, gt.object_id goid, te.corrected_p_val pValCorrected, te.raw_p_val pValRaw, te.target_match_qty targetmatchqty, te.target_total_qty targettotalqty, te.background_match_qty backgroundmatchqty, te.background_total_qty backgroundtotalqty, COUNT(*) OVER () fullRowCount FROM term_enrichment te INNER JOIN dag_node gt ON te.enriched_term_id = gt.object_id INNER JOIN dag d ON gt.dag_id = d.id WHERE te.term_id = '1381475' AND te.enriched_object_type_id = 5 ORDER BY te.corrected_p_val, d.abbr, gt.nm_sort LIMIT 50;
Date: 2026-07-21 06:46:57 Duration: 6s485ms Database: ctdprd51 User: pubeu Bind query: yes
-
SELECT /* ChemGODAO */ d.abbr dagAbbr, d.nm dagNm, gt.level_min_no dagLevelMin, gt.nm gonm, gt.nm_html gonmhtml, gt.acc_txt goacc, gt.object_id goid, te.corrected_p_val pValCorrected, te.raw_p_val pValRaw, te.target_match_qty targetmatchqty, te.target_total_qty targettotalqty, te.background_match_qty backgroundmatchqty, te.background_total_qty backgroundtotalqty, COUNT(*) OVER () fullRowCount FROM term_enrichment te INNER JOIN dag_node gt ON te.enriched_term_id = gt.object_id INNER JOIN dag d ON gt.dag_id = d.id WHERE te.term_id = '1402023' AND te.enriched_object_type_id = 5 ORDER BY te.corrected_p_val, d.abbr, gt.nm_sort LIMIT 50;
Date: 2026-07-21 07:49:43 Duration: 5s548ms Database: ctdprd51 User: pubeu Bind query: yes
-
SELECT /* ChemGODAO */ d.abbr dagAbbr, d.nm dagNm, gt.level_min_no dagLevelMin, gt.nm gonm, gt.nm_html gonmhtml, gt.acc_txt goacc, gt.object_id goid, te.corrected_p_val pValCorrected, te.raw_p_val pValRaw, te.target_match_qty targetmatchqty, te.target_total_qty targettotalqty, te.background_match_qty backgroundmatchqty, te.background_total_qty backgroundtotalqty, COUNT(*) OVER () fullRowCount FROM term_enrichment te INNER JOIN dag_node gt ON te.enriched_term_id = gt.object_id INNER JOIN dag d ON gt.dag_id = d.id WHERE te.term_id = '1365499' AND te.enriched_object_type_id = 5 ORDER BY te.corrected_p_val, d.abbr, gt.nm_sort LIMIT 50;
Date: 2026-07-21 06:24:24 Duration: 5s283ms Database: ctdprd51 User: pubeu Bind query: yes
13 26s45ms 4 6s474ms 6s531ms 6s511ms copy edit.reference_ixn (id, reference_acc_txt, reference_acc_db_id, ixn_id, taxon_acc_txt, taxon_acc_db_id, evidence_cd, source_cd, field_cd, internal_note, create_by, create_tm, mod_by, mod_tm) to stdout;Times Reported Time consuming queries #13
Day Hour Count Duration Avg duration Jul 21 06 1 6s529ms 6s529ms 10 1 6s474ms 6s474ms 14 1 6s531ms 6s531ms 18 1 6s509ms 6s509ms -
COPY edit.reference_ixn (id, reference_acc_txt, reference_acc_db_id, ixn_id, taxon_acc_txt, taxon_acc_db_id, evidence_cd, source_cd, field_cd, internal_note, create_by, create_tm, mod_by, mod_tm) TO stdout;
Date: 2026-07-21 14:01:16 Duration: 6s531ms
-
COPY edit.reference_ixn (id, reference_acc_txt, reference_acc_db_id, ixn_id, taxon_acc_txt, taxon_acc_db_id, evidence_cd, source_cd, field_cd, internal_note, create_by, create_tm, mod_by, mod_tm) TO stdout;
Date: 2026-07-21 06:01:16 Duration: 6s529ms
-
COPY edit.reference_ixn (id, reference_acc_txt, reference_acc_db_id, ixn_id, taxon_acc_txt, taxon_acc_db_id, evidence_cd, source_cd, field_cd, internal_note, create_by, create_tm, mod_by, mod_tm) TO stdout;
Date: 2026-07-21 18:01:15 Duration: 6s509ms
14 24s676ms 4 6s148ms 6s203ms 6s169ms copy edit.ixn_action (ixn_id, action_type_id, action_degree_type_id, position_seq, create_by, create_tm, mod_by, mod_tm) to stdout;Times Reported Time consuming queries #14
Day Hour Count Duration Avg duration Jul 21 06 1 6s148ms 6s148ms 10 1 6s152ms 6s152ms 14 1 6s171ms 6s171ms 18 1 6s203ms 6s203ms -
COPY edit.ixn_action (ixn_id, action_type_id, action_degree_type_id, position_seq, create_by, create_tm, mod_by, mod_tm) TO stdout;
Date: 2026-07-21 18:00:37 Duration: 6s203ms
-
COPY edit.ixn_action (ixn_id, action_type_id, action_degree_type_id, position_seq, create_by, create_tm, mod_by, mod_tm) TO stdout;
Date: 2026-07-21 14:00:38 Duration: 6s171ms
-
COPY edit.ixn_action (ixn_id, action_type_id, action_degree_type_id, position_seq, create_by, create_tm, mod_by, mod_tm) TO stdout;
Date: 2026-07-21 10:00:38 Duration: 6s152ms
15 21s38ms 4 5s61ms 5s614ms 5s259ms select d.nm diseasenm, d.acc_txt diseaseacc, d.acc_db_cd diseaseaccdbcd, d.id diseaseid, g.nm genesymbol, g.acc_txt geneacc, g.acc_db_cd geneaccdbcd, g.id geneid, gd.network_score networkscore, gd.indirect_chem_qty inferredcount, gd.reference_qty referencecount, gd.exposure_reference_qty exposurereferencecount, case when gd.curated_reference_qty > ? then ( select string_agg(a.action_type_cd || ? || a.action_type_nm, ?) from gene_disease_axn a where a.gene_id = gd.gene_id and a.disease_id = gd.disease_id) else null end actiontypes from gene_disease gd inner join term g on gd.gene_id = g.id inner join term d on gd.disease_id = d.id where gd.disease_id in ( select p.descendant_object_id from dag_path p where p.ancestor_object_id = ?) order by actiontypes, gd.network_score desc nulls last, g.nm_sort, d.nm_sort limit ?;Times Reported Time consuming queries #15
Day Hour Count Duration Avg duration Jul 21 16 2 10s128ms 5s64ms 21 2 10s909ms 5s454ms [ User: pubeu - Total duration: 21s38ms - Times executed: 4 ]
-
SELECT /* DiseaseGeneAssnsDAO */ d.nm diseaseNm, d.acc_txt diseaseAcc, d.acc_db_cd diseaseAccDbCd, d.id diseaseId, g.nm geneSymbol, g.acc_txt geneAcc, g.acc_db_cd geneAccDbCd, g.id geneId, gd.network_score networkScore, gd.indirect_chem_qty inferredCount, gd.reference_qty referenceCount, gd.exposure_reference_qty exposureReferenceCount, CASE WHEN gd.curated_reference_qty > 0 THEN ( SELECT STRING_AGG(a.action_type_cd || '^' || a.action_type_nm, '|') FROM gene_disease_axn a WHERE a.gene_id = gd.gene_id AND a.disease_id = gd.disease_id) ELSE NULL END actionTypes FROM gene_disease gd INNER JOIN term g ON gd.gene_id = g.id INNER JOIN term d ON gd.disease_id = d.id WHERE gd.disease_id IN ( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '2189764') ORDER BY actionTypes, gd.network_score DESC NULLS LAST, g.nm_sort, d.nm_sort LIMIT 50;
Date: 2026-07-21 21:58:45 Duration: 5s614ms Database: ctdprd51 User: pubeu Bind query: yes
-
SELECT /* DiseaseGeneAssnsDAO */ d.nm diseaseNm, d.acc_txt diseaseAcc, d.acc_db_cd diseaseAccDbCd, d.id diseaseId, g.nm geneSymbol, g.acc_txt geneAcc, g.acc_db_cd geneAccDbCd, g.id geneId, gd.network_score networkScore, gd.indirect_chem_qty inferredCount, gd.reference_qty referenceCount, gd.exposure_reference_qty exposureReferenceCount, CASE WHEN gd.curated_reference_qty > 0 THEN ( SELECT STRING_AGG(a.action_type_cd || '^' || a.action_type_nm, '|') FROM gene_disease_axn a WHERE a.gene_id = gd.gene_id AND a.disease_id = gd.disease_id) ELSE NULL END actionTypes FROM gene_disease gd INNER JOIN term g ON gd.gene_id = g.id INNER JOIN term d ON gd.disease_id = d.id WHERE gd.disease_id IN ( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '2189764') ORDER BY actionTypes, gd.network_score DESC NULLS LAST, g.nm_sort, d.nm_sort LIMIT 50;
Date: 2026-07-21 21:58:47 Duration: 5s295ms Database: ctdprd51 User: pubeu Bind query: yes
-
SELECT /* DiseaseGeneAssnsDAO */ d.nm diseaseNm, d.acc_txt diseaseAcc, d.acc_db_cd diseaseAccDbCd, d.id diseaseId, g.nm geneSymbol, g.acc_txt geneAcc, g.acc_db_cd geneAccDbCd, g.id geneId, gd.network_score networkScore, gd.indirect_chem_qty inferredCount, gd.reference_qty referenceCount, gd.exposure_reference_qty exposureReferenceCount, CASE WHEN gd.curated_reference_qty > 0 THEN ( SELECT STRING_AGG(a.action_type_cd || '^' || a.action_type_nm, '|') FROM gene_disease_axn a WHERE a.gene_id = gd.gene_id AND a.disease_id = gd.disease_id) ELSE NULL END actionTypes FROM gene_disease gd INNER JOIN term g ON gd.gene_id = g.id INNER JOIN term d ON gd.disease_id = d.id WHERE gd.disease_id IN ( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '2193684') ORDER BY actionTypes, gd.network_score DESC NULLS LAST, g.nm_sort, d.nm_sort LIMIT 50;
Date: 2026-07-21 16:55:53 Duration: 5s67ms Database: ctdprd51 User: pubeu Bind query: yes
16 8s927ms 1 8s927ms 8s927ms 8s927ms 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 gcr.taxon_id = any (array ( select dp.descendant_object_id from term_label l inner join dag_path dp on l.term_id = dp.ancestor_object_id where upper(l.nm) like ? and l.object_type_id = ?)) 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 ?;Times Reported Time consuming queries #16
Day Hour Count Duration Avg duration Jul 21 22 1 8s927ms 8s927ms -
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 */ gcr.taxon_id = ANY (ARRAY ( SELECT /* CIQH.getIxnTaxonWhereEquals.Label */ dp.descendant_object_id FROM term_label l INNER JOIN dag_path dp ON l.term_id = dp.ancestor_object_id WHERE UPPER(l.nm) LIKE 'HOMO SAPIENS' AND l.object_type_id = 1)) 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;
Date: 2026-07-21 22:11:14 Duration: 8s927ms Bind query: yes
17 8s789ms 1 8s789ms 8s789ms 8s789ms 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 gcr.taxon_id = any (array ( select dp.descendant_object_id from term_label l inner join dag_path dp on l.term_id = dp.ancestor_object_id where upper(l.nm) like ? and l.object_type_id = ?)) 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 desc, g.nm_sort, i.sort_txt limit ?;Times Reported Time consuming queries #17
Day Hour Count Duration Avg duration Jul 21 22 1 8s789ms 8s789ms [ User: pubeu - Total duration: 8s789ms - Times executed: 1 ]
-
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 */ gcr.taxon_id = ANY (ARRAY ( SELECT /* CIQH.getIxnTaxonWhereEquals.Label */ dp.descendant_object_id FROM term_label l INNER JOIN dag_path dp ON l.term_id = dp.ancestor_object_id WHERE UPPER(l.nm) LIKE 'HOMO SAPIENS' AND l.object_type_id = 1)) 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 DESC, g.nm_sort, i.sort_txt LIMIT 50;
Date: 2026-07-21 22:11:40 Duration: 8s789ms Database: ctdprd51 User: pubeu Bind query: yes
18 6s734ms 1 6s734ms 6s734ms 6s734ms vacuum analyze log_query_archive;Times Reported Time consuming queries #18
Day Hour Count Duration Avg duration Jul 21 00 1 6s734ms 6s734ms -
VACUUM ANALYZE log_query_archive;
Date: 2026-07-21 00:09:25 Duration: 6s734ms
19 5s413ms 1 5s413ms 5s413ms 5s413ms 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 #19
Day Hour Count Duration Avg duration Jul 21 05 1 5s413ms 5s413ms [ User: pubeu - Total duration: 5s413ms - Times executed: 1 ]
-
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 = 1400764)) 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-07-21 05:48:41 Duration: 5s413ms Database: ctdprd51 User: pubeu Bind query: yes
Most frequent queries (N)
Rank Times executed Total duration Min duration Max duration Avg duration Query 1 5 27s582ms 5s9ms 6s485ms 5s516ms select d.abbr dagabbr, d.nm dagnm, gt.level_min_no daglevelmin, gt.nm gonm, gt.nm_html gonmhtml, gt.acc_txt goacc, gt.object_id goid, te.corrected_p_val pvalcorrected, te.raw_p_val pvalraw, te.target_match_qty targetmatchqty, te.target_total_qty targettotalqty, te.background_match_qty backgroundmatchqty, te.background_total_qty backgroundtotalqty, count(*) over () fullrowcount from term_enrichment te inner join dag_node gt on te.enriched_term_id = gt.object_id inner join dag d on gt.dag_id = d.id where te.term_id = ? and te.enriched_object_type_id = ? order by te.corrected_p_val, d.abbr, gt.nm_sort limit ?;Times Reported Time consuming queries #1
Day Hour Count Duration Avg duration Jul 21 06 2 11s769ms 5s884ms 07 3 15s812ms 5s270ms [ User: pubeu - Total duration: 27s582ms - Times executed: 5 ]
-
SELECT /* ChemGODAO */ d.abbr dagAbbr, d.nm dagNm, gt.level_min_no dagLevelMin, gt.nm gonm, gt.nm_html gonmhtml, gt.acc_txt goacc, gt.object_id goid, te.corrected_p_val pValCorrected, te.raw_p_val pValRaw, te.target_match_qty targetmatchqty, te.target_total_qty targettotalqty, te.background_match_qty backgroundmatchqty, te.background_total_qty backgroundtotalqty, COUNT(*) OVER () fullRowCount FROM term_enrichment te INNER JOIN dag_node gt ON te.enriched_term_id = gt.object_id INNER JOIN dag d ON gt.dag_id = d.id WHERE te.term_id = '1381475' AND te.enriched_object_type_id = 5 ORDER BY te.corrected_p_val, d.abbr, gt.nm_sort LIMIT 50;
Date: 2026-07-21 06:46:57 Duration: 6s485ms Database: ctdprd51 User: pubeu Bind query: yes
-
SELECT /* ChemGODAO */ d.abbr dagAbbr, d.nm dagNm, gt.level_min_no dagLevelMin, gt.nm gonm, gt.nm_html gonmhtml, gt.acc_txt goacc, gt.object_id goid, te.corrected_p_val pValCorrected, te.raw_p_val pValRaw, te.target_match_qty targetmatchqty, te.target_total_qty targettotalqty, te.background_match_qty backgroundmatchqty, te.background_total_qty backgroundtotalqty, COUNT(*) OVER () fullRowCount FROM term_enrichment te INNER JOIN dag_node gt ON te.enriched_term_id = gt.object_id INNER JOIN dag d ON gt.dag_id = d.id WHERE te.term_id = '1402023' AND te.enriched_object_type_id = 5 ORDER BY te.corrected_p_val, d.abbr, gt.nm_sort LIMIT 50;
Date: 2026-07-21 07:49:43 Duration: 5s548ms Database: ctdprd51 User: pubeu Bind query: yes
-
SELECT /* ChemGODAO */ d.abbr dagAbbr, d.nm dagNm, gt.level_min_no dagLevelMin, gt.nm gonm, gt.nm_html gonmhtml, gt.acc_txt goacc, gt.object_id goid, te.corrected_p_val pValCorrected, te.raw_p_val pValRaw, te.target_match_qty targetmatchqty, te.target_total_qty targettotalqty, te.background_match_qty backgroundmatchqty, te.background_total_qty backgroundtotalqty, COUNT(*) OVER () fullRowCount FROM term_enrichment te INNER JOIN dag_node gt ON te.enriched_term_id = gt.object_id INNER JOIN dag d ON gt.dag_id = d.id WHERE te.term_id = '1365499' AND te.enriched_object_type_id = 5 ORDER BY te.corrected_p_val, d.abbr, gt.nm_sort LIMIT 50;
Date: 2026-07-21 06:24:24 Duration: 5s283ms Database: ctdprd51 User: pubeu Bind query: yes
2 4 7m31s 1m52s 1m53s 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 #2
Day Hour Count Duration Avg duration Jul 21 06 1 1m52s 1m52s 10 1 1m52s 1m52s 14 1 1m52s 1m52s 18 1 1m53s 1m53s [ User: postgres - Total duration: 7m31s - Times executed: 4 ]
[ Application: pg_dump - Total duration: 7m31s - Times executed: 4 ]
-
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-07-21 18:06:54 Duration: 1m53s Database: ctdprd51 User: postgres Application: pg_dump
-
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-07-21 10:06:55 Duration: 1m52s Database: ctdprd51 User: postgres Application: pg_dump
-
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-07-21 14:06:55 Duration: 1m52s Database: ctdprd51 User: postgres Application: pg_dump
3 4 3m6s 45s647ms 47s643ms 46s550ms select d.nm diseasenm, d.acc_txt diseaseacc, d.acc_db_cd diseaseaccdbcd, d.id diseaseid, g.nm genesymbol, g.acc_txt geneacc, g.acc_db_cd geneaccdbcd, g.id geneid, gd.network_score networkscore, gd.indirect_chem_qty inferredcount, gd.reference_qty referencecount, gd.exposure_reference_qty exposurereferencecount, case when gd.curated_reference_qty > ? then ( select string_agg(a.action_type_cd || ? || a.action_type_nm, ?) from gene_disease_axn a where a.gene_id = gd.gene_id and a.disease_id = gd.disease_id) else null end actiontypes from gene_disease gd inner join term g on gd.gene_id = g.id inner join term d on gd.disease_id = d.id where gd.disease_id in ( select p.descendant_object_id from dag_path p where p.ancestor_object_id = ?) order by actiontypes, gd.network_score desc nulls last, g.nm_sort, d.nm_sort;Times Reported Time consuming queries #3
Day Hour Count Duration Avg duration Jul 21 00 2 1m32s 46s38ms 01 2 1m34s 47s63ms [ User: pubeu - Total duration: 3m6s - Times executed: 4 ]
-
SELECT /* DiseaseGeneAssnsDAO */ d.nm diseaseNm, d.acc_txt diseaseAcc, d.acc_db_cd diseaseAccDbCd, d.id diseaseId, g.nm geneSymbol, g.acc_txt geneAcc, g.acc_db_cd geneAccDbCd, g.id geneId, gd.network_score networkScore, gd.indirect_chem_qty inferredCount, gd.reference_qty referenceCount, gd.exposure_reference_qty exposureReferenceCount, CASE WHEN gd.curated_reference_qty > 0 THEN ( SELECT STRING_AGG(a.action_type_cd || '^' || a.action_type_nm, '|') FROM gene_disease_axn a WHERE a.gene_id = gd.gene_id AND a.disease_id = gd.disease_id) ELSE NULL END actionTypes FROM gene_disease gd INNER JOIN term g ON gd.gene_id = g.id INNER JOIN term d ON gd.disease_id = d.id WHERE gd.disease_id IN ( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '2194442') ORDER BY actionTypes, gd.network_score DESC NULLS LAST, g.nm_sort, d.nm_sort;
Date: 2026-07-21 01:06:20 Duration: 47s643ms Database: ctdprd51 User: pubeu Bind query: yes
-
SELECT /* DiseaseGeneAssnsDAO */ d.nm diseaseNm, d.acc_txt diseaseAcc, d.acc_db_cd diseaseAccDbCd, d.id diseaseId, g.nm geneSymbol, g.acc_txt geneAcc, g.acc_db_cd geneAccDbCd, g.id geneId, gd.network_score networkScore, gd.indirect_chem_qty inferredCount, gd.reference_qty referenceCount, gd.exposure_reference_qty exposureReferenceCount, CASE WHEN gd.curated_reference_qty > 0 THEN ( SELECT STRING_AGG(a.action_type_cd || '^' || a.action_type_nm, '|') FROM gene_disease_axn a WHERE a.gene_id = gd.gene_id AND a.disease_id = gd.disease_id) ELSE NULL END actionTypes FROM gene_disease gd INNER JOIN term g ON gd.gene_id = g.id INNER JOIN term d ON gd.disease_id = d.id WHERE gd.disease_id IN ( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '2194442') ORDER BY actionTypes, gd.network_score DESC NULLS LAST, g.nm_sort, d.nm_sort;
Date: 2026-07-21 01:08:49 Duration: 46s483ms Database: ctdprd51 User: pubeu Bind query: yes
-
SELECT /* DiseaseGeneAssnsDAO */ d.nm diseaseNm, d.acc_txt diseaseAcc, d.acc_db_cd diseaseAccDbCd, d.id diseaseId, g.nm geneSymbol, g.acc_txt geneAcc, g.acc_db_cd geneAccDbCd, g.id geneId, gd.network_score networkScore, gd.indirect_chem_qty inferredCount, gd.reference_qty referenceCount, gd.exposure_reference_qty exposureReferenceCount, CASE WHEN gd.curated_reference_qty > 0 THEN ( SELECT STRING_AGG(a.action_type_cd || '^' || a.action_type_nm, '|') FROM gene_disease_axn a WHERE a.gene_id = gd.gene_id AND a.disease_id = gd.disease_id) ELSE NULL END actionTypes FROM gene_disease gd INNER JOIN term g ON gd.gene_id = g.id INNER JOIN term d ON gd.disease_id = d.id WHERE gd.disease_id IN ( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '2194442') ORDER BY actionTypes, gd.network_score DESC NULLS LAST, g.nm_sort, d.nm_sort;
Date: 2026-07-21 00:48:44 Duration: 46s429ms Database: ctdprd51 User: pubeu Bind query: yes
4 4 1m36s 24s23ms 24s294ms 24s185ms copy pubc.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) to stdout;Times Reported Time consuming queries #4
Day Hour Count Duration Avg duration Jul 21 06 1 24s23ms 24s23ms 10 1 24s153ms 24s153ms 14 1 24s294ms 24s294ms 18 1 24s269ms 24s269ms -
COPY pubc.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) TO stdout;
Date: 2026-07-21 14:07:19 Duration: 24s294ms
-
COPY pubc.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) TO stdout;
Date: 2026-07-21 18:07:19 Duration: 24s269ms
-
COPY pubc.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) TO stdout;
Date: 2026-07-21 10:07:19 Duration: 24s153ms
5 4 1m19s 19s960ms 20s56ms 19s993ms copy edit.db_link (object_id, object_type_id, acc_txt, db_id, type_cd, is_primary, create_by, create_tm, mod_by, mod_tm) to stdout;Times Reported Time consuming queries #5
Day Hour Count Duration Avg duration Jul 21 06 1 19s960ms 19s960ms 10 1 19s961ms 19s961ms 14 1 20s56ms 20s56ms 18 1 19s993ms 19s993ms [ User: postgres - Total duration: 1m19s - Times executed: 4 ]
[ Application: pg_dump - Total duration: 1m19s - Times executed: 4 ]
-
COPY edit.db_link (object_id, object_type_id, acc_txt, db_id, type_cd, is_primary, create_by, create_tm, mod_by, mod_tm) TO stdout;
Date: 2026-07-21 14:00:22 Duration: 20s56ms Database: ctdprd51 User: postgres Application: pg_dump
-
COPY edit.db_link (object_id, object_type_id, acc_txt, db_id, type_cd, is_primary, create_by, create_tm, mod_by, mod_tm) TO stdout;
Date: 2026-07-21 18:00:21 Duration: 19s993ms Database: ctdprd51 User: postgres Application: pg_dump
-
COPY edit.db_link (object_id, object_type_id, acc_txt, db_id, type_cd, is_primary, create_by, create_tm, mod_by, mod_tm) TO stdout;
Date: 2026-07-21 10:00:22 Duration: 19s961ms Database: ctdprd51 User: postgres Application: pg_dump
6 4 1m1s 15s368ms 15s510ms 15s440ms copy pubc.log_query_bots_original (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, user_agent_pattern) to stdout;Times Reported Time consuming queries #6
Day Hour Count Duration Avg duration Jul 21 06 1 15s368ms 15s368ms 10 1 15s491ms 15s491ms 14 1 15s390ms 15s390ms 18 1 15s510ms 15s510ms -
COPY pubc.log_query_bots_original (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, user_agent_pattern) TO stdout;
Date: 2026-07-21 18:07:34 Duration: 15s510ms
-
COPY pubc.log_query_bots_original (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, user_agent_pattern) TO stdout;
Date: 2026-07-21 10:07:34 Duration: 15s491ms
-
COPY pubc.log_query_bots_original (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, user_agent_pattern) TO stdout;
Date: 2026-07-21 14:07:34 Duration: 15s390ms
7 4 59s541ms 14s819ms 14s942ms 14s885ms copy edit.ixn_actor (ixn_id, position_seq, object_type_id, acc_txt, acc_db_id, object_nm, actor_form_type_id, qual_actor_form_type_id, seq_acc_txt, create_by, create_tm, mod_by, mod_tm) to stdout;Times Reported Time consuming queries #7
Day Hour Count Duration Avg duration Jul 21 06 1 14s894ms 14s894ms 10 1 14s885ms 14s885ms 14 1 14s819ms 14s819ms 18 1 14s942ms 14s942ms -
COPY edit.ixn_actor (ixn_id, position_seq, object_type_id, acc_txt, acc_db_id, object_nm, actor_form_type_id, qual_actor_form_type_id, seq_acc_txt, create_by, create_tm, mod_by, mod_tm) TO stdout;
Date: 2026-07-21 18:00:52 Duration: 14s942ms
-
COPY edit.ixn_actor (ixn_id, position_seq, object_type_id, acc_txt, acc_db_id, object_nm, actor_form_type_id, qual_actor_form_type_id, seq_acc_txt, create_by, create_tm, mod_by, mod_tm) TO stdout;
Date: 2026-07-21 06:00:53 Duration: 14s894ms
-
COPY edit.ixn_actor (ixn_id, position_seq, object_type_id, acc_txt, acc_db_id, object_nm, actor_form_type_id, qual_actor_form_type_id, seq_acc_txt, create_by, create_tm, mod_by, mod_tm) TO stdout;
Date: 2026-07-21 10:00:53 Duration: 14s885ms
8 4 58s208ms 14s510ms 14s587ms 14s552ms copy edit.reference (id, title, journal_nm, issue, pages_txt, page_position_seq, volume, pub_dt_format_mask, pub_start_dt, pub_end_dt, pub_start_season_nm, pub_end_season_nm, is_review, is_author_list_complete, affiliation_txt, abstract_txt, create_by, create_tm, mod_by, mod_tm) to stdout;Times Reported Time consuming queries #8
Day Hour Count Duration Avg duration Jul 21 06 1 14s587ms 14s587ms 10 1 14s539ms 14s539ms 14 1 14s510ms 14s510ms 18 1 14s570ms 14s570ms -
COPY edit.reference (id, title, journal_nm, issue, pages_txt, page_position_seq, volume, pub_dt_format_mask, pub_start_dt, pub_end_dt, pub_start_season_nm, pub_end_season_nm, is_review, is_author_list_complete, affiliation_txt, abstract_txt, create_by, create_tm, mod_by, mod_tm) TO stdout;
Date: 2026-07-21 06:01:07 Duration: 14s587ms
-
COPY edit.reference (id, title, journal_nm, issue, pages_txt, page_position_seq, volume, pub_dt_format_mask, pub_start_dt, pub_end_dt, pub_start_season_nm, pub_end_season_nm, is_review, is_author_list_complete, affiliation_txt, abstract_txt, create_by, create_tm, mod_by, mod_tm) TO stdout;
Date: 2026-07-21 18:01:07 Duration: 14s570ms
-
COPY edit.reference (id, title, journal_nm, issue, pages_txt, page_position_seq, volume, pub_dt_format_mask, pub_start_dt, pub_end_dt, pub_start_season_nm, pub_end_season_nm, is_review, is_author_list_complete, affiliation_txt, abstract_txt, create_by, create_tm, mod_by, mod_tm) TO stdout;
Date: 2026-07-21 10:01:08 Duration: 14s539ms
9 4 29s888ms 7s431ms 7s510ms 7s472ms copy edit.ixn (id, ixn_type_id, parent_id, position_seq, root_id, create_by, create_tm, mod_by, mod_tm) to stdout;Times Reported Time consuming queries #9
Day Hour Count Duration Avg duration Jul 21 06 1 7s466ms 7s466ms 10 1 7s481ms 7s481ms 14 1 7s431ms 7s431ms 18 1 7s510ms 7s510ms -
COPY edit.ixn (id, ixn_type_id, parent_id, position_seq, root_id, create_by, create_tm, mod_by, mod_tm) TO stdout;
Date: 2026-07-21 18:00:31 Duration: 7s510ms
-
COPY edit.ixn (id, ixn_type_id, parent_id, position_seq, root_id, create_by, create_tm, mod_by, mod_tm) TO stdout;
Date: 2026-07-21 10:00:32 Duration: 7s481ms
-
COPY edit.ixn (id, ixn_type_id, parent_id, position_seq, root_id, create_by, create_tm, mod_by, mod_tm) TO stdout;
Date: 2026-07-21 06:00:31 Duration: 7s466ms
10 4 26s45ms 6s474ms 6s531ms 6s511ms copy edit.reference_ixn (id, reference_acc_txt, reference_acc_db_id, ixn_id, taxon_acc_txt, taxon_acc_db_id, evidence_cd, source_cd, field_cd, internal_note, create_by, create_tm, mod_by, mod_tm) to stdout;Times Reported Time consuming queries #10
Day Hour Count Duration Avg duration Jul 21 06 1 6s529ms 6s529ms 10 1 6s474ms 6s474ms 14 1 6s531ms 6s531ms 18 1 6s509ms 6s509ms -
COPY edit.reference_ixn (id, reference_acc_txt, reference_acc_db_id, ixn_id, taxon_acc_txt, taxon_acc_db_id, evidence_cd, source_cd, field_cd, internal_note, create_by, create_tm, mod_by, mod_tm) TO stdout;
Date: 2026-07-21 14:01:16 Duration: 6s531ms
-
COPY edit.reference_ixn (id, reference_acc_txt, reference_acc_db_id, ixn_id, taxon_acc_txt, taxon_acc_db_id, evidence_cd, source_cd, field_cd, internal_note, create_by, create_tm, mod_by, mod_tm) TO stdout;
Date: 2026-07-21 06:01:16 Duration: 6s529ms
-
COPY edit.reference_ixn (id, reference_acc_txt, reference_acc_db_id, ixn_id, taxon_acc_txt, taxon_acc_db_id, evidence_cd, source_cd, field_cd, internal_note, create_by, create_tm, mod_by, mod_tm) TO stdout;
Date: 2026-07-21 18:01:15 Duration: 6s509ms
11 4 24s676ms 6s148ms 6s203ms 6s169ms copy edit.ixn_action (ixn_id, action_type_id, action_degree_type_id, position_seq, create_by, create_tm, mod_by, mod_tm) to stdout;Times Reported Time consuming queries #11
Day Hour Count Duration Avg duration Jul 21 06 1 6s148ms 6s148ms 10 1 6s152ms 6s152ms 14 1 6s171ms 6s171ms 18 1 6s203ms 6s203ms -
COPY edit.ixn_action (ixn_id, action_type_id, action_degree_type_id, position_seq, create_by, create_tm, mod_by, mod_tm) TO stdout;
Date: 2026-07-21 18:00:37 Duration: 6s203ms
-
COPY edit.ixn_action (ixn_id, action_type_id, action_degree_type_id, position_seq, create_by, create_tm, mod_by, mod_tm) TO stdout;
Date: 2026-07-21 14:00:38 Duration: 6s171ms
-
COPY edit.ixn_action (ixn_id, action_type_id, action_degree_type_id, position_seq, create_by, create_tm, mod_by, mod_tm) TO stdout;
Date: 2026-07-21 10:00:38 Duration: 6s152ms
12 4 21s38ms 5s61ms 5s614ms 5s259ms select d.nm diseasenm, d.acc_txt diseaseacc, d.acc_db_cd diseaseaccdbcd, d.id diseaseid, g.nm genesymbol, g.acc_txt geneacc, g.acc_db_cd geneaccdbcd, g.id geneid, gd.network_score networkscore, gd.indirect_chem_qty inferredcount, gd.reference_qty referencecount, gd.exposure_reference_qty exposurereferencecount, case when gd.curated_reference_qty > ? then ( select string_agg(a.action_type_cd || ? || a.action_type_nm, ?) from gene_disease_axn a where a.gene_id = gd.gene_id and a.disease_id = gd.disease_id) else null end actiontypes from gene_disease gd inner join term g on gd.gene_id = g.id inner join term d on gd.disease_id = d.id where gd.disease_id in ( select p.descendant_object_id from dag_path p where p.ancestor_object_id = ?) order by actiontypes, gd.network_score desc nulls last, g.nm_sort, d.nm_sort limit ?;Times Reported Time consuming queries #12
Day Hour Count Duration Avg duration Jul 21 16 2 10s128ms 5s64ms 21 2 10s909ms 5s454ms [ User: pubeu - Total duration: 21s38ms - Times executed: 4 ]
-
SELECT /* DiseaseGeneAssnsDAO */ d.nm diseaseNm, d.acc_txt diseaseAcc, d.acc_db_cd diseaseAccDbCd, d.id diseaseId, g.nm geneSymbol, g.acc_txt geneAcc, g.acc_db_cd geneAccDbCd, g.id geneId, gd.network_score networkScore, gd.indirect_chem_qty inferredCount, gd.reference_qty referenceCount, gd.exposure_reference_qty exposureReferenceCount, CASE WHEN gd.curated_reference_qty > 0 THEN ( SELECT STRING_AGG(a.action_type_cd || '^' || a.action_type_nm, '|') FROM gene_disease_axn a WHERE a.gene_id = gd.gene_id AND a.disease_id = gd.disease_id) ELSE NULL END actionTypes FROM gene_disease gd INNER JOIN term g ON gd.gene_id = g.id INNER JOIN term d ON gd.disease_id = d.id WHERE gd.disease_id IN ( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '2189764') ORDER BY actionTypes, gd.network_score DESC NULLS LAST, g.nm_sort, d.nm_sort LIMIT 50;
Date: 2026-07-21 21:58:45 Duration: 5s614ms Database: ctdprd51 User: pubeu Bind query: yes
-
SELECT /* DiseaseGeneAssnsDAO */ d.nm diseaseNm, d.acc_txt diseaseAcc, d.acc_db_cd diseaseAccDbCd, d.id diseaseId, g.nm geneSymbol, g.acc_txt geneAcc, g.acc_db_cd geneAccDbCd, g.id geneId, gd.network_score networkScore, gd.indirect_chem_qty inferredCount, gd.reference_qty referenceCount, gd.exposure_reference_qty exposureReferenceCount, CASE WHEN gd.curated_reference_qty > 0 THEN ( SELECT STRING_AGG(a.action_type_cd || '^' || a.action_type_nm, '|') FROM gene_disease_axn a WHERE a.gene_id = gd.gene_id AND a.disease_id = gd.disease_id) ELSE NULL END actionTypes FROM gene_disease gd INNER JOIN term g ON gd.gene_id = g.id INNER JOIN term d ON gd.disease_id = d.id WHERE gd.disease_id IN ( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '2189764') ORDER BY actionTypes, gd.network_score DESC NULLS LAST, g.nm_sort, d.nm_sort LIMIT 50;
Date: 2026-07-21 21:58:47 Duration: 5s295ms Database: ctdprd51 User: pubeu Bind query: yes
-
SELECT /* DiseaseGeneAssnsDAO */ d.nm diseaseNm, d.acc_txt diseaseAcc, d.acc_db_cd diseaseAccDbCd, d.id diseaseId, g.nm geneSymbol, g.acc_txt geneAcc, g.acc_db_cd geneAccDbCd, g.id geneId, gd.network_score networkScore, gd.indirect_chem_qty inferredCount, gd.reference_qty referenceCount, gd.exposure_reference_qty exposureReferenceCount, CASE WHEN gd.curated_reference_qty > 0 THEN ( SELECT STRING_AGG(a.action_type_cd || '^' || a.action_type_nm, '|') FROM gene_disease_axn a WHERE a.gene_id = gd.gene_id AND a.disease_id = gd.disease_id) ELSE NULL END actionTypes FROM gene_disease gd INNER JOIN term g ON gd.gene_id = g.id INNER JOIN term d ON gd.disease_id = d.id WHERE gd.disease_id IN ( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '2193684') ORDER BY actionTypes, gd.network_score DESC NULLS LAST, g.nm_sort, d.nm_sort LIMIT 50;
Date: 2026-07-21 16:55:53 Duration: 5s67ms Database: ctdprd51 User: pubeu Bind query: yes
13 1 13m8s 13m8s 13m8s 13m8s 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 = ?))) and gcr.gene_id = any (array (( select gi.id gene_id from term gi where gi.object_type_id = ? and upper(gi.nm) like ?) intersect ( select ai.gene_id from dag_path pi inner join gene_go_annot ai on pi.descendant_object_id = ai.go_term_id inner join db_link dbli on dbli.object_id = pi.ancestor_object_id where dbli.acc_txt = ? and dbli.type_cd = ? and dbli.object_type_id = ?) intersect ( select tp.term_id from term_pathway tp where tp.object_type_id = ? and upper(tp.pathway_acc_txt) = ? and tp.pathway_acc_db_cd = ?))) and gcr.chem_id = any (array ( select dp.descendant_object_id from dag_path dp inner join term t on t.id = dp.ancestor_object_id where upper(t.nm) like ? and t.object_type_id = ?)) and gcr.taxon_id = any (array ( select dp.descendant_object_id from dag_path dp inner join dag_node n on n.id = dp.ancestor_dag_node_id where n.acc_txt = ? and n.dag_id = ?)) 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 ?;Times Reported Time consuming queries #13
Day Hour Count Duration Avg duration Jul 21 23 1 13m8s 13m8s [ User: pubeu - Total duration: 13m8s - Times executed: 1 ]
-
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 = 'gene'))) 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 'VTG1') INTERSECT ( SELECT /* IQH.getMasterGoWhereEquals.Gene */ ai.gene_id FROM dag_path pi INNER JOIN gene_go_annot ai ON pi.descendant_object_id = ai.go_term_id INNER JOIN db_link dbli ON dbli.object_id = pi.ancestor_object_id WHERE dbli.acc_txt = 'GO:0008150' AND dbli.type_cd = 'A' AND dbli.object_type_id = 5) INTERSECT ( SELECT /* IQH.getMasterPathwayWhereEquals.Acc */ tp.term_id FROM term_pathway tp WHERE tp.object_type_id = 4 AND UPPER(tp.pathway_acc_txt) = 'R-HSA-2161541' AND tp.pathway_acc_db_cd = 'REACT'))) AND gcr.chem_id = ANY (ARRAY ( SELECT /* CIQH.getIxnChemWhereEquals.Name */ dp.descendant_object_id FROM dag_path dp INNER JOIN term t ON t.id = dp.ancestor_object_id WHERE UPPER(t.nm) LIKE 'TRIPHENYL PHOSPHATE' AND t.object_type_id = 2)) AND gcr.taxon_id = ANY (ARRAY ( SELECT /* CIQH.getIxnTaxonWhereEquals.Acc */ dp.descendant_object_id FROM dag_path dp INNER JOIN dag_node n ON n.id = dp.ancestor_dag_node_id WHERE n.acc_txt = '9615' AND n.dag_id = 7)) 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;
Date: 2026-07-21 23:52:33 Duration: 13m8s Database: ctdprd51 User: pubeu Bind query: yes
14 1 9m16s 9m16s 9m16s 9m16s select maint_query_logs_archive ();Times Reported Time consuming queries #14
Day Hour Count Duration Avg duration Jul 21 00 1 9m16s 9m16s [ User: pubc - Total duration: 9m16s - Times executed: 1 ]
[ Application: psql - Total duration: 9m16s - Times executed: 1 ]
-
/* * 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-07-21 00:09:18 Duration: 9m16s Database: ctdprd51 User: pubc Application: psql
15 1 2m22s 2m22s 2m22s 2m22s select ii.cd, count(ii.id) cnt from ( select ot.cd, tl.term_id id from object_type ot inner join term_label tl on ot.id = tl.object_type_id where tl.nm_fts @@ to_tsquery(?, ?) union select ?, r.id from reference r where r.title_abstract_fts @@ to_tsquery(?, ?) or r.id in ( select rpr.reference_id from reference_party_role rpr inner join reference_party rp on rpr.reference_party_id = rp.id where (substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ?)) union select ot.cd, l.object_id from db_link l inner join object_type ot on l.object_type_id = ot.id where l.type_cd = ? and (upper(l.acc_txt) like ? or upper(l.acc_txt) like ? or upper(l.acc_txt) like ? or upper(l.acc_txt) like ? or upper(l.acc_txt) like ? or upper(l.acc_txt) like ? or upper(l.acc_txt) like ? or upper(l.acc_txt) like ? or upper(l.acc_txt) like ? or upper(l.acc_txt) like ? or upper(l.acc_txt) like ? or upper(l.acc_txt) like ? or upper(l.acc_txt) like ? or upper(l.acc_txt) like ? or upper(l.acc_txt) like ? or upper(l.acc_txt) like ? or upper(l.acc_txt) like ? or upper(l.acc_txt) like ? or upper(l.acc_txt) like ? or upper(l.acc_txt) like ? or upper(l.acc_txt) like ? or upper(l.acc_txt) like ? or upper(l.acc_txt) like ? or upper(l.acc_txt) like ? or upper(l.acc_txt) like ? or upper(l.acc_txt) like ? or upper(l.acc_txt) like ? or upper(l.acc_txt) like ? or upper(l.acc_txt) like ? or upper(l.acc_txt) like ? or upper(l.acc_txt) like ? or upper(l.acc_txt) like ? or upper(l.acc_txt) like ? or upper(l.acc_txt) like ? or upper(l.acc_txt) like ? or upper(l.acc_txt) like ? or upper(l.acc_txt) like ?)) ii group by ii.cd;Times Reported Time consuming queries #15
Day Hour Count Duration Avg duration Jul 21 03 1 2m22s 2m22s [ User: pubeu - Total duration: 2m22s - Times executed: 1 ]
-
SELECT /* BasicCountsDAO gen */ ii.cd, COUNT(ii.id) cnt FROM ( SELECT ot.cd, tl.term_id id FROM object_type ot INNER JOIN term_label tl ON ot.id = tl.object_type_id WHERE tl.nm_fts @@ to_tsquery('common.english_nostops', '(11,14-EICOSADIENOIC | 11,14EICOSADIENOIC) & ACID & DELPHIN_QT & DELTOIN & DEMETHYLDELAVAINE & A & DEMETHYLDELAVAINE & B & DEOXYANDROGRAPHOLIDE & KARAKOLINE & KARANJIN & NEOKADSURANIC & ACID & B & (2,7-DIDEACETYL-2,7-DIBENZOYL-TAXAYUNNANINE | 2,7DIDEACETYL2,7DIBENZOYLTAXAYUNNANINE) & F & BENZOYLNAPELLINE & (6-DEMETHYLDESOLINE | 6DEMETHYLDESOLINE) & DEOXYACONITINE & R & (-NORCOCLAURINE | NORCOCLAURINE) & IGNAVINE & ISOTALATIZIDINE & JESACONITINE & 3R,8S,9R,10R,13R,14S,17R & (-3-HYDROXY-4,4,9,13,14-PENTAMETHYL-17- | 3HYDROXY4,4,9,13,14PENTAMETHYL17) & E,2R & (-6-METHYL-7- | 6METHYL7) & 2R,3R,4S,5S,6R & (-3,4,5-TRIHYDROXY-6- | 3,4,5TRIHYDROXY6) & 2R,3R,4S,5S,6R & (-3,4,5-TRIHYDROXY-6- | 3,4,5TRIHYDROXY6) & HYDROXYMETHYL & (OXAN-2-YL | OXAN2YL) & OXYMETHYL & (OXAN-2-YL | OXAN2YL) & (OXYHEPT-5-E | OXYHEPT5E)') UNION SELECT 'reference', r.id FROM reference r WHERE r.title_abstract_fts @@ to_tsquery('pg_catalog.english', '(11,14-EICOSADIENOIC | 11,14EICOSADIENOIC) & ACID & DELPHIN_QT & DELTOIN & DEMETHYLDELAVAINE & A & DEMETHYLDELAVAINE & B & DEOXYANDROGRAPHOLIDE & KARAKOLINE & KARANJIN & NEOKADSURANIC & ACID & B & (2,7-DIDEACETYL-2,7-DIBENZOYL-TAXAYUNNANINE | 2,7DIDEACETYL2,7DIBENZOYLTAXAYUNNANINE) & F & BENZOYLNAPELLINE & (6-DEMETHYLDESOLINE | 6DEMETHYLDESOLINE) & DEOXYACONITINE & R & (-NORCOCLAURINE | NORCOCLAURINE) & IGNAVINE & ISOTALATIZIDINE & JESACONITINE & 3R,8S,9R,10R,13R,14S,17R & (-3-HYDROXY-4,4,9,13,14-PENTAMETHYL-17- | 3HYDROXY4,4,9,13,14PENTAMETHYL17) & E,2R & (-6-METHYL-7- | 6METHYL7) & 2R,3R,4S,5S,6R & (-3,4,5-TRIHYDROXY-6- | 3,4,5TRIHYDROXY6) & 2R,3R,4S,5S,6R & (-3,4,5-TRIHYDROXY-6- | 3,4,5TRIHYDROXY6) & HYDROXYMETHYL & (OXAN-2-YL | OXAN2YL) & OXYMETHYL & (OXAN-2-YL | OXAN2YL) & (OXYHEPT-5-E | OXYHEPT5E)') OR r.id IN ( SELECT rpr.reference_id FROM reference_party_role rpr INNER JOIN reference_party rp ON rpr.reference_party_id = rp.id WHERE (SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE '(11,14-EICOSADIENOIC' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE '11,14EICOSADIENOIC)' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'ACID' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'DELPHIN_QT' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'DELTOIN' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'DEMETHYLDELAVAINE' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'A' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'DEMETHYLDELAVAINE' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'B' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'DEOXYANDROGRAPHOLIDE' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'KARAKOLINE' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'KARANJIN' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'NEOKADSURANIC' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'ACID' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'B' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE '(2,7-DIDEACETYL-2,7-DIBENZOYL-TAXAYUNNANINE' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE '2,7DIDEACETYL2,7DIBENZOYLTAXAYUNNANINE)' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'F' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'BENZOYLNAPELLINE' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE '(6-DEMETHYLDESOLINE' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE '6DEMETHYLDESOLINE)' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'DEOXYACONITINE' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'R' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE '(-NORCOCLAURINE' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'NORCOCLAURINE)' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'IGNAVINE' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'ISOTALATIZIDINE' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'JESACONITINE' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE '3R,8S,9R,10R,13R,14S,17R' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE '(-3-HYDROXY-4,4,9,13,14-PENTAMETHYL-17-' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE '3HYDROXY4,4,9,13,14PENTAMETHYL17)' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'E,2R' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE '(-6-METHYL-7-' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE '6METHYL7)' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE '2R,3R,4S,5S,6R' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE '(-3,4,5-TRIHYDROXY-6-' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE '3,4,5TRIHYDROXY6)' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE '2R,3R,4S,5S,6R' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE '(-3,4,5-TRIHYDROXY-6-' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE '3,4,5TRIHYDROXY6)' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'HYDROXYMETHYL' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE '(OXAN-2-YL' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'OXAN2YL)' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'OXYMETHYL' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE '(OXAN-2-YL' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'OXAN2YL)' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE '(OXYHEPT-5-E' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'OXYHEPT5E)')) UNION SELECT ot.cd, l.object_id FROM db_link l INNER JOIN object_type ot on l.object_type_id = ot.id WHERE l.type_cd = 'A' AND (upper(l.acc_txt) LIKE '11,14-EICOSADIENOIC' OR upper(l.acc_txt) LIKE 'ACID' OR upper(l.acc_txt) LIKE 'DELPHIN_QT' OR upper(l.acc_txt) LIKE 'DELTOIN' OR upper(l.acc_txt) LIKE 'DEMETHYLDELAVAINE' OR upper(l.acc_txt) LIKE 'A' OR upper(l.acc_txt) LIKE 'DEMETHYLDELAVAINE' OR upper(l.acc_txt) LIKE 'B' OR upper(l.acc_txt) LIKE 'DEOXYANDROGRAPHOLIDE' OR upper(l.acc_txt) LIKE 'KARAKOLINE' OR upper(l.acc_txt) LIKE 'KARANJIN' OR upper(l.acc_txt) LIKE 'NEOKADSURANIC' OR upper(l.acc_txt) LIKE 'ACID' OR upper(l.acc_txt) LIKE 'B' OR upper(l.acc_txt) LIKE '2,7-DIDEACETYL-2,7-DIBENZOYL-TAXAYUNNANINE' OR upper(l.acc_txt) LIKE 'F' OR upper(l.acc_txt) LIKE 'BENZOYLNAPELLINE' OR upper(l.acc_txt) LIKE '6-DEMETHYLDESOLINE' OR upper(l.acc_txt) LIKE 'DEOXYACONITINE' OR upper(l.acc_txt) LIKE 'R' OR upper(l.acc_txt) LIKE '-NORCOCLAURINE' OR upper(l.acc_txt) LIKE 'IGNAVINE' OR upper(l.acc_txt) LIKE 'ISOTALATIZIDINE' OR upper(l.acc_txt) LIKE 'JESACONITINE' OR upper(l.acc_txt) LIKE '3R,8S,9R,10R,13R,14S,17R' OR upper(l.acc_txt) LIKE '-3-HYDROXY-4,4,9,13,14-PENTAMETHYL-17-' OR upper(l.acc_txt) LIKE 'E,2R' OR upper(l.acc_txt) LIKE '-6-METHYL-7-' OR upper(l.acc_txt) LIKE '2R,3R,4S,5S,6R' OR upper(l.acc_txt) LIKE '-3,4,5-TRIHYDROXY-6-' OR upper(l.acc_txt) LIKE '2R,3R,4S,5S,6R' OR upper(l.acc_txt) LIKE '-3,4,5-TRIHYDROXY-6-' OR upper(l.acc_txt) LIKE 'HYDROXYMETHYL' OR upper(l.acc_txt) LIKE 'OXAN-2-YL' OR upper(l.acc_txt) LIKE 'OXYMETHYL' OR upper(l.acc_txt) LIKE 'OXAN-2-YL' OR upper(l.acc_txt) LIKE 'OXYHEPT-5-E')) ii GROUP BY ii.cd;
Date: 2026-07-21 03:47:54 Duration: 2m22s Database: ctdprd51 User: pubeu Bind query: yes
16 1 8s927ms 8s927ms 8s927ms 8s927ms 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 gcr.taxon_id = any (array ( select dp.descendant_object_id from term_label l inner join dag_path dp on l.term_id = dp.ancestor_object_id where upper(l.nm) like ? and l.object_type_id = ?)) 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 ?;Times Reported Time consuming queries #16
Day Hour Count Duration Avg duration Jul 21 22 1 8s927ms 8s927ms -
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 */ gcr.taxon_id = ANY (ARRAY ( SELECT /* CIQH.getIxnTaxonWhereEquals.Label */ dp.descendant_object_id FROM term_label l INNER JOIN dag_path dp ON l.term_id = dp.ancestor_object_id WHERE UPPER(l.nm) LIKE 'HOMO SAPIENS' AND l.object_type_id = 1)) 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;
Date: 2026-07-21 22:11:14 Duration: 8s927ms Bind query: yes
17 1 8s789ms 8s789ms 8s789ms 8s789ms 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 gcr.taxon_id = any (array ( select dp.descendant_object_id from term_label l inner join dag_path dp on l.term_id = dp.ancestor_object_id where upper(l.nm) like ? and l.object_type_id = ?)) 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 desc, g.nm_sort, i.sort_txt limit ?;Times Reported Time consuming queries #17
Day Hour Count Duration Avg duration Jul 21 22 1 8s789ms 8s789ms [ User: pubeu - Total duration: 8s789ms - Times executed: 1 ]
-
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 */ gcr.taxon_id = ANY (ARRAY ( SELECT /* CIQH.getIxnTaxonWhereEquals.Label */ dp.descendant_object_id FROM term_label l INNER JOIN dag_path dp ON l.term_id = dp.ancestor_object_id WHERE UPPER(l.nm) LIKE 'HOMO SAPIENS' AND l.object_type_id = 1)) 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 DESC, g.nm_sort, i.sort_txt LIMIT 50;
Date: 2026-07-21 22:11:40 Duration: 8s789ms Database: ctdprd51 User: pubeu Bind query: yes
18 1 6s734ms 6s734ms 6s734ms 6s734ms vacuum analyze log_query_archive;Times Reported Time consuming queries #18
Day Hour Count Duration Avg duration Jul 21 00 1 6s734ms 6s734ms -
VACUUM ANALYZE log_query_archive;
Date: 2026-07-21 00:09:25 Duration: 6s734ms
19 1 5s413ms 5s413ms 5s413ms 5s413ms 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 #19
Day Hour Count Duration Avg duration Jul 21 05 1 5s413ms 5s413ms [ User: pubeu - Total duration: 5s413ms - Times executed: 1 ]
-
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 = 1400764)) 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-07-21 05:48:41 Duration: 5s413ms Database: ctdprd51 User: pubeu Bind query: yes
Normalized slowest queries (N)
Rank Min duration Max duration Avg duration Times executed Total duration Query 1 13m8s 13m8s 13m8s 1 13m8s 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 = ?))) and gcr.gene_id = any (array (( select gi.id gene_id from term gi where gi.object_type_id = ? and upper(gi.nm) like ?) intersect ( select ai.gene_id from dag_path pi inner join gene_go_annot ai on pi.descendant_object_id = ai.go_term_id inner join db_link dbli on dbli.object_id = pi.ancestor_object_id where dbli.acc_txt = ? and dbli.type_cd = ? and dbli.object_type_id = ?) intersect ( select tp.term_id from term_pathway tp where tp.object_type_id = ? and upper(tp.pathway_acc_txt) = ? and tp.pathway_acc_db_cd = ?))) and gcr.chem_id = any (array ( select dp.descendant_object_id from dag_path dp inner join term t on t.id = dp.ancestor_object_id where upper(t.nm) like ? and t.object_type_id = ?)) and gcr.taxon_id = any (array ( select dp.descendant_object_id from dag_path dp inner join dag_node n on n.id = dp.ancestor_dag_node_id where n.acc_txt = ? and n.dag_id = ?)) 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 ?;Times Reported Time consuming queries #1
Day Hour Count Duration Avg duration Jul 21 23 1 13m8s 13m8s [ User: pubeu - Total duration: 13m8s - Times executed: 1 ]
-
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 = 'gene'))) 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 'VTG1') INTERSECT ( SELECT /* IQH.getMasterGoWhereEquals.Gene */ ai.gene_id FROM dag_path pi INNER JOIN gene_go_annot ai ON pi.descendant_object_id = ai.go_term_id INNER JOIN db_link dbli ON dbli.object_id = pi.ancestor_object_id WHERE dbli.acc_txt = 'GO:0008150' AND dbli.type_cd = 'A' AND dbli.object_type_id = 5) INTERSECT ( SELECT /* IQH.getMasterPathwayWhereEquals.Acc */ tp.term_id FROM term_pathway tp WHERE tp.object_type_id = 4 AND UPPER(tp.pathway_acc_txt) = 'R-HSA-2161541' AND tp.pathway_acc_db_cd = 'REACT'))) AND gcr.chem_id = ANY (ARRAY ( SELECT /* CIQH.getIxnChemWhereEquals.Name */ dp.descendant_object_id FROM dag_path dp INNER JOIN term t ON t.id = dp.ancestor_object_id WHERE UPPER(t.nm) LIKE 'TRIPHENYL PHOSPHATE' AND t.object_type_id = 2)) AND gcr.taxon_id = ANY (ARRAY ( SELECT /* CIQH.getIxnTaxonWhereEquals.Acc */ dp.descendant_object_id FROM dag_path dp INNER JOIN dag_node n ON n.id = dp.ancestor_dag_node_id WHERE n.acc_txt = '9615' AND n.dag_id = 7)) 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;
Date: 2026-07-21 23:52:33 Duration: 13m8s Database: ctdprd51 User: pubeu Bind query: yes
2 9m16s 9m16s 9m16s 1 9m16s select maint_query_logs_archive ();Times Reported Time consuming queries #2
Day Hour Count Duration Avg duration Jul 21 00 1 9m16s 9m16s [ User: pubc - Total duration: 9m16s - Times executed: 1 ]
[ Application: psql - Total duration: 9m16s - Times executed: 1 ]
-
/* * 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-07-21 00:09:18 Duration: 9m16s Database: ctdprd51 User: pubc Application: psql
3 2m22s 2m22s 2m22s 1 2m22s select ii.cd, count(ii.id) cnt from ( select ot.cd, tl.term_id id from object_type ot inner join term_label tl on ot.id = tl.object_type_id where tl.nm_fts @@ to_tsquery(?, ?) union select ?, r.id from reference r where r.title_abstract_fts @@ to_tsquery(?, ?) or r.id in ( select rpr.reference_id from reference_party_role rpr inner join reference_party rp on rpr.reference_party_id = rp.id where (substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ? or substr(get_reference_party_nm_sort (rp.required_nm), ?, ?) like ?)) union select ot.cd, l.object_id from db_link l inner join object_type ot on l.object_type_id = ot.id where l.type_cd = ? and (upper(l.acc_txt) like ? or upper(l.acc_txt) like ? or upper(l.acc_txt) like ? or upper(l.acc_txt) like ? or upper(l.acc_txt) like ? or upper(l.acc_txt) like ? or upper(l.acc_txt) like ? or upper(l.acc_txt) like ? or upper(l.acc_txt) like ? or upper(l.acc_txt) like ? or upper(l.acc_txt) like ? or upper(l.acc_txt) like ? or upper(l.acc_txt) like ? or upper(l.acc_txt) like ? or upper(l.acc_txt) like ? or upper(l.acc_txt) like ? or upper(l.acc_txt) like ? or upper(l.acc_txt) like ? or upper(l.acc_txt) like ? or upper(l.acc_txt) like ? or upper(l.acc_txt) like ? or upper(l.acc_txt) like ? or upper(l.acc_txt) like ? or upper(l.acc_txt) like ? or upper(l.acc_txt) like ? or upper(l.acc_txt) like ? or upper(l.acc_txt) like ? or upper(l.acc_txt) like ? or upper(l.acc_txt) like ? or upper(l.acc_txt) like ? or upper(l.acc_txt) like ? or upper(l.acc_txt) like ? or upper(l.acc_txt) like ? or upper(l.acc_txt) like ? or upper(l.acc_txt) like ? or upper(l.acc_txt) like ? or upper(l.acc_txt) like ?)) ii group by ii.cd;Times Reported Time consuming queries #3
Day Hour Count Duration Avg duration Jul 21 03 1 2m22s 2m22s [ User: pubeu - Total duration: 2m22s - Times executed: 1 ]
-
SELECT /* BasicCountsDAO gen */ ii.cd, COUNT(ii.id) cnt FROM ( SELECT ot.cd, tl.term_id id FROM object_type ot INNER JOIN term_label tl ON ot.id = tl.object_type_id WHERE tl.nm_fts @@ to_tsquery('common.english_nostops', '(11,14-EICOSADIENOIC | 11,14EICOSADIENOIC) & ACID & DELPHIN_QT & DELTOIN & DEMETHYLDELAVAINE & A & DEMETHYLDELAVAINE & B & DEOXYANDROGRAPHOLIDE & KARAKOLINE & KARANJIN & NEOKADSURANIC & ACID & B & (2,7-DIDEACETYL-2,7-DIBENZOYL-TAXAYUNNANINE | 2,7DIDEACETYL2,7DIBENZOYLTAXAYUNNANINE) & F & BENZOYLNAPELLINE & (6-DEMETHYLDESOLINE | 6DEMETHYLDESOLINE) & DEOXYACONITINE & R & (-NORCOCLAURINE | NORCOCLAURINE) & IGNAVINE & ISOTALATIZIDINE & JESACONITINE & 3R,8S,9R,10R,13R,14S,17R & (-3-HYDROXY-4,4,9,13,14-PENTAMETHYL-17- | 3HYDROXY4,4,9,13,14PENTAMETHYL17) & E,2R & (-6-METHYL-7- | 6METHYL7) & 2R,3R,4S,5S,6R & (-3,4,5-TRIHYDROXY-6- | 3,4,5TRIHYDROXY6) & 2R,3R,4S,5S,6R & (-3,4,5-TRIHYDROXY-6- | 3,4,5TRIHYDROXY6) & HYDROXYMETHYL & (OXAN-2-YL | OXAN2YL) & OXYMETHYL & (OXAN-2-YL | OXAN2YL) & (OXYHEPT-5-E | OXYHEPT5E)') UNION SELECT 'reference', r.id FROM reference r WHERE r.title_abstract_fts @@ to_tsquery('pg_catalog.english', '(11,14-EICOSADIENOIC | 11,14EICOSADIENOIC) & ACID & DELPHIN_QT & DELTOIN & DEMETHYLDELAVAINE & A & DEMETHYLDELAVAINE & B & DEOXYANDROGRAPHOLIDE & KARAKOLINE & KARANJIN & NEOKADSURANIC & ACID & B & (2,7-DIDEACETYL-2,7-DIBENZOYL-TAXAYUNNANINE | 2,7DIDEACETYL2,7DIBENZOYLTAXAYUNNANINE) & F & BENZOYLNAPELLINE & (6-DEMETHYLDESOLINE | 6DEMETHYLDESOLINE) & DEOXYACONITINE & R & (-NORCOCLAURINE | NORCOCLAURINE) & IGNAVINE & ISOTALATIZIDINE & JESACONITINE & 3R,8S,9R,10R,13R,14S,17R & (-3-HYDROXY-4,4,9,13,14-PENTAMETHYL-17- | 3HYDROXY4,4,9,13,14PENTAMETHYL17) & E,2R & (-6-METHYL-7- | 6METHYL7) & 2R,3R,4S,5S,6R & (-3,4,5-TRIHYDROXY-6- | 3,4,5TRIHYDROXY6) & 2R,3R,4S,5S,6R & (-3,4,5-TRIHYDROXY-6- | 3,4,5TRIHYDROXY6) & HYDROXYMETHYL & (OXAN-2-YL | OXAN2YL) & OXYMETHYL & (OXAN-2-YL | OXAN2YL) & (OXYHEPT-5-E | OXYHEPT5E)') OR r.id IN ( SELECT rpr.reference_id FROM reference_party_role rpr INNER JOIN reference_party rp ON rpr.reference_party_id = rp.id WHERE (SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE '(11,14-EICOSADIENOIC' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE '11,14EICOSADIENOIC)' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'ACID' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'DELPHIN_QT' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'DELTOIN' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'DEMETHYLDELAVAINE' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'A' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'DEMETHYLDELAVAINE' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'B' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'DEOXYANDROGRAPHOLIDE' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'KARAKOLINE' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'KARANJIN' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'NEOKADSURANIC' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'ACID' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'B' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE '(2,7-DIDEACETYL-2,7-DIBENZOYL-TAXAYUNNANINE' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE '2,7DIDEACETYL2,7DIBENZOYLTAXAYUNNANINE)' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'F' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'BENZOYLNAPELLINE' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE '(6-DEMETHYLDESOLINE' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE '6DEMETHYLDESOLINE)' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'DEOXYACONITINE' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'R' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE '(-NORCOCLAURINE' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'NORCOCLAURINE)' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'IGNAVINE' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'ISOTALATIZIDINE' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'JESACONITINE' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE '3R,8S,9R,10R,13R,14S,17R' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE '(-3-HYDROXY-4,4,9,13,14-PENTAMETHYL-17-' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE '3HYDROXY4,4,9,13,14PENTAMETHYL17)' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'E,2R' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE '(-6-METHYL-7-' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE '6METHYL7)' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE '2R,3R,4S,5S,6R' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE '(-3,4,5-TRIHYDROXY-6-' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE '3,4,5TRIHYDROXY6)' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE '2R,3R,4S,5S,6R' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE '(-3,4,5-TRIHYDROXY-6-' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE '3,4,5TRIHYDROXY6)' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'HYDROXYMETHYL' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE '(OXAN-2-YL' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'OXAN2YL)' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'OXYMETHYL' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE '(OXAN-2-YL' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'OXAN2YL)' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE '(OXYHEPT-5-E' OR SUBSTR(get_reference_party_nm_sort (rp.required_nm), 1, 128) LIKE 'OXYHEPT5E)')) UNION SELECT ot.cd, l.object_id FROM db_link l INNER JOIN object_type ot on l.object_type_id = ot.id WHERE l.type_cd = 'A' AND (upper(l.acc_txt) LIKE '11,14-EICOSADIENOIC' OR upper(l.acc_txt) LIKE 'ACID' OR upper(l.acc_txt) LIKE 'DELPHIN_QT' OR upper(l.acc_txt) LIKE 'DELTOIN' OR upper(l.acc_txt) LIKE 'DEMETHYLDELAVAINE' OR upper(l.acc_txt) LIKE 'A' OR upper(l.acc_txt) LIKE 'DEMETHYLDELAVAINE' OR upper(l.acc_txt) LIKE 'B' OR upper(l.acc_txt) LIKE 'DEOXYANDROGRAPHOLIDE' OR upper(l.acc_txt) LIKE 'KARAKOLINE' OR upper(l.acc_txt) LIKE 'KARANJIN' OR upper(l.acc_txt) LIKE 'NEOKADSURANIC' OR upper(l.acc_txt) LIKE 'ACID' OR upper(l.acc_txt) LIKE 'B' OR upper(l.acc_txt) LIKE '2,7-DIDEACETYL-2,7-DIBENZOYL-TAXAYUNNANINE' OR upper(l.acc_txt) LIKE 'F' OR upper(l.acc_txt) LIKE 'BENZOYLNAPELLINE' OR upper(l.acc_txt) LIKE '6-DEMETHYLDESOLINE' OR upper(l.acc_txt) LIKE 'DEOXYACONITINE' OR upper(l.acc_txt) LIKE 'R' OR upper(l.acc_txt) LIKE '-NORCOCLAURINE' OR upper(l.acc_txt) LIKE 'IGNAVINE' OR upper(l.acc_txt) LIKE 'ISOTALATIZIDINE' OR upper(l.acc_txt) LIKE 'JESACONITINE' OR upper(l.acc_txt) LIKE '3R,8S,9R,10R,13R,14S,17R' OR upper(l.acc_txt) LIKE '-3-HYDROXY-4,4,9,13,14-PENTAMETHYL-17-' OR upper(l.acc_txt) LIKE 'E,2R' OR upper(l.acc_txt) LIKE '-6-METHYL-7-' OR upper(l.acc_txt) LIKE '2R,3R,4S,5S,6R' OR upper(l.acc_txt) LIKE '-3,4,5-TRIHYDROXY-6-' OR upper(l.acc_txt) LIKE '2R,3R,4S,5S,6R' OR upper(l.acc_txt) LIKE '-3,4,5-TRIHYDROXY-6-' OR upper(l.acc_txt) LIKE 'HYDROXYMETHYL' OR upper(l.acc_txt) LIKE 'OXAN-2-YL' OR upper(l.acc_txt) LIKE 'OXYMETHYL' OR upper(l.acc_txt) LIKE 'OXAN-2-YL' OR upper(l.acc_txt) LIKE 'OXYHEPT-5-E')) ii GROUP BY ii.cd;
Date: 2026-07-21 03:47:54 Duration: 2m22s Database: ctdprd51 User: pubeu Bind query: yes
4 1m52s 1m53s 1m52s 4 7m31s 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 #4
Day Hour Count Duration Avg duration Jul 21 06 1 1m52s 1m52s 10 1 1m52s 1m52s 14 1 1m52s 1m52s 18 1 1m53s 1m53s [ User: postgres - Total duration: 7m31s - Times executed: 4 ]
[ Application: pg_dump - Total duration: 7m31s - Times executed: 4 ]
-
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-07-21 18:06:54 Duration: 1m53s Database: ctdprd51 User: postgres Application: pg_dump
-
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-07-21 10:06:55 Duration: 1m52s Database: ctdprd51 User: postgres Application: pg_dump
-
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-07-21 14:06:55 Duration: 1m52s Database: ctdprd51 User: postgres Application: pg_dump
5 45s647ms 47s643ms 46s550ms 4 3m6s select d.nm diseasenm, d.acc_txt diseaseacc, d.acc_db_cd diseaseaccdbcd, d.id diseaseid, g.nm genesymbol, g.acc_txt geneacc, g.acc_db_cd geneaccdbcd, g.id geneid, gd.network_score networkscore, gd.indirect_chem_qty inferredcount, gd.reference_qty referencecount, gd.exposure_reference_qty exposurereferencecount, case when gd.curated_reference_qty > ? then ( select string_agg(a.action_type_cd || ? || a.action_type_nm, ?) from gene_disease_axn a where a.gene_id = gd.gene_id and a.disease_id = gd.disease_id) else null end actiontypes from gene_disease gd inner join term g on gd.gene_id = g.id inner join term d on gd.disease_id = d.id where gd.disease_id in ( select p.descendant_object_id from dag_path p where p.ancestor_object_id = ?) order by actiontypes, gd.network_score desc nulls last, g.nm_sort, d.nm_sort;Times Reported Time consuming queries #5
Day Hour Count Duration Avg duration Jul 21 00 2 1m32s 46s38ms 01 2 1m34s 47s63ms [ User: pubeu - Total duration: 3m6s - Times executed: 4 ]
-
SELECT /* DiseaseGeneAssnsDAO */ d.nm diseaseNm, d.acc_txt diseaseAcc, d.acc_db_cd diseaseAccDbCd, d.id diseaseId, g.nm geneSymbol, g.acc_txt geneAcc, g.acc_db_cd geneAccDbCd, g.id geneId, gd.network_score networkScore, gd.indirect_chem_qty inferredCount, gd.reference_qty referenceCount, gd.exposure_reference_qty exposureReferenceCount, CASE WHEN gd.curated_reference_qty > 0 THEN ( SELECT STRING_AGG(a.action_type_cd || '^' || a.action_type_nm, '|') FROM gene_disease_axn a WHERE a.gene_id = gd.gene_id AND a.disease_id = gd.disease_id) ELSE NULL END actionTypes FROM gene_disease gd INNER JOIN term g ON gd.gene_id = g.id INNER JOIN term d ON gd.disease_id = d.id WHERE gd.disease_id IN ( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '2194442') ORDER BY actionTypes, gd.network_score DESC NULLS LAST, g.nm_sort, d.nm_sort;
Date: 2026-07-21 01:06:20 Duration: 47s643ms Database: ctdprd51 User: pubeu Bind query: yes
-
SELECT /* DiseaseGeneAssnsDAO */ d.nm diseaseNm, d.acc_txt diseaseAcc, d.acc_db_cd diseaseAccDbCd, d.id diseaseId, g.nm geneSymbol, g.acc_txt geneAcc, g.acc_db_cd geneAccDbCd, g.id geneId, gd.network_score networkScore, gd.indirect_chem_qty inferredCount, gd.reference_qty referenceCount, gd.exposure_reference_qty exposureReferenceCount, CASE WHEN gd.curated_reference_qty > 0 THEN ( SELECT STRING_AGG(a.action_type_cd || '^' || a.action_type_nm, '|') FROM gene_disease_axn a WHERE a.gene_id = gd.gene_id AND a.disease_id = gd.disease_id) ELSE NULL END actionTypes FROM gene_disease gd INNER JOIN term g ON gd.gene_id = g.id INNER JOIN term d ON gd.disease_id = d.id WHERE gd.disease_id IN ( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '2194442') ORDER BY actionTypes, gd.network_score DESC NULLS LAST, g.nm_sort, d.nm_sort;
Date: 2026-07-21 01:08:49 Duration: 46s483ms Database: ctdprd51 User: pubeu Bind query: yes
-
SELECT /* DiseaseGeneAssnsDAO */ d.nm diseaseNm, d.acc_txt diseaseAcc, d.acc_db_cd diseaseAccDbCd, d.id diseaseId, g.nm geneSymbol, g.acc_txt geneAcc, g.acc_db_cd geneAccDbCd, g.id geneId, gd.network_score networkScore, gd.indirect_chem_qty inferredCount, gd.reference_qty referenceCount, gd.exposure_reference_qty exposureReferenceCount, CASE WHEN gd.curated_reference_qty > 0 THEN ( SELECT STRING_AGG(a.action_type_cd || '^' || a.action_type_nm, '|') FROM gene_disease_axn a WHERE a.gene_id = gd.gene_id AND a.disease_id = gd.disease_id) ELSE NULL END actionTypes FROM gene_disease gd INNER JOIN term g ON gd.gene_id = g.id INNER JOIN term d ON gd.disease_id = d.id WHERE gd.disease_id IN ( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '2194442') ORDER BY actionTypes, gd.network_score DESC NULLS LAST, g.nm_sort, d.nm_sort;
Date: 2026-07-21 00:48:44 Duration: 46s429ms Database: ctdprd51 User: pubeu Bind query: yes
6 24s23ms 24s294ms 24s185ms 4 1m36s copy pubc.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) to stdout;Times Reported Time consuming queries #6
Day Hour Count Duration Avg duration Jul 21 06 1 24s23ms 24s23ms 10 1 24s153ms 24s153ms 14 1 24s294ms 24s294ms 18 1 24s269ms 24s269ms -
COPY pubc.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) TO stdout;
Date: 2026-07-21 14:07:19 Duration: 24s294ms
-
COPY pubc.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) TO stdout;
Date: 2026-07-21 18:07:19 Duration: 24s269ms
-
COPY pubc.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) TO stdout;
Date: 2026-07-21 10:07:19 Duration: 24s153ms
7 19s960ms 20s56ms 19s993ms 4 1m19s copy edit.db_link (object_id, object_type_id, acc_txt, db_id, type_cd, is_primary, create_by, create_tm, mod_by, mod_tm) to stdout;Times Reported Time consuming queries #7
Day Hour Count Duration Avg duration Jul 21 06 1 19s960ms 19s960ms 10 1 19s961ms 19s961ms 14 1 20s56ms 20s56ms 18 1 19s993ms 19s993ms [ User: postgres - Total duration: 1m19s - Times executed: 4 ]
[ Application: pg_dump - Total duration: 1m19s - Times executed: 4 ]
-
COPY edit.db_link (object_id, object_type_id, acc_txt, db_id, type_cd, is_primary, create_by, create_tm, mod_by, mod_tm) TO stdout;
Date: 2026-07-21 14:00:22 Duration: 20s56ms Database: ctdprd51 User: postgres Application: pg_dump
-
COPY edit.db_link (object_id, object_type_id, acc_txt, db_id, type_cd, is_primary, create_by, create_tm, mod_by, mod_tm) TO stdout;
Date: 2026-07-21 18:00:21 Duration: 19s993ms Database: ctdprd51 User: postgres Application: pg_dump
-
COPY edit.db_link (object_id, object_type_id, acc_txt, db_id, type_cd, is_primary, create_by, create_tm, mod_by, mod_tm) TO stdout;
Date: 2026-07-21 10:00:22 Duration: 19s961ms Database: ctdprd51 User: postgres Application: pg_dump
8 15s368ms 15s510ms 15s440ms 4 1m1s copy pubc.log_query_bots_original (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, user_agent_pattern) to stdout;Times Reported Time consuming queries #8
Day Hour Count Duration Avg duration Jul 21 06 1 15s368ms 15s368ms 10 1 15s491ms 15s491ms 14 1 15s390ms 15s390ms 18 1 15s510ms 15s510ms -
COPY pubc.log_query_bots_original (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, user_agent_pattern) TO stdout;
Date: 2026-07-21 18:07:34 Duration: 15s510ms
-
COPY pubc.log_query_bots_original (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, user_agent_pattern) TO stdout;
Date: 2026-07-21 10:07:34 Duration: 15s491ms
-
COPY pubc.log_query_bots_original (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, user_agent_pattern) TO stdout;
Date: 2026-07-21 14:07:34 Duration: 15s390ms
9 14s819ms 14s942ms 14s885ms 4 59s541ms copy edit.ixn_actor (ixn_id, position_seq, object_type_id, acc_txt, acc_db_id, object_nm, actor_form_type_id, qual_actor_form_type_id, seq_acc_txt, create_by, create_tm, mod_by, mod_tm) to stdout;Times Reported Time consuming queries #9
Day Hour Count Duration Avg duration Jul 21 06 1 14s894ms 14s894ms 10 1 14s885ms 14s885ms 14 1 14s819ms 14s819ms 18 1 14s942ms 14s942ms -
COPY edit.ixn_actor (ixn_id, position_seq, object_type_id, acc_txt, acc_db_id, object_nm, actor_form_type_id, qual_actor_form_type_id, seq_acc_txt, create_by, create_tm, mod_by, mod_tm) TO stdout;
Date: 2026-07-21 18:00:52 Duration: 14s942ms
-
COPY edit.ixn_actor (ixn_id, position_seq, object_type_id, acc_txt, acc_db_id, object_nm, actor_form_type_id, qual_actor_form_type_id, seq_acc_txt, create_by, create_tm, mod_by, mod_tm) TO stdout;
Date: 2026-07-21 06:00:53 Duration: 14s894ms
-
COPY edit.ixn_actor (ixn_id, position_seq, object_type_id, acc_txt, acc_db_id, object_nm, actor_form_type_id, qual_actor_form_type_id, seq_acc_txt, create_by, create_tm, mod_by, mod_tm) TO stdout;
Date: 2026-07-21 10:00:53 Duration: 14s885ms
10 14s510ms 14s587ms 14s552ms 4 58s208ms copy edit.reference (id, title, journal_nm, issue, pages_txt, page_position_seq, volume, pub_dt_format_mask, pub_start_dt, pub_end_dt, pub_start_season_nm, pub_end_season_nm, is_review, is_author_list_complete, affiliation_txt, abstract_txt, create_by, create_tm, mod_by, mod_tm) to stdout;Times Reported Time consuming queries #10
Day Hour Count Duration Avg duration Jul 21 06 1 14s587ms 14s587ms 10 1 14s539ms 14s539ms 14 1 14s510ms 14s510ms 18 1 14s570ms 14s570ms -
COPY edit.reference (id, title, journal_nm, issue, pages_txt, page_position_seq, volume, pub_dt_format_mask, pub_start_dt, pub_end_dt, pub_start_season_nm, pub_end_season_nm, is_review, is_author_list_complete, affiliation_txt, abstract_txt, create_by, create_tm, mod_by, mod_tm) TO stdout;
Date: 2026-07-21 06:01:07 Duration: 14s587ms
-
COPY edit.reference (id, title, journal_nm, issue, pages_txt, page_position_seq, volume, pub_dt_format_mask, pub_start_dt, pub_end_dt, pub_start_season_nm, pub_end_season_nm, is_review, is_author_list_complete, affiliation_txt, abstract_txt, create_by, create_tm, mod_by, mod_tm) TO stdout;
Date: 2026-07-21 18:01:07 Duration: 14s570ms
-
COPY edit.reference (id, title, journal_nm, issue, pages_txt, page_position_seq, volume, pub_dt_format_mask, pub_start_dt, pub_end_dt, pub_start_season_nm, pub_end_season_nm, is_review, is_author_list_complete, affiliation_txt, abstract_txt, create_by, create_tm, mod_by, mod_tm) TO stdout;
Date: 2026-07-21 10:01:08 Duration: 14s539ms
11 8s927ms 8s927ms 8s927ms 1 8s927ms 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 gcr.taxon_id = any (array ( select dp.descendant_object_id from term_label l inner join dag_path dp on l.term_id = dp.ancestor_object_id where upper(l.nm) like ? and l.object_type_id = ?)) 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 ?;Times Reported Time consuming queries #11
Day Hour Count Duration Avg duration Jul 21 22 1 8s927ms 8s927ms -
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 */ gcr.taxon_id = ANY (ARRAY ( SELECT /* CIQH.getIxnTaxonWhereEquals.Label */ dp.descendant_object_id FROM term_label l INNER JOIN dag_path dp ON l.term_id = dp.ancestor_object_id WHERE UPPER(l.nm) LIKE 'HOMO SAPIENS' AND l.object_type_id = 1)) 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;
Date: 2026-07-21 22:11:14 Duration: 8s927ms Bind query: yes
12 8s789ms 8s789ms 8s789ms 1 8s789ms 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 gcr.taxon_id = any (array ( select dp.descendant_object_id from term_label l inner join dag_path dp on l.term_id = dp.ancestor_object_id where upper(l.nm) like ? and l.object_type_id = ?)) 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 desc, g.nm_sort, i.sort_txt limit ?;Times Reported Time consuming queries #12
Day Hour Count Duration Avg duration Jul 21 22 1 8s789ms 8s789ms [ User: pubeu - Total duration: 8s789ms - Times executed: 1 ]
-
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 */ gcr.taxon_id = ANY (ARRAY ( SELECT /* CIQH.getIxnTaxonWhereEquals.Label */ dp.descendant_object_id FROM term_label l INNER JOIN dag_path dp ON l.term_id = dp.ancestor_object_id WHERE UPPER(l.nm) LIKE 'HOMO SAPIENS' AND l.object_type_id = 1)) 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 DESC, g.nm_sort, i.sort_txt LIMIT 50;
Date: 2026-07-21 22:11:40 Duration: 8s789ms Database: ctdprd51 User: pubeu Bind query: yes
13 7s431ms 7s510ms 7s472ms 4 29s888ms copy edit.ixn (id, ixn_type_id, parent_id, position_seq, root_id, create_by, create_tm, mod_by, mod_tm) to stdout;Times Reported Time consuming queries #13
Day Hour Count Duration Avg duration Jul 21 06 1 7s466ms 7s466ms 10 1 7s481ms 7s481ms 14 1 7s431ms 7s431ms 18 1 7s510ms 7s510ms -
COPY edit.ixn (id, ixn_type_id, parent_id, position_seq, root_id, create_by, create_tm, mod_by, mod_tm) TO stdout;
Date: 2026-07-21 18:00:31 Duration: 7s510ms
-
COPY edit.ixn (id, ixn_type_id, parent_id, position_seq, root_id, create_by, create_tm, mod_by, mod_tm) TO stdout;
Date: 2026-07-21 10:00:32 Duration: 7s481ms
-
COPY edit.ixn (id, ixn_type_id, parent_id, position_seq, root_id, create_by, create_tm, mod_by, mod_tm) TO stdout;
Date: 2026-07-21 06:00:31 Duration: 7s466ms
14 6s734ms 6s734ms 6s734ms 1 6s734ms vacuum analyze log_query_archive;Times Reported Time consuming queries #14
Day Hour Count Duration Avg duration Jul 21 00 1 6s734ms 6s734ms -
VACUUM ANALYZE log_query_archive;
Date: 2026-07-21 00:09:25 Duration: 6s734ms
15 6s474ms 6s531ms 6s511ms 4 26s45ms copy edit.reference_ixn (id, reference_acc_txt, reference_acc_db_id, ixn_id, taxon_acc_txt, taxon_acc_db_id, evidence_cd, source_cd, field_cd, internal_note, create_by, create_tm, mod_by, mod_tm) to stdout;Times Reported Time consuming queries #15
Day Hour Count Duration Avg duration Jul 21 06 1 6s529ms 6s529ms 10 1 6s474ms 6s474ms 14 1 6s531ms 6s531ms 18 1 6s509ms 6s509ms -
COPY edit.reference_ixn (id, reference_acc_txt, reference_acc_db_id, ixn_id, taxon_acc_txt, taxon_acc_db_id, evidence_cd, source_cd, field_cd, internal_note, create_by, create_tm, mod_by, mod_tm) TO stdout;
Date: 2026-07-21 14:01:16 Duration: 6s531ms
-
COPY edit.reference_ixn (id, reference_acc_txt, reference_acc_db_id, ixn_id, taxon_acc_txt, taxon_acc_db_id, evidence_cd, source_cd, field_cd, internal_note, create_by, create_tm, mod_by, mod_tm) TO stdout;
Date: 2026-07-21 06:01:16 Duration: 6s529ms
-
COPY edit.reference_ixn (id, reference_acc_txt, reference_acc_db_id, ixn_id, taxon_acc_txt, taxon_acc_db_id, evidence_cd, source_cd, field_cd, internal_note, create_by, create_tm, mod_by, mod_tm) TO stdout;
Date: 2026-07-21 18:01:15 Duration: 6s509ms
16 6s148ms 6s203ms 6s169ms 4 24s676ms copy edit.ixn_action (ixn_id, action_type_id, action_degree_type_id, position_seq, create_by, create_tm, mod_by, mod_tm) to stdout;Times Reported Time consuming queries #16
Day Hour Count Duration Avg duration Jul 21 06 1 6s148ms 6s148ms 10 1 6s152ms 6s152ms 14 1 6s171ms 6s171ms 18 1 6s203ms 6s203ms -
COPY edit.ixn_action (ixn_id, action_type_id, action_degree_type_id, position_seq, create_by, create_tm, mod_by, mod_tm) TO stdout;
Date: 2026-07-21 18:00:37 Duration: 6s203ms
-
COPY edit.ixn_action (ixn_id, action_type_id, action_degree_type_id, position_seq, create_by, create_tm, mod_by, mod_tm) TO stdout;
Date: 2026-07-21 14:00:38 Duration: 6s171ms
-
COPY edit.ixn_action (ixn_id, action_type_id, action_degree_type_id, position_seq, create_by, create_tm, mod_by, mod_tm) TO stdout;
Date: 2026-07-21 10:00:38 Duration: 6s152ms
17 5s9ms 6s485ms 5s516ms 5 27s582ms select d.abbr dagabbr, d.nm dagnm, gt.level_min_no daglevelmin, gt.nm gonm, gt.nm_html gonmhtml, gt.acc_txt goacc, gt.object_id goid, te.corrected_p_val pvalcorrected, te.raw_p_val pvalraw, te.target_match_qty targetmatchqty, te.target_total_qty targettotalqty, te.background_match_qty backgroundmatchqty, te.background_total_qty backgroundtotalqty, count(*) over () fullrowcount from term_enrichment te inner join dag_node gt on te.enriched_term_id = gt.object_id inner join dag d on gt.dag_id = d.id where te.term_id = ? and te.enriched_object_type_id = ? order by te.corrected_p_val, d.abbr, gt.nm_sort limit ?;Times Reported Time consuming queries #17
Day Hour Count Duration Avg duration Jul 21 06 2 11s769ms 5s884ms 07 3 15s812ms 5s270ms [ User: pubeu - Total duration: 27s582ms - Times executed: 5 ]
-
SELECT /* ChemGODAO */ d.abbr dagAbbr, d.nm dagNm, gt.level_min_no dagLevelMin, gt.nm gonm, gt.nm_html gonmhtml, gt.acc_txt goacc, gt.object_id goid, te.corrected_p_val pValCorrected, te.raw_p_val pValRaw, te.target_match_qty targetmatchqty, te.target_total_qty targettotalqty, te.background_match_qty backgroundmatchqty, te.background_total_qty backgroundtotalqty, COUNT(*) OVER () fullRowCount FROM term_enrichment te INNER JOIN dag_node gt ON te.enriched_term_id = gt.object_id INNER JOIN dag d ON gt.dag_id = d.id WHERE te.term_id = '1381475' AND te.enriched_object_type_id = 5 ORDER BY te.corrected_p_val, d.abbr, gt.nm_sort LIMIT 50;
Date: 2026-07-21 06:46:57 Duration: 6s485ms Database: ctdprd51 User: pubeu Bind query: yes
-
SELECT /* ChemGODAO */ d.abbr dagAbbr, d.nm dagNm, gt.level_min_no dagLevelMin, gt.nm gonm, gt.nm_html gonmhtml, gt.acc_txt goacc, gt.object_id goid, te.corrected_p_val pValCorrected, te.raw_p_val pValRaw, te.target_match_qty targetmatchqty, te.target_total_qty targettotalqty, te.background_match_qty backgroundmatchqty, te.background_total_qty backgroundtotalqty, COUNT(*) OVER () fullRowCount FROM term_enrichment te INNER JOIN dag_node gt ON te.enriched_term_id = gt.object_id INNER JOIN dag d ON gt.dag_id = d.id WHERE te.term_id = '1402023' AND te.enriched_object_type_id = 5 ORDER BY te.corrected_p_val, d.abbr, gt.nm_sort LIMIT 50;
Date: 2026-07-21 07:49:43 Duration: 5s548ms Database: ctdprd51 User: pubeu Bind query: yes
-
SELECT /* ChemGODAO */ d.abbr dagAbbr, d.nm dagNm, gt.level_min_no dagLevelMin, gt.nm gonm, gt.nm_html gonmhtml, gt.acc_txt goacc, gt.object_id goid, te.corrected_p_val pValCorrected, te.raw_p_val pValRaw, te.target_match_qty targetmatchqty, te.target_total_qty targettotalqty, te.background_match_qty backgroundmatchqty, te.background_total_qty backgroundtotalqty, COUNT(*) OVER () fullRowCount FROM term_enrichment te INNER JOIN dag_node gt ON te.enriched_term_id = gt.object_id INNER JOIN dag d ON gt.dag_id = d.id WHERE te.term_id = '1365499' AND te.enriched_object_type_id = 5 ORDER BY te.corrected_p_val, d.abbr, gt.nm_sort LIMIT 50;
Date: 2026-07-21 06:24:24 Duration: 5s283ms Database: ctdprd51 User: pubeu Bind query: yes
18 5s413ms 5s413ms 5s413ms 1 5s413ms 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 #18
Day Hour Count Duration Avg duration Jul 21 05 1 5s413ms 5s413ms [ User: pubeu - Total duration: 5s413ms - Times executed: 1 ]
-
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 = 1400764)) 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-07-21 05:48:41 Duration: 5s413ms Database: ctdprd51 User: pubeu Bind query: yes
19 5s61ms 5s614ms 5s259ms 4 21s38ms select d.nm diseasenm, d.acc_txt diseaseacc, d.acc_db_cd diseaseaccdbcd, d.id diseaseid, g.nm genesymbol, g.acc_txt geneacc, g.acc_db_cd geneaccdbcd, g.id geneid, gd.network_score networkscore, gd.indirect_chem_qty inferredcount, gd.reference_qty referencecount, gd.exposure_reference_qty exposurereferencecount, case when gd.curated_reference_qty > ? then ( select string_agg(a.action_type_cd || ? || a.action_type_nm, ?) from gene_disease_axn a where a.gene_id = gd.gene_id and a.disease_id = gd.disease_id) else null end actiontypes from gene_disease gd inner join term g on gd.gene_id = g.id inner join term d on gd.disease_id = d.id where gd.disease_id in ( select p.descendant_object_id from dag_path p where p.ancestor_object_id = ?) order by actiontypes, gd.network_score desc nulls last, g.nm_sort, d.nm_sort limit ?;Times Reported Time consuming queries #19
Day Hour Count Duration Avg duration Jul 21 16 2 10s128ms 5s64ms 21 2 10s909ms 5s454ms [ User: pubeu - Total duration: 21s38ms - Times executed: 4 ]
-
SELECT /* DiseaseGeneAssnsDAO */ d.nm diseaseNm, d.acc_txt diseaseAcc, d.acc_db_cd diseaseAccDbCd, d.id diseaseId, g.nm geneSymbol, g.acc_txt geneAcc, g.acc_db_cd geneAccDbCd, g.id geneId, gd.network_score networkScore, gd.indirect_chem_qty inferredCount, gd.reference_qty referenceCount, gd.exposure_reference_qty exposureReferenceCount, CASE WHEN gd.curated_reference_qty > 0 THEN ( SELECT STRING_AGG(a.action_type_cd || '^' || a.action_type_nm, '|') FROM gene_disease_axn a WHERE a.gene_id = gd.gene_id AND a.disease_id = gd.disease_id) ELSE NULL END actionTypes FROM gene_disease gd INNER JOIN term g ON gd.gene_id = g.id INNER JOIN term d ON gd.disease_id = d.id WHERE gd.disease_id IN ( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '2189764') ORDER BY actionTypes, gd.network_score DESC NULLS LAST, g.nm_sort, d.nm_sort LIMIT 50;
Date: 2026-07-21 21:58:45 Duration: 5s614ms Database: ctdprd51 User: pubeu Bind query: yes
-
SELECT /* DiseaseGeneAssnsDAO */ d.nm diseaseNm, d.acc_txt diseaseAcc, d.acc_db_cd diseaseAccDbCd, d.id diseaseId, g.nm geneSymbol, g.acc_txt geneAcc, g.acc_db_cd geneAccDbCd, g.id geneId, gd.network_score networkScore, gd.indirect_chem_qty inferredCount, gd.reference_qty referenceCount, gd.exposure_reference_qty exposureReferenceCount, CASE WHEN gd.curated_reference_qty > 0 THEN ( SELECT STRING_AGG(a.action_type_cd || '^' || a.action_type_nm, '|') FROM gene_disease_axn a WHERE a.gene_id = gd.gene_id AND a.disease_id = gd.disease_id) ELSE NULL END actionTypes FROM gene_disease gd INNER JOIN term g ON gd.gene_id = g.id INNER JOIN term d ON gd.disease_id = d.id WHERE gd.disease_id IN ( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '2189764') ORDER BY actionTypes, gd.network_score DESC NULLS LAST, g.nm_sort, d.nm_sort LIMIT 50;
Date: 2026-07-21 21:58:47 Duration: 5s295ms Database: ctdprd51 User: pubeu Bind query: yes
-
SELECT /* DiseaseGeneAssnsDAO */ d.nm diseaseNm, d.acc_txt diseaseAcc, d.acc_db_cd diseaseAccDbCd, d.id diseaseId, g.nm geneSymbol, g.acc_txt geneAcc, g.acc_db_cd geneAccDbCd, g.id geneId, gd.network_score networkScore, gd.indirect_chem_qty inferredCount, gd.reference_qty referenceCount, gd.exposure_reference_qty exposureReferenceCount, CASE WHEN gd.curated_reference_qty > 0 THEN ( SELECT STRING_AGG(a.action_type_cd || '^' || a.action_type_nm, '|') FROM gene_disease_axn a WHERE a.gene_id = gd.gene_id AND a.disease_id = gd.disease_id) ELSE NULL END actionTypes FROM gene_disease gd INNER JOIN term g ON gd.gene_id = g.id INNER JOIN term d ON gd.disease_id = d.id WHERE gd.disease_id IN ( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '2193684') ORDER BY actionTypes, gd.network_score DESC NULLS LAST, g.nm_sort, d.nm_sort LIMIT 50;
Date: 2026-07-21 16:55:53 Duration: 5s67ms Database: ctdprd51 User: pubeu Bind query: yes
Time consuming prepare
Rank Total duration Times executed Min duration Max duration Avg duration Query NO DATASET
Time consuming bind
Rank Total duration Times executed Min duration Max duration Avg duration Query 1 0ms 1 0ms 0ms 0ms ;Times Reported Time consuming bind #1
Day Hour Count Duration Avg duration Jul 21 03 1 0ms 0ms [ User: pubeu - Total duration: 2m22s - Times executed: 1 ]
-
;
Date: Duration: 0ms Database: postgres User: ctdprd51 Remote: pubeu
-
Events
Log levels
Key values
- 22,701 Event entries
- (EVENTLOG entries are formaly LOG level entries that are not queries)
Events distribution (except queries)
Key values
- 0 PANIC entries
- 0 FATAL entries
- 0 ERROR entries
- 0 WARNING entries
- 0 EVENTLOG entries
Events per 5 minutes
NO DATASET
Most Frequent Errors/Events
Key values
- 0 Max number of times the same event was reported
- 0 Total events found
Rank Times reported Error NO DATASET