pgBadger-
Global information
- Generated on Thu Jan 30 04:15:15 2025
- Log file: /project/archive/log/postgres/dbprd51/postgresql.log-20250129
- Parsed 363,337 log entries in 14s
- Log start from 2025-01-29 00:00:01 to 2025-01-29 23:59:38
-
Overview
Global Stats
- 270 Number of unique normalized queries
- 3,921 Number of queries
- 9d7h29m31s Total query duration
- 2025-01-29 00:00:04 First query
- 2025-01-29 23:59:36 Last query
- 49 queries/s at 2025-01-29 08:15:20 Query peak
- 9d7h29m31s Total query duration
- 4h51m25s Prepare/parse total duration
- 17s135ms Bind total duration
- 9d2h37m48s Execute total duration
- 24,378 Number of events
- 15 Number of unique normalized events
- 22,891 Max number of times the same event was reported
- 0 Number of cancellation
- 453 Total number of automatic vacuums
- 49 Total number of automatic analyzes
- 1,064 Number temporary file
- 41.44 GiB Max size of temporary file
- 199.40 MiB Average size of temporary file
- 2,519 Total number of sessions
- 170 sessions at 2025-01-29 00:33:44 Session peak
- 95d53m35s Total duration of sessions
- 54m19s Average duration of sessions
- 1 Average queries per session
- 5m19s Average queries duration per session
- 49m Average idle time per session
- 25,407 Total number of connections
- 16 connections/s at 2025-01-29 12:43:36 Connection peak
- 2 Total number of databases
SQL Traffic
Key values
- 49 queries/s Query Peak
- 2025-01-29 08:15:20 Date
SELECT Traffic
Key values
- 4 queries/s Query Peak
- 2025-01-29 00:39:25 Date
INSERT/UPDATE/DELETE Traffic
Key values
- 47 queries/s Query Peak
- 2025-01-29 08:15:20 Date
Queries duration
Key values
- 9d7h29m31s 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) Jan 29 00 191 0ms 25m8s 19s423ms 1m5s 2m44s 25m19s 01 167 0ms 5m21s 4s911ms 19s929ms 25s836ms 5m31s 02 184 0ms 1h45m21s 38s306ms 16s148ms 1m18s 1h45m29s 03 172 0ms 1h2m3s 26s14ms 33s642ms 1m38s 1h2m21s 04 23 0ms 14s34ms 5s213ms 0ms 5s495ms 1m29s 05 0 0ms 0ms 0ms 0ms 0ms 0ms 06 0 0ms 0ms 0ms 0ms 0ms 0ms 07 0 0ms 0ms 0ms 0ms 0ms 0ms 08 218 0ms 5h3m22s 56m55s 20s450ms 1m36s 8d9h37m35s 09 164 0ms 3m12s 3s447ms 15s722ms 17s384ms 3m16s 10 130 0ms 2h5m31s 1m5s 41s40ms 1m8s 2h5m55s 11 178 0ms 35m6s 17s879ms 29s339ms 4m29s 35m6s 12 181 0ms 21s979ms 2s785ms 19s426ms 32s912ms 1m1s 13 236 1s16ms 1m18s 4s23ms 40s597ms 1m1s 1m27s 14 441 0ms 34m28s 8s491ms 49s826ms 1m18s 35m11s 15 206 0ms 3m32s 5s193ms 25s800ms 2m6s 3m43s 16 183 0ms 15s193ms 2s568ms 15s923ms 20s620ms 35s411ms 17 200 0ms 4m44s 5s84ms 25s415ms 49s837ms 4m56s 18 197 0ms 17m45s 9s499ms 27s855ms 2m6s 17m47s 19 159 0ms 5m4s 4s439ms 11s986ms 13s793ms 5m7s 20 157 0ms 3m1s 4s259ms 17s173ms 28s410ms 3m7s 21 179 0ms 17m44s 8s623ms 19s657ms 24s38ms 17m52s 22 180 0ms 17s827ms 2s361ms 14s189ms 19s334ms 25s222ms 23 175 1s24ms 5m20s 5s809ms 15s989ms 55s869ms 5m25s Day Hour SELECT COPY TO Average Duration Latency Percentile(90) Latency Percentile(95) Latency Percentile(99) Jan 29 00 181 0 10s425ms 12s292ms 15s878ms 1m5s 01 166 0 4s920ms 13s677ms 19s929ms 59s785ms 02 180 0 38s158ms 11s892ms 15s926ms 1m18s 03 162 0 26s438ms 13s177ms 19s368ms 2m43s 04 15 0 3s219ms 0ms 0ms 15s423ms 05 0 0 0ms 0ms 0ms 0ms 06 0 0 0ms 0ms 0ms 0ms 07 0 0 0ms 0ms 0ms 0ms 08 152 11 1m41s 14s24ms 20s780ms 1m36s 09 164 0 3s447ms 8s557ms 15s722ms 26s425ms 10 121 8 1m6s 10s268ms 36s53ms 8m43s 11 178 0 17s879ms 14s94ms 29s339ms 5m27s 12 179 0 2s789ms 13s497ms 18s206ms 34s837ms 13 235 0 4s26ms 19s463ms 40s597ms 1m20s 14 430 10 8s505ms 38s780ms 42s744ms 1m29s 15 206 0 5s193ms 15s513ms 25s800ms 2m33s 16 182 0 2s574ms 12s248ms 15s258ms 24s460ms 17 200 0 5s84ms 12s426ms 25s415ms 3m22s 18 187 10 9s499ms 13s608ms 29s325ms 3m4s 19 159 0 4s439ms 10s293ms 11s986ms 1m10s 20 157 0 4s259ms 11s386ms 17s173ms 1m24s 21 179 0 8s623ms 12s913ms 19s657ms 28s30ms 22 180 0 2s361ms 10s475ms 14s189ms 19s493ms 23 175 0 5s809ms 13s193ms 15s989ms 3m37s Day Hour INSERT UPDATE DELETE COPY FROM Average Duration Latency Percentile(90) Latency Percentile(95) Latency Percentile(99) Jan 29 00 5 4 0 0 3m21s 0ms 0ms 1m27s 01 0 0 0 0 0ms 0ms 0ms 0ms 02 1 0 0 0 2m43s 0ms 0ms 0ms 03 0 0 0 0 0ms 0ms 0ms 0ms 04 8 0 0 0 8s952ms 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 47 0 0 0 4h11m43s 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 Jan 29 00 0 190 190.00 0.00% 01 0 167 167.00 0.00% 02 0 185 185.00 0.00% 03 0 173 173.00 0.00% 04 0 23 23.00 0.00% 05 0 0 0.00 0.00% 06 0 0 0.00 0.00% 07 0 0 0.00 0.00% 08 8 213 26.62 3.76% 09 0 174 174.00 0.00% 10 0 139 139.00 0.00% 11 0 163 163.00 0.00% 12 0 191 191.00 0.00% 13 0 236 236.00 0.00% 14 0 489 489.00 0.00% 15 0 209 209.00 0.00% 16 0 193 193.00 0.00% 17 0 200 200.00 0.00% 18 0 187 187.00 0.00% 19 0 159 159.00 0.00% 20 0 157 157.00 0.00% 21 0 179 179.00 0.00% 22 0 180 180.00 0.00% 23 0 175 175.00 0.00% Day Hour Count Average / Second Jan 29 00 119 0.03/s 01 103 0.03/s 02 127 0.04/s 03 117 0.03/s 04 10,230 2.84/s 05 7,198 2.00/s 06 2,504 0.70/s 07 2,557 0.71/s 08 827 0.23/s 09 79 0.02/s 10 81 0.02/s 11 96 0.03/s 12 118 0.03/s 13 157 0.04/s 14 135 0.04/s 15 143 0.04/s 16 102 0.03/s 17 107 0.03/s 18 97 0.03/s 19 109 0.03/s 20 99 0.03/s 21 101 0.03/s 22 105 0.03/s 23 96 0.03/s Day Hour Count Average Duration Average idle time Jan 29 00 119 19m53s 19m22s 01 103 23m36s 23m28s 02 127 19m40s 18m45s 03 117 20m46s 20m8s 04 73 27m1s 27m 05 72 23m17s 23m17s 06 45 30m30s 30m30s 07 39 30m21s 30m21s 08 203 6h8m57s 5h7m49s 09 80 25m17s 25m10s 10 81 22m19s 20m33s 11 97 27m41s 27m8s 12 107 18m13s 18m8s 13 156 15m48s 15m42s 14 135 18m13s 17m45s 15 144 17m6s 16m59s 16 102 23m38s 23m34s 17 106 23m28s 23m18s 18 98 22m29s 22m10s 19 109 22m31s 22m24s 20 99 24m50s 24m43s 21 101 23m49s 23m34s 22 109 1h41m47s 1h41m43s 23 97 47m51s 47m40s -
Connections
Established Connections
Key values
- 16 connections Connection Peak
- 2025-01-29 12:43:36 Date
Connections per database
Key values
- ctdprd51 Main Database
- 25,407 connections Total
Connections per user
Key values
- pubeu Main User
- 25,407 connections Total
-
Sessions
Simultaneous sessions
Key values
- 170 sessions Session Peak
- 2025-01-29 00:33:44 Date
Histogram of session times
Key values
- 1,511 1800000-3600000ms duration
Sessions per database
Key values
- ctdprd51 Main Database
- 2,519 sessions Total
Sessions per user
Key values
- pubeu Main User
- 2,519 sessions Total
Sessions per host
Key values
- 10.12.5.37 Main Host
- 2,519 sessions Total
Host Count Total Duration Average Duration 10.12.5.122 12 38d7h30m33s 3d4h37m32s 10.12.5.36 24 2h3m24s 5m8s 10.12.5.37 1,025 15d1h23m34s 21m9s 10.12.5.38 374 7d20h32m20s 30m14s 10.12.5.39 384 8d38m17s 30m5s 10.12.5.40 2 11h26m20s 5h43m10s 10.12.5.45 295 5d16h56m10s 27m51s 10.12.5.46 380 7d22h10m37s 30m1s 192.168.201.10 5 7d13h39m19s 1d12h19m51s ::1 17 4d30m39s 5h40m37s [local] 1 2m18s 2m18s -
Checkpoints / Restartpoints
Checkpoints Buffers
Key values
- 1,106,231 buffers Checkpoint Peak
- 2025-01-29 00:44:57 Date
- 1620.007 seconds Highest write time
- 1.214 seconds Sync time
Checkpoints Wal files
Key values
- 834 files Wal files usage Peak
- 2025-01-29 09:08:23 Date
Checkpoints distance
Key values
- 17,253.56 Mo Distance Peak
- 2025-01-29 03:41:41 Date
Checkpoints Activity
↑ Back to the top of the Checkpoint Activity tableDay Hour Written buffers Write time Sync time Total time Jan 29 00 2,014,262 2,645.615s 0.613s 2,666.733s 01 877,010 1,718.955s 0.005s 1,720.933s 02 1,013,250 1,619.238s 0.014s 1,621.558s 03 755,859 2,343.109s 3.08s 2,402.857s 04 0 0s 0s 0s 05 0 0s 0s 0s 06 0 0s 0s 0s 07 0 0s 0s 0s 08 524 1.026s 0.021s 13,552.307s 09 485,562 3,239.75s 0.014s 3,264.985s 10 685,809 4,096.169s 0.11s 4,115.238s 11 523,881 1,767.474s 0.003s 1,774.502s 12 29,196 1,673.956s 0.003s 1,674.154s 13 24,458 1,982.441s 0.005s 1,982.632s 14 9,874 988.871s 0.002s 989.013s 15 18,552 1,747.847s 0.005s 1,747.893s 16 4,312 431.921s 0.003s 432.015s 17 1,317 132.039s 0.003s 132.114s 18 1,891 189.55s 0.004s 189.582s 19 1,400 140.33s 0.004s 140.4s 20 1,613 161.586s 0.004s 161.617s 21 1,948 195.229s 0.003s 195.305s 22 3,874 387.926s 0.003s 387.975s 23 1,469 147.231s 0.003s 147.263s Day Hour Added Removed Recycled Synced files Longest sync Average sync Jan 29 00 0 236 1,612 236 0.542s 0.01s 01 0 0 151 108 0.001s 0.003s 02 0 186 142 87 0.011s 0.001s 03 0 50 2,996 1,009 1.210s 0.13s 04 0 0 0 0 0s 0s 05 0 0 0 0 0s 0s 06 0 0 0 0 0s 0s 07 0 0 0 0 0s 0s 08 0 0 422 85 0.016s 0.002s 09 0 3,566 1,023 305 0.001s 0.002s 10 0 3,719 612 410 0.065s 0.004s 11 0 925 372 206 0.001s 0.002s 12 0 11 0 142 0.001s 0.002s 13 0 11 0 172 0.001s 0.003s 14 0 19 0 162 0.001s 0.001s 15 0 0 0 136 0.001s 0.003s 16 0 1 0 99 0.001s 0.002s 17 0 1 0 32 0.001s 0.002s 18 0 0 0 32 0.001s 0.002s 19 0 1 0 32 0.001s 0.002s 20 0 0 0 33 0.001s 0.002s 21 0 1 0 34 0.001s 0.002s 22 0 1 0 54 0.001s 0.002s 23 0 0 0 23 0.001s 0.002s Day Hour Count Avg time (sec) Jan 29 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 Jan 29 00 8,815,499.00 kB 8,930,081.00 kB 01 1,001,188.00 kB 7,521,647.00 kB 02 4,845,432.00 kB 6,554,973.00 kB 03 8,538,703.14 kB 8,705,628.59 kB 04 0.00 kB 0.00 kB 05 0.00 kB 0.00 kB 06 0.00 kB 0.00 kB 07 0.00 kB 0.00 kB 08 4,430,717.50 kB 8,378,807.50 kB 09 5,953,511.00 kB 8,518,387.00 kB 10 3,340,509.00 kB 7,697,325.00 kB 11 3,052,339.50 kB 8,106,017.00 kB 12 89,565.50 kB 6,582,981.50 kB 13 60,787.00 kB 5,086,399.00 kB 14 127,433.00 kB 4,118,168.00 kB 15 59,087.67 kB 3,363,794.33 kB 16 9,883.50 kB 2,580,831.50 kB 17 3,077.00 kB 2,091,143.50 kB 18 3,485.50 kB 1,694,472.50 kB 19 3,751.00 kB 1,373,248.00 kB 20 4,095.00 kB 1,113,082.00 kB 21 5,425.50 kB 902,496.00 kB 22 10,060.50 kB 733,035.00 kB 23 4,088.00 kB 594,569.00 kB -
Temporary Files
Size of temporary files
Key values
- 15.00 GiB Temp Files size Peak
- 2025-01-29 15:17:55 Date
Number of temporary files
Key values
- 18 per second Temp Files Peak
- 2025-01-29 03:17:56 Date
Temporary Files Activity
↑ Back to the top of the Temporary Files Activity tableDay Hour Count Total size Average size Jan 29 00 6 4.42 MiB 754.67 KiB 01 0 0 0 02 0 0 0 03 840 147.74 GiB 180.10 MiB 04 88 5.84 GiB 67.98 MiB 05 0 0 0 06 0 0 0 07 0 0 0 08 79 4.03 GiB 52.24 MiB 09 0 0 0 10 0 0 0 11 9 8.13 GiB 924.59 MiB 12 0 0 0 13 0 0 0 14 0 0 0 15 42 41.44 GiB 1010.40 MiB 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 Queries generating the most temporary files (N)
Rank Count Total size Min size Max size Avg size Query 1 925 154.91 GiB 128.00 KiB 1.00 GiB 171.49 MiB vacuum full analyze;-
VACUUM FULL ANALYZE;
Date: 2025-01-29 08:17:23 Duration: 5h3m22s
-
VACUUM FULL ANALYZE;
Date: 2025-01-29 03:31:59 Duration: 0ms
2 62 1.99 GiB 7.49 MiB 1.00 GiB 32.79 MiB cluster pub1.term;-
CLUSTER pub1.TERM;
Date: 2025-01-29 03:13:23 Duration: 59s814ms
-
CLUSTER pub1.TERM;
Date: 2025-01-29 03:12:33 Duration: 0ms
3 20 731.00 MiB 20.95 MiB 60.54 MiB 36.55 MiB cluster pub1.term_label;-
CLUSTER pub1.TERM_LABEL;
Date: 2025-01-29 03:13:59 Duration: 35s860ms
-
CLUSTER pub1.TERM_LABEL;
Date: 2025-01-29 03:13:29 Duration: 0ms
4 9 8.13 GiB 129.27 MiB 1.00 GiB 924.59 MiB select pub1.maint_cached_value_refresh_data_metrics ();-
select pub1.maint_cached_value_refresh_data_metrics ();
Date: 2025-01-29 11:08:17 Duration: 35m6s
-
select pub1.maint_cached_value_refresh_data_metrics ();
Date: 2025-01-29 11:03:53 Duration: 0ms
5 6 4.42 MiB 136.00 KiB 1.48 MiB 754.67 KiB vacuum full analyze log_query;-
VACUUM FULL ANALYZE log_query;
Date: 2025-01-29 00:23:33 Duration: 0ms
Queries generating the largest temporary files
Rank Size Query 1 1.00 GiB CLUSTER pub1.TERM;[ Date: 2025-01-29 03:12:33 ]
2 1.00 GiB VACUUM FULL ANALYZE;[ Date: 2025-01-29 03:27:04 ]
3 1.00 GiB VACUUM FULL ANALYZE;[ Date: 2025-01-29 03:27:04 ]
4 1.00 GiB VACUUM FULL ANALYZE;[ Date: 2025-01-29 03:27:04 ]
5 1.00 GiB VACUUM FULL ANALYZE;[ Date: 2025-01-29 03:27:05 ]
6 1.00 GiB VACUUM FULL ANALYZE;[ Date: 2025-01-29 03:27:05 ]
7 1.00 GiB VACUUM FULL ANALYZE;[ Date: 2025-01-29 03:27:05 ]
8 1.00 GiB VACUUM FULL ANALYZE;[ Date: 2025-01-29 03:27:05 ]
9 1.00 GiB VACUUM FULL ANALYZE;[ Date: 2025-01-29 03:27:05 ]
10 1.00 GiB VACUUM FULL ANALYZE;[ Date: 2025-01-29 03:27:05 ]
11 1.00 GiB VACUUM FULL ANALYZE;[ Date: 2025-01-29 03:27:05 ]
12 1.00 GiB VACUUM FULL ANALYZE;[ Date: 2025-01-29 03:27:58 ]
13 1.00 GiB VACUUM FULL ANALYZE;[ Date: 2025-01-29 03:27:58 ]
14 1.00 GiB VACUUM FULL ANALYZE;[ Date: 2025-01-29 03:27:58 ]
15 1.00 GiB VACUUM FULL ANALYZE;[ Date: 2025-01-29 03:27:58 ]
16 1.00 GiB VACUUM FULL ANALYZE;[ Date: 2025-01-29 03:27:58 ]
17 1.00 GiB VACUUM FULL ANALYZE;[ Date: 2025-01-29 03:28:59 ]
18 1.00 GiB VACUUM FULL ANALYZE;[ Date: 2025-01-29 03:28:59 ]
19 1.00 GiB VACUUM FULL ANALYZE;[ Date: 2025-01-29 03:28:59 ]
20 1.00 GiB VACUUM FULL ANALYZE;[ Date: 2025-01-29 03:28:59 ]
-
Vacuums
Vacuums / Analyzes Distribution
Key values
- 298.09 sec Highest CPU-cost vacuum
Table pub1.gene_disease
Database ctdprd51 - 2025-01-29 00:29:26 Date
- 0 sec Highest CPU-cost analyze
Table
Database ctdprd51 - Date
Average Autovacuum Duration
Key values
- 298.09 sec Highest CPU-cost vacuum
Table pub1.gene_disease
Database ctdprd51 - 2025-01-29 00:29:26 Date
Analyzes per table
Key values
- pubc.log_query (25) Main table analyzed (database ctdprd51)
- 49 analyzes Total
Table Number of analyzes ctdprd51.pubc.log_query 25 ctdprd51.pub1.phenotype_term 2 ctdprd51.pub1.term_set_enrichment_agent 2 ctdprd51.pub1.term_comp_agent 2 ctdprd51.pub1.term_set_enrichment 2 ctdprd51.pg_catalog.pg_type 1 ctdprd51.pub1.gene_gene 1 ctdprd51.load.data_load 1 ctdprd51.pub1.gene_gene_ref_throughput 1 ctdprd51.pg_catalog.pg_class 1 ctdprd51.pub1.gene_disease 1 ctdprd51.pub1.term_reference 1 ctdprd51.pub1.gene_gene_reference 1 ctdprd51.pub1.term 1 ctdprd51.pub1.ixn 1 ctdprd51.pub1.reference 1 ctdprd51.pg_catalog.pg_index 1 ctdprd51.pg_catalog.pg_attribute 1 ctdprd51.pub1.gene_chem_ref_gene_form 1 ctdprd51.pub1.term_comp 1 ctdprd51.pub1.slim_term_mapping 1 Total 49 Vacuums per table
Key values
- pub1.phenotype_term (109) Main table vacuumed on database ctdprd51
- 453 vacuums Total
Index Buffer usage Skipped WAL usage Table Vacuums scans hits misses dirtied pins frozen records full page bytes ctdprd51.pub1.phenotype_term 109 2 14,494,866 0 199,859 0 0 776,443 130,360 325,301,100 ctdprd51.pubc.log_query 106 2 336,709 0 5,758 0 4,723 8,308 1,917 4,365,862 ctdprd51.pg_toast.pg_toast_7257590 105 1 7,579 0 29 0 0 51 16 38,347 ctdprd51.pub1.ixn 104 1 114,360,593 0 574,035 1 0 1,226,960 354,725 687,186,983 ctdprd51.pub1.gene_disease 11 1 12,682,543 0 1,150,067 0 0 1,617,249 597,754 2,148,313,342 ctdprd51.pg_catalog.pg_statistic 2 2 1,236 0 319 0 244 628 202 849,526 ctdprd51.pub1.gene_chem_ref_gene_form 1 0 33,000 0 3 0 0 16,451 2 984,652 ctdprd51.pg_toast.pg_toast_2619 1 1 5,068 0 2,266 0 9,568 4,970 1,236 669,522 ctdprd51.pub1.term_set_enrichment 1 0 575 0 3 0 0 247 1 22,992 ctdprd51.pg_catalog.pg_attribute 1 1 525 0 122 0 37 261 100 514,784 ctdprd51.pub1.slim_term_mapping 1 0 605 0 3 0 0 1 1 4,025 ctdprd51.pub1.reference 1 1 328,365 0 114,382 0 0 199,763 48,466 188,054,292 ctdprd51.pg_catalog.pg_class 1 1 292 0 28 0 0 166 29 168,875 ctdprd51.pub1.term_comp_agent 1 0 161 0 4 0 0 37 2 16,422 ctdprd51.pub1.gene_gene_ref_throughput 1 0 14,679 0 3 0 0 1 1 5,633 ctdprd51.pub1.term_reference 1 0 37,583 0 5 0 0 18,737 2 1,117,330 ctdprd51.pub1.term 1 1 988,469 0 136,085 0 34 499,485 275,659 1,273,315,250 ctdprd51.pub1.gene_gene_reference 1 0 30,631 0 2 0 0 1 0 281 ctdprd51.pub1.gene_gene 1 0 12,090 0 4 0 0 1 1 6,741 ctdprd51.pg_toast.pg_toast_486223 1 0 35 0 3 0 0 1 0 188 ctdprd51.pg_catalog.pg_type 1 1 126 0 37 0 0 59 26 91,622 ctdprd51.pub1.term_set_enrichment_agent 1 0 11,310 0 3 0 0 5,638 1 341,061 Total 453 15 143,347,040 184,332 2,183,020 1 14,606 4,375,458 1,410,501 4,631,368,830 Tuples removed per table
Key values
- pub1.gene_disease (33461093) Main table with removed tuples on database ctdprd51
- 55914262 tuples Total removed
Index Tuples Pages Table Vacuums scans removed remain not yet removable removed remain ctdprd51.pub1.gene_disease 11 1 33,461,093 702,682,953 334,610,930 0 5,412,825 ctdprd51.pub1.phenotype_term 109 2 20,298,833 729,957,528 361,599,331 0 7,011,745 ctdprd51.pub1.ixn 104 1 2,111,259 484,514,620 243,810,888 0 56,967,560 ctdprd51.pubc.log_query 106 2 35,788 4,180,481 3,501,936 0 166,187 ctdprd51.pg_toast.pg_toast_2619 1 1 5,712 18,927 44 0 12,592 ctdprd51.pg_catalog.pg_attribute 1 1 774 8,321 0 0 225 ctdprd51.pg_catalog.pg_statistic 2 2 498 6,365 308 0 836 ctdprd51.pg_catalog.pg_type 1 1 133 1,159 0 0 35 ctdprd51.pg_catalog.pg_class 1 1 100 1,808 0 0 61 ctdprd51.pg_toast.pg_toast_7257590 105 1 72 14,838 7,488 0 2,205 ctdprd51.pub1.gene_chem_ref_gene_form 1 0 0 3,036,327 0 0 16,450 ctdprd51.pub1.term_set_enrichment 1 0 0 14,802 0 0 246 ctdprd51.pub1.slim_term_mapping 1 0 0 32,827 0 0 261 ctdprd51.pub1.reference 1 1 0 198,876 0 0 86,558 ctdprd51.pub1.term_comp_agent 1 0 0 3,707 0 0 36 ctdprd51.pub1.gene_gene_ref_throughput 1 0 0 1,407,316 0 0 7,316 ctdprd51.pub1.term_reference 1 0 0 3,466,082 0 0 18,736 ctdprd51.pub1.term 1 1 0 2,099,030 0 0 230,350 ctdprd51.pub1.gene_gene_reference 1 0 0 1,399,897 0 0 15,252 ctdprd51.pub1.gene_gene 1 0 0 1,113,543 0 0 6,020 ctdprd51.pg_toast.pg_toast_486223 1 0 0 18 0 0 4 ctdprd51.pub1.term_set_enrichment_agent 1 0 0 495,983 0 0 5,637 Total 453 15 55,914,262 1,934,655,408 943,530,925 0 69,961,137 Pages removed per table
Key values
- unknown (0) Main table with removed pages on database unknown
- 0 pages Total removed
Pages removed per tables
NO DATASET
Table Number of vacuums Index scans Tuples removed Pages removed ctdprd51.pub1.gene_chem_ref_gene_form 1 0 0 0 ctdprd51.pg_toast.pg_toast_2619 1 1 5712 0 ctdprd51.pg_toast.pg_toast_7257590 105 1 72 0 ctdprd51.pub1.term_set_enrichment 1 0 0 0 ctdprd51.pg_catalog.pg_attribute 1 1 774 0 ctdprd51.pub1.slim_term_mapping 1 0 0 0 ctdprd51.pub1.reference 1 1 0 0 ctdprd51.pub1.ixn 104 1 2111259 0 ctdprd51.pg_catalog.pg_class 1 1 100 0 ctdprd51.pub1.term_comp_agent 1 0 0 0 ctdprd51.pub1.gene_gene_ref_throughput 1 0 0 0 ctdprd51.pub1.term_reference 1 0 0 0 ctdprd51.pub1.term 1 1 0 0 ctdprd51.pub1.gene_gene_reference 1 0 0 0 ctdprd51.pub1.gene_disease 11 1 33461093 0 ctdprd51.pub1.phenotype_term 109 2 20298833 0 ctdprd51.pub1.gene_gene 1 0 0 0 ctdprd51.pg_toast.pg_toast_486223 1 0 0 0 ctdprd51.pg_catalog.pg_type 1 1 133 0 ctdprd51.pg_catalog.pg_statistic 2 2 498 0 ctdprd51.pub1.term_set_enrichment_agent 1 0 0 0 ctdprd51.pubc.log_query 106 2 35788 0 Total 453 15 55,914,262 0 Autovacuum Activity
↑ Back to the top of the Autovacuum Activity tableDay Hour VACUUMs ANALYZEs Jan 29 00 0 9 01 0 4 02 0 5 03 0 2 04 0 0 05 0 0 06 0 0 07 0 0 08 0 6 09 0 1 10 0 4 11 0 3 12 0 5 13 0 3 14 0 1 15 0 1 16 0 1 17 0 1 18 0 0 19 0 1 20 0 1 21 0 0 22 0 1 23 0 0 - 298.09 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
- 3,788 Total read queries
- 119 Total write queries
Queries by database
Key values
- unknown Main database
- 3,113 Requests
- 7d5h37m49s (ctdprd51)
- Main time consuming database
Queries by user
Key values
- unknown Main user
- 5,986 Requests
User Request type Count Duration edit Total 1 9s129ms insert 1 9s129ms editeu Total 5 23s1ms select 5 23s1ms load Total 38 24m43s others 1 2s679ms select 37 24m40s postgres Total 16 4h32m52s copy to 14 7m14s select 2 4h25m37s pub1 Total 8 13m19s ddl 1 4s387ms insert 3 12m57s select 4 17s466ms pub2 Total 1 4s718ms select 1 4s718ms pubeu Total 1,427 7d1h6m42s cte 5 16s114ms insert 41 6d23h46m47s select 1,381 1h19m39s qaeu Total 27 44m54s cte 2 7s102ms select 25 44m47s unknown Total 5,986 2d4h22m9s copy to 65 8m55s cte 15 43s855ms ddl 52 40m20s insert 41 1d6h8m56s others 23 5h9m5s select 5,780 15h40m51s tcl 1 1s446ms update 9 33m14s Duration by user
Key values
- 7d1h6m42s (pubeu) Main time consuming user
User Request type Count Duration edit Total 1 9s129ms insert 1 9s129ms editeu Total 5 23s1ms select 5 23s1ms load Total 38 24m43s others 1 2s679ms select 37 24m40s postgres Total 16 4h32m52s copy to 14 7m14s select 2 4h25m37s pub1 Total 8 13m19s ddl 1 4s387ms insert 3 12m57s select 4 17s466ms pub2 Total 1 4s718ms select 1 4s718ms pubeu Total 1,427 7d1h6m42s cte 5 16s114ms insert 41 6d23h46m47s select 1,381 1h19m39s qaeu Total 27 44m54s cte 2 7s102ms select 25 44m47s unknown Total 5,986 2d4h22m9s copy to 65 8m55s cte 15 43s855ms ddl 52 40m20s insert 41 1d6h8m56s others 23 5h9m5s select 5,780 15h40m51s tcl 1 1s446ms update 9 33m14s Queries by host
Key values
- unknown Main host
- 7,509 Requests
- 9d11h25m18s (unknown)
- Main time consuming host
Queries by application
Key values
- unknown Main application
- 3,909 Requests
- 8d22h8m47s (unknown)
- Main time consuming application
Number of cancelled queries
Key values
- 0 per second Cancelled query Peak
- 2025-01-29 21:05:08 Date
Number of cancelled queries (5 minutes period)
NO DATASET
-
Top Queries
Histogram of query times
Key values
- 3,863 1000-10000ms duration
Slowest individual queries
Rank Duration Query 1 5h3m22s VACUUM FULL ANALYZE;[ Date: 2025-01-29 08:17:23 - Bind query: yes ]
2 4h12m25s INSERT INTO pubc.log_query (type_cd, query_tm, submission_qty, session_id, server_nm, remote_addr, results_qty, basic_query_txt, batch_input_type_txt, results_format_txt, dag_txt, action_type_txt, action_degree_type_txt, http_user_agent, node_nm;[ Date: 2025-01-29 08:15:20 - Bind query: yes ]
3 4h12m23s INSERT INTO pubc.log_query (query_tm, submission_qty, session_id, server_nm, remote_addr, http_user_agent, results_qty, execution_ms, type_cd, basic_query_txt, basic_query_type, node_nm) VALUES (CURRENT_TIMESTAMP, 1, '203B0CDC709AAB3B13A82B6A3752ACA4', 'CTDBASE.ORG', SUBSTR('46.38.241.197', 1, 128), NULLIF (SUBSTR('Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML,1,256),'') ,' 2' ,NULLIF('9',-1) ,'gene' ,SUBSTR('putative elongation of very long chain fatty acids protein 6',1,1000) ,'contains ', NULLIF (NULL, ''));[ Date: 2025-01-29 08:15:20 - Bind query: yes ]
4 4h12m23s INSERT INTO pubc.log_query (type_cd, query_tm, submission_qty, session_id, server_nm, remote_addr, results_qty, basic_query_txt, batch_input_type_txt, results_format_txt, dag_txt, action_type_txt, action_degree_type_txt, http_user_agent, node_nm, execution_ms) VALUES (, CURRENT_TIMESTAMP, 1,,, SUBSTR(, 1, 128),, SUBSTR(, 1, 4000);[ Date: 2025-01-29 08:15:20 - Bind query: yes ]
5 4h12m23s INSERT INTO pubc.log_query (query_tm, submission_qty, session_id, server_nm, remote_addr, http_user_agent, results_qty, execution_ms, type_cd, basic_query_txt, basic_query_type, node_nm) VALUES (CURRENT_TIMESTAMP, 1,,, SUBSTR(, 1, 128);[ Date: 2025-01-29 08:15:20 - Database: ctdprd51 - User: pubeu - Bind query: yes ]
6 4h12m22s INSERT INTO pubc.log_query (type_cd, query_tm, submission_qty, session_id, server_nm, remote_addr;[ Date: 2025-01-29 08:15:20 - Database: ctdprd51 - User: pubeu - Bind query: yes ]
7 4h12m19s INSERT INTO pubc.log_query (type_cd, query_tm, submission_qty, session_id, server_nm, remote_addr, results_qty, basic_query_txt, batch_input_type_txt, results_format_txt, dag_txt, action_type_txt, action_degree_type_txt, http_user_agent, node_nm, execution_ms) VALUES ('venn_chems_curated', CURRENT_TIMESTAMP, 1, 'EC7FC337509500E3D06AD10B61F7616C', 'CTDBASE.ORG', SUBSTR('220.233.199.200', 1, 128), '2', SUBSTR('name:y42h9b.3|name:y57a10a.5', 1, 4000), 'gene', NULLIF ('html', ''), NULLIF (SUBSTR(NULL, 1, 128), ''), NULLIF (SUBSTR(NULL, 1, 4000), ''), NULLIF (SUBSTR('',1,4000),''), NULLIF (SUBSTR('Mozilla / 5.0 (Linux; Android 6.0; Nexus 5 Build / MRA58N) AppleWebKit / 537.36 (KHTML, 1, 256), ''), NULLIF (NULL, ''), NULLIF ('1', -1));[ Date: 2025-01-29 08:15:20 - Database: ctdprd51 - User: pubeu - Bind query: yes ]
8 4h12m18s INSERT INTO pubc.log_query (type_cd, query_tm, submission_qty, session_id, server_nm, remote_addr, results_qty, basic_query_txt, batch_input_type_txt, results_format_txt, dag_txt, action_type_txt, action_degree_type_txt, http_user_agent, node_nm, execution_ms) VALUES ('venn_chems_curated', CURRENT_TIMESTAMP, 1, '04A7D382439C9CD34BFD40DC429D0A0D', 'CTDBASE.ORG', SUBSTR('98.240.235.167', 1, 128), '2', SUBSTR('name:c48b4.9|name:zim-2', 1, 4000), 'gene', NULLIF ('html', ''), NULLIF (SUBSTR(NULL, 1, 128), ''), NULLIF (SUBSTR(NULL, 1, 4000), ''), NULLIF (SUBSTR('',1,4000),''), NULLIF (SUBSTR('Mozilla / 5.0 (Linux; Android 6.0; Nexus 5 Build / MRA58N) AppleWebKit / 537.36 (KHTML, 1, 256), ''), NULLIF (NULL, ''), NULLIF ('1', -1));[ Date: 2025-01-29 08:15:20 - Bind query: yes ]
9 4h12m16s INSERT INTO pubc.log_query (type_cd, query_tm, submission_qty, session_id, server_nm, remote_addr, results_qty, basic_query_txt, batch_input_type_txt, results_format_txt, dag_txt, action_type_txt, action_degree_type_txt, http_user_agent, node_nm, execution_ms) VALUES ('venn_chems_curated', CURRENT_TIMESTAMP, 1, '4FA5964A4AFBAF34C1D8AEB1B22DB17A', 'CTDBASE.ORG', SUBSTR('64.180.104.190', 1, 128), '2', SUBSTR('name:mir3594|name:mir6327', 1, 4000), 'gene', NULLIF ('html', ''), NULLIF (SUBSTR(NULL, 1, 128), ''), NULLIF (SUBSTR(NULL, 1, 4000), ''), NULLIF (SUBSTR('',1,4000),''), NULLIF (SUBSTR('Mozilla / 5.0 (iPhone; CPU iPhone OS 11_0 like Mac OS X) AppleWebKit / 537.36 (KHTML, 1, 256), ''), NULLIF (NULL, ''), NULLIF ('1', -1));[ Date: 2025-01-29 08:15:20 - Bind query: yes ]
10 4h12m16s INSERT INTO pubc.log_query (query_tm, submission_qty, session_id, server_nm, remote_addr, http_user_agent, results_qty, execution_ms, type_cd, basic_query_txt, basic_query_type, node_nm) VALUES (CURRENT_TIMESTAMP, 1, 'C0A7049223935B0DC40D69046F3240B3', 'CTDBASE.ORG', SUBSTR('45.129.181.193', 1, 128), NULLIF (SUBSTR('Mozilla / 5.0 (Windows NT 10.0; Win64; x64) AppleWebKit / 537.36 (KHTML, 1, 256), ''), '1', NULLIF ('4', -1), 'gene', SUBSTR('job18_014864', 1, 1000), 'contains', NULLIF (NULL, ''));[ Date: 2025-01-29 08:15:20 - Database: ctdprd51 - User: pubeu - Bind query: yes ]
11 4h12m15s INSERT INTO pubc.log_query (type_cd, query_tm, submission_qty, session_id, server_nm, remote_addr, results_qty, basic_query_txt, batch_input_type_txt, results_format_txt, dag_txt, action_type_txt, action_degree_type_txt, http_user_agent, node_nm, execution_ms) VALUES ('venn_chems_curated', CURRENT_TIMESTAMP, 1, '0CBBD9D243C3CDB7951D57FC71E1A4CB', 'CTDBASE.ORG', SUBSTR('216.36.174.134', 1, 128), '2', SUBSTR('name:prtfdc1a|name:lncrna:iab4', 1, 4000), 'gene', NULLIF ('html', ''), NULLIF (SUBSTR(NULL, 1, 128), ''), NULLIF (SUBSTR(NULL, 1, 4000), ''), NULLIF (SUBSTR('',1,4000),''), NULLIF (SUBSTR('Mozilla / 5.0 (Linux; Android 6.0; Nexus 5 Build / MRA58N) AppleWebKit / 537.36 (KHTML, 1, 256), ''), NULLIF (NULL, ''), NULLIF ('2', -1));[ Date: 2025-01-29 08:15:20 - Database: ctdprd51 - User: pubeu - Bind query: yes ]
12 4h12m10s INSERT INTO pubc.log_query (type_cd, query_tm, submission_qty, session_id, server_nm, remote_addr, results_qty, basic_query_txt, batch_input_type_txt, results_format_txt, dag_txt, action_type_txt, action_degree_type_txt, http_user_agent, node_nm, execution_ms) VALUES (, CURRENT_TIMESTAMP, 1,,, SUBSTR(, 1, 128),, SUBSTR(, 1, 4000),, NULLIF (, ''), NULLIF (SUBSTR(, 1, 128), '');[ Date: 2025-01-29 08:15:20 - Database: ctdprd51 - User: pubeu - Bind query: yes ]
13 4h12m9s INSERT INTO pubc.log_query (query_tm, submission_qty;[ Date: 2025-01-29 08:15:20 - Database: ctdprd51 - User: pubeu - Bind query: yes ]
14 4h12m8s INSERT INTO pubc.log_query (type_cd, query_tm, submission_qty, session_id, server_nm, remote_addr, results_qty, basic_query_txt, batch_input_type_txt, results_format_txt, dag_txt, action_type_txt, action_degree_type_txt, http_user_agent, node_nm, execution_ms) VALUES ('venn_chems_curated', CURRENT_TIMESTAMP, 1, '2A51CF2857249F49B0BCBE3FA28A66FC', 'CTDBASE.ORG', SUBSTR('73.134.160.109', 1, 128), '2', SUBSTR('name:c3h17orf75|name:ighv4-5', 1, 4000), 'gene', NULLIF ('html', ''), NULLIF (SUBSTR(NULL, 1, 128), ''), NULLIF (SUBSTR(NULL, 1, 4000), ''), NULLIF (SUBSTR('',1,4000),''), NULLIF (SUBSTR('Mozilla / 5.0 (iPhone; CPU iPhone OS 11_0 like Mac OS X) AppleWebKit / 537.36 (KHTML, 1, 256), ''), NULLIF (NULL, ''), NULLIF ('2', -1));[ Date: 2025-01-29 08:15:20 - Database: ctdprd51 - User: pubeu - Bind query: yes ]
15 4h12m7s INSERT INTO pubc.log_query (type_cd, query_tm, submission_qty, session_id, server_nm, remote_addr, results_qty, basic_query_txt, batch_input_type_txt, results_format_txt, dag_txt, action_type_txt, action_degree_type_txt, http_user_agent, node_nm, execution_ms) VALUES ('venn_chems_curated', CURRENT_TIMESTAMP, 1, 'E57B3F42CD79DCF24ADB3755596A370D', 'CTDBASE.ORG', SUBSTR('49.191.98.234', 1, 128), '2', SUBSTR('name:inx-9|name:t11f8.4', 1, 4000), 'gene', NULLIF ('html', ''), NULLIF (SUBSTR(NULL, 1, 128), ''), NULLIF (SUBSTR(NULL, 1, 4000), ''), NULLIF (SUBSTR('',1,4000),''), NULLIF (SUBSTR('Mozilla / 5.0 (Linux; Android 5.0; SM - G900P Build / LRX21T) AppleWebKit / 537.36 (KHTML, 1, 256), ''), NULLIF (NULL, ''), NULLIF ('1', -1));[ Date: 2025-01-29 08:15:20 - Database: ctdprd51 - User: pubeu - Bind query: yes ]
16 4h12m5s INSERT INTO pubc.log_query (type_cd, query_tm, submission_qty, session_id, server_nm, remote_addr, results_qty, basic_query_txt, batch_input_type_txt, results_format_txt, dag_txt, action_type_txt, action_degree_type_txt, http_user_agent, node_nm, execution_ms) VALUES (, CURRENT_TIMESTAMP, 1,,, SUBSTR(, 1, 128),, SUBSTR(, 1, 4000),, NULLIF (, ''), NULLIF (SUBSTR(, 1, 128), ''), NULLIF (SUBSTR(, 1, 4000), ''), NULLIF (SUBSTR(, 1, 4000), '');[ Date: 2025-01-29 08:15:20 - Database: ctdprd51 - User: pubeu - Bind query: yes ]
17 4h12m3s INSERT INTO pubc.log_query (type_cd, query_tm, submission_qty, session_id, server_nm, remote_addr, results_qty, basic_query_txt, batch_input_type_txt, results_format_txt, dag_txt, action_type_txt, action_degree_type_txt, http_user_agent, node_nm, execution_ms) VALUES;[ Date: 2025-01-29 08:15:20 - Database: ctdprd51 - User: pubeu - Bind query: yes ]
18 4h12m INSERT INTO pubc.log_query;[ Date: 2025-01-29 08:15:20 - Database: ctdprd51 - User: pubeu - Bind query: yes ]
19 4h11m58s INSERT INTO pubc.log_query (type_cd, query_tm, submission_qty, session_id, server_nm, remote_addr, results_qty, basic_query_txt, batch_input_type_txt, results_format_txt, dag_txt, action_type_txt, action_degree_type_txt, http_user_agent, node_nm, execution_ms) VALUES ('venn_chems_curated', CURRENT_TIMESTAMP, 1, 'E43C7C840A183AE27B425FFEC7E58607', 'CTDBASE.ORG', SUBSTR('77.103.48.234', 1, 128), '2', SUBSTR('name:y17g7b.18|name:mrps-11', 1, 4000), 'gene', NULLIF ('html', ''), NULLIF (SUBSTR(NULL, 1, 128), ''), NULLIF (SUBSTR(NULL, 1, 4000), ''), NULLIF (SUBSTR('',1,4000),''), NULLIF (SUBSTR('Mozilla / 5.0 (Linux; Android 5.0; SM - G900P Build / LRX21T) AppleWebKit / 537.36 (KHTML, 1, 256), ''), NULLIF (NULL, ''), NULLIF ('2', -1));[ Date: 2025-01-29 08:15:20 - Bind query: yes ]
20 4h11m56s INSERT INTO pubc.log_query (type_cd, query_tm, submission_qty, session_id, server_nm, remote_addr, results_qty, basic_query_txt, batch_input_type_txt, results_format_txt, dag_txt, action_type_txt, action_degree_type_txt, http_user_agent, node_nm, execution_ms) VALUES (, CURRENT_TIMESTAMP, 1,;[ Date: 2025-01-29 08:15:20 - Database: ctdprd51 - User: pubeu - Bind query: yes ]
Time consuming queries (N)
Rank Total duration Times executed Min duration Max duration Avg duration Query 1 2d23h20m44s 20 7s544ms 4h12m19s 3h34m2s insert into pubc.log_query (type_cd, query_tm, submission_qty, session_id, server_nm, remote_addr, results_qty, basic_query_txt, batch_input_type_txt, results_format_txt, dag_txt, action_type_txt, action_degree_type_txt, http_user_agent, node_nm, execution_ms) values (?, current_timestamp, ?, ?, ?, substr(?, ?, ?), ?, substr(?, ?, ?), ?, nullif (?, ?), nullif (substr(null, ?, ?), ?), nullif (substr(null, ?, ?), ?), nullif (substr(?, ?, ?), ?), nullif (substr(? ? ', ?));Times Reported Time consuming queries #1
Day Hour Count Duration Avg duration Jan 29 04 3 30s110ms 10s36ms 08 17 2d23h20m14s 4h11m46s [ User: pubeu - Total duration: 2d10h43m40s - Times executed: 14 ]
-
INSERT INTO pubc.log_query (type_cd, query_tm, submission_qty, session_id, server_nm, remote_addr, results_qty, basic_query_txt, batch_input_type_txt, results_format_txt, dag_txt, action_type_txt, action_degree_type_txt, http_user_agent, node_nm, execution_ms) VALUES ('venn_chems_curated', CURRENT_TIMESTAMP, 1, 'EC7FC337509500E3D06AD10B61F7616C', 'CTDBASE.ORG', SUBSTR('220.233.199.200', 1, 128), '2', SUBSTR('name:y42h9b.3|name:y57a10a.5', 1, 4000), 'gene', NULLIF ('html', ''), NULLIF (SUBSTR(NULL, 1, 128), ''), NULLIF (SUBSTR(NULL, 1, 4000), ''), NULLIF (SUBSTR('',1,4000),''), NULLIF (SUBSTR('Mozilla / 5.0 (Linux; Android 6.0; Nexus 5 Build / MRA58N) AppleWebKit / 537.36 (KHTML, 1, 256), ''), NULLIF (NULL, ''), NULLIF ('1', -1));
Date: 2025-01-29 08:15:20 Duration: 4h12m19s Database: ctdprd51 User: pubeu Bind query: yes
-
INSERT INTO pubc.log_query (type_cd, query_tm, submission_qty, session_id, server_nm, remote_addr, results_qty, basic_query_txt, batch_input_type_txt, results_format_txt, dag_txt, action_type_txt, action_degree_type_txt, http_user_agent, node_nm, execution_ms) VALUES ('venn_chems_curated', CURRENT_TIMESTAMP, 1, '04A7D382439C9CD34BFD40DC429D0A0D', 'CTDBASE.ORG', SUBSTR('98.240.235.167', 1, 128), '2', SUBSTR('name:c48b4.9|name:zim-2', 1, 4000), 'gene', NULLIF ('html', ''), NULLIF (SUBSTR(NULL, 1, 128), ''), NULLIF (SUBSTR(NULL, 1, 4000), ''), NULLIF (SUBSTR('',1,4000),''), NULLIF (SUBSTR('Mozilla / 5.0 (Linux; Android 6.0; Nexus 5 Build / MRA58N) AppleWebKit / 537.36 (KHTML, 1, 256), ''), NULLIF (NULL, ''), NULLIF ('1', -1));
Date: 2025-01-29 08:15:20 Duration: 4h12m18s Bind query: yes
-
INSERT INTO pubc.log_query (type_cd, query_tm, submission_qty, session_id, server_nm, remote_addr, results_qty, basic_query_txt, batch_input_type_txt, results_format_txt, dag_txt, action_type_txt, action_degree_type_txt, http_user_agent, node_nm, execution_ms) VALUES ('venn_chems_curated', CURRENT_TIMESTAMP, 1, '4FA5964A4AFBAF34C1D8AEB1B22DB17A', 'CTDBASE.ORG', SUBSTR('64.180.104.190', 1, 128), '2', SUBSTR('name:mir3594|name:mir6327', 1, 4000), 'gene', NULLIF ('html', ''), NULLIF (SUBSTR(NULL, 1, 128), ''), NULLIF (SUBSTR(NULL, 1, 4000), ''), NULLIF (SUBSTR('',1,4000),''), NULLIF (SUBSTR('Mozilla / 5.0 (iPhone; CPU iPhone OS 11_0 like Mac OS X) AppleWebKit / 537.36 (KHTML, 1, 256), ''), NULLIF (NULL, ''), NULLIF ('1', -1));
Date: 2025-01-29 08:15:20 Duration: 4h12m16s Bind query: yes
2 12h34m50s 4 2s76ms 4h12m 3h8m42s insert into pubc.log_query;Times Reported Time consuming queries #2
Day Hour Count Duration Avg duration Jan 29 04 1 2s76ms 2s76ms 08 3 12h34m48s 4h11m36s [ User: pubeu - Total duration: 12h34m50s - Times executed: 4 ]
-
INSERT INTO pubc.log_query;
Date: 2025-01-29 08:15:20 Duration: 4h12m Database: ctdprd51 User: pubeu Bind query: yes
-
INSERT INTO pubc.log_query;
Date: 2025-01-29 08:15:20 Duration: 4h11m34s Database: ctdprd51 User: pubeu Bind query: yes
-
INSERT INTO pubc.log_query;
Date: 2025-01-29 08:15:20 Duration: 4h11m13s Database: ctdprd51 User: pubeu Bind query: yes
3 8h23m36s 2 4h11m14s 4h12m22s 4h11m48s insert into pubc.log_query (type_cd, query_tm, submission_qty, session_id, server_nm, remote_addr;Times Reported Time consuming queries #3
Day Hour Count Duration Avg duration Jan 29 08 2 8h23m36s 4h11m48s [ User: pubeu - Total duration: 8h23m36s - Times executed: 2 ]
-
INSERT INTO pubc.log_query (type_cd, query_tm, submission_qty, session_id, server_nm, remote_addr;
Date: 2025-01-29 08:15:20 Duration: 4h12m22s Database: ctdprd51 User: pubeu Bind query: yes
-
INSERT INTO pubc.log_query (type_cd, query_tm, submission_qty, session_id, server_nm, remote_addr;
Date: 2025-01-29 08:15:20 Duration: 4h11m14s Database: ctdprd51 User: pubeu Bind query: yes
4 8h23m25s 2 4h11m22s 4h12m3s 4h11m42s insert into pubc.log_query (type_cd, query_tm, submission_qty, session_id, server_nm, remote_addr, results_qty, basic_query_txt, batch_input_type_txt, results_format_txt, dag_txt, action_type_txt, action_degree_type_txt, http_user_agent, node_nm, execution_ms) values;Times Reported Time consuming queries #4
Day Hour Count Duration Avg duration Jan 29 08 2 8h23m25s 4h11m42s [ User: pubeu - Total duration: 8h23m25s - Times executed: 2 ]
-
INSERT INTO pubc.log_query (type_cd, query_tm, submission_qty, session_id, server_nm, remote_addr, results_qty, basic_query_txt, batch_input_type_txt, results_format_txt, dag_txt, action_type_txt, action_degree_type_txt, http_user_agent, node_nm, execution_ms) VALUES;
Date: 2025-01-29 08:15:20 Duration: 4h12m3s Database: ctdprd51 User: pubeu Bind query: yes
-
INSERT INTO pubc.log_query (type_cd, query_tm, submission_qty, session_id, server_nm, remote_addr, results_qty, basic_query_txt, batch_input_type_txt, results_format_txt, dag_txt, action_type_txt, action_degree_type_txt, http_user_agent, node_nm, execution_ms) VALUES;
Date: 2025-01-29 08:15:20 Duration: 4h11m22s Database: ctdprd51 User: pubeu Bind query: yes
5 5h3m22s 1 0ms 5h3m22s 5h3m22s vacuum full analyze;Times Reported Time consuming queries #5
Day Hour Count Duration Avg duration Jan 29 08 1 5h3m22s 5h3m22s -
VACUUM FULL ANALYZE;
Date: 2025-01-29 08:17:23 Duration: 5h3m22s Bind query: yes
-
VACUUM FULL ANALYZE;
Date: 2025-01-29 03:31:59 Duration: 0ms
6 4h25m37s 2 2h10m18s 2h15m18s 2h12m48s select n.tableoid, n.oid, n.nspname, ( select rolname from pg_catalog.pg_roles where oid = nspowner) as rolname, ( select pg_catalog.array_agg(acl order by row_n) from ( select acl, row_n from pg_catalog.unnest(coalesce(n.nspacl, pg_catalog.acldefault(?, n.nspowner))) with ordinality as perm (acl, row_n) where not exists ( select ? from pg_catalog.unnest(coalesce(pip.initprivs, pg_catalog.acldefault(?, n.nspowner))) as init (init_acl) where acl = init_acl)) as foo) as nspacl, ( select pg_catalog.array_agg(acl order by row_n) from ( select acl, row_n from pg_catalog.unnest(coalesce(pip.initprivs, pg_catalog.acldefault(?, n.nspowner))) with ordinality as initp (acl, row_n) where not exists ( select ? from pg_catalog.unnest(coalesce(n.nspacl, pg_catalog.acldefault(?, n.nspowner))) as permp (orig_acl) where acl = orig_acl)) as foo) as rnspacl, null as initnspacl, null as initrnspacl from pg_namespace n left join pg_init_privs pip on (n.oid = pip.objoid and pip.classoid = ?::regclass and pip.objsubid = ?);Times Reported Time consuming queries #6
Day Hour Count Duration Avg duration Jan 29 08 2 4h25m37s 2h12m48s [ User: postgres - Total duration: 4h25m37s - Times executed: 2 ]
[ Application: pg_dump - Total duration: 4h25m37s - Times executed: 2 ]
-
SELECT n.tableoid, n.oid, n.nspname, ( SELECT rolname FROM pg_catalog.pg_roles WHERE oid = nspowner) AS rolname, ( SELECT pg_catalog.array_agg(acl ORDER BY row_n) FROM ( SELECT acl, row_n FROM pg_catalog.unnest(coalesce(n.nspacl, pg_catalog.acldefault('n', n.nspowner))) WITH ORDINALITY AS perm (acl, row_n) WHERE NOT EXISTS ( SELECT 1 FROM pg_catalog.unnest(coalesce(pip.initprivs, pg_catalog.acldefault('n', n.nspowner))) AS init (init_acl) WHERE acl = init_acl)) as foo) as nspacl, ( SELECT pg_catalog.array_agg(acl ORDER BY row_n) FROM ( SELECT acl, row_n FROM pg_catalog.unnest(coalesce(pip.initprivs, pg_catalog.acldefault('n', n.nspowner))) WITH ORDINALITY AS initp (acl, row_n) WHERE NOT EXISTS ( SELECT 1 FROM pg_catalog.unnest(coalesce(n.nspacl, pg_catalog.acldefault('n', n.nspowner))) AS permp (orig_acl) WHERE acl = orig_acl)) as foo) as rnspacl, NULL as initnspacl, NULL as initrnspacl FROM pg_namespace n LEFT JOIN pg_init_privs pip ON (n.oid = pip.objoid AND pip.classoid = 'pg_namespace'::regclass AND pip.objsubid = 0);
Date: 2025-01-29 08:15:20 Duration: 2h15m18s Database: ctdprd51 User: postgres Application: pg_dump
-
SELECT n.tableoid, n.oid, n.nspname, ( SELECT rolname FROM pg_catalog.pg_roles WHERE oid = nspowner) AS rolname, ( SELECT pg_catalog.array_agg(acl ORDER BY row_n) FROM ( SELECT acl, row_n FROM pg_catalog.unnest(coalesce(n.nspacl, pg_catalog.acldefault('n', n.nspowner))) WITH ORDINALITY AS perm (acl, row_n) WHERE NOT EXISTS ( SELECT 1 FROM pg_catalog.unnest(coalesce(pip.initprivs, pg_catalog.acldefault('n', n.nspowner))) AS init (init_acl) WHERE acl = init_acl)) as foo) as nspacl, ( SELECT pg_catalog.array_agg(acl ORDER BY row_n) FROM ( SELECT acl, row_n FROM pg_catalog.unnest(coalesce(pip.initprivs, pg_catalog.acldefault('n', n.nspowner))) WITH ORDINALITY AS initp (acl, row_n) WHERE NOT EXISTS ( SELECT 1 FROM pg_catalog.unnest(coalesce(n.nspacl, pg_catalog.acldefault('n', n.nspowner))) AS permp (orig_acl) WHERE acl = orig_acl)) as foo) as rnspacl, NULL as initnspacl, NULL as initrnspacl FROM pg_namespace n LEFT JOIN pg_init_privs pip ON (n.oid = pip.objoid AND pip.classoid = 'pg_namespace'::regclass AND pip.objsubid = 0);
Date: 2025-01-29 08:15:20 Duration: 2h10m18s Database: ctdprd51 User: postgres Application: pg_dump
7 4h12m25s 1 4h12m25s 4h12m25s 4h12m25s insert into pubc.log_query (type_cd, query_tm, submission_qty, session_id, server_nm, remote_addr, results_qty, basic_query_txt, batch_input_type_txt, results_format_txt, dag_txt, action_type_txt, action_degree_type_txt, http_user_agent, node_nm;Times Reported Time consuming queries #7
Day Hour Count Duration Avg duration Jan 29 08 1 4h12m25s 4h12m25s -
INSERT INTO pubc.log_query (type_cd, query_tm, submission_qty, session_id, server_nm, remote_addr, results_qty, basic_query_txt, batch_input_type_txt, results_format_txt, dag_txt, action_type_txt, action_degree_type_txt, http_user_agent, node_nm;
Date: 2025-01-29 08:15:20 Duration: 4h12m25s Bind query: yes
8 4h12m23s 1 4h12m23s 4h12m23s 4h12m23s insert into pubc.log_query (query_tm, submission_qty, session_id, server_nm, remote_addr, http_user_agent, results_qty, execution_ms, type_cd, basic_query_txt, basic_query_type, node_nm) values (current_timestamp, ?, ?, ?, substr(?, ?, ?), nullif (substr(? ? ? ? ? gene ? putative elongation of very long chain fatty acids protein ? ? contains ? '));Times Reported Time consuming queries #8
Day Hour Count Duration Avg duration Jan 29 08 1 4h12m23s 4h12m23s -
INSERT INTO pubc.log_query (query_tm, submission_qty, session_id, server_nm, remote_addr, http_user_agent, results_qty, execution_ms, type_cd, basic_query_txt, basic_query_type, node_nm) VALUES (CURRENT_TIMESTAMP, 1, '203B0CDC709AAB3B13A82B6A3752ACA4', 'CTDBASE.ORG', SUBSTR('46.38.241.197', 1, 128), NULLIF (SUBSTR('Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML,1,256),'') ,' 2' ,NULLIF('9',-1) ,'gene' ,SUBSTR('putative elongation of very long chain fatty acids protein 6',1,1000) ,'contains ', NULLIF (NULL, ''));
Date: 2025-01-29 08:15:20 Duration: 4h12m23s Bind query: yes
9 4h12m23s 1 4h12m23s 4h12m23s 4h12m23s insert into pubc.log_query (type_cd, query_tm, submission_qty, session_id, server_nm, remote_addr, results_qty, basic_query_txt, batch_input_type_txt, results_format_txt, dag_txt, action_type_txt, action_degree_type_txt, http_user_agent, node_nm, execution_ms) values (, current_timestamp, ?,,, substr(, ?, ?),, substr(, ?, ?);Times Reported Time consuming queries #9
Day Hour Count Duration Avg duration Jan 29 08 1 4h12m23s 4h12m23s -
INSERT INTO pubc.log_query (type_cd, query_tm, submission_qty, session_id, server_nm, remote_addr, results_qty, basic_query_txt, batch_input_type_txt, results_format_txt, dag_txt, action_type_txt, action_degree_type_txt, http_user_agent, node_nm, execution_ms) VALUES (, CURRENT_TIMESTAMP, 1,,, SUBSTR(, 1, 128),, SUBSTR(, 1, 4000);
Date: 2025-01-29 08:15:20 Duration: 4h12m23s Bind query: yes
10 4h12m23s 1 4h12m23s 4h12m23s 4h12m23s insert into pubc.log_query (query_tm, submission_qty, session_id, server_nm, remote_addr, http_user_agent, results_qty, execution_ms, type_cd, basic_query_txt, basic_query_type, node_nm) values (current_timestamp, ?,,, substr(, ?, ?);Times Reported Time consuming queries #10
Day Hour Count Duration Avg duration Jan 29 08 1 4h12m23s 4h12m23s [ User: pubeu - Total duration: 4h12m23s - Times executed: 1 ]
-
INSERT INTO pubc.log_query (query_tm, submission_qty, session_id, server_nm, remote_addr, http_user_agent, results_qty, execution_ms, type_cd, basic_query_txt, basic_query_type, node_nm) VALUES (CURRENT_TIMESTAMP, 1,,, SUBSTR(, 1, 128);
Date: 2025-01-29 08:15:20 Duration: 4h12m23s Database: ctdprd51 User: pubeu Bind query: yes
11 4h12m16s 1 4h12m16s 4h12m16s 4h12m16s insert into pubc.log_query (query_tm, submission_qty, session_id, server_nm, remote_addr, http_user_agent, results_qty, execution_ms, type_cd, basic_query_txt, basic_query_type, node_nm) values (current_timestamp, ?, ?, ?, substr(?, ?, ?), nullif (substr(? ? ? ? ? gene ? job18_014864 ? contains ? '));Times Reported Time consuming queries #11
Day Hour Count Duration Avg duration Jan 29 08 1 4h12m16s 4h12m16s [ User: pubeu - Total duration: 4h12m16s - Times executed: 1 ]
-
INSERT INTO pubc.log_query (query_tm, submission_qty, session_id, server_nm, remote_addr, http_user_agent, results_qty, execution_ms, type_cd, basic_query_txt, basic_query_type, node_nm) VALUES (CURRENT_TIMESTAMP, 1, 'C0A7049223935B0DC40D69046F3240B3', 'CTDBASE.ORG', SUBSTR('45.129.181.193', 1, 128), NULLIF (SUBSTR('Mozilla / 5.0 (Windows NT 10.0; Win64; x64) AppleWebKit / 537.36 (KHTML, 1, 256), ''), '1', NULLIF ('4', -1), 'gene', SUBSTR('job18_014864', 1, 1000), 'contains', NULLIF (NULL, ''));
Date: 2025-01-29 08:15:20 Duration: 4h12m16s Database: ctdprd51 User: pubeu Bind query: yes
12 4h12m10s 1 4h12m10s 4h12m10s 4h12m10s insert into pubc.log_query (type_cd, query_tm, submission_qty, session_id, server_nm, remote_addr, results_qty, basic_query_txt, batch_input_type_txt, results_format_txt, dag_txt, action_type_txt, action_degree_type_txt, http_user_agent, node_nm, execution_ms) values (, current_timestamp, ?,,, substr(, ?, ?),, substr(, ?, ?),, nullif (, ?), nullif (substr(, ?, ?), ?);Times Reported Time consuming queries #12
Day Hour Count Duration Avg duration Jan 29 08 1 4h12m10s 4h12m10s [ User: pubeu - Total duration: 4h12m10s - Times executed: 1 ]
-
INSERT INTO pubc.log_query (type_cd, query_tm, submission_qty, session_id, server_nm, remote_addr, results_qty, basic_query_txt, batch_input_type_txt, results_format_txt, dag_txt, action_type_txt, action_degree_type_txt, http_user_agent, node_nm, execution_ms) VALUES (, CURRENT_TIMESTAMP, 1,,, SUBSTR(, 1, 128),, SUBSTR(, 1, 4000),, NULLIF (, ''), NULLIF (SUBSTR(, 1, 128), '');
Date: 2025-01-29 08:15:20 Duration: 4h12m10s Database: ctdprd51 User: pubeu Bind query: yes
13 4h12m9s 1 4h12m9s 4h12m9s 4h12m9s insert into pubc.log_query (query_tm, submission_qty;Times Reported Time consuming queries #13
Day Hour Count Duration Avg duration Jan 29 08 1 4h12m9s 4h12m9s [ User: pubeu - Total duration: 4h12m9s - Times executed: 1 ]
-
INSERT INTO pubc.log_query (query_tm, submission_qty;
Date: 2025-01-29 08:15:20 Duration: 4h12m9s Database: ctdprd51 User: pubeu Bind query: yes
14 4h12m5s 1 4h12m5s 4h12m5s 4h12m5s insert into pubc.log_query (type_cd, query_tm, submission_qty, session_id, server_nm, remote_addr, results_qty, basic_query_txt, batch_input_type_txt, results_format_txt, dag_txt, action_type_txt, action_degree_type_txt, http_user_agent, node_nm, execution_ms) values (, current_timestamp, ?,,, substr(, ?, ?),, substr(, ?, ?),, nullif (, ?), nullif (substr(, ?, ?), ?), nullif (substr(, ?, ?), ?), nullif (substr(, ?, ?), ?);Times Reported Time consuming queries #14
Day Hour Count Duration Avg duration Jan 29 08 1 4h12m5s 4h12m5s [ User: pubeu - Total duration: 4h12m5s - Times executed: 1 ]
-
INSERT INTO pubc.log_query (type_cd, query_tm, submission_qty, session_id, server_nm, remote_addr, results_qty, basic_query_txt, batch_input_type_txt, results_format_txt, dag_txt, action_type_txt, action_degree_type_txt, http_user_agent, node_nm, execution_ms) VALUES (, CURRENT_TIMESTAMP, 1,,, SUBSTR(, 1, 128),, SUBSTR(, 1, 4000),, NULLIF (, ''), NULLIF (SUBSTR(, 1, 128), ''), NULLIF (SUBSTR(, 1, 4000), ''), NULLIF (SUBSTR(, 1, 4000), '');
Date: 2025-01-29 08:15:20 Duration: 4h12m5s Database: ctdprd51 User: pubeu Bind query: yes
15 4h11m57s 2 14s34ms 4h11m43s 2h5m58s insert into pubc.log_query (type_cd, query_tm;Times Reported Time consuming queries #15
Day Hour Count Duration Avg duration Jan 29 04 1 14s34ms 14s34ms 08 1 4h11m43s 4h11m43s [ User: pubeu - Total duration: 4h11m43s - Times executed: 1 ]
-
INSERT INTO pubc.log_query (type_cd, query_tm;
Date: 2025-01-29 08:15:20 Duration: 4h11m43s Database: ctdprd51 User: pubeu Bind query: yes
-
INSERT INTO pubc.log_query (type_cd, query_tm;
Date: 2025-01-29 04:02:19 Duration: 14s34ms Bind query: yes
16 4h11m56s 1 4h11m56s 4h11m56s 4h11m56s insert into pubc.log_query (type_cd, query_tm, submission_qty, session_id, server_nm, remote_addr, results_qty, basic_query_txt, batch_input_type_txt, results_format_txt, dag_txt, action_type_txt, action_degree_type_txt, http_user_agent, node_nm, execution_ms) values (, current_timestamp, ?,;Times Reported Time consuming queries #16
Day Hour Count Duration Avg duration Jan 29 08 1 4h11m56s 4h11m56s [ User: pubeu - Total duration: 4h11m56s - Times executed: 1 ]
-
INSERT INTO pubc.log_query (type_cd, query_tm, submission_qty, session_id, server_nm, remote_addr, results_qty, basic_query_txt, batch_input_type_txt, results_format_txt, dag_txt, action_type_txt, action_degree_type_txt, http_user_agent, node_nm, execution_ms) VALUES (, CURRENT_TIMESTAMP, 1,;
Date: 2025-01-29 08:15:20 Duration: 4h11m56s Database: ctdprd51 User: pubeu Bind query: yes
17 4h11m55s 2 9s145ms 4h11m46s 2h5m57s insert into pubc.log_query (type_cd, query_tm, submission_qty, session_id, server_nm, remote_addr, results_qty, basic_query_txt, batch_input_type_txt;Times Reported Time consuming queries #17
Day Hour Count Duration Avg duration Jan 29 04 1 9s145ms 9s145ms 08 1 4h11m46s 4h11m46s [ User: pubeu - Total duration: 4h11m46s - Times executed: 1 ]
-
INSERT INTO pubc.log_query (type_cd, query_tm, submission_qty, session_id, server_nm, remote_addr, results_qty, basic_query_txt, batch_input_type_txt;
Date: 2025-01-29 08:15:20 Duration: 4h11m46s Database: ctdprd51 User: pubeu Bind query: yes
-
INSERT INTO pubc.log_query (type_cd, query_tm, submission_qty, session_id, server_nm, remote_addr, results_qty, basic_query_txt, batch_input_type_txt;
Date: 2025-01-29 04:02:19 Duration: 9s145ms Bind query: yes
18 4h11m51s 1 4h11m51s 4h11m51s 4h11m51s insert into pubc.log_query (type_cd, query_tm, submission_qty, session_id, server_nm, remote_addr, results_qty, basic_query_txt, batch_input_type_txt, results_format_txt, dag_txt, action_type_txt, action_degree_type_txt, http_user_agent, node_nm, execution_ms) values (, current_timestamp, ?,,, substr(, ?, ?),, substr(, ?, ?),;Times Reported Time consuming queries #18
Day Hour Count Duration Avg duration Jan 29 08 1 4h11m51s 4h11m51s [ User: pubeu - Total duration: 4h11m51s - Times executed: 1 ]
-
INSERT INTO pubc.log_query (type_cd, query_tm, submission_qty, session_id, server_nm, remote_addr, results_qty, basic_query_txt, batch_input_type_txt, results_format_txt, dag_txt, action_type_txt, action_degree_type_txt, http_user_agent, node_nm, execution_ms) VALUES (, CURRENT_TIMESTAMP, 1,,, SUBSTR(, 1, 128),, SUBSTR(, 1, 4000),;
Date: 2025-01-29 08:15:20 Duration: 4h11m51s Database: ctdprd51 User: pubeu Bind query: yes
19 4h11m40s 1 4h11m40s 4h11m40s 4h11m40s insert into pubc.log_query (type_cd, query_tm, submission_qty, session_id, server_nm, remote_addr, results_qty, basic_query_txt, batch_input_type_txt, results_format_txt;Times Reported Time consuming queries #19
Day Hour Count Duration Avg duration Jan 29 08 1 4h11m40s 4h11m40s [ User: pubeu - Total duration: 4h11m40s - Times executed: 1 ]
-
INSERT INTO pubc.log_query (type_cd, query_tm, submission_qty, session_id, server_nm, remote_addr, results_qty, basic_query_txt, batch_input_type_txt, results_format_txt;
Date: 2025-01-29 08:15:20 Duration: 4h11m40s Database: ctdprd51 User: pubeu Bind query: yes
20 4h11m35s 1 4h11m35s 4h11m35s 4h11m35s insert into pubc.log_query (query_tm, submission_qty, session_id, server_nm, remote_addr, http_user_agent, results_qty, execution_ms, type_cd, basic_query_txt, basic_query_type, node_nm) values (current_timestamp, ?, ?, ?, substr(?, ?, ?), nullif (substr(? ? ? ? ? gene ? z169_09976 ? contains ? '));Times Reported Time consuming queries #20
Day Hour Count Duration Avg duration Jan 29 08 1 4h11m35s 4h11m35s [ User: pubeu - Total duration: 4h11m35s - Times executed: 1 ]
-
INSERT INTO pubc.log_query (query_tm, submission_qty, session_id, server_nm, remote_addr, http_user_agent, results_qty, execution_ms, type_cd, basic_query_txt, basic_query_type, node_nm) VALUES (CURRENT_TIMESTAMP, 1, 'E964CE05E0FF3962154CE42F4A6BB2A7', 'CTDBASE.ORG', SUBSTR('94.16.113.171', 1, 128), NULLIF (SUBSTR('Mozilla / 5.0 (Windows NT 10.0; Win64; x64) AppleWebKit / 537.36 (KHTML, 1, 256), ''), '1', NULLIF ('5', -1), 'gene', SUBSTR('z169_09976', 1, 1000), 'contains', NULLIF (NULL, ''));
Date: 2025-01-29 08:15:20 Duration: 4h11m35s Database: ctdprd51 User: pubeu Bind query: yes
Most frequent queries (N)
Rank Times executed Total duration Min duration Max duration Avg duration Query 1 910 44m44s 1s3ms 7s357ms 2s950ms select distinct associatedterm.nm || ? || o.cd || ? || associatedterm.nm_html || ? || associatedterm.acc_txt || ? || associatedterm.acc_db_cd as associatedterm, associatedterm.id associatedtermid, ptr.ixn_id ixnid, associatedterm.object_type_id || ? || associatedterm.nm_sort associatedtermnmsort, coalesce(associatedterm.secondary_nm, ?) casrn, phenotypeterm.nm || ? || ? || ? || phenotypeterm.nm_html || ? || phenotypeterm.acc_txt || ? || phenotypeterm.acc_db_cd as phenotype, phenotypeterm.id phenotypeid, ( select string_agg(distinct taxonterm.nm || ? || ? || ? || taxonterm.nm_html || ? || taxonterm.acc_txt || ? || taxonterm.acc_db_cd || ? || coalesce(taxonterm.secondary_nm, ?), ?)) as taxonterms, ( select string_agg(distinct anatomyterm.nm_html || ? || anatomyterm.acc_txt || ? || ia.level_seq || ? || anatomyterm.acc_db_cd || ? || anatomyterm.nm, ?)) as anatomyterms, count(distinct taxonterm.nm) taxoncount, i.ixn_prose_html ixnprosehtml, i.ixn_prose_txt ixnprose, i.sort_txt ixnsort, ( select string_agg(distinct r.acc_txt, ?)) as references, count(distinct ptr.reference_id) refcount, pt.indirect_term_qty inferredcount, count(*) over () fullrowcount from phenotype_term_reference ptr inner join phenotype_term pt on ptr.term_id = pt.term_id and ptr.phenotype_id = pt.phenotype_id inner join term associatedterm on ptr.term_id = associatedterm.id inner join term phenotypeterm on ptr.phenotype_id = phenotypeterm.id left outer join term taxonterm on ptr.taxon_id = taxonterm.id inner join reference r on ptr.reference_id = r.id inner join ixn i on ptr.ixn_id = i.id inner join object_type o on associatedterm.object_type_id = o.id left outer join ixn_anatomy ia on ptr.ixn_id = ia.ixn_id left outer join term anatomyterm on ia.anatomy_id = anatomyterm.id where associatedterm.id = any (array (( select p.descendant_object_id from dag_path p where p.ancestor_object_id = ?))) and ptr.term_object_type_id = ? group by associatedterm, associatedtermnmsort, phenotype, casrn, ixnid, ixnprosehtml, ixnprose, ixnsort, associatedtermid, phenotypeid, inferredcount order by associatedtermnmsort limit ?;Times Reported Time consuming queries #1
Day Hour Count Duration Avg duration Jan 29 00 43 2m22s 3s316ms 01 41 2m13s 3s267ms 02 56 2m49s 3s19ms 03 45 2m25s 3s224ms 04 5 18s951ms 3s790ms 08 24 1m20s 3s353ms 09 38 1m28s 2s334ms 10 32 1m29s 2s792ms 11 52 2m24s 2s773ms 12 50 2m7s 2s542ms 13 59 3m9s 3s216ms 14 33 1m40s 3s34ms 15 54 2m49s 3s135ms 16 44 1m59s 2s723ms 17 62 3m3s 2s959ms 18 41 1m56s 2s834ms 19 45 2m9s 2s878ms 20 54 2m30s 2s780ms 21 46 2m22s 3s94ms 22 46 2m18s 3s16ms 23 40 1m45s 2s637ms [ User: pubeu - Total duration: 9m55s - Times executed: 195 ]
-
select distinct /* ChemPhenotypesAssnsDAO */ associatedTerm.nm || '^' || o.cd || '^' || associatedTerm.nm_html || '^' || associatedTerm.acc_txt || '^' || associatedTerm.acc_db_cd as associatedTerm, associatedTerm.id associatedTermId, ptr.ixn_id ixnId, associatedTerm.object_type_id || '|' || associatedTerm.nm_sort associatedTermNmSort, COALESCE(associatedTerm.secondary_nm, '') casRN, phenotypeTerm.nm || '^' || 'go' || '^' || phenotypeTerm.nm_html || '^' || phenotypeTerm.acc_txt || '^' || phenotypeTerm.acc_db_cd as phenotype, phenotypeTerm.id phenotypeId, ( SELECT STRING_AGG(distinct taxonTerm.nm || '^' || 'taxon' || '^' || taxonTerm.nm_html || '^' || taxonTerm.acc_txt || '^' || taxonTerm.acc_db_cd || '^' || COALESCE(taxonTerm.secondary_nm, ''), '|')) as taxonTerms, ( SELECT STRING_AGG(distinct anatomyTerm.nm_html || '^' || anatomyTerm.acc_txt || '^' || ia.level_seq || '^' || anatomyTerm.acc_db_cd || '^' || anatomyTerm.nm, '|')) as anatomyTerms, COUNT(DISTINCT taxonTerm.nm) taxonCount, i.ixn_prose_html ixnProseHtml, i.ixn_prose_txt ixnProse, i.sort_txt ixnSort, ( SELECT STRING_AGG(distinct r.acc_txt, '|')) as references, COUNT(DISTINCT ptr.reference_id) refCount, pt.indirect_term_qty inferredCount, COUNT(*) OVER () fullRowCount from phenotype_term_reference ptr inner join phenotype_term pt on ptr.term_id = pt.term_id and ptr.phenotype_id = pt.phenotype_id inner join term associatedTerm on ptr.term_id = associatedTerm.id inner join term phenotypeTerm on ptr.phenotype_id = phenotypeTerm.id left outer join term taxonTerm on ptr.taxon_id = taxonTerm.id inner join reference r on ptr.reference_id = r.id inner join ixn i on ptr.ixn_id = i.id inner join object_type o on associatedTerm.object_type_id = o.id left outer join ixn_anatomy ia on ptr.ixn_id = ia.ixn_id left outer join term anatomyTerm on ia.anatomy_id = anatomyTerm.id where associatedTerm.id = ANY (ARRAY (( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '1399640'))) and ptr.term_object_type_id = 2 group by associatedTerm, associatedTermNmSort, phenotype, casRN, ixnId, ixnProseHtml, ixnProse, ixnSort, associatedTermId, phenotypeId, inferredCount ORDER BY associatedTermNmSort LIMIT 50;
Date: 2025-01-29 03:37:36 Duration: 7s357ms Bind query: yes
-
select distinct /* ChemPhenotypesAssnsDAO */ associatedTerm.nm || '^' || o.cd || '^' || associatedTerm.nm_html || '^' || associatedTerm.acc_txt || '^' || associatedTerm.acc_db_cd as associatedTerm, associatedTerm.id associatedTermId, ptr.ixn_id ixnId, associatedTerm.object_type_id || '|' || associatedTerm.nm_sort associatedTermNmSort, COALESCE(associatedTerm.secondary_nm, '') casRN, phenotypeTerm.nm || '^' || 'go' || '^' || phenotypeTerm.nm_html || '^' || phenotypeTerm.acc_txt || '^' || phenotypeTerm.acc_db_cd as phenotype, phenotypeTerm.id phenotypeId, ( SELECT STRING_AGG(distinct taxonTerm.nm || '^' || 'taxon' || '^' || taxonTerm.nm_html || '^' || taxonTerm.acc_txt || '^' || taxonTerm.acc_db_cd || '^' || COALESCE(taxonTerm.secondary_nm, ''), '|')) as taxonTerms, ( SELECT STRING_AGG(distinct anatomyTerm.nm_html || '^' || anatomyTerm.acc_txt || '^' || ia.level_seq || '^' || anatomyTerm.acc_db_cd || '^' || anatomyTerm.nm, '|')) as anatomyTerms, COUNT(DISTINCT taxonTerm.nm) taxonCount, i.ixn_prose_html ixnProseHtml, i.ixn_prose_txt ixnProse, i.sort_txt ixnSort, ( SELECT STRING_AGG(distinct r.acc_txt, '|')) as references, COUNT(DISTINCT ptr.reference_id) refCount, pt.indirect_term_qty inferredCount, COUNT(*) OVER () fullRowCount from phenotype_term_reference ptr inner join phenotype_term pt on ptr.term_id = pt.term_id and ptr.phenotype_id = pt.phenotype_id inner join term associatedTerm on ptr.term_id = associatedTerm.id inner join term phenotypeTerm on ptr.phenotype_id = phenotypeTerm.id left outer join term taxonTerm on ptr.taxon_id = taxonTerm.id inner join reference r on ptr.reference_id = r.id inner join ixn i on ptr.ixn_id = i.id inner join object_type o on associatedTerm.object_type_id = o.id left outer join ixn_anatomy ia on ptr.ixn_id = ia.ixn_id left outer join term anatomyTerm on ia.anatomy_id = anatomyTerm.id where associatedTerm.id = ANY (ARRAY (( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '1399640'))) and ptr.term_object_type_id = 2 group by associatedTerm, associatedTermNmSort, phenotype, casRN, ixnId, ixnProseHtml, ixnProse, ixnSort, associatedTermId, phenotypeId, inferredCount ORDER BY associatedTermNmSort LIMIT 50;
Date: 2025-01-29 03:20:14 Duration: 7s104ms Bind query: yes
-
select distinct /* ChemPhenotypesAssnsDAO */ associatedTerm.nm || '^' || o.cd || '^' || associatedTerm.nm_html || '^' || associatedTerm.acc_txt || '^' || associatedTerm.acc_db_cd as associatedTerm, associatedTerm.id associatedTermId, ptr.ixn_id ixnId, associatedTerm.object_type_id || '|' || associatedTerm.nm_sort associatedTermNmSort, COALESCE(associatedTerm.secondary_nm, '') casRN, phenotypeTerm.nm || '^' || 'go' || '^' || phenotypeTerm.nm_html || '^' || phenotypeTerm.acc_txt || '^' || phenotypeTerm.acc_db_cd as phenotype, phenotypeTerm.id phenotypeId, ( SELECT STRING_AGG(distinct taxonTerm.nm || '^' || 'taxon' || '^' || taxonTerm.nm_html || '^' || taxonTerm.acc_txt || '^' || taxonTerm.acc_db_cd || '^' || COALESCE(taxonTerm.secondary_nm, ''), '|')) as taxonTerms, ( SELECT STRING_AGG(distinct anatomyTerm.nm_html || '^' || anatomyTerm.acc_txt || '^' || ia.level_seq || '^' || anatomyTerm.acc_db_cd || '^' || anatomyTerm.nm, '|')) as anatomyTerms, COUNT(DISTINCT taxonTerm.nm) taxonCount, i.ixn_prose_html ixnProseHtml, i.ixn_prose_txt ixnProse, i.sort_txt ixnSort, ( SELECT STRING_AGG(distinct r.acc_txt, '|')) as references, COUNT(DISTINCT ptr.reference_id) refCount, pt.indirect_term_qty inferredCount, COUNT(*) OVER () fullRowCount from phenotype_term_reference ptr inner join phenotype_term pt on ptr.term_id = pt.term_id and ptr.phenotype_id = pt.phenotype_id inner join term associatedTerm on ptr.term_id = associatedTerm.id inner join term phenotypeTerm on ptr.phenotype_id = phenotypeTerm.id left outer join term taxonTerm on ptr.taxon_id = taxonTerm.id inner join reference r on ptr.reference_id = r.id inner join ixn i on ptr.ixn_id = i.id inner join object_type o on associatedTerm.object_type_id = o.id left outer join ixn_anatomy ia on ptr.ixn_id = ia.ixn_id left outer join term anatomyTerm on ia.anatomy_id = anatomyTerm.id where associatedTerm.id = ANY (ARRAY (( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '1399640'))) and ptr.term_object_type_id = 2 group by associatedTerm, associatedTermNmSort, phenotype, casRN, ixnId, ixnProseHtml, ixnProse, ixnSort, associatedTermId, phenotypeId, inferredCount ORDER BY associatedTermNmSort LIMIT 50;
Date: 2025-01-29 13:44:55 Duration: 6s904ms Bind query: yes
2 639 28m11s 1s 5s127ms 2s647ms select e.reference_acc_txt || ? || r.abbr_authors_txt || ? || r.pub_start_yr as ref, ( select string_agg(distinct expstudyfactor.study_factor_nm, ?)) as studyfactornms, ( select string_agg(distinct eventproject.project_nm, ?)) as associatedstudytitles, ( select string_agg(distinct stressorterm.nm || ? || ( select cd from object_type where id = stressorterm.object_type_id) || ? || stressorterm.nm_html || ? || stressorterm.acc_txt || ? || stressorterm.acc_db_cd, ?)) as stressoragents, ( select string_agg(distinct coalesce(receptorterm.nm, ?) || ? || coalesce(( select cd from object_type where id = receptorterm.object_type_id), ?) || ? || coalesce(receptorterm.nm_html, ?) || ? || coalesce(receptorterm.acc_txt, ?) || ? || coalesce(receptorterm.acc_db_cd, ?) || ? || receptor.description, ?)) as receptors, ( select string_agg(distinct country.nm, ?)) as studycountries, ( select string_agg(distinct location.locality_txt, ?)) as localities, ( select string_agg(distinct event.medium_nm || ? || coalesce(event.medium_term_acc_txt, ?), ?)) as assaymediums, ( select string_agg(distinct exposuremarkerterm.nm || ? || ( select cd from object_type where id = exposuremarkerterm.object_type_id) || ? || exposuremarkerterm.nm_html || ? || exposuremarkerterm.acc_txt || ? || exposuremarkerterm.acc_db_cd, ?)) as assayedmarkers, ( select string_agg(distinct diseaseterm.nm || ? || ( select cd from object_type where id = diseaseterm.object_type_id) || ? || diseaseterm.nm_html || ? || diseaseterm.acc_txt || ? || diseaseterm.acc_db_cd, ?)) as diseases, ( select string_agg(distinct phenotypeterm.nm || ? || ( select cd from object_type where id = phenotypeterm.object_type_id) || ? || phenotypeterm.nm_html || ? || phenotypeterm.acc_txt || ? || phenotypeterm.acc_db_cd, ?)) as phenotypes, ( select string_agg(distinct anatomyterm.nm_html || ? || anatomyterm.acc_txt || ? || anatomyterm.id || ? || anatomyterm.acc_db_cd || ? || anatomyterm.nm, ?)) as anatomyterms, re.author_summary summary, count(*) over () fullrowcount from exposure e inner join reference r on e.reference_id = r.id inner join exp_stressor stressor on e.exp_stressor_id = stressor.id left outer join exp_receptor receptor on e.exp_receptor_id = receptor.id left outer join term receptorterm on receptor.term_id = receptorterm.id left outer join exp_event event on e.exp_event_id = event.id left outer join exp_event_project eventproject on event.id = eventproject.exp_event_id left outer join exp_event_location location on e.exp_event_id = location.exp_event_id left outer join country on location.country_id = country.id left outer join term exposuremarkerterm on event.exp_marker_term_id = exposuremarkerterm.id left outer join exp_outcome outcome on e.exp_outcome_id = outcome.id left outer join term diseaseterm on outcome.disease_id = diseaseterm.id left outer join term phenotypeterm on outcome.phenotype_id = phenotypeterm.id inner join term stressorterm on stressor.chem_id = stressorterm.id left outer join exp_anatomy expanatomy on outcome.id = expanatomy.exp_outcome_id left outer join term anatomyterm on expanatomy.anatomy_id = anatomyterm.id inner join reference_exp re on e.reference_id = re.reference_id left outer join exp_study_factor expstudyfactor on re.id = expstudyfactor.reference_exp_id where e.reference_id = any (array ( select reference_id from exposure e1, term chem, exp_stressor s1 where chem.id in ( select descendant_object_id from dag_path where ancestor_object_id = ?) and chem.id = s1.chem_id and s1.id = e1.exp_stressor_id union select reference_id from exposure e1, term t, exp_event event1 where t.id in ( select descendant_object_id from dag_path where ancestor_object_id = ?) and t.id = event1.exp_marker_term_id and event1.exp_marker_type_id in ( select id from exp_marker_type where nm like ?) and event1.id = e1.exp_event_id)) group by e.reference_acc_txt, r.abbr_authors_txt, pub_start_yr, re.author_summary order by stressoragents limit ?;Times Reported Time consuming queries #2
Day Hour Count Duration Avg duration Jan 29 00 38 1m34s 2s493ms 01 27 1m21s 3s14ms 02 40 1m53s 2s832ms 03 37 1m33s 2s527ms 04 3 9s585ms 3s195ms 08 21 55s794ms 2s656ms 09 25 1m6s 2s651ms 10 21 41s729ms 1s987ms 11 35 1m30s 2s589ms 12 27 1m9s 2s574ms 13 37 1m48s 2s921ms 14 22 52s759ms 2s398ms 15 31 1m27s 2s838ms 16 29 1m14s 2s567ms 17 35 1m48s 3s95ms 18 35 1m27s 2s492ms 19 31 1m16s 2s457ms 20 29 1m8s 2s346ms 21 35 1m27s 2s489ms 22 37 1m34s 2s559ms 23 44 2m10s 2s958ms [ User: pubeu - Total duration: 5m49s - Times executed: 132 ]
-
SELECT /* ChemExposureStudiesAssnsDAO */ e.reference_acc_txt || '^' || r.abbr_authors_txt || '^' || r.pub_start_yr as ref, ( SELECT STRING_AGG(DISTINCT expStudyFactor.study_factor_nm, ' | ')) as studyFactorNms, ( SELECT STRING_AGG(DISTINCT eventProject.project_nm, ' | ')) as associatedStudyTitles, ( SELECT STRING_AGG(distinct stressorTerm.nm || '^' || ( select cd from object_type where id = stressorTerm.object_type_id) || '^' || stressorTerm.nm_html || '^' || stressorTerm.acc_txt || '^' || stressorTerm.acc_db_cd, '|')) as stressorAgents, ( SELECT STRING_AGG(distinct COALESCE(receptorTerm.nm, '') || '^' || COALESCE(( select cd from object_type where id = receptorTerm.object_type_id), '') || '^' || COALESCE(receptorTerm.nm_html, '') || '^' || COALESCE(receptorTerm.acc_txt, '') || '^' || COALESCE(receptorTerm.acc_db_cd, '') || '^' || receptor.description, '|')) as receptors, ( SELECT STRING_AGG(distinct country.nm, ' | ')) as studyCountries, ( SELECT STRING_AGG(distinct location.locality_txt, ' | ')) as localities, ( SELECT STRING_AGG(distinct event.medium_nm || '^' || COALESCE(event.medium_term_acc_txt, ''), ' | ')) as assayMediums, ( SELECT STRING_AGG(distinct exposureMarkerTerm.nm || '^' || ( select cd from object_type where id = exposureMarkerTerm.object_type_id) || '^' || exposureMarkerTerm.nm_html || '^' || exposureMarkerTerm.acc_txt || '^' || exposureMarkerTerm.acc_db_cd, '|')) as assayedMarkers, ( SELECT STRING_AGG(distinct diseaseTerm.nm || '^' || ( select cd from object_type where id = diseaseTerm.object_type_id) || '^' || diseaseTerm.nm_html || '^' || diseaseTerm.acc_txt || '^' || diseaseTerm.acc_db_cd, '|')) as diseases, ( SELECT STRING_AGG(distinct phenotypeTerm.nm || '^' || ( select cd from object_type where id = phenotypeTerm.object_type_id) || '^' || phenotypeTerm.nm_html || '^' || phenotypeTerm.acc_txt || '^' || phenotypeTerm.acc_db_cd, '|')) as phenotypes, ( SELECT STRING_AGG(distinct anatomyTerm.nm_html || '^' || anatomyTerm.acc_txt || '^' || anatomyTerm.id || '^' || anatomyTerm.acc_db_cd || '^' || anatomyTerm.nm, '|')) as anatomyTerms, re.author_summary summary, COUNT(*) OVER () fullRowCount FROM exposure e inner join reference r ON e.reference_id = r.id inner join exp_stressor stressor ON e.exp_stressor_id = stressor.id left outer join exp_receptor receptor ON e.exp_receptor_id = receptor.id left outer join term receptorTerm ON receptor.term_id = receptorTerm.id left outer join exp_event event ON e.exp_event_id = event.id left outer join exp_event_project eventProject ON event.id = eventProject.exp_event_id left outer join exp_event_location location ON e.exp_event_id = location.exp_event_id left outer join country ON location.country_id = country.id left outer join term exposureMarkerTerm ON event.exp_marker_term_id = exposureMarkerTerm.id left outer join exp_outcome outcome ON e.exp_outcome_id = outcome.id left outer join term diseaseTerm ON outcome.disease_id = diseaseTerm.id left outer join term phenotypeTerm ON outcome.phenotype_id = phenotypeTerm.id inner join term stressorTerm ON stressor.chem_id = stressorTerm.id left outer join exp_anatomy expAnatomy ON outcome.id = expAnatomy.exp_outcome_id Left outer join term anatomyTerm ON expAnatomy.anatomy_id = anatomyTerm.id inner join reference_exp re ON e.reference_id = re.reference_id left outer join exp_study_factor expStudyFactor on re.id = expStudyFactor.reference_exp_id where e.reference_id = ANY (ARRAY ( select reference_id from exposure e1, term chem, exp_stressor s1 where chem.id in ( select descendant_object_id from dag_path where ancestor_object_id = '1399640') and chem.id = s1.chem_id and s1.id = e1.exp_stressor_id union select reference_id from exposure e1, term t, exp_event event1 where t.id in ( select descendant_object_id from dag_path where ancestor_object_id = '1399640') and t.id = event1.exp_marker_term_id and event1.exp_marker_type_id in ( select id from exp_marker_type where nm like 'chem%') and event1.id = e1.exp_event_id)) group by e.reference_acc_txt, r.abbr_authors_txt, pub_start_yr, re.author_summary order by stressorAgents LIMIT 50;
Date: 2025-01-29 03:32:47 Duration: 5s127ms Bind query: yes
-
SELECT /* ChemExposureStudiesAssnsDAO */ e.reference_acc_txt || '^' || r.abbr_authors_txt || '^' || r.pub_start_yr as ref, ( SELECT STRING_AGG(DISTINCT expStudyFactor.study_factor_nm, ' | ')) as studyFactorNms, ( SELECT STRING_AGG(DISTINCT eventProject.project_nm, ' | ')) as associatedStudyTitles, ( SELECT STRING_AGG(distinct stressorTerm.nm || '^' || ( select cd from object_type where id = stressorTerm.object_type_id) || '^' || stressorTerm.nm_html || '^' || stressorTerm.acc_txt || '^' || stressorTerm.acc_db_cd, '|')) as stressorAgents, ( SELECT STRING_AGG(distinct COALESCE(receptorTerm.nm, '') || '^' || COALESCE(( select cd from object_type where id = receptorTerm.object_type_id), '') || '^' || COALESCE(receptorTerm.nm_html, '') || '^' || COALESCE(receptorTerm.acc_txt, '') || '^' || COALESCE(receptorTerm.acc_db_cd, '') || '^' || receptor.description, '|')) as receptors, ( SELECT STRING_AGG(distinct country.nm, ' | ')) as studyCountries, ( SELECT STRING_AGG(distinct location.locality_txt, ' | ')) as localities, ( SELECT STRING_AGG(distinct event.medium_nm || '^' || COALESCE(event.medium_term_acc_txt, ''), ' | ')) as assayMediums, ( SELECT STRING_AGG(distinct exposureMarkerTerm.nm || '^' || ( select cd from object_type where id = exposureMarkerTerm.object_type_id) || '^' || exposureMarkerTerm.nm_html || '^' || exposureMarkerTerm.acc_txt || '^' || exposureMarkerTerm.acc_db_cd, '|')) as assayedMarkers, ( SELECT STRING_AGG(distinct diseaseTerm.nm || '^' || ( select cd from object_type where id = diseaseTerm.object_type_id) || '^' || diseaseTerm.nm_html || '^' || diseaseTerm.acc_txt || '^' || diseaseTerm.acc_db_cd, '|')) as diseases, ( SELECT STRING_AGG(distinct phenotypeTerm.nm || '^' || ( select cd from object_type where id = phenotypeTerm.object_type_id) || '^' || phenotypeTerm.nm_html || '^' || phenotypeTerm.acc_txt || '^' || phenotypeTerm.acc_db_cd, '|')) as phenotypes, ( SELECT STRING_AGG(distinct anatomyTerm.nm_html || '^' || anatomyTerm.acc_txt || '^' || anatomyTerm.id || '^' || anatomyTerm.acc_db_cd || '^' || anatomyTerm.nm, '|')) as anatomyTerms, re.author_summary summary, COUNT(*) OVER () fullRowCount FROM exposure e inner join reference r ON e.reference_id = r.id inner join exp_stressor stressor ON e.exp_stressor_id = stressor.id left outer join exp_receptor receptor ON e.exp_receptor_id = receptor.id left outer join term receptorTerm ON receptor.term_id = receptorTerm.id left outer join exp_event event ON e.exp_event_id = event.id left outer join exp_event_project eventProject ON event.id = eventProject.exp_event_id left outer join exp_event_location location ON e.exp_event_id = location.exp_event_id left outer join country ON location.country_id = country.id left outer join term exposureMarkerTerm ON event.exp_marker_term_id = exposureMarkerTerm.id left outer join exp_outcome outcome ON e.exp_outcome_id = outcome.id left outer join term diseaseTerm ON outcome.disease_id = diseaseTerm.id left outer join term phenotypeTerm ON outcome.phenotype_id = phenotypeTerm.id inner join term stressorTerm ON stressor.chem_id = stressorTerm.id left outer join exp_anatomy expAnatomy ON outcome.id = expAnatomy.exp_outcome_id Left outer join term anatomyTerm ON expAnatomy.anatomy_id = anatomyTerm.id inner join reference_exp re ON e.reference_id = re.reference_id left outer join exp_study_factor expStudyFactor on re.id = expStudyFactor.reference_exp_id where e.reference_id = ANY (ARRAY ( select reference_id from exposure e1, term chem, exp_stressor s1 where chem.id in ( select descendant_object_id from dag_path where ancestor_object_id = '1399640') and chem.id = s1.chem_id and s1.id = e1.exp_stressor_id union select reference_id from exposure e1, term t, exp_event event1 where t.id in ( select descendant_object_id from dag_path where ancestor_object_id = '1399640') and t.id = event1.exp_marker_term_id and event1.exp_marker_type_id in ( select id from exp_marker_type where nm like 'chem%') and event1.id = e1.exp_event_id)) group by e.reference_acc_txt, r.abbr_authors_txt, pub_start_yr, re.author_summary order by stressorAgents LIMIT 50;
Date: 2025-01-29 21:36:07 Duration: 5s118ms Bind query: yes
-
SELECT /* ChemExposureStudiesAssnsDAO */ e.reference_acc_txt || '^' || r.abbr_authors_txt || '^' || r.pub_start_yr as ref, ( SELECT STRING_AGG(DISTINCT expStudyFactor.study_factor_nm, ' | ')) as studyFactorNms, ( SELECT STRING_AGG(DISTINCT eventProject.project_nm, ' | ')) as associatedStudyTitles, ( SELECT STRING_AGG(distinct stressorTerm.nm || '^' || ( select cd from object_type where id = stressorTerm.object_type_id) || '^' || stressorTerm.nm_html || '^' || stressorTerm.acc_txt || '^' || stressorTerm.acc_db_cd, '|')) as stressorAgents, ( SELECT STRING_AGG(distinct COALESCE(receptorTerm.nm, '') || '^' || COALESCE(( select cd from object_type where id = receptorTerm.object_type_id), '') || '^' || COALESCE(receptorTerm.nm_html, '') || '^' || COALESCE(receptorTerm.acc_txt, '') || '^' || COALESCE(receptorTerm.acc_db_cd, '') || '^' || receptor.description, '|')) as receptors, ( SELECT STRING_AGG(distinct country.nm, ' | ')) as studyCountries, ( SELECT STRING_AGG(distinct location.locality_txt, ' | ')) as localities, ( SELECT STRING_AGG(distinct event.medium_nm || '^' || COALESCE(event.medium_term_acc_txt, ''), ' | ')) as assayMediums, ( SELECT STRING_AGG(distinct exposureMarkerTerm.nm || '^' || ( select cd from object_type where id = exposureMarkerTerm.object_type_id) || '^' || exposureMarkerTerm.nm_html || '^' || exposureMarkerTerm.acc_txt || '^' || exposureMarkerTerm.acc_db_cd, '|')) as assayedMarkers, ( SELECT STRING_AGG(distinct diseaseTerm.nm || '^' || ( select cd from object_type where id = diseaseTerm.object_type_id) || '^' || diseaseTerm.nm_html || '^' || diseaseTerm.acc_txt || '^' || diseaseTerm.acc_db_cd, '|')) as diseases, ( SELECT STRING_AGG(distinct phenotypeTerm.nm || '^' || ( select cd from object_type where id = phenotypeTerm.object_type_id) || '^' || phenotypeTerm.nm_html || '^' || phenotypeTerm.acc_txt || '^' || phenotypeTerm.acc_db_cd, '|')) as phenotypes, ( SELECT STRING_AGG(distinct anatomyTerm.nm_html || '^' || anatomyTerm.acc_txt || '^' || anatomyTerm.id || '^' || anatomyTerm.acc_db_cd || '^' || anatomyTerm.nm, '|')) as anatomyTerms, re.author_summary summary, COUNT(*) OVER () fullRowCount FROM exposure e inner join reference r ON e.reference_id = r.id inner join exp_stressor stressor ON e.exp_stressor_id = stressor.id left outer join exp_receptor receptor ON e.exp_receptor_id = receptor.id left outer join term receptorTerm ON receptor.term_id = receptorTerm.id left outer join exp_event event ON e.exp_event_id = event.id left outer join exp_event_project eventProject ON event.id = eventProject.exp_event_id left outer join exp_event_location location ON e.exp_event_id = location.exp_event_id left outer join country ON location.country_id = country.id left outer join term exposureMarkerTerm ON event.exp_marker_term_id = exposureMarkerTerm.id left outer join exp_outcome outcome ON e.exp_outcome_id = outcome.id left outer join term diseaseTerm ON outcome.disease_id = diseaseTerm.id left outer join term phenotypeTerm ON outcome.phenotype_id = phenotypeTerm.id inner join term stressorTerm ON stressor.chem_id = stressorTerm.id left outer join exp_anatomy expAnatomy ON outcome.id = expAnatomy.exp_outcome_id Left outer join term anatomyTerm ON expAnatomy.anatomy_id = anatomyTerm.id inner join reference_exp re ON e.reference_id = re.reference_id left outer join exp_study_factor expStudyFactor on re.id = expStudyFactor.reference_exp_id where e.reference_id = ANY (ARRAY ( select reference_id from exposure e1, term chem, exp_stressor s1 where chem.id in ( select descendant_object_id from dag_path where ancestor_object_id = '1399640') and chem.id = s1.chem_id and s1.id = e1.exp_stressor_id union select reference_id from exposure e1, term t, exp_event event1 where t.id in ( select descendant_object_id from dag_path where ancestor_object_id = '1399640') and t.id = event1.exp_marker_term_id and event1.exp_marker_type_id in ( select id from exp_marker_type where nm like 'chem%') and event1.id = e1.exp_event_id)) group by e.reference_acc_txt, r.abbr_authors_txt, pub_start_yr, re.author_summary order by stressorAgents LIMIT 50;
Date: 2025-01-29 13:47:50 Duration: 5s88ms Database: ctdprd51 User: pubeu Bind query: yes
3 347 6m52s 1s88ms 1s339ms 1s187ms select distinct stressorterm.nm as chemnm, stressorterm.nm_html as chemnmhtml, stressorterm.nm_sort as chemnmsort, stressorterm.acc_txt as chemacc, ( select string_agg(distinct stressorsrctype.nm || ? || stressorsrctype.cd, ?)) as stressorsrctypenm, stressor.src_details as stressorsrcdetails, stressor.sample_qty as stressorsampleqty, stressor.note as stressornote, receptor.qty as nbrreceptors, receptor.description as receptors, receptor.note as receptornotes, receptorterm.nm || ? || ( select cd from object_type where id = receptor.object_type_id) || ? || receptorterm.nm_html || ? || receptorterm.acc_txt || ? || receptorterm.acc_db_cd as receptorterms, ( select string_agg(distinct receptortobaccouse.tobacco_use_nm || ? || receptortobaccouse.pct, ?)) as smokerstatus, receptor.age as agerange, receptor.age_uom_nm as ageuomnm, receptor.age_qualifier_nm as agequalifiernm, receptor.gender_nm as gendernmsearch, receptor.id receptorid, ( select string_agg(pct || ? || gender_nm || ? || gender_nm_html, ?) from exp_receptor_gender where exp_receptor_id = receptor.id) as genderdetails, ( select string_agg(distinct receptorrace.race_nm || ? || receptorrace.pct, ?)) as receptorrace, ( select string_agg(distinct eventassaymethod.nm, ?)) as assaymethods, event.medium_nm as medium, event.medium_term_acc_txt as mediumacctxt, ( select string_agg(distinct eventproject.project_nm, ?)) as associatedstudytitles, event.collection_start_yr || ? || event.collection_end_yr as collectionstartandendyr, event.detection_limit as detectionlimit, event.detection_limit_uom as detectionlimituom, event.detection_freq as detectionfreq, event.note as eventnote, ( select string_agg(distinct eventlocation.geographic_region_nm, ?)) as stateorprovince, ( select string_agg(distinct eventlocation.locality_txt, ?)) as localitytxt, ( select string_agg(distinct country.nm, ?)) as studycountries, exposuremarkerterm.nm || ? || ( select cd from object_type where id = exposuremarkerterm.object_type_id) || ? || exposuremarkerterm.nm_html || ? || exposuremarkerterm.acc_txt || ? || exposuremarkerterm.acc_db_cd as assayedmarkers, event.exp_marker_lvl as assaylevel, assay_uom as measurement, assay_measurement_stat as measurementstat, assay_note as assaynote, eiot.description as outcomerltnp, diseaseterm.nm || ? || ? || ? || diseaseterm.nm_html || ? || diseaseterm.acc_txt || ? || diseaseterm.acc_db_cd as diseasefield, phenotypeterm.nm || ? || ? || ? || phenotypeterm.nm_html || ? || phenotypeterm.acc_txt || ? || phenotypeterm.acc_db_cd as phenotypefield, outcome.phenotype_action_degree_type_nm as phenotypeactiondegreetypenm, e.reference_acc_txt || ? || r.abbr_authors_txt || ? || r.pub_start_yr as ref, r.abbr_authors_txt as abbrauthorstxt, ( select string_agg(distinct expstudyfactor.study_factor_nm, ?)) as studyfactornms, ( select string_agg(distinct anatomyterm.nm_html || ? || anatomyterm.acc_txt || ? || anatomyterm.id || ? || anatomyterm.acc_db_cd || ? || anatomyterm.nm, ?)) as anatomyterms, outcome.note as outcomenote, eventlocation.exp_event_id as eventid, count(*) over () fullrowcount from exposure e inner join exp_stressor stressor on e.exp_stressor_id = stressor.id inner join term stressorterm on stressor.chem_id = stressorterm.id left outer join exp_receptor receptor on e.exp_receptor_id = receptor.id left outer join exp_event event on e.exp_event_id = event.id left outer join term exposuremarkerterm on event.exp_marker_term_id = exposuremarkerterm.id left outer join exp_outcome outcome on e.exp_outcome_id = outcome.id left outer join exp_outcome_ixn_type eiot on outcome.exp_outcome_ixn_type_id = eiot.id left outer join term diseaseterm on outcome.disease_id = diseaseterm.id left outer join term phenotypeterm on outcome.phenotype_id = phenotypeterm.id left outer join term receptorterm on receptor.term_id = receptorterm.id inner join reference r on e.reference_id = r.id left outer join exp_stressor_stressor_src esss on stressor.id = esss.exp_stressor_id left outer join exp_stressor_src_type stressorsrctype on esss.exp_stressor_src_type_id = stressorsrctype.id left outer join exp_receptor_tobacco_use receptortobaccouse on receptor.id = receptortobaccouse.exp_receptor_id left outer join exp_receptor_race receptorrace on receptor.id = receptorrace.exp_receptor_id left outer join exp_event_assay_method eventassaymethod on event.id = eventassaymethod.exp_event_id left outer join exp_event_location eventlocation on event.id = eventlocation.exp_event_id left outer join exp_anatomy expanatomy on outcome.id = expanatomy.exp_outcome_id left outer join term anatomyterm on expanatomy.anatomy_id = anatomyterm.id left outer join country on eventlocation.country_id = country.id left outer join exp_event_project eventproject on event.id = eventproject.exp_event_id left outer join reference_exp referenceexp on e.reference_acc_txt = referenceexp.reference_acc_txt and e.reference_acc_db_id = referenceexp.reference_acc_db_id left outer join exp_study_factor expstudyfactor on referenceexp.id = expstudyfactor.reference_exp_id where exposuremarkerterm.id = ? or receptorterm.id = ? group by chemnm, chemnmhtml, chemnmsort, chemacc, stressorsrcdetails, stressorsampleqty, stressornote, receptorterms, medium, mediumacctxt, assayedmarkers, assaylevel, measurement, measurementstat, assaynote, outcomerltnp, diseasefield, phenotypefield, phenotypeactiondegreetypenm, ref, r.abbr_authors_txt, collectionstartandendyr, receptorid, detectionlimit, detectionlimituom, detectionfreq, eventnote, outcomenote, eventid order by chemnmsort limit ?;Times Reported Time consuming queries #3
Day Hour Count Duration Avg duration Jan 29 00 9 10s815ms 1s201ms 01 22 25s849ms 1s174ms 02 23 26s985ms 1s173ms 03 20 23s952ms 1s197ms 04 1 1s139ms 1s139ms 08 6 7s69ms 1s178ms 09 7 8s474ms 1s210ms 10 13 15s686ms 1s206ms 11 16 19s171ms 1s198ms 12 14 16s693ms 1s192ms 13 21 24s837ms 1s182ms 14 17 20s131ms 1s184ms 15 19 22s980ms 1s209ms 16 14 16s502ms 1s178ms 17 26 30s674ms 1s179ms 18 20 23s972ms 1s198ms 19 18 21s51ms 1s169ms 20 14 16s333ms 1s166ms 21 21 24s840ms 1s182ms 22 20 23s886ms 1s194ms 23 26 31s88ms 1s195ms [ User: pubeu - Total duration: 1m36s - Times executed: 81 ]
-
SELECT DISTINCT stressorTerm.nm as chemNm, stressorTerm.nm_html as chemNmHtml, stressorTerm.nm_sort as chemNmSort, stressorTerm.acc_txt as chemAcc, ( SELECT STRING_AGG(distinct stressorSrcType.nm || '^' || stressorSrcType.cd, '|')) as stressorSrcTypeNm, stressor.src_details as stressorSrcDetails, stressor.sample_qty as stressorSampleQty, stressor.note as stressorNote, receptor.qty as nbrReceptors, receptor.description as receptors, receptor.note as receptorNotes, receptorTerm.nm || '^' || ( select cd from object_type where id = receptor.object_type_id) || '^' || receptorTerm.nm_html || '^' || receptorTerm.acc_txt || '^' || receptorTerm.acc_db_cd as receptorTerms, ( SELECT STRING_AGG(distinct receptorTobaccoUse.tobacco_use_nm || '^' || receptorTobaccoUse.pct, ' | ')) as smokerStatus, receptor.age as ageRange, receptor.age_uom_nm as ageUOMNm, receptor.age_qualifier_nm as ageQualifierNm, receptor.gender_nm as genderNmSearch, receptor.id receptorID, ( SELECT STRING_AGG(pct || '^' || gender_nm || '^' || gender_nm_html, '|') from exp_receptor_gender where exp_receptor_id = receptor.id) as genderDetails, ( SELECT STRING_AGG(DISTINCT receptorRace.race_nm || '^' || receptorRace.pct, ' | ')) as receptorRace, ( SELECT STRING_AGG(DISTINCT eventAssayMethod.nm, ' | ')) as assayMethods, event.medium_nm as medium, event.medium_term_acc_txt as mediumAccTxt, ( SELECT STRING_AGG(DISTINCT eventProject.project_nm, ' | ')) as associatedStudyTitles, event.collection_start_yr || '-' || event.collection_end_yr as collectionStartAndEndYr, event.detection_limit as detectionLimit, event.detection_limit_uom as detectionLimitUOM, event.detection_freq as detectionFreq, event.note as eventNote, ( SELECT STRING_AGG(DISTINCT eventLocation.geographic_region_nm, ' | ')) as stateOrProvince, ( SELECT STRING_AGG(DISTINCT eventLocation.locality_txt, ' | ')) as localityTxt, ( SELECT STRING_AGG(distinct country.nm, ' | ')) as studyCountries, exposureMarkerTerm.nm || '^' || ( select cd from object_type where id = exposureMarkerTerm.object_type_id) || '^' || exposureMarkerTerm.nm_html || '^' || exposureMarkerTerm.acc_txt || '^' || exposureMarkerTerm.acc_db_cd as assayedMarkers, event.exp_marker_lvl as assayLevel, assay_uom as measurement, assay_measurement_stat as measurementStat, assay_note as assayNote, eiot.description as outcomeRltnp, diseaseTerm.nm || '^' || 'disease' || '^' || diseaseTerm.nm_html || '^' || diseaseTerm.acc_txt || '^' || diseaseTerm.acc_db_cd as diseaseField, phenotypeTerm.nm || '^' || 'go' || '^' || phenotypeTerm.nm_html || '^' || phenotypeTerm.acc_txt || '^' || phenotypeTerm.acc_db_cd as phenotypeField, outcome.phenotype_action_degree_type_nm as phenotypeActionDegreeTypeNm, e.reference_acc_txt || '^' || r.abbr_authors_txt || '^' || r.pub_start_yr as ref, r.abbr_authors_txt as abbrAuthorsTxt, ( SELECT STRING_AGG(DISTINCT expStudyFactor.study_factor_nm, ' | ')) as studyFactorNms, ( SELECT STRING_AGG(distinct anatomyTerm.nm_html || '^' || anatomyTerm.acc_txt || '^' || anatomyTerm.id || '^' || anatomyTerm.acc_db_cd || '^' || anatomyTerm.nm, '|')) as anatomyTerms, outcome.note as outcomeNote, eventLocation.exp_event_id as eventID, COUNT(*) OVER () fullRowCount FROM exposure e inner join exp_stressor stressor ON e.exp_stressor_id = stressor.id inner join term stressorTerm ON stressor.chem_id = stressorTerm.id left outer join exp_receptor receptor ON e.exp_receptor_id = receptor.id left outer join exp_event event ON e.exp_event_id = event.id left outer join term exposureMarkerTerm ON event.exp_marker_term_id = exposureMarkerTerm.id left outer join exp_outcome outcome ON e.exp_outcome_id = outcome.id left outer join exp_outcome_ixn_type eiot ON outcome.exp_outcome_ixn_type_id = eiot.id left outer join term diseaseTerm ON outcome.disease_id = diseaseTerm.id left outer join term phenotypeTerm ON outcome.phenotype_id = phenotypeTerm.id left outer join term receptorTerm ON receptor.term_id = receptorTerm.id inner join reference r ON e.reference_id = r.id left outer join exp_stressor_stressor_src esss ON stressor.id = esss.exp_stressor_id left outer join exp_stressor_src_type stressorSrcType ON esss.exp_stressor_src_type_id = stressorSrcType.id left outer join exp_receptor_tobacco_use receptorTobaccoUse ON receptor.id = receptorTobaccoUse.exp_receptor_id left outer join exp_receptor_race receptorRace ON receptor.id = receptorRace.exp_receptor_id left outer join exp_event_assay_method eventAssayMethod ON event.id = eventAssayMethod.exp_event_id left outer join exp_event_location eventLocation ON event.id = eventLocation.exp_event_id left outer join exp_anatomy expAnatomy ON outcome.id = expAnatomy.exp_outcome_id Left outer join term anatomyTerm ON expAnatomy.anatomy_id = anatomyTerm.id left outer join country ON eventLocation.country_id = country.id left outer join exp_event_project eventProject ON event.id = eventProject.exp_event_id left outer join reference_exp referenceExp on e.reference_acc_txt = referenceExp.reference_acc_txt and e.reference_acc_db_id = referenceExp.reference_acc_db_id left outer join exp_study_factor expStudyFactor on referenceExp.id = expStudyFactor.reference_exp_id where exposureMarkerTerm.id = '2078795' or receptorTerm.id = '2078795' GROUP BY chemNm, chemNmHtml, chemNmSort, chemAcc, stressorSrcDetails, stressorSampleQty, stressorNote, receptorTerms, medium, mediumAccTxt, assayedMarkers, assayLevel, measurement, measurementStat, assayNote, outcomeRltnp, diseaseField, phenotypeField, phenotypeActionDegreeTypeNm, ref, r.abbr_authors_txt, collectionStartAndEndYr, receptorID, detectionLimit, detectionLimitUOM, detectionFreq, eventNote, outcomeNote, eventID order by chemNmSort LIMIT 50;
Date: 2025-01-29 18:06:40 Duration: 1s339ms Bind query: yes
-
SELECT DISTINCT stressorTerm.nm as chemNm, stressorTerm.nm_html as chemNmHtml, stressorTerm.nm_sort as chemNmSort, stressorTerm.acc_txt as chemAcc, ( SELECT STRING_AGG(distinct stressorSrcType.nm || '^' || stressorSrcType.cd, '|')) as stressorSrcTypeNm, stressor.src_details as stressorSrcDetails, stressor.sample_qty as stressorSampleQty, stressor.note as stressorNote, receptor.qty as nbrReceptors, receptor.description as receptors, receptor.note as receptorNotes, receptorTerm.nm || '^' || ( select cd from object_type where id = receptor.object_type_id) || '^' || receptorTerm.nm_html || '^' || receptorTerm.acc_txt || '^' || receptorTerm.acc_db_cd as receptorTerms, ( SELECT STRING_AGG(distinct receptorTobaccoUse.tobacco_use_nm || '^' || receptorTobaccoUse.pct, ' | ')) as smokerStatus, receptor.age as ageRange, receptor.age_uom_nm as ageUOMNm, receptor.age_qualifier_nm as ageQualifierNm, receptor.gender_nm as genderNmSearch, receptor.id receptorID, ( SELECT STRING_AGG(pct || '^' || gender_nm || '^' || gender_nm_html, '|') from exp_receptor_gender where exp_receptor_id = receptor.id) as genderDetails, ( SELECT STRING_AGG(DISTINCT receptorRace.race_nm || '^' || receptorRace.pct, ' | ')) as receptorRace, ( SELECT STRING_AGG(DISTINCT eventAssayMethod.nm, ' | ')) as assayMethods, event.medium_nm as medium, event.medium_term_acc_txt as mediumAccTxt, ( SELECT STRING_AGG(DISTINCT eventProject.project_nm, ' | ')) as associatedStudyTitles, event.collection_start_yr || '-' || event.collection_end_yr as collectionStartAndEndYr, event.detection_limit as detectionLimit, event.detection_limit_uom as detectionLimitUOM, event.detection_freq as detectionFreq, event.note as eventNote, ( SELECT STRING_AGG(DISTINCT eventLocation.geographic_region_nm, ' | ')) as stateOrProvince, ( SELECT STRING_AGG(DISTINCT eventLocation.locality_txt, ' | ')) as localityTxt, ( SELECT STRING_AGG(distinct country.nm, ' | ')) as studyCountries, exposureMarkerTerm.nm || '^' || ( select cd from object_type where id = exposureMarkerTerm.object_type_id) || '^' || exposureMarkerTerm.nm_html || '^' || exposureMarkerTerm.acc_txt || '^' || exposureMarkerTerm.acc_db_cd as assayedMarkers, event.exp_marker_lvl as assayLevel, assay_uom as measurement, assay_measurement_stat as measurementStat, assay_note as assayNote, eiot.description as outcomeRltnp, diseaseTerm.nm || '^' || 'disease' || '^' || diseaseTerm.nm_html || '^' || diseaseTerm.acc_txt || '^' || diseaseTerm.acc_db_cd as diseaseField, phenotypeTerm.nm || '^' || 'go' || '^' || phenotypeTerm.nm_html || '^' || phenotypeTerm.acc_txt || '^' || phenotypeTerm.acc_db_cd as phenotypeField, outcome.phenotype_action_degree_type_nm as phenotypeActionDegreeTypeNm, e.reference_acc_txt || '^' || r.abbr_authors_txt || '^' || r.pub_start_yr as ref, r.abbr_authors_txt as abbrAuthorsTxt, ( SELECT STRING_AGG(DISTINCT expStudyFactor.study_factor_nm, ' | ')) as studyFactorNms, ( SELECT STRING_AGG(distinct anatomyTerm.nm_html || '^' || anatomyTerm.acc_txt || '^' || anatomyTerm.id || '^' || anatomyTerm.acc_db_cd || '^' || anatomyTerm.nm, '|')) as anatomyTerms, outcome.note as outcomeNote, eventLocation.exp_event_id as eventID, COUNT(*) OVER () fullRowCount FROM exposure e inner join exp_stressor stressor ON e.exp_stressor_id = stressor.id inner join term stressorTerm ON stressor.chem_id = stressorTerm.id left outer join exp_receptor receptor ON e.exp_receptor_id = receptor.id left outer join exp_event event ON e.exp_event_id = event.id left outer join term exposureMarkerTerm ON event.exp_marker_term_id = exposureMarkerTerm.id left outer join exp_outcome outcome ON e.exp_outcome_id = outcome.id left outer join exp_outcome_ixn_type eiot ON outcome.exp_outcome_ixn_type_id = eiot.id left outer join term diseaseTerm ON outcome.disease_id = diseaseTerm.id left outer join term phenotypeTerm ON outcome.phenotype_id = phenotypeTerm.id left outer join term receptorTerm ON receptor.term_id = receptorTerm.id inner join reference r ON e.reference_id = r.id left outer join exp_stressor_stressor_src esss ON stressor.id = esss.exp_stressor_id left outer join exp_stressor_src_type stressorSrcType ON esss.exp_stressor_src_type_id = stressorSrcType.id left outer join exp_receptor_tobacco_use receptorTobaccoUse ON receptor.id = receptorTobaccoUse.exp_receptor_id left outer join exp_receptor_race receptorRace ON receptor.id = receptorRace.exp_receptor_id left outer join exp_event_assay_method eventAssayMethod ON event.id = eventAssayMethod.exp_event_id left outer join exp_event_location eventLocation ON event.id = eventLocation.exp_event_id left outer join exp_anatomy expAnatomy ON outcome.id = expAnatomy.exp_outcome_id Left outer join term anatomyTerm ON expAnatomy.anatomy_id = anatomyTerm.id left outer join country ON eventLocation.country_id = country.id left outer join exp_event_project eventProject ON event.id = eventProject.exp_event_id left outer join reference_exp referenceExp on e.reference_acc_txt = referenceExp.reference_acc_txt and e.reference_acc_db_id = referenceExp.reference_acc_db_id left outer join exp_study_factor expStudyFactor on referenceExp.id = expStudyFactor.reference_exp_id where exposureMarkerTerm.id = '2057877' or receptorTerm.id = '2057877' GROUP BY chemNm, chemNmHtml, chemNmSort, chemAcc, stressorSrcDetails, stressorSampleQty, stressorNote, receptorTerms, medium, mediumAccTxt, assayedMarkers, assayLevel, measurement, measurementStat, assayNote, outcomeRltnp, diseaseField, phenotypeField, phenotypeActionDegreeTypeNm, ref, r.abbr_authors_txt, collectionStartAndEndYr, receptorID, detectionLimit, detectionLimitUOM, detectionFreq, eventNote, outcomeNote, eventID order by chemNmSort LIMIT 50;
Date: 2025-01-29 15:31:07 Duration: 1s296ms Bind query: yes
-
SELECT DISTINCT stressorTerm.nm as chemNm, stressorTerm.nm_html as chemNmHtml, stressorTerm.nm_sort as chemNmSort, stressorTerm.acc_txt as chemAcc, ( SELECT STRING_AGG(distinct stressorSrcType.nm || '^' || stressorSrcType.cd, '|')) as stressorSrcTypeNm, stressor.src_details as stressorSrcDetails, stressor.sample_qty as stressorSampleQty, stressor.note as stressorNote, receptor.qty as nbrReceptors, receptor.description as receptors, receptor.note as receptorNotes, receptorTerm.nm || '^' || ( select cd from object_type where id = receptor.object_type_id) || '^' || receptorTerm.nm_html || '^' || receptorTerm.acc_txt || '^' || receptorTerm.acc_db_cd as receptorTerms, ( SELECT STRING_AGG(distinct receptorTobaccoUse.tobacco_use_nm || '^' || receptorTobaccoUse.pct, ' | ')) as smokerStatus, receptor.age as ageRange, receptor.age_uom_nm as ageUOMNm, receptor.age_qualifier_nm as ageQualifierNm, receptor.gender_nm as genderNmSearch, receptor.id receptorID, ( SELECT STRING_AGG(pct || '^' || gender_nm || '^' || gender_nm_html, '|') from exp_receptor_gender where exp_receptor_id = receptor.id) as genderDetails, ( SELECT STRING_AGG(DISTINCT receptorRace.race_nm || '^' || receptorRace.pct, ' | ')) as receptorRace, ( SELECT STRING_AGG(DISTINCT eventAssayMethod.nm, ' | ')) as assayMethods, event.medium_nm as medium, event.medium_term_acc_txt as mediumAccTxt, ( SELECT STRING_AGG(DISTINCT eventProject.project_nm, ' | ')) as associatedStudyTitles, event.collection_start_yr || '-' || event.collection_end_yr as collectionStartAndEndYr, event.detection_limit as detectionLimit, event.detection_limit_uom as detectionLimitUOM, event.detection_freq as detectionFreq, event.note as eventNote, ( SELECT STRING_AGG(DISTINCT eventLocation.geographic_region_nm, ' | ')) as stateOrProvince, ( SELECT STRING_AGG(DISTINCT eventLocation.locality_txt, ' | ')) as localityTxt, ( SELECT STRING_AGG(distinct country.nm, ' | ')) as studyCountries, exposureMarkerTerm.nm || '^' || ( select cd from object_type where id = exposureMarkerTerm.object_type_id) || '^' || exposureMarkerTerm.nm_html || '^' || exposureMarkerTerm.acc_txt || '^' || exposureMarkerTerm.acc_db_cd as assayedMarkers, event.exp_marker_lvl as assayLevel, assay_uom as measurement, assay_measurement_stat as measurementStat, assay_note as assayNote, eiot.description as outcomeRltnp, diseaseTerm.nm || '^' || 'disease' || '^' || diseaseTerm.nm_html || '^' || diseaseTerm.acc_txt || '^' || diseaseTerm.acc_db_cd as diseaseField, phenotypeTerm.nm || '^' || 'go' || '^' || phenotypeTerm.nm_html || '^' || phenotypeTerm.acc_txt || '^' || phenotypeTerm.acc_db_cd as phenotypeField, outcome.phenotype_action_degree_type_nm as phenotypeActionDegreeTypeNm, e.reference_acc_txt || '^' || r.abbr_authors_txt || '^' || r.pub_start_yr as ref, r.abbr_authors_txt as abbrAuthorsTxt, ( SELECT STRING_AGG(DISTINCT expStudyFactor.study_factor_nm, ' | ')) as studyFactorNms, ( SELECT STRING_AGG(distinct anatomyTerm.nm_html || '^' || anatomyTerm.acc_txt || '^' || anatomyTerm.id || '^' || anatomyTerm.acc_db_cd || '^' || anatomyTerm.nm, '|')) as anatomyTerms, outcome.note as outcomeNote, eventLocation.exp_event_id as eventID, COUNT(*) OVER () fullRowCount FROM exposure e inner join exp_stressor stressor ON e.exp_stressor_id = stressor.id inner join term stressorTerm ON stressor.chem_id = stressorTerm.id left outer join exp_receptor receptor ON e.exp_receptor_id = receptor.id left outer join exp_event event ON e.exp_event_id = event.id left outer join term exposureMarkerTerm ON event.exp_marker_term_id = exposureMarkerTerm.id left outer join exp_outcome outcome ON e.exp_outcome_id = outcome.id left outer join exp_outcome_ixn_type eiot ON outcome.exp_outcome_ixn_type_id = eiot.id left outer join term diseaseTerm ON outcome.disease_id = diseaseTerm.id left outer join term phenotypeTerm ON outcome.phenotype_id = phenotypeTerm.id left outer join term receptorTerm ON receptor.term_id = receptorTerm.id inner join reference r ON e.reference_id = r.id left outer join exp_stressor_stressor_src esss ON stressor.id = esss.exp_stressor_id left outer join exp_stressor_src_type stressorSrcType ON esss.exp_stressor_src_type_id = stressorSrcType.id left outer join exp_receptor_tobacco_use receptorTobaccoUse ON receptor.id = receptorTobaccoUse.exp_receptor_id left outer join exp_receptor_race receptorRace ON receptor.id = receptorRace.exp_receptor_id left outer join exp_event_assay_method eventAssayMethod ON event.id = eventAssayMethod.exp_event_id left outer join exp_event_location eventLocation ON event.id = eventLocation.exp_event_id left outer join exp_anatomy expAnatomy ON outcome.id = expAnatomy.exp_outcome_id Left outer join term anatomyTerm ON expAnatomy.anatomy_id = anatomyTerm.id left outer join country ON eventLocation.country_id = country.id left outer join exp_event_project eventProject ON event.id = eventProject.exp_event_id left outer join reference_exp referenceExp on e.reference_acc_txt = referenceExp.reference_acc_txt and e.reference_acc_db_id = referenceExp.reference_acc_db_id left outer join exp_study_factor expStudyFactor on referenceExp.id = expStudyFactor.reference_exp_id where exposureMarkerTerm.id = '2055806' or receptorTerm.id = '2055806' GROUP BY chemNm, chemNmHtml, chemNmSort, chemAcc, stressorSrcDetails, stressorSampleQty, stressorNote, receptorTerms, medium, mediumAccTxt, assayedMarkers, assayLevel, measurement, measurementStat, assayNote, outcomeRltnp, diseaseField, phenotypeField, phenotypeActionDegreeTypeNm, ref, r.abbr_authors_txt, collectionStartAndEndYr, receptorID, detectionLimit, detectionLimitUOM, detectionFreq, eventNote, outcomeNote, eventID order by chemNmSort LIMIT 50;
Date: 2025-01-29 03:26:13 Duration: 1s295ms Bind query: yes
4 303 8m9s 1s244ms 2s82ms 1s614ms select c.nm chemnm, c.nm_html chemnmhtml, c.acc_txt chemacc, c.secondary_nm casrn, c.id chemid, d.nm diseasenm, d.acc_txt diseaseacc, d.acc_db_cd diseaseaccdbcd, d.id diseaseid, cd.network_score networkscore, cd.indirect_gene_qty inferredcount, cd.reference_qty referencecount, cd.exposure_reference_qty exposurereferencecount, case when cd.curated_reference_qty > ? then ( select string_agg(a.action_type_cd || ? || a.action_type_nm, ?) from chem_disease_axn a where a.chem_id = cd.chem_id and a.disease_id = cd.disease_id) else null end actiontypes from chem_disease cd inner join term c on cd.chem_id = c.id inner join term d on cd.disease_id = d.id where cd.chem_id in ( select p.descendant_object_id from dag_path p where p.ancestor_object_id = ?) order by actiontypes, cd.network_score desc nulls last, d.nm_sort, c.nm_sort limit ?;Times Reported Time consuming queries #4
Day Hour Count Duration Avg duration Jan 29 00 17 26s313ms 1s547ms 01 20 31s813ms 1s590ms 02 14 24s83ms 1s720ms 03 14 21s825ms 1s558ms 04 1 1s305ms 1s305ms 08 8 13s365ms 1s670ms 09 14 23s667ms 1s690ms 10 15 25s741ms 1s716ms 11 17 27s168ms 1s598ms 12 13 21s457ms 1s650ms 13 22 37s655ms 1s711ms 14 17 25s632ms 1s507ms 15 15 24s781ms 1s652ms 16 13 20s143ms 1s549ms 17 13 20s205ms 1s554ms 18 14 23s496ms 1s678ms 19 15 24s285ms 1s619ms 20 11 18s187ms 1s653ms 21 15 22s573ms 1s504ms 22 24 37s759ms 1s573ms 23 11 17s668ms 1s606ms [ User: pubeu - Total duration: 1m32s - Times executed: 58 ]
-
SELECT /* ChemDiseaseAssnsDAO */ c.nm chemnm, c.nm_html chemnmhtml, c.acc_txt chemacc, c.secondary_nm casRN, c.id chemid, d.nm diseasenm, d.acc_txt diseaseacc, d.acc_db_cd diseaseaccdbcd, d.id diseaseid, cd.network_score networkscore, cd.indirect_gene_qty inferredcount, cd.reference_qty referencecount, cd.exposure_reference_qty exposureReferenceCount, CASE WHEN cd.curated_reference_qty > 0 THEN ( SELECT STRING_AGG(a.action_type_cd || '^' || a.action_type_nm, '|') FROM chem_disease_axn a WHERE a.chem_id = cd.chem_id AND a.disease_id = cd.disease_id) ELSE NULL END actiontypes FROM chem_disease cd INNER JOIN term c ON cd.chem_id = c.id INNER JOIN term d ON cd.disease_id = d.id WHERE cd.chem_id IN ( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '1399640') ORDER BY actionTypes, cd.network_score DESC NULLS LAST, d.nm_sort, c.nm_sort LIMIT 50;
Date: 2025-01-29 09:22:50 Duration: 2s82ms Bind query: yes
-
SELECT /* ChemDiseaseAssnsDAO */ c.nm chemnm, c.nm_html chemnmhtml, c.acc_txt chemacc, c.secondary_nm casRN, c.id chemid, d.nm diseasenm, d.acc_txt diseaseacc, d.acc_db_cd diseaseaccdbcd, d.id diseaseid, cd.network_score networkscore, cd.indirect_gene_qty inferredcount, cd.reference_qty referencecount, cd.exposure_reference_qty exposureReferenceCount, CASE WHEN cd.curated_reference_qty > 0 THEN ( SELECT STRING_AGG(a.action_type_cd || '^' || a.action_type_nm, '|') FROM chem_disease_axn a WHERE a.chem_id = cd.chem_id AND a.disease_id = cd.disease_id) ELSE NULL END actiontypes FROM chem_disease cd INNER JOIN term c ON cd.chem_id = c.id INNER JOIN term d ON cd.disease_id = d.id WHERE cd.chem_id IN ( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '1399640') ORDER BY actionTypes, cd.network_score DESC NULLS LAST, d.nm_sort, c.nm_sort LIMIT 50;
Date: 2025-01-29 03:31:52 Duration: 2s70ms Bind query: yes
-
SELECT /* ChemDiseaseAssnsDAO */ c.nm chemnm, c.nm_html chemnmhtml, c.acc_txt chemacc, c.secondary_nm casRN, c.id chemid, d.nm diseasenm, d.acc_txt diseaseacc, d.acc_db_cd diseaseaccdbcd, d.id diseaseid, cd.network_score networkscore, cd.indirect_gene_qty inferredcount, cd.reference_qty referencecount, cd.exposure_reference_qty exposureReferenceCount, CASE WHEN cd.curated_reference_qty > 0 THEN ( SELECT STRING_AGG(a.action_type_cd || '^' || a.action_type_nm, '|') FROM chem_disease_axn a WHERE a.chem_id = cd.chem_id AND a.disease_id = cd.disease_id) ELSE NULL END actiontypes FROM chem_disease cd INNER JOIN term c ON cd.chem_id = c.id INNER JOIN term d ON cd.disease_id = d.id WHERE cd.chem_id IN ( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '1399640') ORDER BY actionTypes, cd.network_score DESC NULLS LAST, d.nm_sort, c.nm_sort LIMIT 50;
Date: 2025-01-29 11:07:51 Duration: 2s29ms Bind query: yes
5 169 47m 4s84ms 34m28s 16s688ms select g.nm "GeneSymbol", g.acc_txt "GeneID", d.nm "DiseaseName", d.acc_db_cd || ? || d.acc_txt "DiseaseID", case when gdr.via_chem_id is null then ( select string_agg(a.action_type_nm, ? order by a.action_type_nm) from gene_disease_axn a where a.gene_id = gdr.gene_id and a.disease_id = gdr.disease_id) else null end "DirectEvidence", c.nm "InferenceChemicalName", gdr.network_score "InferenceScore", string_agg(gdr.source_acc_txt, ? order by gdr.source_acc_txt) "OmimIDs", string_agg(r.acc_txt, ? order by r.acc_txt) "PubMedIDs" from gene_disease_reference gdr inner join term g on gdr.gene_id = g.id inner join term d on gdr.disease_id = d.id left outer join reference r on gdr.reference_id = r.id left outer join term c on gdr.via_chem_id = c.id group by g.nm, g.acc_txt, d.nm, d.acc_db_cd || ? || d.acc_txt, case when gdr.via_chem_id is null then ( select string_agg(a.action_type_nm, ? order by a.action_type_nm) from gene_disease_axn a where a.gene_id = gdr.gene_id and a.disease_id = gdr.disease_id) else null end, c.nm, gdr.network_score order by g.nm, d.nm;Times Reported Time consuming queries #5
Day Hour Count Duration Avg duration Jan 29 14 169 47m 16s688ms [ User: qaeu - Total duration: 34m28s - Times executed: 1 ]
-
SELECT /* AllGDRelationsDAO */ g.nm "GeneSymbol", g.acc_txt "GeneID", d.nm "DiseaseName", d.acc_db_cd || ':' || d.acc_txt "DiseaseID", CASE WHEN gdr.via_chem_id IS NULL THEN ( SELECT STRING_AGG(a.action_type_nm, '|' ORDER BY a.action_type_nm) FROM gene_disease_axn a WHERE a.gene_id = gdr.gene_id AND a.disease_id = gdr.disease_id) ELSE NULL END "DirectEvidence", c.nm "InferenceChemicalName", gdr.network_score "InferenceScore", STRING_AGG(gdr.source_acc_txt, '|' ORDER BY gdr.source_acc_txt) "OmimIDs", STRING_AGG(r.acc_txt, '|' ORDER BY r.acc_txt) "PubMedIDs" FROM gene_disease_reference gdr INNER JOIN term g ON gdr.gene_id = g.id INNER JOIN term d ON gdr.disease_id = d.id LEFT OUTER JOIN reference r ON gdr.reference_id = r.id LEFT OUTER JOIN term c ON gdr.via_chem_id = c.id GROUP BY g.nm, g.acc_txt, d.nm, d.acc_db_cd || ':' || d.acc_txt, CASE WHEN gdr.via_chem_id IS NULL THEN ( SELECT STRING_AGG(a.action_type_nm, '|' ORDER BY a.action_type_nm) FROM gene_disease_axn a WHERE a.gene_id = gdr.gene_id AND a.disease_id = gdr.disease_id) ELSE NULL END, c.nm, gdr.network_score ORDER BY g.nm, d.nm;
Date: 2025-01-29 14:15:20 Duration: 34m28s Database: ctdprd51 User: qaeu Bind query: yes
-
SELECT /* AllGDRelationsDAO */ g.nm "GeneSymbol", g.acc_txt "GeneID", d.nm "DiseaseName", d.acc_db_cd || ':' || d.acc_txt "DiseaseID", CASE WHEN gdr.via_chem_id IS NULL THEN ( SELECT STRING_AGG(a.action_type_nm, '|' ORDER BY a.action_type_nm) FROM gene_disease_axn a WHERE a.gene_id = gdr.gene_id AND a.disease_id = gdr.disease_id) ELSE NULL END "DirectEvidence", c.nm "InferenceChemicalName", gdr.network_score "InferenceScore", STRING_AGG(gdr.source_acc_txt, '|' ORDER BY gdr.source_acc_txt) "OmimIDs", STRING_AGG(r.acc_txt, '|' ORDER BY r.acc_txt) "PubMedIDs" FROM gene_disease_reference gdr INNER JOIN term g ON gdr.gene_id = g.id INNER JOIN term d ON gdr.disease_id = d.id LEFT OUTER JOIN reference r ON gdr.reference_id = r.id LEFT OUTER JOIN term c ON gdr.via_chem_id = c.id GROUP BY g.nm, g.acc_txt, d.nm, d.acc_db_cd || ':' || d.acc_txt, CASE WHEN gdr.via_chem_id IS NULL THEN ( SELECT STRING_AGG(a.action_type_nm, '|' ORDER BY a.action_type_nm) FROM gene_disease_axn a WHERE a.gene_id = gdr.gene_id AND a.disease_id = gdr.disease_id) ELSE NULL END, c.nm, gdr.network_score ORDER BY g.nm, d.nm;
Date: 2025-01-29 14:34:59 Duration: 5s732ms Bind query: yes
-
SELECT /* AllGDRelationsDAO */ g.nm "GeneSymbol", g.acc_txt "GeneID", d.nm "DiseaseName", d.acc_db_cd || ':' || d.acc_txt "DiseaseID", CASE WHEN gdr.via_chem_id IS NULL THEN ( SELECT STRING_AGG(a.action_type_nm, '|' ORDER BY a.action_type_nm) FROM gene_disease_axn a WHERE a.gene_id = gdr.gene_id AND a.disease_id = gdr.disease_id) ELSE NULL END "DirectEvidence", c.nm "InferenceChemicalName", gdr.network_score "InferenceScore", STRING_AGG(gdr.source_acc_txt, '|' ORDER BY gdr.source_acc_txt) "OmimIDs", STRING_AGG(r.acc_txt, '|' ORDER BY r.acc_txt) "PubMedIDs" FROM gene_disease_reference gdr INNER JOIN term g ON gdr.gene_id = g.id INNER JOIN term d ON gdr.disease_id = d.id LEFT OUTER JOIN reference r ON gdr.reference_id = r.id LEFT OUTER JOIN term c ON gdr.via_chem_id = c.id GROUP BY g.nm, g.acc_txt, d.nm, d.acc_db_cd || ':' || d.acc_txt, CASE WHEN gdr.via_chem_id IS NULL THEN ( SELECT STRING_AGG(a.action_type_nm, '|' ORDER BY a.action_type_nm) FROM gene_disease_axn a WHERE a.gene_id = gdr.gene_id AND a.disease_id = gdr.disease_id) ELSE NULL END, c.nm, gdr.network_score ORDER BY g.nm, d.nm;
Date: 2025-01-29 14:28:17 Duration: 5s467ms Bind query: yes
6 143 3m32s 1s442ms 2s132ms 1s485ms select coalesce(d.abbr_display, d.nm_display) nm # ?, d.description # ?, coalesce(d.abbr, d.nm) anchor # ?, get_homepage_url (d.id) url # ? from db d # ? where d.id in (# ? select l.db_id # ? from db_link l # ? where l.type_cd = ? # ? and l.object_type_id = ?) # ? order by ?;Times Reported Time consuming queries #6
Day Hour Count Duration Avg duration Jan 29 00 2 2s903ms 1s451ms 01 2 3s9ms 1s504ms 02 4 5s834ms 1s458ms 03 1 1s573ms 1s573ms 08 20 30s445ms 1s522ms 09 22 32s658ms 1s484ms 10 4 5s918ms 1s479ms 11 4 5s960ms 1s490ms 12 5 7s471ms 1s494ms 13 5 7s464ms 1s492ms 14 9 13s516ms 1s501ms 15 8 12s54ms 1s506ms 16 3 4s387ms 1s462ms 17 8 11s726ms 1s465ms 18 14 20s575ms 1s469ms 19 7 10s289ms 1s469ms 21 8 11s705ms 1s463ms 22 8 11s790ms 1s473ms 23 9 13s156ms 1s461ms [ User: pubeu - Total duration: 49s17ms - Times executed: 33 ]
-
SELECT COALESCE(d.abbr_display, d.nm_display) nm # 015, d.description # 015, COALESCE(d.abbr, d.nm) anchor # 015, get_homepage_url (d.id) url # 015 FROM db d # 015 WHERE d.id IN (# 015 SELECT l.db_id # 015 FROM db_link l # 015 WHERE l.type_cd = 'X' # 015 AND l.object_type_id = 4) # 015 ORDER BY 1;
Date: 2025-01-29 08:19:29 Duration: 2s132ms Bind query: yes
-
SELECT COALESCE(d.abbr_display, d.nm_display) nm # 015, d.description # 015, COALESCE(d.abbr, d.nm) anchor # 015, get_homepage_url (d.id) url # 015 FROM db d # 015 WHERE d.id IN (# 015 SELECT l.db_id # 015 FROM db_link l # 015 WHERE l.type_cd = 'X' # 015 AND l.object_type_id = 4) # 015 ORDER BY 1;
Date: 2025-01-29 08:31:42 Duration: 1s641ms Database: ctdprd51 User: pubeu Bind query: yes
-
SELECT COALESCE(d.abbr_display, d.nm_display) nm # 015, d.description # 015, COALESCE(d.abbr, d.nm) anchor # 015, get_homepage_url (d.id) url # 015 FROM db d # 015 WHERE d.id IN (# 015 SELECT l.db_id # 015 FROM db_link l # 015 WHERE l.type_cd = 'X' # 015 AND l.object_type_id = 4) # 015 ORDER BY 1;
Date: 2025-01-29 15:37:07 Duration: 1s607ms Bind query: yes
7 124 5m20s 2s476ms 4s443ms 2s581ms select root_id, edit.get_ixn_prose (i.id) from edit.ixn i, edit.reference_ixn ri where ri.ixn_id = i.id and ri.reference_acc_txt = ? order by root_id desc;Times Reported Time consuming queries #7
Day Hour Count Duration Avg duration Jan 29 14 85 3m39s 2s584ms 15 12 30s588ms 2s549ms 16 27 1m9s 2s587ms -
select root_id, edit.get_ixn_prose (i.id) from edit.ixn i, edit.reference_ixn ri where ri.ixn_id = i.id and ri.reference_acc_txt = '39298647' order by root_id desc;
Date: 2025-01-29 14:05:25 Duration: 4s443ms Bind query: yes
-
select root_id, edit.get_ixn_prose (i.id) from edit.ixn i, edit.reference_ixn ri where ri.ixn_id = i.id and ri.reference_acc_txt = '39298647' order by root_id desc;
Date: 2025-01-29 16:39:44 Duration: 2s792ms Bind query: yes
-
select root_id, edit.get_ixn_prose (i.id) from edit.ixn i, edit.reference_ixn ri where ri.ixn_id = i.id and ri.reference_acc_txt = '39298647' order by root_id desc;
Date: 2025-01-29 14:25:30 Duration: 2s758ms Bind query: yes
8 90 5m24s 1s6ms 11s528ms 3s605ms 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 #8
Day Hour Count Duration Avg duration Jan 29 00 4 8s949ms 2s237ms 01 7 29s892ms 4s270ms 02 3 10s589ms 3s529ms 03 2 4s177ms 2s88ms 08 5 17s392ms 3s478ms 09 7 17s874ms 2s553ms 10 3 9s975ms 3s325ms 11 1 4s11ms 4s11ms 12 3 16s892ms 5s630ms 13 5 22s397ms 4s479ms 14 5 28s680ms 5s736ms 16 5 19s600ms 3s920ms 17 7 20s682ms 2s954ms 18 6 28s604ms 4s767ms 19 3 6s925ms 2s308ms 20 6 19s851ms 3s308ms 21 3 6s180ms 2s60ms 22 10 31s462ms 3s146ms 23 5 20s372ms 4s74ms [ User: pubeu - Total duration: 54s746ms - Times executed: 15 ]
-
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 = '2090292') ORDER BY actionTypes, gd.network_score DESC NULLS LAST, g.nm_sort, d.nm_sort LIMIT 50;
Date: 2025-01-29 13:24:33 Duration: 11s528ms 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 = '2090292') ORDER BY actionTypes, gd.network_score DESC NULLS LAST, g.nm_sort, d.nm_sort LIMIT 50;
Date: 2025-01-29 14:47:05 Duration: 11s490ms 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 = '2092048') ORDER BY actionTypes, gd.network_score DESC NULLS LAST, g.nm_sort, d.nm_sort LIMIT 50;
Date: 2025-01-29 08:45:09 Duration: 10s641ms Bind query: yes
9 84 10m22s 1s25ms 28s447ms 7s408ms select sq.*, count(*) over () fullrowcount from ( select distinct gt.nm gonm, gt.nm_html gonmhtml, gt.nm_sort gonmsort, gt.acc_txt goacc, gt.object_id goid, g.nm genesymbol, g.nm_sort genesymbolsort, g.acc_txt geneacc, g.acc_db_cd geneaccdbcd, g.id geneid from dag_node gt inner join gene_go_annot gga on gt.object_id = gga.go_term_id inner join term g on gga.gene_id = g.id where gt.id in ( select p.descendant_dag_node_id from dag_path p where p.ancestor_object_id = ?) and gga.is_not = false) sq order by sq.gonmsort, sq.genesymbolsort limit ?;Times Reported Time consuming queries #9
Day Hour Count Duration Avg duration Jan 29 00 3 4s753ms 1s584ms 01 4 42s152ms 10s538ms 02 6 36s455ms 6s75ms 03 4 30s152ms 7s538ms 04 1 4s570ms 4s570ms 08 3 11s80ms 3s693ms 09 6 35s625ms 5s937ms 11 5 51s627ms 10s325ms 12 4 49s626ms 12s406ms 13 5 1m13s 14s702ms 14 6 27s968ms 4s661ms 15 3 30s475ms 10s158ms 16 6 22s415ms 3s735ms 17 4 33s516ms 8s379ms 18 5 15s863ms 3s172ms 20 8 41s858ms 5s232ms 21 9 1m31s 10s200ms 22 2 18s853ms 9s426ms [ User: pubeu - Total duration: 1m37s - Times executed: 15 ]
-
SELECT /* GoGenesDAO */ sq.*, COUNT(*) OVER () fullRowCount FROM ( SELECT DISTINCT gt.nm gonm, gt.nm_html gonmhtml, gt.nm_sort gonmsort, gt.acc_txt goacc, gt.object_id goid, g.nm genesymbol, g.nm_sort genesymbolsort, g.acc_txt geneacc, g.acc_db_cd geneaccdbcd, g.id geneid FROM dag_node gt INNER JOIN gene_go_annot gga ON gt.object_id = gga.go_term_id INNER JOIN term g ON gga.gene_id = g.id WHERE gt.id IN ( SELECT p.descendant_dag_node_id FROM dag_path p WHERE p.ancestor_object_id = '1222378') AND gga.is_not = false) sq ORDER BY sq.gonmsort, sq.genesymbolsort LIMIT 50;
Date: 2025-01-29 13:45:27 Duration: 28s447ms Database: ctdprd51 User: pubeu Bind query: yes
-
SELECT /* GoGenesDAO */ sq.*, COUNT(*) OVER () fullRowCount FROM ( SELECT DISTINCT gt.nm gonm, gt.nm_html gonmhtml, gt.nm_sort gonmsort, gt.acc_txt goacc, gt.object_id goid, g.nm genesymbol, g.nm_sort genesymbolsort, g.acc_txt geneacc, g.acc_db_cd geneaccdbcd, g.id geneid FROM dag_node gt INNER JOIN gene_go_annot gga ON gt.object_id = gga.go_term_id INNER JOIN term g ON gga.gene_id = g.id WHERE gt.id IN ( SELECT p.descendant_dag_node_id FROM dag_path p WHERE p.ancestor_object_id = '1222378') AND gga.is_not = false) sq ORDER BY sq.gonmsort, sq.genesymbolsort LIMIT 50;
Date: 2025-01-29 15:17:21 Duration: 26s337ms Bind query: yes
-
SELECT /* GoGenesDAO */ sq.*, COUNT(*) OVER () fullRowCount FROM ( SELECT DISTINCT gt.nm gonm, gt.nm_html gonmhtml, gt.nm_sort gonmsort, gt.acc_txt goacc, gt.object_id goid, g.nm genesymbol, g.nm_sort genesymbolsort, g.acc_txt geneacc, g.acc_db_cd geneaccdbcd, g.id geneid FROM dag_node gt INNER JOIN gene_go_annot gga ON gt.object_id = gga.go_term_id INNER JOIN term g ON gga.gene_id = g.id WHERE gt.id IN ( SELECT p.descendant_dag_node_id FROM dag_path p WHERE p.ancestor_object_id = '1222378') AND gga.is_not = false) sq ORDER BY sq.gonmsort, sq.genesymbolsort LIMIT 50;
Date: 2025-01-29 02:22:10 Duration: 25s642ms Bind query: yes
10 77 1m39s 1s182ms 1s591ms 1s297ms select distinct stressorterm.nm as chemnm, stressorterm.nm_html as chemnmhtml, stressorterm.nm_sort as chemnmsort, stressorterm.acc_txt as chemacc, ( select string_agg(distinct stressorsrctype.nm || ? || stressorsrctype.cd, ?)) as stressorsrctypenm, stressor.src_details as stressorsrcdetails, stressor.sample_qty as stressorsampleqty, stressor.note as stressornote, receptor.qty as nbrreceptors, receptor.description as receptors, receptor.note as receptornotes, receptorterm.nm || ? || ( select cd from object_type where id = receptor.object_type_id) || ? || receptorterm.nm_html || ? || receptorterm.acc_txt || ? || receptorterm.acc_db_cd as receptorterms, ( select string_agg(distinct receptortobaccouse.tobacco_use_nm || ? || receptortobaccouse.pct, ?)) as smokerstatus, receptor.age as agerange, receptor.age_uom_nm as ageuomnm, receptor.age_qualifier_nm as agequalifiernm, receptor.gender_nm as gendernmsearch, receptor.id receptorid, ( select string_agg(pct || ? || gender_nm || ? || gender_nm_html, ?) from exp_receptor_gender where exp_receptor_id = receptor.id) as genderdetails, ( select string_agg(distinct receptorrace.race_nm || ? || receptorrace.pct, ?)) as receptorrace, ( select string_agg(distinct eventassaymethod.nm, ?)) as assaymethods, event.medium_nm as medium, event.medium_term_acc_txt as mediumacctxt, ( select string_agg(distinct eventproject.project_nm, ?)) as associatedstudytitles, event.collection_start_yr || ? || event.collection_end_yr as collectionstartandendyr, event.detection_limit as detectionlimit, event.detection_limit_uom as detectionlimituom, event.detection_freq as detectionfreq, event.note as eventnote, ( select string_agg(distinct eventlocation.geographic_region_nm, ?)) as stateorprovince, ( select string_agg(distinct eventlocation.locality_txt, ?)) as localitytxt, ( select string_agg(distinct country.nm, ?)) as studycountries, exposuremarkerterm.nm || ? || ( select cd from object_type where id = exposuremarkerterm.object_type_id) || ? || exposuremarkerterm.nm_html || ? || exposuremarkerterm.acc_txt || ? || exposuremarkerterm.acc_db_cd as assayedmarkers, event.exp_marker_lvl as assaylevel, assay_uom as measurement, assay_measurement_stat as measurementstat, assay_note as assaynote, eiot.description as outcomerltnp, diseaseterm.nm || ? || ? || ? || diseaseterm.nm_html || ? || diseaseterm.acc_txt || ? || diseaseterm.acc_db_cd as diseasefield, phenotypeterm.nm || ? || ? || ? || phenotypeterm.nm_html || ? || phenotypeterm.acc_txt || ? || phenotypeterm.acc_db_cd as phenotypefield, outcome.phenotype_action_degree_type_nm as phenotypeactiondegreetypenm, e.reference_acc_txt || ? || r.abbr_authors_txt || ? || r.pub_start_yr as ref, r.abbr_authors_txt as abbrauthorstxt, ( select string_agg(distinct expstudyfactor.study_factor_nm, ?)) as studyfactornms, ( select string_agg(distinct anatomyterm.nm_html || ? || anatomyterm.acc_txt || ? || anatomyterm.id || ? || anatomyterm.acc_db_cd || ? || anatomyterm.nm, ?)) as anatomyterms, outcome.note as outcomenote, eventlocation.exp_event_id as eventid, count(*) over () fullrowcount from exposure e inner join exp_stressor stressor on e.exp_stressor_id = stressor.id inner join term stressorterm on stressor.chem_id = stressorterm.id left outer join exp_receptor receptor on e.exp_receptor_id = receptor.id left outer join exp_event event on e.exp_event_id = event.id left outer join term exposuremarkerterm on event.exp_marker_term_id = exposuremarkerterm.id left outer join exp_outcome outcome on e.exp_outcome_id = outcome.id left outer join exp_outcome_ixn_type eiot on outcome.exp_outcome_ixn_type_id = eiot.id left outer join term diseaseterm on outcome.disease_id = diseaseterm.id left outer join term phenotypeterm on outcome.phenotype_id = phenotypeterm.id left outer join term receptorterm on receptor.term_id = receptorterm.id inner join reference r on e.reference_id = r.id left outer join exp_stressor_stressor_src esss on stressor.id = esss.exp_stressor_id left outer join exp_stressor_src_type stressorsrctype on esss.exp_stressor_src_type_id = stressorsrctype.id left outer join exp_receptor_tobacco_use receptortobaccouse on receptor.id = receptortobaccouse.exp_receptor_id left outer join exp_receptor_race receptorrace on receptor.id = receptorrace.exp_receptor_id left outer join exp_event_assay_method eventassaymethod on event.id = eventassaymethod.exp_event_id left outer join exp_event_location eventlocation on event.id = eventlocation.exp_event_id left outer join exp_anatomy expanatomy on outcome.id = expanatomy.exp_outcome_id left outer join term anatomyterm on expanatomy.anatomy_id = anatomyterm.id left outer join country on eventlocation.country_id = country.id left outer join exp_event_project eventproject on event.id = eventproject.exp_event_id left outer join reference_exp referenceexp on e.reference_acc_txt = referenceexp.reference_acc_txt and e.reference_acc_db_id = referenceexp.reference_acc_db_id left outer join exp_study_factor expstudyfactor on referenceexp.id = expstudyfactor.reference_exp_id where outcome.phenotype_id in ( select descendant_object_id from dag_path where ancestor_object_id = ?) or receptorterm.id in ( select descendant_object_id from dag_path where ancestor_object_id = ?) group by chemnm, chemnmhtml, chemnmsort, chemacc, stressorsrcdetails, stressorsampleqty, stressornote, receptorterms, medium, mediumacctxt, assayedmarkers, assaylevel, measurement, measurementstat, assaynote, outcomerltnp, diseasefield, phenotypefield, phenotypeactiondegreetypenm, ref, r.abbr_authors_txt, collectionstartandendyr, receptorid, detectionlimit, detectionlimituom, detectionfreq, eventnote, outcomenote, eventid order by chemnmsort limit ?;Times Reported Time consuming queries #10
Day Hour Count Duration Avg duration Jan 29 00 2 2s532ms 1s266ms 01 3 3s834ms 1s278ms 02 2 2s573ms 1s286ms 03 4 5s153ms 1s288ms 08 1 1s331ms 1s331ms 09 2 2s632ms 1s316ms 10 2 2s618ms 1s309ms 11 1 1s345ms 1s345ms 12 3 3s928ms 1s309ms 13 1 1s271ms 1s271ms 14 1 1s327ms 1s327ms 15 8 10s390ms 1s298ms 16 4 5s121ms 1s280ms 17 6 7s687ms 1s281ms 18 9 11s650ms 1s294ms 19 5 6s444ms 1s288ms 20 4 5s120ms 1s280ms 21 4 5s173ms 1s293ms 22 8 10s610ms 1s326ms 23 7 9s185ms 1s312ms [ User: pubeu - Total duration: 20s737ms - Times executed: 16 ]
-
SELECT DISTINCT stressorTerm.nm as chemNm, stressorTerm.nm_html as chemNmHtml, stressorTerm.nm_sort as chemNmSort, stressorTerm.acc_txt as chemAcc, ( SELECT STRING_AGG(distinct stressorSrcType.nm || '^' || stressorSrcType.cd, '|')) as stressorSrcTypeNm, stressor.src_details as stressorSrcDetails, stressor.sample_qty as stressorSampleQty, stressor.note as stressorNote, receptor.qty as nbrReceptors, receptor.description as receptors, receptor.note as receptorNotes, receptorTerm.nm || '^' || ( select cd from object_type where id = receptor.object_type_id) || '^' || receptorTerm.nm_html || '^' || receptorTerm.acc_txt || '^' || receptorTerm.acc_db_cd as receptorTerms, ( SELECT STRING_AGG(distinct receptorTobaccoUse.tobacco_use_nm || '^' || receptorTobaccoUse.pct, ' | ')) as smokerStatus, receptor.age as ageRange, receptor.age_uom_nm as ageUOMNm, receptor.age_qualifier_nm as ageQualifierNm, receptor.gender_nm as genderNmSearch, receptor.id receptorID, ( SELECT STRING_AGG(pct || '^' || gender_nm || '^' || gender_nm_html, '|') from exp_receptor_gender where exp_receptor_id = receptor.id) as genderDetails, ( SELECT STRING_AGG(DISTINCT receptorRace.race_nm || '^' || receptorRace.pct, ' | ')) as receptorRace, ( SELECT STRING_AGG(DISTINCT eventAssayMethod.nm, ' | ')) as assayMethods, event.medium_nm as medium, event.medium_term_acc_txt as mediumAccTxt, ( SELECT STRING_AGG(DISTINCT eventProject.project_nm, ' | ')) as associatedStudyTitles, event.collection_start_yr || '-' || event.collection_end_yr as collectionStartAndEndYr, event.detection_limit as detectionLimit, event.detection_limit_uom as detectionLimitUOM, event.detection_freq as detectionFreq, event.note as eventNote, ( SELECT STRING_AGG(DISTINCT eventLocation.geographic_region_nm, ' | ')) as stateOrProvince, ( SELECT STRING_AGG(DISTINCT eventLocation.locality_txt, ' | ')) as localityTxt, ( SELECT STRING_AGG(distinct country.nm, ' | ')) as studyCountries, exposureMarkerTerm.nm || '^' || ( select cd from object_type where id = exposureMarkerTerm.object_type_id) || '^' || exposureMarkerTerm.nm_html || '^' || exposureMarkerTerm.acc_txt || '^' || exposureMarkerTerm.acc_db_cd as assayedMarkers, event.exp_marker_lvl as assayLevel, assay_uom as measurement, assay_measurement_stat as measurementStat, assay_note as assayNote, eiot.description as outcomeRltnp, diseaseTerm.nm || '^' || 'disease' || '^' || diseaseTerm.nm_html || '^' || diseaseTerm.acc_txt || '^' || diseaseTerm.acc_db_cd as diseaseField, phenotypeTerm.nm || '^' || 'go' || '^' || phenotypeTerm.nm_html || '^' || phenotypeTerm.acc_txt || '^' || phenotypeTerm.acc_db_cd as phenotypeField, outcome.phenotype_action_degree_type_nm as phenotypeActionDegreeTypeNm, e.reference_acc_txt || '^' || r.abbr_authors_txt || '^' || r.pub_start_yr as ref, r.abbr_authors_txt as abbrAuthorsTxt, ( SELECT STRING_AGG(DISTINCT expStudyFactor.study_factor_nm, ' | ')) as studyFactorNms, ( SELECT STRING_AGG(distinct anatomyTerm.nm_html || '^' || anatomyTerm.acc_txt || '^' || anatomyTerm.id || '^' || anatomyTerm.acc_db_cd || '^' || anatomyTerm.nm, '|')) as anatomyTerms, outcome.note as outcomeNote, eventLocation.exp_event_id as eventID, COUNT(*) OVER () fullRowCount FROM exposure e inner join exp_stressor stressor ON e.exp_stressor_id = stressor.id inner join term stressorTerm ON stressor.chem_id = stressorTerm.id left outer join exp_receptor receptor ON e.exp_receptor_id = receptor.id left outer join exp_event event ON e.exp_event_id = event.id left outer join term exposureMarkerTerm ON event.exp_marker_term_id = exposureMarkerTerm.id left outer join exp_outcome outcome ON e.exp_outcome_id = outcome.id left outer join exp_outcome_ixn_type eiot ON outcome.exp_outcome_ixn_type_id = eiot.id left outer join term diseaseTerm ON outcome.disease_id = diseaseTerm.id left outer join term phenotypeTerm ON outcome.phenotype_id = phenotypeTerm.id left outer join term receptorTerm ON receptor.term_id = receptorTerm.id inner join reference r ON e.reference_id = r.id left outer join exp_stressor_stressor_src esss ON stressor.id = esss.exp_stressor_id left outer join exp_stressor_src_type stressorSrcType ON esss.exp_stressor_src_type_id = stressorSrcType.id left outer join exp_receptor_tobacco_use receptorTobaccoUse ON receptor.id = receptorTobaccoUse.exp_receptor_id left outer join exp_receptor_race receptorRace ON receptor.id = receptorRace.exp_receptor_id left outer join exp_event_assay_method eventAssayMethod ON event.id = eventAssayMethod.exp_event_id left outer join exp_event_location eventLocation ON event.id = eventLocation.exp_event_id left outer join exp_anatomy expAnatomy ON outcome.id = expAnatomy.exp_outcome_id Left outer join term anatomyTerm ON expAnatomy.anatomy_id = anatomyTerm.id left outer join country ON eventLocation.country_id = country.id left outer join exp_event_project eventProject ON event.id = eventProject.exp_event_id left outer join reference_exp referenceExp on e.reference_acc_txt = referenceExp.reference_acc_txt and e.reference_acc_db_id = referenceExp.reference_acc_db_id left outer join exp_study_factor expStudyFactor on referenceExp.id = expStudyFactor.reference_exp_id where outcome.phenotype_id in ( select descendant_object_id from dag_path where ancestor_object_id = '1252024') or receptorTerm.id in ( select descendant_object_id from dag_path where ancestor_object_id = '1252024') GROUP BY chemNm, chemNmHtml, chemNmSort, chemAcc, stressorSrcDetails, stressorSampleQty, stressorNote, receptorTerms, medium, mediumAccTxt, assayedMarkers, assayLevel, measurement, measurementStat, assayNote, outcomeRltnp, diseaseField, phenotypeField, phenotypeActionDegreeTypeNm, ref, r.abbr_authors_txt, collectionStartAndEndYr, receptorID, detectionLimit, detectionLimitUOM, detectionFreq, eventNote, outcomeNote, eventID order by chemNmSort LIMIT 50;
Date: 2025-01-29 22:54:48 Duration: 1s591ms Bind query: yes
-
SELECT DISTINCT stressorTerm.nm as chemNm, stressorTerm.nm_html as chemNmHtml, stressorTerm.nm_sort as chemNmSort, stressorTerm.acc_txt as chemAcc, ( SELECT STRING_AGG(distinct stressorSrcType.nm || '^' || stressorSrcType.cd, '|')) as stressorSrcTypeNm, stressor.src_details as stressorSrcDetails, stressor.sample_qty as stressorSampleQty, stressor.note as stressorNote, receptor.qty as nbrReceptors, receptor.description as receptors, receptor.note as receptorNotes, receptorTerm.nm || '^' || ( select cd from object_type where id = receptor.object_type_id) || '^' || receptorTerm.nm_html || '^' || receptorTerm.acc_txt || '^' || receptorTerm.acc_db_cd as receptorTerms, ( SELECT STRING_AGG(distinct receptorTobaccoUse.tobacco_use_nm || '^' || receptorTobaccoUse.pct, ' | ')) as smokerStatus, receptor.age as ageRange, receptor.age_uom_nm as ageUOMNm, receptor.age_qualifier_nm as ageQualifierNm, receptor.gender_nm as genderNmSearch, receptor.id receptorID, ( SELECT STRING_AGG(pct || '^' || gender_nm || '^' || gender_nm_html, '|') from exp_receptor_gender where exp_receptor_id = receptor.id) as genderDetails, ( SELECT STRING_AGG(DISTINCT receptorRace.race_nm || '^' || receptorRace.pct, ' | ')) as receptorRace, ( SELECT STRING_AGG(DISTINCT eventAssayMethod.nm, ' | ')) as assayMethods, event.medium_nm as medium, event.medium_term_acc_txt as mediumAccTxt, ( SELECT STRING_AGG(DISTINCT eventProject.project_nm, ' | ')) as associatedStudyTitles, event.collection_start_yr || '-' || event.collection_end_yr as collectionStartAndEndYr, event.detection_limit as detectionLimit, event.detection_limit_uom as detectionLimitUOM, event.detection_freq as detectionFreq, event.note as eventNote, ( SELECT STRING_AGG(DISTINCT eventLocation.geographic_region_nm, ' | ')) as stateOrProvince, ( SELECT STRING_AGG(DISTINCT eventLocation.locality_txt, ' | ')) as localityTxt, ( SELECT STRING_AGG(distinct country.nm, ' | ')) as studyCountries, exposureMarkerTerm.nm || '^' || ( select cd from object_type where id = exposureMarkerTerm.object_type_id) || '^' || exposureMarkerTerm.nm_html || '^' || exposureMarkerTerm.acc_txt || '^' || exposureMarkerTerm.acc_db_cd as assayedMarkers, event.exp_marker_lvl as assayLevel, assay_uom as measurement, assay_measurement_stat as measurementStat, assay_note as assayNote, eiot.description as outcomeRltnp, diseaseTerm.nm || '^' || 'disease' || '^' || diseaseTerm.nm_html || '^' || diseaseTerm.acc_txt || '^' || diseaseTerm.acc_db_cd as diseaseField, phenotypeTerm.nm || '^' || 'go' || '^' || phenotypeTerm.nm_html || '^' || phenotypeTerm.acc_txt || '^' || phenotypeTerm.acc_db_cd as phenotypeField, outcome.phenotype_action_degree_type_nm as phenotypeActionDegreeTypeNm, e.reference_acc_txt || '^' || r.abbr_authors_txt || '^' || r.pub_start_yr as ref, r.abbr_authors_txt as abbrAuthorsTxt, ( SELECT STRING_AGG(DISTINCT expStudyFactor.study_factor_nm, ' | ')) as studyFactorNms, ( SELECT STRING_AGG(distinct anatomyTerm.nm_html || '^' || anatomyTerm.acc_txt || '^' || anatomyTerm.id || '^' || anatomyTerm.acc_db_cd || '^' || anatomyTerm.nm, '|')) as anatomyTerms, outcome.note as outcomeNote, eventLocation.exp_event_id as eventID, COUNT(*) OVER () fullRowCount FROM exposure e inner join exp_stressor stressor ON e.exp_stressor_id = stressor.id inner join term stressorTerm ON stressor.chem_id = stressorTerm.id left outer join exp_receptor receptor ON e.exp_receptor_id = receptor.id left outer join exp_event event ON e.exp_event_id = event.id left outer join term exposureMarkerTerm ON event.exp_marker_term_id = exposureMarkerTerm.id left outer join exp_outcome outcome ON e.exp_outcome_id = outcome.id left outer join exp_outcome_ixn_type eiot ON outcome.exp_outcome_ixn_type_id = eiot.id left outer join term diseaseTerm ON outcome.disease_id = diseaseTerm.id left outer join term phenotypeTerm ON outcome.phenotype_id = phenotypeTerm.id left outer join term receptorTerm ON receptor.term_id = receptorTerm.id inner join reference r ON e.reference_id = r.id left outer join exp_stressor_stressor_src esss ON stressor.id = esss.exp_stressor_id left outer join exp_stressor_src_type stressorSrcType ON esss.exp_stressor_src_type_id = stressorSrcType.id left outer join exp_receptor_tobacco_use receptorTobaccoUse ON receptor.id = receptorTobaccoUse.exp_receptor_id left outer join exp_receptor_race receptorRace ON receptor.id = receptorRace.exp_receptor_id left outer join exp_event_assay_method eventAssayMethod ON event.id = eventAssayMethod.exp_event_id left outer join exp_event_location eventLocation ON event.id = eventLocation.exp_event_id left outer join exp_anatomy expAnatomy ON outcome.id = expAnatomy.exp_outcome_id Left outer join term anatomyTerm ON expAnatomy.anatomy_id = anatomyTerm.id left outer join country ON eventLocation.country_id = country.id left outer join exp_event_project eventProject ON event.id = eventProject.exp_event_id left outer join reference_exp referenceExp on e.reference_acc_txt = referenceExp.reference_acc_txt and e.reference_acc_db_id = referenceExp.reference_acc_db_id left outer join exp_study_factor expStudyFactor on referenceExp.id = expStudyFactor.reference_exp_id where outcome.phenotype_id in ( select descendant_object_id from dag_path where ancestor_object_id = '1222525') or receptorTerm.id in ( select descendant_object_id from dag_path where ancestor_object_id = '1222525') GROUP BY chemNm, chemNmHtml, chemNmSort, chemAcc, stressorSrcDetails, stressorSampleQty, stressorNote, receptorTerms, medium, mediumAccTxt, assayedMarkers, assayLevel, measurement, measurementStat, assayNote, outcomeRltnp, diseaseField, phenotypeField, phenotypeActionDegreeTypeNm, ref, r.abbr_authors_txt, collectionStartAndEndYr, receptorID, detectionLimit, detectionLimitUOM, detectionFreq, eventNote, outcomeNote, eventID order by chemNmSort LIMIT 50;
Date: 2025-01-29 19:54:53 Duration: 1s420ms Bind query: yes
-
SELECT DISTINCT stressorTerm.nm as chemNm, stressorTerm.nm_html as chemNmHtml, stressorTerm.nm_sort as chemNmSort, stressorTerm.acc_txt as chemAcc, ( SELECT STRING_AGG(distinct stressorSrcType.nm || '^' || stressorSrcType.cd, '|')) as stressorSrcTypeNm, stressor.src_details as stressorSrcDetails, stressor.sample_qty as stressorSampleQty, stressor.note as stressorNote, receptor.qty as nbrReceptors, receptor.description as receptors, receptor.note as receptorNotes, receptorTerm.nm || '^' || ( select cd from object_type where id = receptor.object_type_id) || '^' || receptorTerm.nm_html || '^' || receptorTerm.acc_txt || '^' || receptorTerm.acc_db_cd as receptorTerms, ( SELECT STRING_AGG(distinct receptorTobaccoUse.tobacco_use_nm || '^' || receptorTobaccoUse.pct, ' | ')) as smokerStatus, receptor.age as ageRange, receptor.age_uom_nm as ageUOMNm, receptor.age_qualifier_nm as ageQualifierNm, receptor.gender_nm as genderNmSearch, receptor.id receptorID, ( SELECT STRING_AGG(pct || '^' || gender_nm || '^' || gender_nm_html, '|') from exp_receptor_gender where exp_receptor_id = receptor.id) as genderDetails, ( SELECT STRING_AGG(DISTINCT receptorRace.race_nm || '^' || receptorRace.pct, ' | ')) as receptorRace, ( SELECT STRING_AGG(DISTINCT eventAssayMethod.nm, ' | ')) as assayMethods, event.medium_nm as medium, event.medium_term_acc_txt as mediumAccTxt, ( SELECT STRING_AGG(DISTINCT eventProject.project_nm, ' | ')) as associatedStudyTitles, event.collection_start_yr || '-' || event.collection_end_yr as collectionStartAndEndYr, event.detection_limit as detectionLimit, event.detection_limit_uom as detectionLimitUOM, event.detection_freq as detectionFreq, event.note as eventNote, ( SELECT STRING_AGG(DISTINCT eventLocation.geographic_region_nm, ' | ')) as stateOrProvince, ( SELECT STRING_AGG(DISTINCT eventLocation.locality_txt, ' | ')) as localityTxt, ( SELECT STRING_AGG(distinct country.nm, ' | ')) as studyCountries, exposureMarkerTerm.nm || '^' || ( select cd from object_type where id = exposureMarkerTerm.object_type_id) || '^' || exposureMarkerTerm.nm_html || '^' || exposureMarkerTerm.acc_txt || '^' || exposureMarkerTerm.acc_db_cd as assayedMarkers, event.exp_marker_lvl as assayLevel, assay_uom as measurement, assay_measurement_stat as measurementStat, assay_note as assayNote, eiot.description as outcomeRltnp, diseaseTerm.nm || '^' || 'disease' || '^' || diseaseTerm.nm_html || '^' || diseaseTerm.acc_txt || '^' || diseaseTerm.acc_db_cd as diseaseField, phenotypeTerm.nm || '^' || 'go' || '^' || phenotypeTerm.nm_html || '^' || phenotypeTerm.acc_txt || '^' || phenotypeTerm.acc_db_cd as phenotypeField, outcome.phenotype_action_degree_type_nm as phenotypeActionDegreeTypeNm, e.reference_acc_txt || '^' || r.abbr_authors_txt || '^' || r.pub_start_yr as ref, r.abbr_authors_txt as abbrAuthorsTxt, ( SELECT STRING_AGG(DISTINCT expStudyFactor.study_factor_nm, ' | ')) as studyFactorNms, ( SELECT STRING_AGG(distinct anatomyTerm.nm_html || '^' || anatomyTerm.acc_txt || '^' || anatomyTerm.id || '^' || anatomyTerm.acc_db_cd || '^' || anatomyTerm.nm, '|')) as anatomyTerms, outcome.note as outcomeNote, eventLocation.exp_event_id as eventID, COUNT(*) OVER () fullRowCount FROM exposure e inner join exp_stressor stressor ON e.exp_stressor_id = stressor.id inner join term stressorTerm ON stressor.chem_id = stressorTerm.id left outer join exp_receptor receptor ON e.exp_receptor_id = receptor.id left outer join exp_event event ON e.exp_event_id = event.id left outer join term exposureMarkerTerm ON event.exp_marker_term_id = exposureMarkerTerm.id left outer join exp_outcome outcome ON e.exp_outcome_id = outcome.id left outer join exp_outcome_ixn_type eiot ON outcome.exp_outcome_ixn_type_id = eiot.id left outer join term diseaseTerm ON outcome.disease_id = diseaseTerm.id left outer join term phenotypeTerm ON outcome.phenotype_id = phenotypeTerm.id left outer join term receptorTerm ON receptor.term_id = receptorTerm.id inner join reference r ON e.reference_id = r.id left outer join exp_stressor_stressor_src esss ON stressor.id = esss.exp_stressor_id left outer join exp_stressor_src_type stressorSrcType ON esss.exp_stressor_src_type_id = stressorSrcType.id left outer join exp_receptor_tobacco_use receptorTobaccoUse ON receptor.id = receptorTobaccoUse.exp_receptor_id left outer join exp_receptor_race receptorRace ON receptor.id = receptorRace.exp_receptor_id left outer join exp_event_assay_method eventAssayMethod ON event.id = eventAssayMethod.exp_event_id left outer join exp_event_location eventLocation ON event.id = eventLocation.exp_event_id left outer join exp_anatomy expAnatomy ON outcome.id = expAnatomy.exp_outcome_id Left outer join term anatomyTerm ON expAnatomy.anatomy_id = anatomyTerm.id left outer join country ON eventLocation.country_id = country.id left outer join exp_event_project eventProject ON event.id = eventProject.exp_event_id left outer join reference_exp referenceExp on e.reference_acc_txt = referenceExp.reference_acc_txt and e.reference_acc_db_id = referenceExp.reference_acc_db_id left outer join exp_study_factor expStudyFactor on referenceExp.id = expStudyFactor.reference_exp_id where outcome.phenotype_id in ( select descendant_object_id from dag_path where ancestor_object_id = '1247397') or receptorTerm.id in ( select descendant_object_id from dag_path where ancestor_object_id = '1247397') GROUP BY chemNm, chemNmHtml, chemNmSort, chemAcc, stressorSrcDetails, stressorSampleQty, stressorNote, receptorTerms, medium, mediumAccTxt, assayedMarkers, assayLevel, measurement, measurementStat, assayNote, outcomeRltnp, diseaseField, phenotypeField, phenotypeActionDegreeTypeNm, ref, r.abbr_authors_txt, collectionStartAndEndYr, receptorID, detectionLimit, detectionLimitUOM, detectionFreq, eventNote, outcomeNote, eventID order by chemNmSort LIMIT 50;
Date: 2025-01-29 18:10:31 Duration: 1s412ms Bind query: yes
11 74 1h30m15s 1s506ms 17m45s 1m13s select phenotypeterm.nm gonm, phenotypeterm.nm_html gonmhtml, phenotypeterm.acc_txt goacc, phenotypeterm.id goid, diseaseterm.nm diseasenm, diseaseterm.acc_txt diseaseacc, diseaseterm.acc_db_cd diseaseaccdbcd, diseaseterm.id diseaseid, via_gene_qty genenetworkcount, via_chem_qty chemnetworkcount, indirect_reference_qty referencecount, count(*) over () fullrowcount from phenotype_term pt inner join term phenotypeterm on pt.phenotype_id = phenotypeterm.id inner join term diseaseterm on pt.term_id = diseaseterm.id where phenotypeterm.id in ( select distinct dp.descendant_object_id from dag_path dp where dp.ancestor_object_id = ?) and diseaseterm.object_type_id = ? order by chemnetworkcount desc, genenetworkcount desc limit ?;Times Reported Time consuming queries #11
Day Hour Count Duration Avg duration Jan 29 00 5 10s446ms 2s89ms 01 5 5m28s 1m5s 02 2 4s320ms 2s160ms 03 2 3s369ms 1s684ms 08 3 8s760ms 2s920ms 09 5 3m22s 40s507ms 11 7 9m56s 1m25s 12 1 3s411ms 3s411ms 13 5 9s524ms 1s904ms 14 5 1m43s 20s604ms 15 1 3m32s 3m32s 16 2 4s843ms 2s421ms 17 5 8m8s 1m37s 18 7 22m8s 3m9s 19 5 5m10s 1m2s 20 4 3m7s 46s852ms 21 4 17m51s 4m27s 22 2 3s58ms 1s529ms 23 4 8m57s 2m14s [ User: pubeu - Total duration: 4m41s - Times executed: 20 ]
-
SELECT /* GoDiseasesDAO */ phenotypeTerm.nm goNm, phenotypeTerm.nm_html goNmHTML, phenotypeTerm.acc_txt goAcc, phenotypeTerm.id goId, diseaseTerm.nm diseaseNm, diseaseTerm.acc_txt diseaseAcc, diseaseTerm.acc_db_cd diseaseAccDBCd, diseaseTerm.id diseaseId, via_gene_qty geneNetworkCount, via_chem_qty chemNetworkCount, indirect_reference_qty referenceCount, COUNT(*) OVER () fullRowCount FROM phenotype_term pt inner join term phenotypeTerm on pt.phenotype_id = phenotypeTerm.id inner join term diseaseTerm on pt.term_id = diseaseTerm.id WHERE phenotypeTerm.id IN ( select distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id = '1246190') and diseaseTerm.object_type_id = 3 ORDER BY chemNetworkCount desc, geneNetworkCount desc LIMIT 50;
Date: 2025-01-29 18:23:45 Duration: 17m45s Bind query: yes
-
SELECT /* GoDiseasesDAO */ phenotypeTerm.nm goNm, phenotypeTerm.nm_html goNmHTML, phenotypeTerm.acc_txt goAcc, phenotypeTerm.id goId, diseaseTerm.nm diseaseNm, diseaseTerm.acc_txt diseaseAcc, diseaseTerm.acc_db_cd diseaseAccDBCd, diseaseTerm.id diseaseId, via_gene_qty geneNetworkCount, via_chem_qty chemNetworkCount, indirect_reference_qty referenceCount, COUNT(*) OVER () fullRowCount FROM phenotype_term pt inner join term phenotypeTerm on pt.phenotype_id = phenotypeTerm.id inner join term diseaseTerm on pt.term_id = diseaseTerm.id WHERE phenotypeTerm.id IN ( select distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id = '1246190') and diseaseTerm.object_type_id = 3 ORDER BY chemNetworkCount desc, geneNetworkCount desc LIMIT 50;
Date: 2025-01-29 21:25:34 Duration: 17m44s Bind query: yes
-
SELECT /* GoDiseasesDAO */ phenotypeTerm.nm goNm, phenotypeTerm.nm_html goNmHTML, phenotypeTerm.acc_txt goAcc, phenotypeTerm.id goId, diseaseTerm.nm diseaseNm, diseaseTerm.acc_txt diseaseAcc, diseaseTerm.acc_db_cd diseaseAccDBCd, diseaseTerm.id diseaseId, via_gene_qty geneNetworkCount, via_chem_qty chemNetworkCount, indirect_reference_qty referenceCount, COUNT(*) OVER () fullRowCount FROM phenotype_term pt inner join term phenotypeTerm on pt.phenotype_id = phenotypeTerm.id inner join term diseaseTerm on pt.term_id = diseaseTerm.id WHERE phenotypeTerm.id IN ( select distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id = '1236326') and diseaseTerm.object_type_id = 3 ORDER BY chemNetworkCount desc, geneNetworkCount desc LIMIT 50;
Date: 2025-01-29 11:42:15 Duration: 5m21s Bind query: yes
12 69 2m40s 1s6ms 6s55ms 2s324ms select * from ( select g.nm genesymbol, g.nm_sort genesymbolsort, g.acc_txt geneacc, g.acc_db_cd geneaccdbcd, g.id geneid, count(*) over () fullrowcount from term g where g.id in ( select gt.gene_id from dag_path dp inner join gene_taxon gt on dp.descendant_object_id = gt.taxon_id where dp.ancestor_object_id = ? union all select gcr.gene_id from dag_path dp inner join gene_chem_reference gcr on dp.descendant_object_id = gcr.taxon_id where dp.ancestor_object_id = ?) offset ?) mq order by mq.genesymbolsort limit ?;Times Reported Time consuming queries #12
Day Hour Count Duration Avg duration Jan 29 00 20 54s782ms 2s739ms 01 3 6s79ms 2s26ms 02 2 4s283ms 2s141ms 03 3 5s531ms 1s843ms 10 2 3s746ms 1s873ms 11 2 4s491ms 2s245ms 12 2 3s746ms 1s873ms 13 5 12s770ms 2s554ms 14 4 7s501ms 1s875ms 15 4 8s858ms 2s214ms 16 3 4s185ms 1s395ms 17 6 15s705ms 2s617ms 18 2 7s337ms 3s668ms 19 3 4s170ms 1s390ms 20 3 4s6ms 1s335ms 21 2 8s219ms 4s109ms 22 1 2s161ms 2s161ms 23 2 2s793ms 1s396ms [ User: pubeu - Total duration: 43s134ms - Times executed: 19 ]
-
SELECT /* TaxonGenesDAO */ * FROM ( SELECT g.nm genesymbol, g.nm_sort genesymbolsort, g.acc_txt geneacc, g.acc_db_cd geneaccdbcd, g.id geneid, COUNT(*) OVER () fullRowCount FROM term g WHERE g.id IN ( SELECT gt.gene_id FROM dag_path dp INNER JOIN gene_taxon gt ON dp.descendant_object_id = gt.taxon_id WHERE dp.ancestor_object_id = '655388' UNION ALL SELECT gcr.gene_id FROM dag_path dp INNER JOIN gene_chem_reference gcr ON dp.descendant_object_id = gcr.taxon_id WHERE dp.ancestor_object_id = '655388') OFFSET 0) mq ORDER BY mq.genesymbolsort LIMIT 50;
Date: 2025-01-29 21:02:22 Duration: 6s55ms Bind query: yes
-
SELECT /* TaxonGenesDAO */ * FROM ( SELECT g.nm genesymbol, g.nm_sort genesymbolsort, g.acc_txt geneacc, g.acc_db_cd geneaccdbcd, g.id geneid, COUNT(*) OVER () fullRowCount FROM term g WHERE g.id IN ( SELECT gt.gene_id FROM dag_path dp INNER JOIN gene_taxon gt ON dp.descendant_object_id = gt.taxon_id WHERE dp.ancestor_object_id = '655388' UNION ALL SELECT gcr.gene_id FROM dag_path dp INNER JOIN gene_chem_reference gcr ON dp.descendant_object_id = gcr.taxon_id WHERE dp.ancestor_object_id = '655388') OFFSET 0) mq ORDER BY mq.genesymbolsort LIMIT 50;
Date: 2025-01-29 17:20:46 Duration: 5s820ms Bind query: yes
-
SELECT /* TaxonGenesDAO */ * FROM ( SELECT g.nm genesymbol, g.nm_sort genesymbolsort, g.acc_txt geneacc, g.acc_db_cd geneaccdbcd, g.id geneid, COUNT(*) OVER () fullRowCount FROM term g WHERE g.id IN ( SELECT gt.gene_id FROM dag_path dp INNER JOIN gene_taxon gt ON dp.descendant_object_id = gt.taxon_id WHERE dp.ancestor_object_id = '655388' UNION ALL SELECT gcr.gene_id FROM dag_path dp INNER JOIN gene_chem_reference gcr ON dp.descendant_object_id = gcr.taxon_id WHERE dp.ancestor_object_id = '655388') OFFSET 0) mq ORDER BY mq.genesymbolsort LIMIT 50;
Date: 2025-01-29 18:13:06 Duration: 5s777ms Bind query: yes
13 68 1m30s 1s215ms 1s852ms 1s335ms select distinct stressorterm.nm as chemnm, stressorterm.nm_html as chemnmhtml, stressorterm.nm_sort as chemnmsort, stressorterm.acc_txt as chemacc, ( select string_agg(distinct stressorsrctype.nm || ? || stressorsrctype.cd, ?)) as stressorsrctypenm, stressor.src_details as stressorsrcdetails, stressor.sample_qty as stressorsampleqty, stressor.note as stressornote, receptor.qty as nbrreceptors, receptor.description as receptors, receptor.note as receptornotes, receptorterm.nm || ? || ( select cd from object_type where id = receptor.object_type_id) || ? || receptorterm.nm_html || ? || receptorterm.acc_txt || ? || receptorterm.acc_db_cd as receptorterms, ( select string_agg(distinct receptortobaccouse.tobacco_use_nm || ? || receptortobaccouse.pct, ?)) as smokerstatus, receptor.age as agerange, receptor.age_uom_nm as ageuomnm, receptor.age_qualifier_nm as agequalifiernm, receptor.gender_nm as gendernmsearch, receptor.id receptorid, ( select string_agg(pct || ? || gender_nm || ? || gender_nm_html, ?) from exp_receptor_gender where exp_receptor_id = receptor.id) as genderdetails, ( select string_agg(distinct receptorrace.race_nm || ? || receptorrace.pct, ?)) as receptorrace, ( select string_agg(distinct eventassaymethod.nm, ?)) as assaymethods, event.medium_nm as medium, event.medium_term_acc_txt as mediumacctxt, ( select string_agg(distinct eventproject.project_nm, ?)) as associatedstudytitles, event.collection_start_yr || ? || event.collection_end_yr as collectionstartandendyr, event.detection_limit as detectionlimit, event.detection_limit_uom as detectionlimituom, event.detection_freq as detectionfreq, event.note as eventnote, ( select string_agg(distinct eventlocation.geographic_region_nm, ?)) as stateorprovince, ( select string_agg(distinct eventlocation.locality_txt, ?)) as localitytxt, ( select string_agg(distinct country.nm, ?)) as studycountries, exposuremarkerterm.nm || ? || ( select cd from object_type where id = exposuremarkerterm.object_type_id) || ? || exposuremarkerterm.nm_html || ? || exposuremarkerterm.acc_txt || ? || exposuremarkerterm.acc_db_cd as assayedmarkers, event.exp_marker_lvl as assaylevel, assay_uom as measurement, assay_measurement_stat as measurementstat, assay_note as assaynote, eiot.description as outcomerltnp, diseaseterm.nm || ? || ? || ? || diseaseterm.nm_html || ? || diseaseterm.acc_txt || ? || diseaseterm.acc_db_cd as diseasefield, phenotypeterm.nm || ? || ? || ? || phenotypeterm.nm_html || ? || phenotypeterm.acc_txt || ? || phenotypeterm.acc_db_cd as phenotypefield, outcome.phenotype_action_degree_type_nm as phenotypeactiondegreetypenm, e.reference_acc_txt || ? || r.abbr_authors_txt || ? || r.pub_start_yr as ref, r.abbr_authors_txt as abbrauthorstxt, ( select string_agg(distinct expstudyfactor.study_factor_nm, ?)) as studyfactornms, ( select string_agg(distinct anatomyterm.nm_html || ? || anatomyterm.acc_txt || ? || anatomyterm.id || ? || anatomyterm.acc_db_cd || ? || anatomyterm.nm, ?)) as anatomyterms, outcome.note as outcomenote, eventlocation.exp_event_id as eventid, count(*) over () fullrowcount from exposure e inner join exp_stressor stressor on e.exp_stressor_id = stressor.id inner join term stressorterm on stressor.chem_id = stressorterm.id left outer join exp_receptor receptor on e.exp_receptor_id = receptor.id left outer join exp_event event on e.exp_event_id = event.id left outer join term exposuremarkerterm on event.exp_marker_term_id = exposuremarkerterm.id left outer join exp_outcome outcome on e.exp_outcome_id = outcome.id left outer join exp_outcome_ixn_type eiot on outcome.exp_outcome_ixn_type_id = eiot.id left outer join term diseaseterm on outcome.disease_id = diseaseterm.id left outer join term phenotypeterm on outcome.phenotype_id = phenotypeterm.id left outer join term receptorterm on receptor.term_id = receptorterm.id inner join reference r on e.reference_id = r.id left outer join exp_stressor_stressor_src esss on stressor.id = esss.exp_stressor_id left outer join exp_stressor_src_type stressorsrctype on esss.exp_stressor_src_type_id = stressorsrctype.id left outer join exp_receptor_tobacco_use receptortobaccouse on receptor.id = receptortobaccouse.exp_receptor_id left outer join exp_receptor_race receptorrace on receptor.id = receptorrace.exp_receptor_id left outer join exp_event_assay_method eventassaymethod on event.id = eventassaymethod.exp_event_id left outer join exp_event_location eventlocation on event.id = eventlocation.exp_event_id left outer join exp_anatomy expanatomy on outcome.id = expanatomy.exp_outcome_id left outer join term anatomyterm on expanatomy.anatomy_id = anatomyterm.id left outer join country on eventlocation.country_id = country.id left outer join exp_event_project eventproject on event.id = eventproject.exp_event_id left outer join reference_exp referenceexp on e.reference_acc_txt = referenceexp.reference_acc_txt and e.reference_acc_db_id = referenceexp.reference_acc_db_id left outer join exp_study_factor expstudyfactor on referenceexp.id = expstudyfactor.reference_exp_id where outcome.disease_id in ( select descendant_object_id from dag_path where ancestor_object_id = ?) or receptorterm.id in ( select descendant_object_id from dag_path where ancestor_object_id = ?) group by chemnm, chemnmhtml, chemnmsort, chemacc, stressorsrcdetails, stressorsampleqty, stressornote, receptorterms, medium, mediumacctxt, assayedmarkers, assaylevel, measurement, measurementstat, assaynote, outcomerltnp, diseasefield, phenotypefield, phenotypeactiondegreetypenm, ref, r.abbr_authors_txt, collectionstartandendyr, receptorid, detectionlimit, detectionlimituom, detectionfreq, eventnote, outcomenote, eventid order by chemnmsort limit ?;Times Reported Time consuming queries #13
Day Hour Count Duration Avg duration Jan 29 00 3 3s914ms 1s304ms 01 2 2s571ms 1s285ms 02 2 2s685ms 1s342ms 03 3 3s854ms 1s284ms 08 4 5s682ms 1s420ms 09 2 2s643ms 1s321ms 10 4 5s301ms 1s325ms 11 1 1s437ms 1s437ms 12 4 5s671ms 1s417ms 13 3 4s42ms 1s347ms 14 7 9s139ms 1s305ms 15 4 5s781ms 1s445ms 16 2 2s557ms 1s278ms 17 1 1s279ms 1s279ms 18 7 9s195ms 1s313ms 19 6 7s685ms 1s280ms 20 2 2s521ms 1s260ms 21 4 5s408ms 1s352ms 22 4 5s482ms 1s370ms 23 3 3s953ms 1s317ms [ User: pubeu - Total duration: 19s694ms - Times executed: 15 ]
-
SELECT DISTINCT stressorTerm.nm as chemNm, stressorTerm.nm_html as chemNmHtml, stressorTerm.nm_sort as chemNmSort, stressorTerm.acc_txt as chemAcc, ( SELECT STRING_AGG(distinct stressorSrcType.nm || '^' || stressorSrcType.cd, '|')) as stressorSrcTypeNm, stressor.src_details as stressorSrcDetails, stressor.sample_qty as stressorSampleQty, stressor.note as stressorNote, receptor.qty as nbrReceptors, receptor.description as receptors, receptor.note as receptorNotes, receptorTerm.nm || '^' || ( select cd from object_type where id = receptor.object_type_id) || '^' || receptorTerm.nm_html || '^' || receptorTerm.acc_txt || '^' || receptorTerm.acc_db_cd as receptorTerms, ( SELECT STRING_AGG(distinct receptorTobaccoUse.tobacco_use_nm || '^' || receptorTobaccoUse.pct, ' | ')) as smokerStatus, receptor.age as ageRange, receptor.age_uom_nm as ageUOMNm, receptor.age_qualifier_nm as ageQualifierNm, receptor.gender_nm as genderNmSearch, receptor.id receptorID, ( SELECT STRING_AGG(pct || '^' || gender_nm || '^' || gender_nm_html, '|') from exp_receptor_gender where exp_receptor_id = receptor.id) as genderDetails, ( SELECT STRING_AGG(DISTINCT receptorRace.race_nm || '^' || receptorRace.pct, ' | ')) as receptorRace, ( SELECT STRING_AGG(DISTINCT eventAssayMethod.nm, ' | ')) as assayMethods, event.medium_nm as medium, event.medium_term_acc_txt as mediumAccTxt, ( SELECT STRING_AGG(DISTINCT eventProject.project_nm, ' | ')) as associatedStudyTitles, event.collection_start_yr || '-' || event.collection_end_yr as collectionStartAndEndYr, event.detection_limit as detectionLimit, event.detection_limit_uom as detectionLimitUOM, event.detection_freq as detectionFreq, event.note as eventNote, ( SELECT STRING_AGG(DISTINCT eventLocation.geographic_region_nm, ' | ')) as stateOrProvince, ( SELECT STRING_AGG(DISTINCT eventLocation.locality_txt, ' | ')) as localityTxt, ( SELECT STRING_AGG(distinct country.nm, ' | ')) as studyCountries, exposureMarkerTerm.nm || '^' || ( select cd from object_type where id = exposureMarkerTerm.object_type_id) || '^' || exposureMarkerTerm.nm_html || '^' || exposureMarkerTerm.acc_txt || '^' || exposureMarkerTerm.acc_db_cd as assayedMarkers, event.exp_marker_lvl as assayLevel, assay_uom as measurement, assay_measurement_stat as measurementStat, assay_note as assayNote, eiot.description as outcomeRltnp, diseaseTerm.nm || '^' || 'disease' || '^' || diseaseTerm.nm_html || '^' || diseaseTerm.acc_txt || '^' || diseaseTerm.acc_db_cd as diseaseField, phenotypeTerm.nm || '^' || 'go' || '^' || phenotypeTerm.nm_html || '^' || phenotypeTerm.acc_txt || '^' || phenotypeTerm.acc_db_cd as phenotypeField, outcome.phenotype_action_degree_type_nm as phenotypeActionDegreeTypeNm, e.reference_acc_txt || '^' || r.abbr_authors_txt || '^' || r.pub_start_yr as ref, r.abbr_authors_txt as abbrAuthorsTxt, ( SELECT STRING_AGG(DISTINCT expStudyFactor.study_factor_nm, ' | ')) as studyFactorNms, ( SELECT STRING_AGG(distinct anatomyTerm.nm_html || '^' || anatomyTerm.acc_txt || '^' || anatomyTerm.id || '^' || anatomyTerm.acc_db_cd || '^' || anatomyTerm.nm, '|')) as anatomyTerms, outcome.note as outcomeNote, eventLocation.exp_event_id as eventID, COUNT(*) OVER () fullRowCount FROM exposure e inner join exp_stressor stressor ON e.exp_stressor_id = stressor.id inner join term stressorTerm ON stressor.chem_id = stressorTerm.id left outer join exp_receptor receptor ON e.exp_receptor_id = receptor.id left outer join exp_event event ON e.exp_event_id = event.id left outer join term exposureMarkerTerm ON event.exp_marker_term_id = exposureMarkerTerm.id left outer join exp_outcome outcome ON e.exp_outcome_id = outcome.id left outer join exp_outcome_ixn_type eiot ON outcome.exp_outcome_ixn_type_id = eiot.id left outer join term diseaseTerm ON outcome.disease_id = diseaseTerm.id left outer join term phenotypeTerm ON outcome.phenotype_id = phenotypeTerm.id left outer join term receptorTerm ON receptor.term_id = receptorTerm.id inner join reference r ON e.reference_id = r.id left outer join exp_stressor_stressor_src esss ON stressor.id = esss.exp_stressor_id left outer join exp_stressor_src_type stressorSrcType ON esss.exp_stressor_src_type_id = stressorSrcType.id left outer join exp_receptor_tobacco_use receptorTobaccoUse ON receptor.id = receptorTobaccoUse.exp_receptor_id left outer join exp_receptor_race receptorRace ON receptor.id = receptorRace.exp_receptor_id left outer join exp_event_assay_method eventAssayMethod ON event.id = eventAssayMethod.exp_event_id left outer join exp_event_location eventLocation ON event.id = eventLocation.exp_event_id left outer join exp_anatomy expAnatomy ON outcome.id = expAnatomy.exp_outcome_id Left outer join term anatomyTerm ON expAnatomy.anatomy_id = anatomyTerm.id left outer join country ON eventLocation.country_id = country.id left outer join exp_event_project eventProject ON event.id = eventProject.exp_event_id left outer join reference_exp referenceExp on e.reference_acc_txt = referenceExp.reference_acc_txt and e.reference_acc_db_id = referenceExp.reference_acc_db_id left outer join exp_study_factor expStudyFactor on referenceExp.id = expStudyFactor.reference_exp_id where outcome.disease_id in ( select descendant_object_id from dag_path where ancestor_object_id = '2079972') or receptorTerm.id in ( select descendant_object_id from dag_path where ancestor_object_id = '2079972') GROUP BY chemNm, chemNmHtml, chemNmSort, chemAcc, stressorSrcDetails, stressorSampleQty, stressorNote, receptorTerms, medium, mediumAccTxt, assayedMarkers, assayLevel, measurement, measurementStat, assayNote, outcomeRltnp, diseaseField, phenotypeField, phenotypeActionDegreeTypeNm, ref, r.abbr_authors_txt, collectionStartAndEndYr, receptorID, detectionLimit, detectionLimitUOM, detectionFreq, eventNote, outcomeNote, eventID order by chemNmSort LIMIT 50;
Date: 2025-01-29 15:18:15 Duration: 1s852ms Bind query: yes
-
SELECT DISTINCT stressorTerm.nm as chemNm, stressorTerm.nm_html as chemNmHtml, stressorTerm.nm_sort as chemNmSort, stressorTerm.acc_txt as chemAcc, ( SELECT STRING_AGG(distinct stressorSrcType.nm || '^' || stressorSrcType.cd, '|')) as stressorSrcTypeNm, stressor.src_details as stressorSrcDetails, stressor.sample_qty as stressorSampleQty, stressor.note as stressorNote, receptor.qty as nbrReceptors, receptor.description as receptors, receptor.note as receptorNotes, receptorTerm.nm || '^' || ( select cd from object_type where id = receptor.object_type_id) || '^' || receptorTerm.nm_html || '^' || receptorTerm.acc_txt || '^' || receptorTerm.acc_db_cd as receptorTerms, ( SELECT STRING_AGG(distinct receptorTobaccoUse.tobacco_use_nm || '^' || receptorTobaccoUse.pct, ' | ')) as smokerStatus, receptor.age as ageRange, receptor.age_uom_nm as ageUOMNm, receptor.age_qualifier_nm as ageQualifierNm, receptor.gender_nm as genderNmSearch, receptor.id receptorID, ( SELECT STRING_AGG(pct || '^' || gender_nm || '^' || gender_nm_html, '|') from exp_receptor_gender where exp_receptor_id = receptor.id) as genderDetails, ( SELECT STRING_AGG(DISTINCT receptorRace.race_nm || '^' || receptorRace.pct, ' | ')) as receptorRace, ( SELECT STRING_AGG(DISTINCT eventAssayMethod.nm, ' | ')) as assayMethods, event.medium_nm as medium, event.medium_term_acc_txt as mediumAccTxt, ( SELECT STRING_AGG(DISTINCT eventProject.project_nm, ' | ')) as associatedStudyTitles, event.collection_start_yr || '-' || event.collection_end_yr as collectionStartAndEndYr, event.detection_limit as detectionLimit, event.detection_limit_uom as detectionLimitUOM, event.detection_freq as detectionFreq, event.note as eventNote, ( SELECT STRING_AGG(DISTINCT eventLocation.geographic_region_nm, ' | ')) as stateOrProvince, ( SELECT STRING_AGG(DISTINCT eventLocation.locality_txt, ' | ')) as localityTxt, ( SELECT STRING_AGG(distinct country.nm, ' | ')) as studyCountries, exposureMarkerTerm.nm || '^' || ( select cd from object_type where id = exposureMarkerTerm.object_type_id) || '^' || exposureMarkerTerm.nm_html || '^' || exposureMarkerTerm.acc_txt || '^' || exposureMarkerTerm.acc_db_cd as assayedMarkers, event.exp_marker_lvl as assayLevel, assay_uom as measurement, assay_measurement_stat as measurementStat, assay_note as assayNote, eiot.description as outcomeRltnp, diseaseTerm.nm || '^' || 'disease' || '^' || diseaseTerm.nm_html || '^' || diseaseTerm.acc_txt || '^' || diseaseTerm.acc_db_cd as diseaseField, phenotypeTerm.nm || '^' || 'go' || '^' || phenotypeTerm.nm_html || '^' || phenotypeTerm.acc_txt || '^' || phenotypeTerm.acc_db_cd as phenotypeField, outcome.phenotype_action_degree_type_nm as phenotypeActionDegreeTypeNm, e.reference_acc_txt || '^' || r.abbr_authors_txt || '^' || r.pub_start_yr as ref, r.abbr_authors_txt as abbrAuthorsTxt, ( SELECT STRING_AGG(DISTINCT expStudyFactor.study_factor_nm, ' | ')) as studyFactorNms, ( SELECT STRING_AGG(distinct anatomyTerm.nm_html || '^' || anatomyTerm.acc_txt || '^' || anatomyTerm.id || '^' || anatomyTerm.acc_db_cd || '^' || anatomyTerm.nm, '|')) as anatomyTerms, outcome.note as outcomeNote, eventLocation.exp_event_id as eventID, COUNT(*) OVER () fullRowCount FROM exposure e inner join exp_stressor stressor ON e.exp_stressor_id = stressor.id inner join term stressorTerm ON stressor.chem_id = stressorTerm.id left outer join exp_receptor receptor ON e.exp_receptor_id = receptor.id left outer join exp_event event ON e.exp_event_id = event.id left outer join term exposureMarkerTerm ON event.exp_marker_term_id = exposureMarkerTerm.id left outer join exp_outcome outcome ON e.exp_outcome_id = outcome.id left outer join exp_outcome_ixn_type eiot ON outcome.exp_outcome_ixn_type_id = eiot.id left outer join term diseaseTerm ON outcome.disease_id = diseaseTerm.id left outer join term phenotypeTerm ON outcome.phenotype_id = phenotypeTerm.id left outer join term receptorTerm ON receptor.term_id = receptorTerm.id inner join reference r ON e.reference_id = r.id left outer join exp_stressor_stressor_src esss ON stressor.id = esss.exp_stressor_id left outer join exp_stressor_src_type stressorSrcType ON esss.exp_stressor_src_type_id = stressorSrcType.id left outer join exp_receptor_tobacco_use receptorTobaccoUse ON receptor.id = receptorTobaccoUse.exp_receptor_id left outer join exp_receptor_race receptorRace ON receptor.id = receptorRace.exp_receptor_id left outer join exp_event_assay_method eventAssayMethod ON event.id = eventAssayMethod.exp_event_id left outer join exp_event_location eventLocation ON event.id = eventLocation.exp_event_id left outer join exp_anatomy expAnatomy ON outcome.id = expAnatomy.exp_outcome_id Left outer join term anatomyTerm ON expAnatomy.anatomy_id = anatomyTerm.id left outer join country ON eventLocation.country_id = country.id left outer join exp_event_project eventProject ON event.id = eventProject.exp_event_id left outer join reference_exp referenceExp on e.reference_acc_txt = referenceExp.reference_acc_txt and e.reference_acc_db_id = referenceExp.reference_acc_db_id left outer join exp_study_factor expStudyFactor on referenceExp.id = expStudyFactor.reference_exp_id where outcome.disease_id in ( select descendant_object_id from dag_path where ancestor_object_id = '2079972') or receptorTerm.id in ( select descendant_object_id from dag_path where ancestor_object_id = '2079972') GROUP BY chemNm, chemNmHtml, chemNmSort, chemAcc, stressorSrcDetails, stressorSampleQty, stressorNote, receptorTerms, medium, mediumAccTxt, assayedMarkers, assayLevel, measurement, measurementStat, assayNote, outcomeRltnp, diseaseField, phenotypeField, phenotypeActionDegreeTypeNm, ref, r.abbr_authors_txt, collectionStartAndEndYr, receptorID, detectionLimit, detectionLimitUOM, detectionFreq, eventNote, outcomeNote, eventID order by chemNmSort LIMIT 50;
Date: 2025-01-29 08:18:01 Duration: 1s817ms Bind query: yes
-
SELECT DISTINCT stressorTerm.nm as chemNm, stressorTerm.nm_html as chemNmHtml, stressorTerm.nm_sort as chemNmSort, stressorTerm.acc_txt as chemAcc, ( SELECT STRING_AGG(distinct stressorSrcType.nm || '^' || stressorSrcType.cd, '|')) as stressorSrcTypeNm, stressor.src_details as stressorSrcDetails, stressor.sample_qty as stressorSampleQty, stressor.note as stressorNote, receptor.qty as nbrReceptors, receptor.description as receptors, receptor.note as receptorNotes, receptorTerm.nm || '^' || ( select cd from object_type where id = receptor.object_type_id) || '^' || receptorTerm.nm_html || '^' || receptorTerm.acc_txt || '^' || receptorTerm.acc_db_cd as receptorTerms, ( SELECT STRING_AGG(distinct receptorTobaccoUse.tobacco_use_nm || '^' || receptorTobaccoUse.pct, ' | ')) as smokerStatus, receptor.age as ageRange, receptor.age_uom_nm as ageUOMNm, receptor.age_qualifier_nm as ageQualifierNm, receptor.gender_nm as genderNmSearch, receptor.id receptorID, ( SELECT STRING_AGG(pct || '^' || gender_nm || '^' || gender_nm_html, '|') from exp_receptor_gender where exp_receptor_id = receptor.id) as genderDetails, ( SELECT STRING_AGG(DISTINCT receptorRace.race_nm || '^' || receptorRace.pct, ' | ')) as receptorRace, ( SELECT STRING_AGG(DISTINCT eventAssayMethod.nm, ' | ')) as assayMethods, event.medium_nm as medium, event.medium_term_acc_txt as mediumAccTxt, ( SELECT STRING_AGG(DISTINCT eventProject.project_nm, ' | ')) as associatedStudyTitles, event.collection_start_yr || '-' || event.collection_end_yr as collectionStartAndEndYr, event.detection_limit as detectionLimit, event.detection_limit_uom as detectionLimitUOM, event.detection_freq as detectionFreq, event.note as eventNote, ( SELECT STRING_AGG(DISTINCT eventLocation.geographic_region_nm, ' | ')) as stateOrProvince, ( SELECT STRING_AGG(DISTINCT eventLocation.locality_txt, ' | ')) as localityTxt, ( SELECT STRING_AGG(distinct country.nm, ' | ')) as studyCountries, exposureMarkerTerm.nm || '^' || ( select cd from object_type where id = exposureMarkerTerm.object_type_id) || '^' || exposureMarkerTerm.nm_html || '^' || exposureMarkerTerm.acc_txt || '^' || exposureMarkerTerm.acc_db_cd as assayedMarkers, event.exp_marker_lvl as assayLevel, assay_uom as measurement, assay_measurement_stat as measurementStat, assay_note as assayNote, eiot.description as outcomeRltnp, diseaseTerm.nm || '^' || 'disease' || '^' || diseaseTerm.nm_html || '^' || diseaseTerm.acc_txt || '^' || diseaseTerm.acc_db_cd as diseaseField, phenotypeTerm.nm || '^' || 'go' || '^' || phenotypeTerm.nm_html || '^' || phenotypeTerm.acc_txt || '^' || phenotypeTerm.acc_db_cd as phenotypeField, outcome.phenotype_action_degree_type_nm as phenotypeActionDegreeTypeNm, e.reference_acc_txt || '^' || r.abbr_authors_txt || '^' || r.pub_start_yr as ref, r.abbr_authors_txt as abbrAuthorsTxt, ( SELECT STRING_AGG(DISTINCT expStudyFactor.study_factor_nm, ' | ')) as studyFactorNms, ( SELECT STRING_AGG(distinct anatomyTerm.nm_html || '^' || anatomyTerm.acc_txt || '^' || anatomyTerm.id || '^' || anatomyTerm.acc_db_cd || '^' || anatomyTerm.nm, '|')) as anatomyTerms, outcome.note as outcomeNote, eventLocation.exp_event_id as eventID, COUNT(*) OVER () fullRowCount FROM exposure e inner join exp_stressor stressor ON e.exp_stressor_id = stressor.id inner join term stressorTerm ON stressor.chem_id = stressorTerm.id left outer join exp_receptor receptor ON e.exp_receptor_id = receptor.id left outer join exp_event event ON e.exp_event_id = event.id left outer join term exposureMarkerTerm ON event.exp_marker_term_id = exposureMarkerTerm.id left outer join exp_outcome outcome ON e.exp_outcome_id = outcome.id left outer join exp_outcome_ixn_type eiot ON outcome.exp_outcome_ixn_type_id = eiot.id left outer join term diseaseTerm ON outcome.disease_id = diseaseTerm.id left outer join term phenotypeTerm ON outcome.phenotype_id = phenotypeTerm.id left outer join term receptorTerm ON receptor.term_id = receptorTerm.id inner join reference r ON e.reference_id = r.id left outer join exp_stressor_stressor_src esss ON stressor.id = esss.exp_stressor_id left outer join exp_stressor_src_type stressorSrcType ON esss.exp_stressor_src_type_id = stressorSrcType.id left outer join exp_receptor_tobacco_use receptorTobaccoUse ON receptor.id = receptorTobaccoUse.exp_receptor_id left outer join exp_receptor_race receptorRace ON receptor.id = receptorRace.exp_receptor_id left outer join exp_event_assay_method eventAssayMethod ON event.id = eventAssayMethod.exp_event_id left outer join exp_event_location eventLocation ON event.id = eventLocation.exp_event_id left outer join exp_anatomy expAnatomy ON outcome.id = expAnatomy.exp_outcome_id Left outer join term anatomyTerm ON expAnatomy.anatomy_id = anatomyTerm.id left outer join country ON eventLocation.country_id = country.id left outer join exp_event_project eventProject ON event.id = eventProject.exp_event_id left outer join reference_exp referenceExp on e.reference_acc_txt = referenceExp.reference_acc_txt and e.reference_acc_db_id = referenceExp.reference_acc_db_id left outer join exp_study_factor expStudyFactor on referenceExp.id = expStudyFactor.reference_exp_id where outcome.disease_id in ( select descendant_object_id from dag_path where ancestor_object_id = '2079972') or receptorTerm.id in ( select descendant_object_id from dag_path where ancestor_object_id = '2079972') GROUP BY chemNm, chemNmHtml, chemNmSort, chemAcc, stressorSrcDetails, stressorSampleQty, stressorNote, receptorTerms, medium, mediumAccTxt, assayedMarkers, assayLevel, measurement, measurementStat, assayNote, outcomeRltnp, diseaseField, phenotypeField, phenotypeActionDegreeTypeNm, ref, r.abbr_authors_txt, collectionStartAndEndYr, receptorID, detectionLimit, detectionLimitUOM, detectionFreq, eventNote, outcomeNote, eventID order by chemNmSort LIMIT 50;
Date: 2025-01-29 12:47:34 Duration: 1s705ms Bind query: yes
14 57 5m26s 1s44ms 17s369ms 5s731ms select distinct associatedterm.nm || ? || o.cd || ? || associatedterm.nm_html || ? || associatedterm.acc_txt || ? || associatedterm.acc_db_cd as associatedterm, associatedterm.id associatedtermid, ptr.ixn_id ixnid, associatedterm.object_type_id || ? || associatedterm.nm_sort associatedtermnmsort, coalesce(associatedterm.secondary_nm, ?) casrn, phenotypeterm.nm || ? || ? || ? || phenotypeterm.nm_html || ? || phenotypeterm.acc_txt || ? || phenotypeterm.acc_db_cd as phenotype, phenotypeterm.id phenotypeid, ( select string_agg(distinct taxonterm.nm || ? || ? || ? || taxonterm.nm_html || ? || taxonterm.acc_txt || ? || taxonterm.acc_db_cd || ? || coalesce(taxonterm.secondary_nm, ?), ?)) as taxonterms, ( select string_agg(distinct anatomyterm.nm_html || ? || anatomyterm.acc_txt || ? || ia.level_seq || ? || anatomyterm.acc_db_cd || ? || anatomyterm.nm, ?)) as anatomyterms, count(distinct taxonterm.nm) taxoncount, i.ixn_prose_html ixnprosehtml, i.ixn_prose_txt ixnprose, i.sort_txt ixnsort, ( select string_agg(distinct r.acc_txt, ?)) as references, count(distinct ptr.reference_id) refcount, pt.indirect_term_qty inferredcount, count(*) over () fullrowcount from phenotype_term_reference ptr inner join phenotype_term pt on ptr.term_id = pt.term_id and ptr.phenotype_id = pt.phenotype_id inner join term associatedterm on ptr.term_id = associatedterm.id inner join term phenotypeterm on ptr.phenotype_id = phenotypeterm.id left outer join term taxonterm on ptr.taxon_id = taxonterm.id inner join reference r on ptr.reference_id = r.id inner join ixn i on ptr.ixn_id = i.id inner join object_type o on associatedterm.object_type_id = o.id left outer join ixn_anatomy ia on ptr.ixn_id = ia.ixn_id left outer join term anatomyterm on ia.anatomy_id = anatomyterm.id where phenotypeterm.id = any (array (( select p.descendant_object_id from dag_path p where p.ancestor_object_id = ?))) and associatedterm.object_type_id = ? group by associatedterm, associatedtermnmsort, phenotype, casrn, ixnid, ixnprosehtml, ixnprose, ixnsort, associatedtermid, phenotypeid, inferredcount order by associatedtermnmsort limit ?;Times Reported Time consuming queries #14
Day Hour Count Duration Avg duration Jan 29 00 2 8s434ms 4s217ms 01 1 5s709ms 5s709ms 02 4 11s619ms 2s904ms 03 2 12s524ms 6s262ms 08 1 5s503ms 5s503ms 09 3 21s267ms 7s89ms 10 3 29s879ms 9s959ms 11 3 23s273ms 7s757ms 12 1 11s87ms 11s87ms 13 6 42s274ms 7s45ms 14 2 10s625ms 5s312ms 15 2 9s117ms 4s558ms 16 1 2s907ms 2s907ms 17 4 16s847ms 4s211ms 18 2 17s458ms 8s729ms 19 4 9s533ms 2s383ms 20 2 27s75ms 13s537ms 21 4 9s1ms 2s250ms 22 5 20s762ms 4s152ms 23 5 31s780ms 6s356ms [ User: pubeu - Total duration: 1m22s - Times executed: 13 ]
-
select distinct /* ChemPhenotypesAssnsDAO */ associatedTerm.nm || '^' || o.cd || '^' || associatedTerm.nm_html || '^' || associatedTerm.acc_txt || '^' || associatedTerm.acc_db_cd as associatedTerm, associatedTerm.id associatedTermId, ptr.ixn_id ixnId, associatedTerm.object_type_id || '|' || associatedTerm.nm_sort associatedTermNmSort, COALESCE(associatedTerm.secondary_nm, '') casRN, phenotypeTerm.nm || '^' || 'go' || '^' || phenotypeTerm.nm_html || '^' || phenotypeTerm.acc_txt || '^' || phenotypeTerm.acc_db_cd as phenotype, phenotypeTerm.id phenotypeId, ( SELECT STRING_AGG(distinct taxonTerm.nm || '^' || 'taxon' || '^' || taxonTerm.nm_html || '^' || taxonTerm.acc_txt || '^' || taxonTerm.acc_db_cd || '^' || COALESCE(taxonTerm.secondary_nm, ''), '|')) as taxonTerms, ( SELECT STRING_AGG(distinct anatomyTerm.nm_html || '^' || anatomyTerm.acc_txt || '^' || ia.level_seq || '^' || anatomyTerm.acc_db_cd || '^' || anatomyTerm.nm, '|')) as anatomyTerms, COUNT(DISTINCT taxonTerm.nm) taxonCount, i.ixn_prose_html ixnProseHtml, i.ixn_prose_txt ixnProse, i.sort_txt ixnSort, ( SELECT STRING_AGG(distinct r.acc_txt, '|')) as references, COUNT(DISTINCT ptr.reference_id) refCount, pt.indirect_term_qty inferredCount, COUNT(*) OVER () fullRowCount from phenotype_term_reference ptr inner join phenotype_term pt on ptr.term_id = pt.term_id and ptr.phenotype_id = pt.phenotype_id inner join term associatedTerm on ptr.term_id = associatedTerm.id inner join term phenotypeTerm on ptr.phenotype_id = phenotypeTerm.id left outer join term taxonTerm on ptr.taxon_id = taxonTerm.id inner join reference r on ptr.reference_id = r.id inner join ixn i on ptr.ixn_id = i.id inner join object_type o on associatedTerm.object_type_id = o.id left outer join ixn_anatomy ia on ptr.ixn_id = ia.ixn_id left outer join term anatomyTerm on ia.anatomy_id = anatomyTerm.id where phenotypeTerm.id = ANY (ARRAY (( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '1222378'))) and associatedTerm.object_type_id = 2 group by associatedTerm, associatedTermNmSort, phenotype, casRN, ixnId, ixnProseHtml, ixnProse, ixnSort, associatedTermId, phenotypeId, inferredCount ORDER BY associatedTermNmSort LIMIT 50;
Date: 2025-01-29 10:26:17 Duration: 17s369ms Bind query: yes
-
select distinct /* ChemPhenotypesAssnsDAO */ associatedTerm.nm || '^' || o.cd || '^' || associatedTerm.nm_html || '^' || associatedTerm.acc_txt || '^' || associatedTerm.acc_db_cd as associatedTerm, associatedTerm.id associatedTermId, ptr.ixn_id ixnId, associatedTerm.object_type_id || '|' || associatedTerm.nm_sort associatedTermNmSort, COALESCE(associatedTerm.secondary_nm, '') casRN, phenotypeTerm.nm || '^' || 'go' || '^' || phenotypeTerm.nm_html || '^' || phenotypeTerm.acc_txt || '^' || phenotypeTerm.acc_db_cd as phenotype, phenotypeTerm.id phenotypeId, ( SELECT STRING_AGG(distinct taxonTerm.nm || '^' || 'taxon' || '^' || taxonTerm.nm_html || '^' || taxonTerm.acc_txt || '^' || taxonTerm.acc_db_cd || '^' || COALESCE(taxonTerm.secondary_nm, ''), '|')) as taxonTerms, ( SELECT STRING_AGG(distinct anatomyTerm.nm_html || '^' || anatomyTerm.acc_txt || '^' || ia.level_seq || '^' || anatomyTerm.acc_db_cd || '^' || anatomyTerm.nm, '|')) as anatomyTerms, COUNT(DISTINCT taxonTerm.nm) taxonCount, i.ixn_prose_html ixnProseHtml, i.ixn_prose_txt ixnProse, i.sort_txt ixnSort, ( SELECT STRING_AGG(distinct r.acc_txt, '|')) as references, COUNT(DISTINCT ptr.reference_id) refCount, pt.indirect_term_qty inferredCount, COUNT(*) OVER () fullRowCount from phenotype_term_reference ptr inner join phenotype_term pt on ptr.term_id = pt.term_id and ptr.phenotype_id = pt.phenotype_id inner join term associatedTerm on ptr.term_id = associatedTerm.id inner join term phenotypeTerm on ptr.phenotype_id = phenotypeTerm.id left outer join term taxonTerm on ptr.taxon_id = taxonTerm.id inner join reference r on ptr.reference_id = r.id inner join ixn i on ptr.ixn_id = i.id inner join object_type o on associatedTerm.object_type_id = o.id left outer join ixn_anatomy ia on ptr.ixn_id = ia.ixn_id left outer join term anatomyTerm on ia.anatomy_id = anatomyTerm.id where phenotypeTerm.id = ANY (ARRAY (( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '1222378'))) and associatedTerm.object_type_id = 2 group by associatedTerm, associatedTermNmSort, phenotype, casRN, ixnId, ixnProseHtml, ixnProse, ixnSort, associatedTermId, phenotypeId, inferredCount ORDER BY associatedTermNmSort LIMIT 50;
Date: 2025-01-29 20:57:11 Duration: 16s186ms Bind query: yes
-
select distinct /* ChemPhenotypesAssnsDAO */ associatedTerm.nm || '^' || o.cd || '^' || associatedTerm.nm_html || '^' || associatedTerm.acc_txt || '^' || associatedTerm.acc_db_cd as associatedTerm, associatedTerm.id associatedTermId, ptr.ixn_id ixnId, associatedTerm.object_type_id || '|' || associatedTerm.nm_sort associatedTermNmSort, COALESCE(associatedTerm.secondary_nm, '') casRN, phenotypeTerm.nm || '^' || 'go' || '^' || phenotypeTerm.nm_html || '^' || phenotypeTerm.acc_txt || '^' || phenotypeTerm.acc_db_cd as phenotype, phenotypeTerm.id phenotypeId, ( SELECT STRING_AGG(distinct taxonTerm.nm || '^' || 'taxon' || '^' || taxonTerm.nm_html || '^' || taxonTerm.acc_txt || '^' || taxonTerm.acc_db_cd || '^' || COALESCE(taxonTerm.secondary_nm, ''), '|')) as taxonTerms, ( SELECT STRING_AGG(distinct anatomyTerm.nm_html || '^' || anatomyTerm.acc_txt || '^' || ia.level_seq || '^' || anatomyTerm.acc_db_cd || '^' || anatomyTerm.nm, '|')) as anatomyTerms, COUNT(DISTINCT taxonTerm.nm) taxonCount, i.ixn_prose_html ixnProseHtml, i.ixn_prose_txt ixnProse, i.sort_txt ixnSort, ( SELECT STRING_AGG(distinct r.acc_txt, '|')) as references, COUNT(DISTINCT ptr.reference_id) refCount, pt.indirect_term_qty inferredCount, COUNT(*) OVER () fullRowCount from phenotype_term_reference ptr inner join phenotype_term pt on ptr.term_id = pt.term_id and ptr.phenotype_id = pt.phenotype_id inner join term associatedTerm on ptr.term_id = associatedTerm.id inner join term phenotypeTerm on ptr.phenotype_id = phenotypeTerm.id left outer join term taxonTerm on ptr.taxon_id = taxonTerm.id inner join reference r on ptr.reference_id = r.id inner join ixn i on ptr.ixn_id = i.id inner join object_type o on associatedTerm.object_type_id = o.id left outer join ixn_anatomy ia on ptr.ixn_id = ia.ixn_id left outer join term anatomyTerm on ia.anatomy_id = anatomyTerm.id where phenotypeTerm.id = ANY (ARRAY (( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '1222378'))) and associatedTerm.object_type_id = 2 group by associatedTerm, associatedTermNmSort, phenotype, casRN, ixnId, ixnProseHtml, ixnProse, ixnSort, associatedTermId, phenotypeId, inferredCount ORDER BY associatedTermNmSort LIMIT 50;
Date: 2025-01-29 23:08:31 Duration: 16s170ms Bind query: yes
15 53 3m51s 1s7ms 7s5ms 4s363ms 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 #15
Day Hour Count Duration Avg duration Jan 29 00 4 21s593ms 5s398ms 01 3 15s817ms 5s272ms 02 1 5s302ms 5s302ms 03 1 5s750ms 5s750ms 04 1 5s825ms 5s825ms 08 3 16s614ms 5s538ms 09 3 16s961ms 5s653ms 11 3 10s908ms 3s636ms 13 5 23s142ms 4s628ms 14 1 7s5ms 7s5ms 15 5 19s117ms 3s823ms 16 1 5s813ms 5s813ms 17 3 16s132ms 5s377ms 18 8 28s963ms 3s620ms 19 2 6s366ms 3s183ms 20 2 6s110ms 3s55ms 21 2 2s69ms 1s34ms 22 3 7s355ms 2s451ms 23 2 10s435ms 5s217ms [ User: pubeu - Total duration: 29s907ms - Times executed: 8 ]
-
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 = '1290810' AND te.enriched_object_type_id = 5 ORDER BY te.corrected_p_val, d.abbr, gt.nm_sort LIMIT 50;
Date: 2025-01-29 14:27:41 Duration: 7s5ms 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 = '1437806' AND te.enriched_object_type_id = 5 ORDER BY te.corrected_p_val, d.abbr, gt.nm_sort LIMIT 50;
Date: 2025-01-29 08:35:26 Duration: 6s138ms 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 = '1266824' AND te.enriched_object_type_id = 5 ORDER BY te.corrected_p_val, d.abbr, gt.nm_sort LIMIT 50;
Date: 2025-01-29 09:25:56 Duration: 6s136ms Bind query: yes
16 41 2m46s 3s851ms 4s601ms 4s59ms select coalesce(st.alt_nm, t.nm) slimtermnm, ( select count(*) from slim_term_mapping stm inner join chem_disease cd on cd.disease_id = stm.mapped_term_id where cd.chem_id in ( select p.descendant_object_id from dag_path p where p.ancestor_object_id = ?) and stm.slim_term_id = st.slim_term_id and cd.curated_reference_qty > ?) curatedcount, ( select count(*) from slim_term_mapping stm inner join chem_disease cd on cd.disease_id = stm.mapped_term_id where cd.chem_id in ( select p.descendant_object_id from dag_path p where p.ancestor_object_id = ?) and stm.slim_term_id = st.slim_term_id and cd.indirect_gene_qty > ?) inferredcount from slim_term st inner join term t on st.slim_term_id = t.id where st.slim_id = ? order by ?;Times Reported Time consuming queries #16
Day Hour Count Duration Avg duration Jan 29 00 1 3s903ms 3s903ms 01 8 32s35ms 4s4ms 03 1 4s 4s 08 4 16s523ms 4s130ms 10 3 12s208ms 4s69ms 11 1 3s920ms 3s920ms 13 1 3s928ms 3s928ms 14 1 3s999ms 3s999ms 15 5 20s510ms 4s102ms 16 9 36s805ms 4s89ms 17 1 3s926ms 3s926ms 20 2 7s917ms 3s958ms 21 3 12s660ms 4s220ms 22 1 4s81ms 4s81ms [ User: pubeu - Total duration: 44s745ms - Times executed: 11 ]
-
SELECT /* ChemDiseasesBySlimDAO */ COALESCE(st.alt_nm, t.nm) slimTermNm, ( SELECT COUNT(*) FROM slim_term_mapping stm INNER JOIN chem_disease cd ON cd.disease_id = stm.mapped_term_id WHERE cd.chem_id IN ( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '1368182') AND stm.slim_term_id = st.slim_term_id AND cd.curated_reference_qty > 0) curatedCount, ( SELECT COUNT(*) FROM slim_term_mapping stm INNER JOIN chem_disease cd ON cd.disease_id = stm.mapped_term_id WHERE cd.chem_id IN ( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '1368182') AND stm.slim_term_id = st.slim_term_id AND cd.indirect_gene_qty > 0) inferredCount FROM slim_term st INNER JOIN term t ON st.slim_term_id = t.id WHERE st.slim_id = 1 ORDER BY 1;
Date: 2025-01-29 16:39:45 Duration: 4s601ms Bind query: yes
-
SELECT /* ChemDiseasesBySlimDAO */ COALESCE(st.alt_nm, t.nm) slimTermNm, ( SELECT COUNT(*) FROM slim_term_mapping stm INNER JOIN chem_disease cd ON cd.disease_id = stm.mapped_term_id WHERE cd.chem_id IN ( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '1438358') AND stm.slim_term_id = st.slim_term_id AND cd.curated_reference_qty > 0) curatedCount, ( SELECT COUNT(*) FROM slim_term_mapping stm INNER JOIN chem_disease cd ON cd.disease_id = stm.mapped_term_id WHERE cd.chem_id IN ( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '1438358') AND stm.slim_term_id = st.slim_term_id AND cd.indirect_gene_qty > 0) inferredCount FROM slim_term st INNER JOIN term t ON st.slim_term_id = t.id WHERE st.slim_id = 1 ORDER BY 1;
Date: 2025-01-29 21:31:42 Duration: 4s538ms Database: ctdprd51 User: pubeu Bind query: yes
-
SELECT /* ChemDiseasesBySlimDAO */ COALESCE(st.alt_nm, t.nm) slimTermNm, ( SELECT COUNT(*) FROM slim_term_mapping stm INNER JOIN chem_disease cd ON cd.disease_id = stm.mapped_term_id WHERE cd.chem_id IN ( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '1442993') AND stm.slim_term_id = st.slim_term_id AND cd.curated_reference_qty > 0) curatedCount, ( SELECT COUNT(*) FROM slim_term_mapping stm INNER JOIN chem_disease cd ON cd.disease_id = stm.mapped_term_id WHERE cd.chem_id IN ( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '1442993') AND stm.slim_term_id = st.slim_term_id AND cd.indirect_gene_qty > 0) inferredCount FROM slim_term st INNER JOIN term t ON st.slim_term_id = t.id WHERE st.slim_id = 1 ORDER BY 1;
Date: 2025-01-29 15:55:34 Duration: 4s284ms Bind query: yes
17 36 53s243ms 1s347ms 1s596ms 1s478ms select g.nm genesymbol, g.id geneid, g.acc_txt geneacc, g.acc_db_cd geneaccdbcd, count(gcr.ixn_id) ixncount, count(distinct gcr.taxon_id) taxoncount, count(*) over () fullrowcount from gene_chem_reference gcr inner join term g on gcr.gene_id = g.id where gcr.chem_id in ( select p.descendant_object_id from dag_path p where p.ancestor_object_id = ?) group by g.nm, g.id, g.acc_txt, g.acc_db_cd, g.nm_sort order by count(gcr.ixn_id) desc, g.nm_sort limit ?;Times Reported Time consuming queries #17
Day Hour Count Duration Avg duration Jan 29 00 4 5s909ms 1s477ms 01 1 1s504ms 1s504ms 08 1 1s503ms 1s503ms 09 4 5s966ms 1s491ms 10 2 3s58ms 1s529ms 11 1 1s470ms 1s470ms 12 2 2s952ms 1s476ms 13 2 2s970ms 1s485ms 15 2 2s975ms 1s487ms 16 4 5s842ms 1s460ms 17 1 1s466ms 1s466ms 19 1 1s450ms 1s450ms 20 3 4s393ms 1s464ms 21 3 4s440ms 1s480ms 22 1 1s448ms 1s448ms 23 4 5s890ms 1s472ms -
SELECT /* ChemGenesDAO */ g.nm geneSymbol, g.id geneId, g.acc_txt geneAcc, g.acc_db_cd geneAccDbCd, COUNT(gcr.ixn_id) ixnCount, COUNT(DISTINCT gcr.taxon_id) taxonCount, COUNT(*) OVER () fullRowCount FROM gene_chem_reference gcr INNER JOIN term g ON gcr.gene_id = g.id WHERE gcr.chem_id IN ( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '1267689') GROUP BY g.nm, g.id, g.acc_txt, g.acc_db_cd, g.nm_sort ORDER BY COUNT(gcr.ixn_id) DESC, g.nm_sort LIMIT 50;
Date: 2025-01-29 09:54:58 Duration: 1s596ms Bind query: yes
-
SELECT /* ChemGenesDAO */ g.nm geneSymbol, g.id geneId, g.acc_txt geneAcc, g.acc_db_cd geneAccDbCd, COUNT(gcr.ixn_id) ixnCount, COUNT(DISTINCT gcr.taxon_id) taxonCount, COUNT(*) OVER () fullRowCount FROM gene_chem_reference gcr INNER JOIN term g ON gcr.gene_id = g.id WHERE gcr.chem_id IN ( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '1399640') GROUP BY g.nm, g.id, g.acc_txt, g.acc_db_cd, g.nm_sort ORDER BY COUNT(gcr.ixn_id) DESC, g.nm_sort LIMIT 50;
Date: 2025-01-29 15:54:57 Duration: 1s547ms Bind query: yes
-
SELECT /* ChemGenesDAO */ g.nm geneSymbol, g.id geneId, g.acc_txt geneAcc, g.acc_db_cd geneAccDbCd, COUNT(gcr.ixn_id) ixnCount, COUNT(DISTINCT gcr.taxon_id) taxonCount, COUNT(*) OVER () fullRowCount FROM gene_chem_reference gcr INNER JOIN term g ON gcr.gene_id = g.id WHERE gcr.chem_id IN ( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '1399640') GROUP BY g.nm, g.id, g.acc_txt, g.acc_db_cd, g.nm_sort ORDER BY COUNT(gcr.ixn_id) DESC, g.nm_sort LIMIT 50;
Date: 2025-01-29 10:51:15 Duration: 1s540ms Bind query: yes
18 23 38s63ms 1s17ms 3s67ms 1s654ms select phenotypeterm.nm gonm, phenotypeterm.nm_html gonmhtml, phenotypeterm.acc_txt goacc, phenotypeterm.id goid, diseaseterm.nm diseasenm, diseaseterm.acc_txt diseaseacc, diseaseterm.acc_db_cd diseaseaccdbcd, diseaseterm.id diseaseid, via_gene_qty genenetworkcount, via_chem_qty chemnetworkcount, indirect_reference_qty referencecount, count(*) over () fullrowcount from phenotype_term pt inner join term phenotypeterm on pt.phenotype_id = phenotypeterm.id inner join term diseaseterm on pt.term_id = diseaseterm.id where diseaseterm.id in ( select distinct dp.descendant_object_id from dag_path dp where dp.ancestor_object_id = ?) and diseaseterm.object_type_id = ? order by chemnetworkcount desc, genenetworkcount desc limit ?;Times Reported Time consuming queries #18
Day Hour Count Duration Avg duration Jan 29 03 1 1s19ms 1s19ms 08 1 2s738ms 2s738ms 09 1 1s160ms 1s160ms 10 2 3s537ms 1s768ms 11 2 4s715ms 2s357ms 12 1 1s130ms 1s130ms 13 2 2s806ms 1s403ms 14 3 6s849ms 2s283ms 15 1 1s802ms 1s802ms 16 2 3s520ms 1s760ms 17 1 1s42ms 1s42ms 18 1 1s18ms 1s18ms 19 1 1s175ms 1s175ms 20 1 1s28ms 1s28ms 23 3 4s518ms 1s506ms [ User: pubeu - Total duration: 4s931ms - Times executed: 3 ]
-
SELECT /* GoDiseasesDAO */ phenotypeTerm.nm goNm, phenotypeTerm.nm_html goNmHTML, phenotypeTerm.acc_txt goAcc, phenotypeTerm.id goId, diseaseTerm.nm diseaseNm, diseaseTerm.acc_txt diseaseAcc, diseaseTerm.acc_db_cd diseaseAccDBCd, diseaseTerm.id diseaseId, via_gene_qty geneNetworkCount, via_chem_qty chemNetworkCount, indirect_reference_qty referenceCount, COUNT(*) OVER () fullRowCount FROM phenotype_term pt inner join term phenotypeTerm on pt.phenotype_id = phenotypeTerm.id inner join term diseaseTerm on pt.term_id = diseaseTerm.id WHERE diseaseTerm.id IN ( select distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id = '2079972') and diseaseTerm.object_type_id = 3 ORDER BY chemNetworkCount desc, geneNetworkCount desc LIMIT 50;
Date: 2025-01-29 14:20:50 Duration: 3s67ms Bind query: yes
-
SELECT /* GoDiseasesDAO */ phenotypeTerm.nm goNm, phenotypeTerm.nm_html goNmHTML, phenotypeTerm.acc_txt goAcc, phenotypeTerm.id goId, diseaseTerm.nm diseaseNm, diseaseTerm.acc_txt diseaseAcc, diseaseTerm.acc_db_cd diseaseAccDBCd, diseaseTerm.id diseaseId, via_gene_qty geneNetworkCount, via_chem_qty chemNetworkCount, indirect_reference_qty referenceCount, COUNT(*) OVER () fullRowCount FROM phenotype_term pt inner join term phenotypeTerm on pt.phenotype_id = phenotypeTerm.id inner join term diseaseTerm on pt.term_id = diseaseTerm.id WHERE diseaseTerm.id IN ( select distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id = '2079972') and diseaseTerm.object_type_id = 3 ORDER BY chemNetworkCount desc, geneNetworkCount desc LIMIT 50;
Date: 2025-01-29 08:58:33 Duration: 2s738ms Bind query: yes
-
SELECT /* GoDiseasesDAO */ phenotypeTerm.nm goNm, phenotypeTerm.nm_html goNmHTML, phenotypeTerm.acc_txt goAcc, phenotypeTerm.id goId, diseaseTerm.nm diseaseNm, diseaseTerm.acc_txt diseaseAcc, diseaseTerm.acc_db_cd diseaseAccDBCd, diseaseTerm.id diseaseId, via_gene_qty geneNetworkCount, via_chem_qty chemNetworkCount, indirect_reference_qty referenceCount, COUNT(*) OVER () fullRowCount FROM phenotype_term pt inner join term phenotypeTerm on pt.phenotype_id = phenotypeTerm.id inner join term diseaseTerm on pt.term_id = diseaseTerm.id WHERE diseaseTerm.id IN ( select distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id = '2079972') and diseaseTerm.object_type_id = 3 ORDER BY chemNetworkCount desc, geneNetworkCount desc LIMIT 50;
Date: 2025-01-29 10:04:54 Duration: 2s520ms Bind query: yes
19 20 2d23h20m44s 7s544ms 4h12m19s 3h34m2s insert into pubc.log_query (type_cd, query_tm, submission_qty, session_id, server_nm, remote_addr, results_qty, basic_query_txt, batch_input_type_txt, results_format_txt, dag_txt, action_type_txt, action_degree_type_txt, http_user_agent, node_nm, execution_ms) values (?, current_timestamp, ?, ?, ?, substr(?, ?, ?), ?, substr(?, ?, ?), ?, nullif (?, ?), nullif (substr(null, ?, ?), ?), nullif (substr(null, ?, ?), ?), nullif (substr(?, ?, ?), ?), nullif (substr(? ? ', ?));Times Reported Time consuming queries #19
Day Hour Count Duration Avg duration Jan 29 04 3 30s110ms 10s36ms 08 17 2d23h20m14s 4h11m46s [ User: pubeu - Total duration: 2d10h43m40s - Times executed: 14 ]
-
INSERT INTO pubc.log_query (type_cd, query_tm, submission_qty, session_id, server_nm, remote_addr, results_qty, basic_query_txt, batch_input_type_txt, results_format_txt, dag_txt, action_type_txt, action_degree_type_txt, http_user_agent, node_nm, execution_ms) VALUES ('venn_chems_curated', CURRENT_TIMESTAMP, 1, 'EC7FC337509500E3D06AD10B61F7616C', 'CTDBASE.ORG', SUBSTR('220.233.199.200', 1, 128), '2', SUBSTR('name:y42h9b.3|name:y57a10a.5', 1, 4000), 'gene', NULLIF ('html', ''), NULLIF (SUBSTR(NULL, 1, 128), ''), NULLIF (SUBSTR(NULL, 1, 4000), ''), NULLIF (SUBSTR('',1,4000),''), NULLIF (SUBSTR('Mozilla / 5.0 (Linux; Android 6.0; Nexus 5 Build / MRA58N) AppleWebKit / 537.36 (KHTML, 1, 256), ''), NULLIF (NULL, ''), NULLIF ('1', -1));
Date: 2025-01-29 08:15:20 Duration: 4h12m19s Database: ctdprd51 User: pubeu Bind query: yes
-
INSERT INTO pubc.log_query (type_cd, query_tm, submission_qty, session_id, server_nm, remote_addr, results_qty, basic_query_txt, batch_input_type_txt, results_format_txt, dag_txt, action_type_txt, action_degree_type_txt, http_user_agent, node_nm, execution_ms) VALUES ('venn_chems_curated', CURRENT_TIMESTAMP, 1, '04A7D382439C9CD34BFD40DC429D0A0D', 'CTDBASE.ORG', SUBSTR('98.240.235.167', 1, 128), '2', SUBSTR('name:c48b4.9|name:zim-2', 1, 4000), 'gene', NULLIF ('html', ''), NULLIF (SUBSTR(NULL, 1, 128), ''), NULLIF (SUBSTR(NULL, 1, 4000), ''), NULLIF (SUBSTR('',1,4000),''), NULLIF (SUBSTR('Mozilla / 5.0 (Linux; Android 6.0; Nexus 5 Build / MRA58N) AppleWebKit / 537.36 (KHTML, 1, 256), ''), NULLIF (NULL, ''), NULLIF ('1', -1));
Date: 2025-01-29 08:15:20 Duration: 4h12m18s Bind query: yes
-
INSERT INTO pubc.log_query (type_cd, query_tm, submission_qty, session_id, server_nm, remote_addr, results_qty, basic_query_txt, batch_input_type_txt, results_format_txt, dag_txt, action_type_txt, action_degree_type_txt, http_user_agent, node_nm, execution_ms) VALUES ('venn_chems_curated', CURRENT_TIMESTAMP, 1, '4FA5964A4AFBAF34C1D8AEB1B22DB17A', 'CTDBASE.ORG', SUBSTR('64.180.104.190', 1, 128), '2', SUBSTR('name:mir3594|name:mir6327', 1, 4000), 'gene', NULLIF ('html', ''), NULLIF (SUBSTR(NULL, 1, 128), ''), NULLIF (SUBSTR(NULL, 1, 4000), ''), NULLIF (SUBSTR('',1,4000),''), NULLIF (SUBSTR('Mozilla / 5.0 (iPhone; CPU iPhone OS 11_0 like Mac OS X) AppleWebKit / 537.36 (KHTML, 1, 256), ''), NULLIF (NULL, ''), NULLIF ('1', -1));
Date: 2025-01-29 08:15:20 Duration: 4h12m16s Bind query: yes
20 20 1m28s 4s243ms 5s27ms 4s437ms select g.nm "GeneSymbol", g.acc_txt "GeneID", d.nm "DiseaseName", d.acc_db_cd || ? || d.acc_txt "DiseaseID", case when gdr.via_chem_id is null then ( select string_agg(a.action_type_nm, ? order by a.action_type_nm) from gene_disease_axn a where a.gene_id = gdr.gene_id and a.disease_id = gdr.disease_id) else null end "DirectEvidence", c.nm "InferenceChemicalName", gdr.network_score "InferenceScore", string_agg(gdr.source_acc_txt, ? order by gdr.source_acc_txt) "OmimIDs", string_agg(r.acc_txt, ? order by r.acc_txt) "PubMedIDs" from gene_disease_reference gdr inner join term g on gdr.gene_id = g.id inner join term d on gdr.disease_id = d.id left outer join reference r on gdr.reference_id = r.id left outer join term c on gdr.via_chem_id = c.id group by g.nm, g.acc_txt, d.nm, d.acc_db_cd || ? || d.acc_txt, case when gdr.via_chem_id is null then ( select string_agg(a.action_type_nm, ? order by a.action_type_nm) from gene_disease_axn a where a.gene_id = gdr.gene_id and a.disease_id = gdr.disease_id) else null end, c.nm, gdr.network_score order by g.nm, d.nm g.nm "GeneSymbol", g.acc_txt "GeneID", d.nm "DiseaseName", d.acc_db_cd || ? || d.acc_txt "DiseaseID", case when gdr.via_chem_id is null then ( select string_agg(a.action_type_nm, ? order by a.action_type_nm) from gene_disease_axn a where a.gene_id = gdr.gene_id and a.disease_id = gdr.disease_id) else null end "DirectEvidence", c.nm "InferenceChemicalName", gdr.network_score "InferenceScore", string_agg(gdr.source_acc_txt, ? order by gdr.source_acc_txt) "OmimIDs", string_agg(r.acc_txt, ? order by r.acc_txt) "PubMedIDs" from gene_disease_reference gdr inner join term g on gdr.gene_id = g.id inner join term d on gdr.disease_id = d.id left outer join reference r on gdr.reference_id = r.id left outer join term c on gdr.via_chem_id = c.id group by g.nm, g.acc_txt, d.nm, d.acc_db_cd || ? || d.acc_txt, case when gdr.via_chem_id is null then ( select string_agg(a.action_type_nm, ? order by a.action_type_nm) from gene_disease_axn a where a.gene_id = gdr.gene_id and a.disease_id = gdr.disease_id) else null end, c.nm, gdr.network_score order by g.nm, d.nm;Times Reported Time consuming queries #20
Day Hour Count Duration Avg duration Jan 29 14 20 1m28s 4s437ms -
SELECT /* AllGDRelationsDAO */ g.nm "GeneSymbol", g.acc_txt "GeneID", d.nm "DiseaseName", d.acc_db_cd || ':' || d.acc_txt "DiseaseID", CASE WHEN gdr.via_chem_id IS NULL THEN ( SELECT STRING_AGG(a.action_type_nm, '|' ORDER BY a.action_type_nm) FROM gene_disease_axn a WHERE a.gene_id = gdr.gene_id AND a.disease_id = gdr.disease_id) ELSE NULL END "DirectEvidence", c.nm "InferenceChemicalName", gdr.network_score "InferenceScore", STRING_AGG(gdr.source_acc_txt, '|' ORDER BY gdr.source_acc_txt) "OmimIDs", STRING_AGG(r.acc_txt, '|' ORDER BY r.acc_txt) "PubMedIDs" FROM gene_disease_reference gdr INNER JOIN term g ON gdr.gene_id = g.id INNER JOIN term d ON gdr.disease_id = d.id LEFT OUTER JOIN reference r ON gdr.reference_id = r.id LEFT OUTER JOIN term c ON gdr.via_chem_id = c.id GROUP BY g.nm, g.acc_txt, d.nm, d.acc_db_cd || ':' || d.acc_txt, CASE WHEN gdr.via_chem_id IS NULL THEN ( SELECT STRING_AGG(a.action_type_nm, '|' ORDER BY a.action_type_nm) FROM gene_disease_axn a WHERE a.gene_id = gdr.gene_id AND a.disease_id = gdr.disease_id) ELSE NULL END, c.nm, gdr.network_score ORDER BY g.nm, d.nm g.nm "GeneSymbol", g.acc_txt "GeneID", d.nm "DiseaseName", d.acc_db_cd || ':' || d.acc_txt "DiseaseID", CASE WHEN gdr.via_chem_id IS NULL THEN ( SELECT STRING_AGG(a.action_type_nm, '|' ORDER BY a.action_type_nm) FROM gene_disease_axn a WHERE a.gene_id = gdr.gene_id AND a.disease_id = gdr.disease_id) ELSE NULL END "DirectEvidence", c.nm "InferenceChemicalName", gdr.network_score "InferenceScore", STRING_AGG(gdr.source_acc_txt, '|' ORDER BY gdr.source_acc_txt) "OmimIDs", STRING_AGG(r.acc_txt, '|' ORDER BY r.acc_txt) "PubMedIDs" FROM gene_disease_reference gdr INNER JOIN term g ON gdr.gene_id = g.id INNER JOIN term d ON gdr.disease_id = d.id LEFT OUTER JOIN reference r ON gdr.reference_id = r.id LEFT OUTER JOIN term c ON gdr.via_chem_id = c.id GROUP BY g.nm, g.acc_txt, d.nm, d.acc_db_cd || ':' || d.acc_txt, CASE WHEN gdr.via_chem_id IS NULL THEN ( SELECT STRING_AGG(a.action_type_nm, '|' ORDER BY a.action_type_nm) FROM gene_disease_axn a WHERE a.gene_id = gdr.gene_id AND a.disease_id = gdr.disease_id) ELSE NULL END, c.nm, gdr.network_score ORDER BY g.nm, d.nm;
Date: 2025-01-29 14:41:09 Duration: 5s27ms Bind query: yes
-
SELECT /* AllGDRelationsDAO */ g.nm "GeneSymbol", g.acc_txt "GeneID", d.nm "DiseaseName", d.acc_db_cd || ':' || d.acc_txt "DiseaseID", CASE WHEN gdr.via_chem_id IS NULL THEN ( SELECT STRING_AGG(a.action_type_nm, '|' ORDER BY a.action_type_nm) FROM gene_disease_axn a WHERE a.gene_id = gdr.gene_id AND a.disease_id = gdr.disease_id) ELSE NULL END "DirectEvidence", c.nm "InferenceChemicalName", gdr.network_score "InferenceScore", STRING_AGG(gdr.source_acc_txt, '|' ORDER BY gdr.source_acc_txt) "OmimIDs", STRING_AGG(r.acc_txt, '|' ORDER BY r.acc_txt) "PubMedIDs" FROM gene_disease_reference gdr INNER JOIN term g ON gdr.gene_id = g.id INNER JOIN term d ON gdr.disease_id = d.id LEFT OUTER JOIN reference r ON gdr.reference_id = r.id LEFT OUTER JOIN term c ON gdr.via_chem_id = c.id GROUP BY g.nm, g.acc_txt, d.nm, d.acc_db_cd || ':' || d.acc_txt, CASE WHEN gdr.via_chem_id IS NULL THEN ( SELECT STRING_AGG(a.action_type_nm, '|' ORDER BY a.action_type_nm) FROM gene_disease_axn a WHERE a.gene_id = gdr.gene_id AND a.disease_id = gdr.disease_id) ELSE NULL END, c.nm, gdr.network_score ORDER BY g.nm, d.nm g.nm "GeneSymbol", g.acc_txt "GeneID", d.nm "DiseaseName", d.acc_db_cd || ':' || d.acc_txt "DiseaseID", CASE WHEN gdr.via_chem_id IS NULL THEN ( SELECT STRING_AGG(a.action_type_nm, '|' ORDER BY a.action_type_nm) FROM gene_disease_axn a WHERE a.gene_id = gdr.gene_id AND a.disease_id = gdr.disease_id) ELSE NULL END "DirectEvidence", c.nm "InferenceChemicalName", gdr.network_score "InferenceScore", STRING_AGG(gdr.source_acc_txt, '|' ORDER BY gdr.source_acc_txt) "OmimIDs", STRING_AGG(r.acc_txt, '|' ORDER BY r.acc_txt) "PubMedIDs" FROM gene_disease_reference gdr INNER JOIN term g ON gdr.gene_id = g.id INNER JOIN term d ON gdr.disease_id = d.id LEFT OUTER JOIN reference r ON gdr.reference_id = r.id LEFT OUTER JOIN term c ON gdr.via_chem_id = c.id GROUP BY g.nm, g.acc_txt, d.nm, d.acc_db_cd || ':' || d.acc_txt, CASE WHEN gdr.via_chem_id IS NULL THEN ( SELECT STRING_AGG(a.action_type_nm, '|' ORDER BY a.action_type_nm) FROM gene_disease_axn a WHERE a.gene_id = gdr.gene_id AND a.disease_id = gdr.disease_id) ELSE NULL END, c.nm, gdr.network_score ORDER BY g.nm, d.nm;
Date: 2025-01-29 14:41:30 Duration: 4s838ms Bind query: yes
-
SELECT /* AllGDRelationsDAO */ g.nm "GeneSymbol", g.acc_txt "GeneID", d.nm "DiseaseName", d.acc_db_cd || ':' || d.acc_txt "DiseaseID", CASE WHEN gdr.via_chem_id IS NULL THEN ( SELECT STRING_AGG(a.action_type_nm, '|' ORDER BY a.action_type_nm) FROM gene_disease_axn a WHERE a.gene_id = gdr.gene_id AND a.disease_id = gdr.disease_id) ELSE NULL END "DirectEvidence", c.nm "InferenceChemicalName", gdr.network_score "InferenceScore", STRING_AGG(gdr.source_acc_txt, '|' ORDER BY gdr.source_acc_txt) "OmimIDs", STRING_AGG(r.acc_txt, '|' ORDER BY r.acc_txt) "PubMedIDs" FROM gene_disease_reference gdr INNER JOIN term g ON gdr.gene_id = g.id INNER JOIN term d ON gdr.disease_id = d.id LEFT OUTER JOIN reference r ON gdr.reference_id = r.id LEFT OUTER JOIN term c ON gdr.via_chem_id = c.id GROUP BY g.nm, g.acc_txt, d.nm, d.acc_db_cd || ':' || d.acc_txt, CASE WHEN gdr.via_chem_id IS NULL THEN ( SELECT STRING_AGG(a.action_type_nm, '|' ORDER BY a.action_type_nm) FROM gene_disease_axn a WHERE a.gene_id = gdr.gene_id AND a.disease_id = gdr.disease_id) ELSE NULL END, c.nm, gdr.network_score ORDER BY g.nm, d.nm g.nm "GeneSymbol", g.acc_txt "GeneID", d.nm "DiseaseName", d.acc_db_cd || ':' || d.acc_txt "DiseaseID", CASE WHEN gdr.via_chem_id IS NULL THEN ( SELECT STRING_AGG(a.action_type_nm, '|' ORDER BY a.action_type_nm) FROM gene_disease_axn a WHERE a.gene_id = gdr.gene_id AND a.disease_id = gdr.disease_id) ELSE NULL END "DirectEvidence", c.nm "InferenceChemicalName", gdr.network_score "InferenceScore", STRING_AGG(gdr.source_acc_txt, '|' ORDER BY gdr.source_acc_txt) "OmimIDs", STRING_AGG(r.acc_txt, '|' ORDER BY r.acc_txt) "PubMedIDs" FROM gene_disease_reference gdr INNER JOIN term g ON gdr.gene_id = g.id INNER JOIN term d ON gdr.disease_id = d.id LEFT OUTER JOIN reference r ON gdr.reference_id = r.id LEFT OUTER JOIN term c ON gdr.via_chem_id = c.id GROUP BY g.nm, g.acc_txt, d.nm, d.acc_db_cd || ':' || d.acc_txt, CASE WHEN gdr.via_chem_id IS NULL THEN ( SELECT STRING_AGG(a.action_type_nm, '|' ORDER BY a.action_type_nm) FROM gene_disease_axn a WHERE a.gene_id = gdr.gene_id AND a.disease_id = gdr.disease_id) ELSE NULL END, c.nm, gdr.network_score ORDER BY g.nm, d.nm;
Date: 2025-01-29 14:26:15 Duration: 4s491ms Bind query: yes
Normalized slowest queries (N)
Rank Min duration Max duration Avg duration Times executed Total duration Query 1 0ms 5h3m22s 5h3m22s 1 5h3m22s vacuum full analyze;Times Reported Time consuming queries #1
Day Hour Count Duration Avg duration Jan 29 08 1 5h3m22s 5h3m22s -
VACUUM FULL ANALYZE;
Date: 2025-01-29 08:17:23 Duration: 5h3m22s Bind query: yes
-
VACUUM FULL ANALYZE;
Date: 2025-01-29 03:31:59 Duration: 0ms
2 4h12m25s 4h12m25s 4h12m25s 1 4h12m25s insert into pubc.log_query (type_cd, query_tm, submission_qty, session_id, server_nm, remote_addr, results_qty, basic_query_txt, batch_input_type_txt, results_format_txt, dag_txt, action_type_txt, action_degree_type_txt, http_user_agent, node_nm;Times Reported Time consuming queries #2
Day Hour Count Duration Avg duration Jan 29 08 1 4h12m25s 4h12m25s -
INSERT INTO pubc.log_query (type_cd, query_tm, submission_qty, session_id, server_nm, remote_addr, results_qty, basic_query_txt, batch_input_type_txt, results_format_txt, dag_txt, action_type_txt, action_degree_type_txt, http_user_agent, node_nm;
Date: 2025-01-29 08:15:20 Duration: 4h12m25s Bind query: yes
3 4h12m23s 4h12m23s 4h12m23s 1 4h12m23s insert into pubc.log_query (query_tm, submission_qty, session_id, server_nm, remote_addr, http_user_agent, results_qty, execution_ms, type_cd, basic_query_txt, basic_query_type, node_nm) values (current_timestamp, ?, ?, ?, substr(?, ?, ?), nullif (substr(? ? ? ? ? gene ? putative elongation of very long chain fatty acids protein ? ? contains ? '));Times Reported Time consuming queries #3
Day Hour Count Duration Avg duration Jan 29 08 1 4h12m23s 4h12m23s -
INSERT INTO pubc.log_query (query_tm, submission_qty, session_id, server_nm, remote_addr, http_user_agent, results_qty, execution_ms, type_cd, basic_query_txt, basic_query_type, node_nm) VALUES (CURRENT_TIMESTAMP, 1, '203B0CDC709AAB3B13A82B6A3752ACA4', 'CTDBASE.ORG', SUBSTR('46.38.241.197', 1, 128), NULLIF (SUBSTR('Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML,1,256),'') ,' 2' ,NULLIF('9',-1) ,'gene' ,SUBSTR('putative elongation of very long chain fatty acids protein 6',1,1000) ,'contains ', NULLIF (NULL, ''));
Date: 2025-01-29 08:15:20 Duration: 4h12m23s Bind query: yes
4 4h12m23s 4h12m23s 4h12m23s 1 4h12m23s insert into pubc.log_query (type_cd, query_tm, submission_qty, session_id, server_nm, remote_addr, results_qty, basic_query_txt, batch_input_type_txt, results_format_txt, dag_txt, action_type_txt, action_degree_type_txt, http_user_agent, node_nm, execution_ms) values (, current_timestamp, ?,,, substr(, ?, ?),, substr(, ?, ?);Times Reported Time consuming queries #4
Day Hour Count Duration Avg duration Jan 29 08 1 4h12m23s 4h12m23s -
INSERT INTO pubc.log_query (type_cd, query_tm, submission_qty, session_id, server_nm, remote_addr, results_qty, basic_query_txt, batch_input_type_txt, results_format_txt, dag_txt, action_type_txt, action_degree_type_txt, http_user_agent, node_nm, execution_ms) VALUES (, CURRENT_TIMESTAMP, 1,,, SUBSTR(, 1, 128),, SUBSTR(, 1, 4000);
Date: 2025-01-29 08:15:20 Duration: 4h12m23s Bind query: yes
5 4h12m23s 4h12m23s 4h12m23s 1 4h12m23s insert into pubc.log_query (query_tm, submission_qty, session_id, server_nm, remote_addr, http_user_agent, results_qty, execution_ms, type_cd, basic_query_txt, basic_query_type, node_nm) values (current_timestamp, ?,,, substr(, ?, ?);Times Reported Time consuming queries #5
Day Hour Count Duration Avg duration Jan 29 08 1 4h12m23s 4h12m23s [ User: pubeu - Total duration: 4h12m23s - Times executed: 1 ]
-
INSERT INTO pubc.log_query (query_tm, submission_qty, session_id, server_nm, remote_addr, http_user_agent, results_qty, execution_ms, type_cd, basic_query_txt, basic_query_type, node_nm) VALUES (CURRENT_TIMESTAMP, 1,,, SUBSTR(, 1, 128);
Date: 2025-01-29 08:15:20 Duration: 4h12m23s Database: ctdprd51 User: pubeu Bind query: yes
6 4h12m16s 4h12m16s 4h12m16s 1 4h12m16s insert into pubc.log_query (query_tm, submission_qty, session_id, server_nm, remote_addr, http_user_agent, results_qty, execution_ms, type_cd, basic_query_txt, basic_query_type, node_nm) values (current_timestamp, ?, ?, ?, substr(?, ?, ?), nullif (substr(? ? ? ? ? gene ? job18_014864 ? contains ? '));Times Reported Time consuming queries #6
Day Hour Count Duration Avg duration Jan 29 08 1 4h12m16s 4h12m16s [ User: pubeu - Total duration: 4h12m16s - Times executed: 1 ]
-
INSERT INTO pubc.log_query (query_tm, submission_qty, session_id, server_nm, remote_addr, http_user_agent, results_qty, execution_ms, type_cd, basic_query_txt, basic_query_type, node_nm) VALUES (CURRENT_TIMESTAMP, 1, 'C0A7049223935B0DC40D69046F3240B3', 'CTDBASE.ORG', SUBSTR('45.129.181.193', 1, 128), NULLIF (SUBSTR('Mozilla / 5.0 (Windows NT 10.0; Win64; x64) AppleWebKit / 537.36 (KHTML, 1, 256), ''), '1', NULLIF ('4', -1), 'gene', SUBSTR('job18_014864', 1, 1000), 'contains', NULLIF (NULL, ''));
Date: 2025-01-29 08:15:20 Duration: 4h12m16s Database: ctdprd51 User: pubeu Bind query: yes
7 4h12m10s 4h12m10s 4h12m10s 1 4h12m10s insert into pubc.log_query (type_cd, query_tm, submission_qty, session_id, server_nm, remote_addr, results_qty, basic_query_txt, batch_input_type_txt, results_format_txt, dag_txt, action_type_txt, action_degree_type_txt, http_user_agent, node_nm, execution_ms) values (, current_timestamp, ?,,, substr(, ?, ?),, substr(, ?, ?),, nullif (, ?), nullif (substr(, ?, ?), ?);Times Reported Time consuming queries #7
Day Hour Count Duration Avg duration Jan 29 08 1 4h12m10s 4h12m10s [ User: pubeu - Total duration: 4h12m10s - Times executed: 1 ]
-
INSERT INTO pubc.log_query (type_cd, query_tm, submission_qty, session_id, server_nm, remote_addr, results_qty, basic_query_txt, batch_input_type_txt, results_format_txt, dag_txt, action_type_txt, action_degree_type_txt, http_user_agent, node_nm, execution_ms) VALUES (, CURRENT_TIMESTAMP, 1,,, SUBSTR(, 1, 128),, SUBSTR(, 1, 4000),, NULLIF (, ''), NULLIF (SUBSTR(, 1, 128), '');
Date: 2025-01-29 08:15:20 Duration: 4h12m10s Database: ctdprd51 User: pubeu Bind query: yes
8 4h12m9s 4h12m9s 4h12m9s 1 4h12m9s insert into pubc.log_query (query_tm, submission_qty;Times Reported Time consuming queries #8
Day Hour Count Duration Avg duration Jan 29 08 1 4h12m9s 4h12m9s [ User: pubeu - Total duration: 4h12m9s - Times executed: 1 ]
-
INSERT INTO pubc.log_query (query_tm, submission_qty;
Date: 2025-01-29 08:15:20 Duration: 4h12m9s Database: ctdprd51 User: pubeu Bind query: yes
9 4h12m5s 4h12m5s 4h12m5s 1 4h12m5s insert into pubc.log_query (type_cd, query_tm, submission_qty, session_id, server_nm, remote_addr, results_qty, basic_query_txt, batch_input_type_txt, results_format_txt, dag_txt, action_type_txt, action_degree_type_txt, http_user_agent, node_nm, execution_ms) values (, current_timestamp, ?,,, substr(, ?, ?),, substr(, ?, ?),, nullif (, ?), nullif (substr(, ?, ?), ?), nullif (substr(, ?, ?), ?), nullif (substr(, ?, ?), ?);Times Reported Time consuming queries #9
Day Hour Count Duration Avg duration Jan 29 08 1 4h12m5s 4h12m5s [ User: pubeu - Total duration: 4h12m5s - Times executed: 1 ]
-
INSERT INTO pubc.log_query (type_cd, query_tm, submission_qty, session_id, server_nm, remote_addr, results_qty, basic_query_txt, batch_input_type_txt, results_format_txt, dag_txt, action_type_txt, action_degree_type_txt, http_user_agent, node_nm, execution_ms) VALUES (, CURRENT_TIMESTAMP, 1,,, SUBSTR(, 1, 128),, SUBSTR(, 1, 4000),, NULLIF (, ''), NULLIF (SUBSTR(, 1, 128), ''), NULLIF (SUBSTR(, 1, 4000), ''), NULLIF (SUBSTR(, 1, 4000), '');
Date: 2025-01-29 08:15:20 Duration: 4h12m5s Database: ctdprd51 User: pubeu Bind query: yes
10 4h11m56s 4h11m56s 4h11m56s 1 4h11m56s insert into pubc.log_query (type_cd, query_tm, submission_qty, session_id, server_nm, remote_addr, results_qty, basic_query_txt, batch_input_type_txt, results_format_txt, dag_txt, action_type_txt, action_degree_type_txt, http_user_agent, node_nm, execution_ms) values (, current_timestamp, ?,;Times Reported Time consuming queries #10
Day Hour Count Duration Avg duration Jan 29 08 1 4h11m56s 4h11m56s [ User: pubeu - Total duration: 4h11m56s - Times executed: 1 ]
-
INSERT INTO pubc.log_query (type_cd, query_tm, submission_qty, session_id, server_nm, remote_addr, results_qty, basic_query_txt, batch_input_type_txt, results_format_txt, dag_txt, action_type_txt, action_degree_type_txt, http_user_agent, node_nm, execution_ms) VALUES (, CURRENT_TIMESTAMP, 1,;
Date: 2025-01-29 08:15:20 Duration: 4h11m56s Database: ctdprd51 User: pubeu Bind query: yes
11 4h11m51s 4h11m51s 4h11m51s 1 4h11m51s insert into pubc.log_query (type_cd, query_tm, submission_qty, session_id, server_nm, remote_addr, results_qty, basic_query_txt, batch_input_type_txt, results_format_txt, dag_txt, action_type_txt, action_degree_type_txt, http_user_agent, node_nm, execution_ms) values (, current_timestamp, ?,,, substr(, ?, ?),, substr(, ?, ?),;Times Reported Time consuming queries #11
Day Hour Count Duration Avg duration Jan 29 08 1 4h11m51s 4h11m51s [ User: pubeu - Total duration: 4h11m51s - Times executed: 1 ]
-
INSERT INTO pubc.log_query (type_cd, query_tm, submission_qty, session_id, server_nm, remote_addr, results_qty, basic_query_txt, batch_input_type_txt, results_format_txt, dag_txt, action_type_txt, action_degree_type_txt, http_user_agent, node_nm, execution_ms) VALUES (, CURRENT_TIMESTAMP, 1,,, SUBSTR(, 1, 128),, SUBSTR(, 1, 4000),;
Date: 2025-01-29 08:15:20 Duration: 4h11m51s Database: ctdprd51 User: pubeu Bind query: yes
12 4h11m14s 4h12m22s 4h11m48s 2 8h23m36s insert into pubc.log_query (type_cd, query_tm, submission_qty, session_id, server_nm, remote_addr;Times Reported Time consuming queries #12
Day Hour Count Duration Avg duration Jan 29 08 2 8h23m36s 4h11m48s [ User: pubeu - Total duration: 8h23m36s - Times executed: 2 ]
-
INSERT INTO pubc.log_query (type_cd, query_tm, submission_qty, session_id, server_nm, remote_addr;
Date: 2025-01-29 08:15:20 Duration: 4h12m22s Database: ctdprd51 User: pubeu Bind query: yes
-
INSERT INTO pubc.log_query (type_cd, query_tm, submission_qty, session_id, server_nm, remote_addr;
Date: 2025-01-29 08:15:20 Duration: 4h11m14s Database: ctdprd51 User: pubeu Bind query: yes
13 4h11m22s 4h12m3s 4h11m42s 2 8h23m25s insert into pubc.log_query (type_cd, query_tm, submission_qty, session_id, server_nm, remote_addr, results_qty, basic_query_txt, batch_input_type_txt, results_format_txt, dag_txt, action_type_txt, action_degree_type_txt, http_user_agent, node_nm, execution_ms) values;Times Reported Time consuming queries #13
Day Hour Count Duration Avg duration Jan 29 08 2 8h23m25s 4h11m42s [ User: pubeu - Total duration: 8h23m25s - Times executed: 2 ]
-
INSERT INTO pubc.log_query (type_cd, query_tm, submission_qty, session_id, server_nm, remote_addr, results_qty, basic_query_txt, batch_input_type_txt, results_format_txt, dag_txt, action_type_txt, action_degree_type_txt, http_user_agent, node_nm, execution_ms) VALUES;
Date: 2025-01-29 08:15:20 Duration: 4h12m3s Database: ctdprd51 User: pubeu Bind query: yes
-
INSERT INTO pubc.log_query (type_cd, query_tm, submission_qty, session_id, server_nm, remote_addr, results_qty, basic_query_txt, batch_input_type_txt, results_format_txt, dag_txt, action_type_txt, action_degree_type_txt, http_user_agent, node_nm, execution_ms) VALUES;
Date: 2025-01-29 08:15:20 Duration: 4h11m22s Database: ctdprd51 User: pubeu Bind query: yes
14 4h11m40s 4h11m40s 4h11m40s 1 4h11m40s insert into pubc.log_query (type_cd, query_tm, submission_qty, session_id, server_nm, remote_addr, results_qty, basic_query_txt, batch_input_type_txt, results_format_txt;Times Reported Time consuming queries #14
Day Hour Count Duration Avg duration Jan 29 08 1 4h11m40s 4h11m40s [ User: pubeu - Total duration: 4h11m40s - Times executed: 1 ]
-
INSERT INTO pubc.log_query (type_cd, query_tm, submission_qty, session_id, server_nm, remote_addr, results_qty, basic_query_txt, batch_input_type_txt, results_format_txt;
Date: 2025-01-29 08:15:20 Duration: 4h11m40s Database: ctdprd51 User: pubeu Bind query: yes
15 4h11m35s 4h11m35s 4h11m35s 1 4h11m35s insert into pubc.log_query (query_tm, submission_qty, session_id, server_nm, remote_addr, http_user_agent, results_qty, execution_ms, type_cd, basic_query_txt, basic_query_type, node_nm) values (current_timestamp, ?, ?, ?, substr(?, ?, ?), nullif (substr(? ? ? ? ? gene ? z169_09976 ? contains ? '));Times Reported Time consuming queries #15
Day Hour Count Duration Avg duration Jan 29 08 1 4h11m35s 4h11m35s [ User: pubeu - Total duration: 4h11m35s - Times executed: 1 ]
-
INSERT INTO pubc.log_query (query_tm, submission_qty, session_id, server_nm, remote_addr, http_user_agent, results_qty, execution_ms, type_cd, basic_query_txt, basic_query_type, node_nm) VALUES (CURRENT_TIMESTAMP, 1, 'E964CE05E0FF3962154CE42F4A6BB2A7', 'CTDBASE.ORG', SUBSTR('94.16.113.171', 1, 128), NULLIF (SUBSTR('Mozilla / 5.0 (Windows NT 10.0; Win64; x64) AppleWebKit / 537.36 (KHTML, 1, 256), ''), '1', NULLIF ('5', -1), 'gene', SUBSTR('z169_09976', 1, 1000), 'contains', NULLIF (NULL, ''));
Date: 2025-01-29 08:15:20 Duration: 4h11m35s Database: ctdprd51 User: pubeu Bind query: yes
16 7s544ms 4h12m19s 3h34m2s 20 2d23h20m44s insert into pubc.log_query (type_cd, query_tm, submission_qty, session_id, server_nm, remote_addr, results_qty, basic_query_txt, batch_input_type_txt, results_format_txt, dag_txt, action_type_txt, action_degree_type_txt, http_user_agent, node_nm, execution_ms) values (?, current_timestamp, ?, ?, ?, substr(?, ?, ?), ?, substr(?, ?, ?), ?, nullif (?, ?), nullif (substr(null, ?, ?), ?), nullif (substr(null, ?, ?), ?), nullif (substr(?, ?, ?), ?), nullif (substr(? ? ', ?));Times Reported Time consuming queries #16
Day Hour Count Duration Avg duration Jan 29 04 3 30s110ms 10s36ms 08 17 2d23h20m14s 4h11m46s [ User: pubeu - Total duration: 2d10h43m40s - Times executed: 14 ]
-
INSERT INTO pubc.log_query (type_cd, query_tm, submission_qty, session_id, server_nm, remote_addr, results_qty, basic_query_txt, batch_input_type_txt, results_format_txt, dag_txt, action_type_txt, action_degree_type_txt, http_user_agent, node_nm, execution_ms) VALUES ('venn_chems_curated', CURRENT_TIMESTAMP, 1, 'EC7FC337509500E3D06AD10B61F7616C', 'CTDBASE.ORG', SUBSTR('220.233.199.200', 1, 128), '2', SUBSTR('name:y42h9b.3|name:y57a10a.5', 1, 4000), 'gene', NULLIF ('html', ''), NULLIF (SUBSTR(NULL, 1, 128), ''), NULLIF (SUBSTR(NULL, 1, 4000), ''), NULLIF (SUBSTR('',1,4000),''), NULLIF (SUBSTR('Mozilla / 5.0 (Linux; Android 6.0; Nexus 5 Build / MRA58N) AppleWebKit / 537.36 (KHTML, 1, 256), ''), NULLIF (NULL, ''), NULLIF ('1', -1));
Date: 2025-01-29 08:15:20 Duration: 4h12m19s Database: ctdprd51 User: pubeu Bind query: yes
-
INSERT INTO pubc.log_query (type_cd, query_tm, submission_qty, session_id, server_nm, remote_addr, results_qty, basic_query_txt, batch_input_type_txt, results_format_txt, dag_txt, action_type_txt, action_degree_type_txt, http_user_agent, node_nm, execution_ms) VALUES ('venn_chems_curated', CURRENT_TIMESTAMP, 1, '04A7D382439C9CD34BFD40DC429D0A0D', 'CTDBASE.ORG', SUBSTR('98.240.235.167', 1, 128), '2', SUBSTR('name:c48b4.9|name:zim-2', 1, 4000), 'gene', NULLIF ('html', ''), NULLIF (SUBSTR(NULL, 1, 128), ''), NULLIF (SUBSTR(NULL, 1, 4000), ''), NULLIF (SUBSTR('',1,4000),''), NULLIF (SUBSTR('Mozilla / 5.0 (Linux; Android 6.0; Nexus 5 Build / MRA58N) AppleWebKit / 537.36 (KHTML, 1, 256), ''), NULLIF (NULL, ''), NULLIF ('1', -1));
Date: 2025-01-29 08:15:20 Duration: 4h12m18s Bind query: yes
-
INSERT INTO pubc.log_query (type_cd, query_tm, submission_qty, session_id, server_nm, remote_addr, results_qty, basic_query_txt, batch_input_type_txt, results_format_txt, dag_txt, action_type_txt, action_degree_type_txt, http_user_agent, node_nm, execution_ms) VALUES ('venn_chems_curated', CURRENT_TIMESTAMP, 1, '4FA5964A4AFBAF34C1D8AEB1B22DB17A', 'CTDBASE.ORG', SUBSTR('64.180.104.190', 1, 128), '2', SUBSTR('name:mir3594|name:mir6327', 1, 4000), 'gene', NULLIF ('html', ''), NULLIF (SUBSTR(NULL, 1, 128), ''), NULLIF (SUBSTR(NULL, 1, 4000), ''), NULLIF (SUBSTR('',1,4000),''), NULLIF (SUBSTR('Mozilla / 5.0 (iPhone; CPU iPhone OS 11_0 like Mac OS X) AppleWebKit / 537.36 (KHTML, 1, 256), ''), NULLIF (NULL, ''), NULLIF ('1', -1));
Date: 2025-01-29 08:15:20 Duration: 4h12m16s Bind query: yes
17 2s76ms 4h12m 3h8m42s 4 12h34m50s insert into pubc.log_query;Times Reported Time consuming queries #17
Day Hour Count Duration Avg duration Jan 29 04 1 2s76ms 2s76ms 08 3 12h34m48s 4h11m36s [ User: pubeu - Total duration: 12h34m50s - Times executed: 4 ]
-
INSERT INTO pubc.log_query;
Date: 2025-01-29 08:15:20 Duration: 4h12m Database: ctdprd51 User: pubeu Bind query: yes
-
INSERT INTO pubc.log_query;
Date: 2025-01-29 08:15:20 Duration: 4h11m34s Database: ctdprd51 User: pubeu Bind query: yes
-
INSERT INTO pubc.log_query;
Date: 2025-01-29 08:15:20 Duration: 4h11m13s Database: ctdprd51 User: pubeu Bind query: yes
18 2h10m18s 2h15m18s 2h12m48s 2 4h25m37s select n.tableoid, n.oid, n.nspname, ( select rolname from pg_catalog.pg_roles where oid = nspowner) as rolname, ( select pg_catalog.array_agg(acl order by row_n) from ( select acl, row_n from pg_catalog.unnest(coalesce(n.nspacl, pg_catalog.acldefault(?, n.nspowner))) with ordinality as perm (acl, row_n) where not exists ( select ? from pg_catalog.unnest(coalesce(pip.initprivs, pg_catalog.acldefault(?, n.nspowner))) as init (init_acl) where acl = init_acl)) as foo) as nspacl, ( select pg_catalog.array_agg(acl order by row_n) from ( select acl, row_n from pg_catalog.unnest(coalesce(pip.initprivs, pg_catalog.acldefault(?, n.nspowner))) with ordinality as initp (acl, row_n) where not exists ( select ? from pg_catalog.unnest(coalesce(n.nspacl, pg_catalog.acldefault(?, n.nspowner))) as permp (orig_acl) where acl = orig_acl)) as foo) as rnspacl, null as initnspacl, null as initrnspacl from pg_namespace n left join pg_init_privs pip on (n.oid = pip.objoid and pip.classoid = ?::regclass and pip.objsubid = ?);Times Reported Time consuming queries #18
Day Hour Count Duration Avg duration Jan 29 08 2 4h25m37s 2h12m48s [ User: postgres - Total duration: 4h25m37s - Times executed: 2 ]
[ Application: pg_dump - Total duration: 4h25m37s - Times executed: 2 ]
-
SELECT n.tableoid, n.oid, n.nspname, ( SELECT rolname FROM pg_catalog.pg_roles WHERE oid = nspowner) AS rolname, ( SELECT pg_catalog.array_agg(acl ORDER BY row_n) FROM ( SELECT acl, row_n FROM pg_catalog.unnest(coalesce(n.nspacl, pg_catalog.acldefault('n', n.nspowner))) WITH ORDINALITY AS perm (acl, row_n) WHERE NOT EXISTS ( SELECT 1 FROM pg_catalog.unnest(coalesce(pip.initprivs, pg_catalog.acldefault('n', n.nspowner))) AS init (init_acl) WHERE acl = init_acl)) as foo) as nspacl, ( SELECT pg_catalog.array_agg(acl ORDER BY row_n) FROM ( SELECT acl, row_n FROM pg_catalog.unnest(coalesce(pip.initprivs, pg_catalog.acldefault('n', n.nspowner))) WITH ORDINALITY AS initp (acl, row_n) WHERE NOT EXISTS ( SELECT 1 FROM pg_catalog.unnest(coalesce(n.nspacl, pg_catalog.acldefault('n', n.nspowner))) AS permp (orig_acl) WHERE acl = orig_acl)) as foo) as rnspacl, NULL as initnspacl, NULL as initrnspacl FROM pg_namespace n LEFT JOIN pg_init_privs pip ON (n.oid = pip.objoid AND pip.classoid = 'pg_namespace'::regclass AND pip.objsubid = 0);
Date: 2025-01-29 08:15:20 Duration: 2h15m18s Database: ctdprd51 User: postgres Application: pg_dump
-
SELECT n.tableoid, n.oid, n.nspname, ( SELECT rolname FROM pg_catalog.pg_roles WHERE oid = nspowner) AS rolname, ( SELECT pg_catalog.array_agg(acl ORDER BY row_n) FROM ( SELECT acl, row_n FROM pg_catalog.unnest(coalesce(n.nspacl, pg_catalog.acldefault('n', n.nspowner))) WITH ORDINALITY AS perm (acl, row_n) WHERE NOT EXISTS ( SELECT 1 FROM pg_catalog.unnest(coalesce(pip.initprivs, pg_catalog.acldefault('n', n.nspowner))) AS init (init_acl) WHERE acl = init_acl)) as foo) as nspacl, ( SELECT pg_catalog.array_agg(acl ORDER BY row_n) FROM ( SELECT acl, row_n FROM pg_catalog.unnest(coalesce(pip.initprivs, pg_catalog.acldefault('n', n.nspowner))) WITH ORDINALITY AS initp (acl, row_n) WHERE NOT EXISTS ( SELECT 1 FROM pg_catalog.unnest(coalesce(n.nspacl, pg_catalog.acldefault('n', n.nspowner))) AS permp (orig_acl) WHERE acl = orig_acl)) as foo) as rnspacl, NULL as initnspacl, NULL as initrnspacl FROM pg_namespace n LEFT JOIN pg_init_privs pip ON (n.oid = pip.objoid AND pip.classoid = 'pg_namespace'::regclass AND pip.objsubid = 0);
Date: 2025-01-29 08:15:20 Duration: 2h10m18s Database: ctdprd51 User: postgres Application: pg_dump
19 14s34ms 4h11m43s 2h5m58s 2 4h11m57s insert into pubc.log_query (type_cd, query_tm;Times Reported Time consuming queries #19
Day Hour Count Duration Avg duration Jan 29 04 1 14s34ms 14s34ms 08 1 4h11m43s 4h11m43s [ User: pubeu - Total duration: 4h11m43s - Times executed: 1 ]
-
INSERT INTO pubc.log_query (type_cd, query_tm;
Date: 2025-01-29 08:15:20 Duration: 4h11m43s Database: ctdprd51 User: pubeu Bind query: yes
-
INSERT INTO pubc.log_query (type_cd, query_tm;
Date: 2025-01-29 04:02:19 Duration: 14s34ms Bind query: yes
20 9s145ms 4h11m46s 2h5m57s 2 4h11m55s insert into pubc.log_query (type_cd, query_tm, submission_qty, session_id, server_nm, remote_addr, results_qty, basic_query_txt, batch_input_type_txt;Times Reported Time consuming queries #20
Day Hour Count Duration Avg duration Jan 29 04 1 9s145ms 9s145ms 08 1 4h11m46s 4h11m46s [ User: pubeu - Total duration: 4h11m46s - Times executed: 1 ]
-
INSERT INTO pubc.log_query (type_cd, query_tm, submission_qty, session_id, server_nm, remote_addr, results_qty, basic_query_txt, batch_input_type_txt;
Date: 2025-01-29 08:15:20 Duration: 4h11m46s Database: ctdprd51 User: pubeu Bind query: yes
-
INSERT INTO pubc.log_query (type_cd, query_tm, submission_qty, session_id, server_nm, remote_addr, results_qty, basic_query_txt, batch_input_type_txt;
Date: 2025-01-29 04:02:19 Duration: 9s145ms 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 5s179ms 1 5s179ms 5s179ms 5s179ms select /* PWA_TERM_SQL */ ;Times Reported Time consuming bind #1
Day Hour Count Duration Avg duration Jan 29 04 1 5s179ms 5s179ms -
select /* PWA_TERM_SQL */ ;
Date: 2025-01-29 04:02:08 Duration: 5s179ms Database: postgres parameters: $1 = '0633968415BD09F5D405221C7ACCAEA1', $2 = 'CTDBASE.ORG', $3 = '37.221.194.170', $4 = 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/100.0.6115.337 Safari/537.36', $5 = '2', $6 = '5', $7 = 'chem', $8 = '1,3-butadiene, 2-chloro-', $9 = 'contains', $10 = NULL
2 4s856ms 1 4s856ms 4s856ms 4s856ms SELECT /* TermLabelsHelper */ ;Times Reported Time consuming bind #2
Day Hour Count Duration Avg duration 08 1 4s856ms 4s856ms -
SELECT /* TermLabelsHelper */ ;
Date: 2025-01-29 04:02:19 Duration: 4s856ms Database: postgres
3 3s56ms 1 3s56ms 3s56ms 3s56ms SELECT /* GeneDiseaseRefActionsDAO */ DISTINCT;Times Reported Time consuming bind #3
Day Hour Count Duration Avg duration 08 1 3s56ms 3s56ms -
SELECT /* GeneDiseaseRefActionsDAO */ DISTINCT;
Date: 2025-01-29 04:02:19 Duration: 3s56ms Database: postgres parameters: $1 = '203B0CDC709AAB3B13A82B6A3752ACA4', $2 = 'CTDBASE.ORG', $3 = '46.38.241.197', $4 = 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/100.0.4194.682 Safari/537.36', $5 = '2', $6 = '9', $7 = 'gene', $8 = 'putative elongation of very long chain fatty acids protein 6', $9 = 'contains', $10 = NULL
4 0ms 98 0ms 0ms 0ms ;Times Reported Time consuming bind #4
Day Hour Count Duration Avg duration Jan 28 08 2 0ms 0ms 09 8 0ms 0ms 10 7 0ms 0ms 11 4 0ms 0ms 12 1 0ms 0ms 13 4 0ms 0ms 14 1 0ms 0ms Jan 29 00 1 0ms 0ms 02 1 0ms 0ms 03 1 0ms 0ms 08 5 0ms 0ms 09 6 0ms 0ms 10 13 0ms 0ms 11 1 0ms 0ms 12 12 0ms 0ms 14 20 0ms 0ms 15 3 0ms 0ms 16 8 0ms 0ms [ User: pubeu - Total duration: 1m17s - Times executed: 26 ]
-
;
Date: Duration: 0ms Database: postgres parameters: $1 = '1439319'
-
Events
Log levels
Key values
- 230,335 Log entries
Events distribution
Key values
- 0 PANIC entries
- 22946 FATAL entries
- 113 ERROR entries
- 1319 WARNING entries
Most Frequent Errors/Events
Key values
- 22,891 Max number of times the same event was reported
- 24,378 Total events found
Rank Times reported Error 1 22,891 FATAL: remaining connection slots are reserved for non-replication superuser connections
Times Reported Most Frequent Error / Event #1
Day Hour Count Jan 29 04 10,117 05 7,126 06 2,459 07 2,518 08 671 2 1,046 WARNING: skipping "..." --- only table or database owner can vacuum it
Times Reported Most Frequent Error / Event #2
Day Hour Count Jan 29 03 1,046 3 224 WARNING: skipping "..." --- only superuser or database owner can vacuum it
Times Reported Most Frequent Error / Event #3
Day Hour Count Jan 29 03 224 4 61 ERROR: syntax error in ts"..."
Times Reported Most Frequent Error / Event #4
Day Hour Count Jan 29 00 1 01 2 02 4 03 1 11 2 16 1 17 1 19 2 20 9 21 15 22 16 23 7 - ERROR: syntax error in ts"JCB ETC"
- ERROR: syntax error in ts"SATELLITE & HEAD & K<T"
- ERROR: syntax error in ts"(F-682 HILOGIC | F682 HILOGIC)"
Statement: SELECT /* GeneBasicQueryDAO */ sq.* ,COUNT(*) OVER() fullRowCount FROM ( SELECT /* label */ t.acc_txt acc ,'name:' || t.nm accQueryStr ,t.nm ,t.nm_html nmHtml ,t.secondary_nm secondaryNm ,l.nm matchedNm ,lt.nm_display matchedType ,CASE WHEN lt.nm_display='Symbol' THEN true ELSE false END isNameMatch ,CASE WHEN lt.nm_display='Name' THEN true ELSE false END isSecondaryNameMatch ,t.has_chems hasChems ,t.has_diseases hasDiseases ,t.has_exposures hasExposures ,CASE WHEN UPPER(l.nm) = $1 THEN 1 ELSE 2 END relevance ,t.nm_sort ,t.id ,t.has_phenotypes hasPhenotypes FROM term t INNER JOIN term_label l ON l.term_id = t.id INNER JOIN term_label_type lt ON l.term_label_type_id = lt.id WHERE l.object_type_id = 4 AND t.object_type_id = 4 AND l.id IN( SELECT FIRST_VALUE(i.id) OVER(PARTITION BY i.term_id ORDER BY it.priority_seq, i.nm) FROM term_label i INNER JOIN term_label_type it ON i.term_label_type_id = it.id WHERE i.object_type_id = 4 AND i.nm_fts @@ to_tsquery('common.english_nostops', $2) ) UNION ALL SELECT /* acc */ t.acc_txt acc ,'name:' || t.nm accQueryStr ,t.nm ,t.nm_html nmHtml ,t.secondary_nm secondaryNm ,l.acc_txt matchednm ,'Accession' matchedtype ,false isNameMatch ,false isSecondaryNameMatch ,t.has_chems hasChems ,t.has_diseases hasDiseases ,t.has_exposures hasExposures ,1 relevance ,t.nm_sort ,t.id ,t.has_phenotypes hasPhenotypes FROM db_link l INNER JOIN term t ON l.object_id = t.id WHERE l.type_cd = 'A' AND l.object_type_id = 4 AND (upper( l.acc_txt ) = $3 ) ORDER BY 13,14 ) sq LIMIT 50
Date: 2025-01-29 00:02:09
Statement: SELECT /* GeneBasicQueryDAO */ sq.* ,COUNT(*) OVER() fullRowCount FROM ( SELECT /* label */ t.acc_txt acc ,'name:' || t.nm accQueryStr ,t.nm ,t.nm_html nmHtml ,t.secondary_nm secondaryNm ,l.nm matchedNm ,lt.nm_display matchedType ,CASE WHEN lt.nm_display='Symbol' THEN true ELSE false END isNameMatch ,CASE WHEN lt.nm_display='Name' THEN true ELSE false END isSecondaryNameMatch ,t.has_chems hasChems ,t.has_diseases hasDiseases ,t.has_exposures hasExposures ,CASE WHEN UPPER(l.nm) = $1 THEN 1 ELSE 2 END relevance ,t.nm_sort ,t.id ,t.has_phenotypes hasPhenotypes FROM term t INNER JOIN term_label l ON l.term_id = t.id INNER JOIN term_label_type lt ON l.term_label_type_id = lt.id WHERE l.object_type_id = 4 AND t.object_type_id = 4 AND l.id IN( SELECT FIRST_VALUE(i.id) OVER(PARTITION BY i.term_id ORDER BY it.priority_seq, i.nm) FROM term_label i INNER JOIN term_label_type it ON i.term_label_type_id = it.id WHERE i.object_type_id = 4 AND i.nm_fts @@ to_tsquery('common.english_nostops', $2) ) UNION ALL SELECT /* acc */ t.acc_txt acc ,'name:' || t.nm accQueryStr ,t.nm ,t.nm_html nmHtml ,t.secondary_nm secondaryNm ,l.acc_txt matchednm ,'Accession' matchedtype ,false isNameMatch ,false isSecondaryNameMatch ,t.has_chems hasChems ,t.has_diseases hasDiseases ,t.has_exposures hasExposures ,1 relevance ,t.nm_sort ,t.id ,t.has_phenotypes hasPhenotypes FROM db_link l INNER JOIN term t ON l.object_id = t.id WHERE l.type_cd = 'A' AND l.object_type_id = 4 AND (upper( l.acc_txt ) = $3 OR upper( l.acc_txt ) = $4 OR upper( l.acc_txt ) = $5 OR upper( l.acc_txt ) = $6 ) ORDER BY 13,14 ) sq LIMIT 50
Date: 2025-01-29 03:52:15
Statement: SELECT /* GeneBasicQueryDAO */ sq.* ,COUNT(*) OVER() fullRowCount FROM ( SELECT /* label */ t.acc_txt acc ,'name:' || t.nm accQueryStr ,t.nm ,t.nm_html nmHtml ,t.secondary_nm secondaryNm ,l.nm matchedNm ,lt.nm_display matchedType ,CASE WHEN lt.nm_display='Symbol' THEN true ELSE false END isNameMatch ,CASE WHEN lt.nm_display='Name' THEN true ELSE false END isSecondaryNameMatch ,t.has_chems hasChems ,t.has_diseases hasDiseases ,t.has_exposures hasExposures ,CASE WHEN UPPER(l.nm) = $1 THEN 1 ELSE 2 END relevance ,t.nm_sort ,t.id ,t.has_phenotypes hasPhenotypes FROM term t INNER JOIN term_label l ON l.term_id = t.id INNER JOIN term_label_type lt ON l.term_label_type_id = lt.id WHERE l.object_type_id = 4 AND t.object_type_id = 4 AND l.id IN( SELECT FIRST_VALUE(i.id) OVER(PARTITION BY i.term_id ORDER BY it.priority_seq, i.nm) FROM term_label i INNER JOIN term_label_type it ON i.term_label_type_id = it.id WHERE i.object_type_id = 4 AND i.nm_fts @@ to_tsquery('common.english_nostops', $2) ) UNION ALL SELECT /* acc */ t.acc_txt acc ,'name:' || t.nm accQueryStr ,t.nm ,t.nm_html nmHtml ,t.secondary_nm secondaryNm ,l.acc_txt matchednm ,'Accession' matchedtype ,false isNameMatch ,false isSecondaryNameMatch ,t.has_chems hasChems ,t.has_diseases hasDiseases ,t.has_exposures hasExposures ,1 relevance ,t.nm_sort ,t.id ,t.has_phenotypes hasPhenotypes FROM db_link l INNER JOIN term t ON l.object_id = t.id WHERE l.type_cd = 'A' AND l.object_type_id = 4 AND (upper( l.acc_txt ) = $3 OR upper( l.acc_txt ) = $4 OR upper( l.acc_txt ) = $5 ) ORDER BY 13,14 ) sq LIMIT 50
Date: 2025-01-29 21:32:55
5 54 FATAL: connection to client lost
Times Reported Most Frequent Error / Event #5
Day Hour Count Jan 29 08 52 18 1 21 1 6 43 WARNING: skipping "..." --- only superuser can vacuum it
Times Reported Most Frequent Error / Event #6
Day Hour Count Jan 29 03 43 7 34 LOG: could not send data to client: Broken pipe
Times Reported Most Frequent Error / Event #7
Day Hour Count Jan 29 08 32 18 1 21 1 - ERROR: could not send data to client: Broken pipe
- ERROR: could not send data to client: Broken pipe
- ERROR: could not send data to client: Broken pipe
Statement: SELECT row_to_json(T) FROM ( SELECT CASE
Date: 2025-01-29 08:15:20
Statement: SELECT row_to_json(T) FROM ( SELECT CASE WHEN pg_is_in_recovery() THEN 0 ELSE pg_wal_lsn_diff(pg_current_wal_lsn(),'0/00000000') END AS WRITE, CASE WHEN NOT pg_is_in_recovery() THEN 0 ELSE pg_wal_lsn_diff(pg_last_wal_receive_lsn(),'0/00000000') END AS RECEIVE, count(*) FROM pg_ls_waldir() AS COUNT ) T;
Date: 2025-01-29 08:15:20
Statement: SELECT row_to_json(T) FROM ( SELECT CASE WHEN pg_is_in_recovery() THEN 0 ELSE pg_wal_lsn_diff(pg_current_wal_lsn(),'0/00000000')
Date: 2025-01-29 08:15:20
8 9 ERROR: canceling statement due to user request
Times Reported Most Frequent Error / Event #8
Day Hour Count Jan 29 08 9 - ERROR: canceling statement due to user request
- ERROR: canceling statement due to user request
Statement: SELECT row_to_json(T) FROM ( SELECT CASE WHEN pg_is_in_recovery() THEN 0 ELSE pg_wal_lsn_diff(pg_current_wal_lsn(),'0/00000000') END AS WRITE, CASE WHEN NOT pg_is_in_recovery() THEN 0 ELSE pg_wal_lsn_diff(pg_last_wal_receive_lsn(),'0/00000000') END AS RECEIVE, count(*) FROM pg_ls_waldir() AS COUNT ) T;
Date: 2025-01-29 08:15:20 Database: postgres Application: User: zbx_monitor Remote:
Statement: SELECT row_to_json(T) FROM ( SELECT CASE WHEN pg_is_in_recovery() THEN 0
Date: 2025-01-29 08:15:20 Database: postgres Application: User: zbx_monitor Remote:
9 6 WARNING: there is no transaction in progress
Times Reported Most Frequent Error / Event #9
Day Hour Count Jan 29 03 2 10 4 10 5 LOG: could not receive data from client: Connection timed out
Times Reported Most Frequent Error / Event #10
Day Hour Count Jan 29 22 4 23 1 11 1 ERROR: syntax error at or near "..."
Times Reported Most Frequent Error / Event #11
Day Hour Count Jan 29 08 1 - ERROR: syntax error at or near "*" at character 31
Statement: select pg_is_in_backup() from *;
Date: 2025-01-29 08:36:10 Database: postgres Application: psql User: postgres Remote:
12 1 FATAL: ELSE pg_wal_lsn_diff(...),'0/00000000') END AS WRITE, CASE WHEN NOT pg_is_in_recovery() THEN 0 ELSE pg_wal_lsn_diff(...),'0/00000000') END AS RECEIVE, count(...) FROM pg_ls_waldir() AS COUNT ) T
Times Reported Most Frequent Error / Event #12
Day Hour Count Jan 29 08 1 13 1 ERROR: END AS WRITE, CASE WHEN NOT pg_is_in_recovery() THEN 0 ELSE pg_wal_lsn_diff(...),'0/00000000') END AS RECEIVE, count(...) FROM pg_ls_waldir() AS COUNT ) T; FROM (...) THEN 0 ELSE pg_wal_lsn_diff(...),'0/00000000') END AS WRITE, CASE WHEN NOT pg_is_in_recovery() THEN 0 ELSE pg_wal_lsn_diff(...),'0/00000000') END AS RECEIVE, count(...) FROM pg_ls_waldir() AS COUNT ) T
Times Reported Most Frequent Error / Event #13
Day Hour Count Jan 29 08 1 - ERROR: END AS WRITE, CASE WHEN NOT pg_is_in_recovery() THEN 0 ELSE pg_wal_lsn_diff(pg_last_wal_receive_lsn(),'0/00000000') END AS RECEIVE, count(*) FROM pg_ls_waldir() AS COUNT ) T; FROM ( SELECT CASE WHEN pg_is_in_recovery() THEN 0 ELSE pg_wal_lsn_diff(pg_current_wal_lsn(),'0/00000000') END AS WRITE, CASE WHEN NOT pg_is_in_recovery() THEN 0 ELSE pg_wal_lsn_diff(pg_last_wal_receive_lsn(),'0/00000000') END AS RECEIVE, count(*) FROM pg_ls_waldir() AS COUNT ) T
Statement: SELECT row_to_json(T)
Date: 2025-01-29 08:15:20
14 1 ERROR: unterminated quoted identifier at or near ""..."
Times Reported Most Frequent Error / Event #14
Day Hour Count Jan 29 13 1 - ERROR: unterminated quoted identifier at or near "" " at character 199
Statement: -- Should return 0 rows select cd, t.nm, t.nm_sort from pub1.term t ,pub1.object_type ot where upper( nm ) <> nm_sort and t.object_type_id = ot.id and cd <> 'pathway' order by cd, nm"
Date: 2025-01-29 13:18:02 Database: ctdprd51 Application: pgAdmin 4 - CONN:6588111 User: pub1 Remote:
15 1 ERROR: END AS RECEIVE, count(...) FROM pg_ls_waldir() AS COUNT ) T; FROM (...) THEN 0 ELSE pg_wal_lsn_diff(...),'0/00000000') END AS WRITE, CASE WHEN NOT pg_is_in_recovery() THEN 0 ELSE pg_wal_lsn_diff(...),'0/00000000') END AS RECEIVE, count(...) FROM pg_ls_waldir() AS COUNT ) T
Times Reported Most Frequent Error / Event #15
Day Hour Count Jan 29 08 1 - ERROR: END AS RECEIVE, count(*) FROM pg_ls_waldir() AS COUNT ) T; FROM ( SELECT CASE WHEN pg_is_in_recovery() THEN 0 ELSE pg_wal_lsn_diff(pg_current_wal_lsn(),'0/00000000') END AS WRITE, CASE WHEN NOT pg_is_in_recovery() THEN 0 ELSE pg_wal_lsn_diff(pg_last_wal_receive_lsn(),'0/00000000') END AS RECEIVE, count(*) FROM pg_ls_waldir() AS COUNT ) T
Statement: SELECT row_to_json(T)
Date: 2025-01-29 08:15:20