pgBadger-
Global information
- Generated on Wed Apr 2 04:10:03 2025
- Log file: /project/archive/log/postgres/dbdev51/postgresql.log-20250401
- Parsed 12,621 log entries in 2s
- Log start from 2025-04-01 00:04:59 to 2025-04-01 23:58:36
-
Overview
Global Stats
- 35 Number of unique normalized queries
- 59 Number of queries
- 19m23s Total query duration
- 2025-04-01 05:45:12 First query
- 2025-04-01 14:50:12 Last query
- 1 queries/s at 2025-04-01 10:57:23 Query peak
- 19m23s Total query duration
- 2m17s Prepare/parse total duration
- 0ms Bind total duration
- 17m6s Execute total duration
- 23 Number of events
- 8 Number of unique normalized events
- 11 Max number of times the same event was reported
- 0 Number of cancellation
- 0 Total number of automatic vacuums
- 2 Total number of automatic analyzes
- 40 Number temporary file
- 160.70 MiB Max size of temporary file
- 107.75 MiB Average size of temporary file
- 1,521 Total number of sessions
- 38 sessions at 2025-04-01 10:57:23 Session peak
- 37d4h59m Total duration of sessions
- 35m13s Average duration of sessions
- 0 Average queries per session
- 765ms Average queries duration per session
- 35m12s Average idle time per session
- 1,521 Total number of connections
- 9 connections/s at 2025-04-01 05:45:08 Connection peak
- 2 Total number of databases
SQL Traffic
Key values
- 1 queries/s Query Peak
- 2025-04-01 10:57:23 Date
SELECT Traffic
Key values
- 1 queries/s Query Peak
- 2025-04-01 11:16:25 Date
INSERT/UPDATE/DELETE Traffic
Key values
- 1 queries/s Query Peak
- 2025-04-01 14:35:00 Date
Queries duration
Key values
- 19m23s 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) Apr 01 00 0 0ms 0ms 0ms 0ms 0ms 0ms 01 0 0ms 0ms 0ms 0ms 0ms 0ms 02 0 0ms 0ms 0ms 0ms 0ms 0ms 03 0 0ms 0ms 0ms 0ms 0ms 0ms 04 0 0ms 0ms 0ms 0ms 0ms 0ms 05 18 0ms 4s693ms 2s397ms 16s130ms 23s672ms 23s672ms 06 0 0ms 0ms 0ms 0ms 0ms 0ms 07 0 0ms 0ms 0ms 0ms 0ms 0ms 08 0 0ms 0ms 0ms 0ms 0ms 0ms 09 1 0ms 2s528ms 2s528ms 0ms 0ms 2s528ms 10 4 0ms 2m33s 39s850ms 1s898ms 1s968ms 2m33s 11 3 0ms 1s572ms 1s553ms 0ms 1s572ms 3s87ms 12 0 0ms 0ms 0ms 0ms 0ms 0ms 13 18 0ms 1m33s 13s128ms 13s561ms 58s103ms 1m33s 14 15 0ms 2m26s 38s701ms 2m17s 2m25s 2m26s 15 0 0ms 0ms 0ms 0ms 0ms 0ms 16 0 0ms 0ms 0ms 0ms 0ms 0ms 17 0 0ms 0ms 0ms 0ms 0ms 0ms 18 0 0ms 0ms 0ms 0ms 0ms 0ms 19 0 0ms 0ms 0ms 0ms 0ms 0ms 20 0 0ms 0ms 0ms 0ms 0ms 0ms 21 0 0ms 0ms 0ms 0ms 0ms 0ms 22 0 0ms 0ms 0ms 0ms 0ms 0ms 23 0 0ms 0ms 0ms 0ms 0ms 0ms Day Hour SELECT COPY TO Average Duration Latency Percentile(90) Latency Percentile(95) Latency Percentile(99) Apr 01 00 0 0 0ms 0ms 0ms 0ms 01 0 0 0ms 0ms 0ms 0ms 02 0 0 0ms 0ms 0ms 0ms 03 0 0 0ms 0ms 0ms 0ms 04 0 0 0ms 0ms 0ms 0ms 05 17 0 2s341ms 0ms 16s130ms 23s672ms 06 0 0 0ms 0ms 0ms 0ms 07 0 0 0ms 0ms 0ms 0ms 08 0 0 0ms 0ms 0ms 0ms 09 1 0 2s528ms 0ms 0ms 2s528ms 10 3 0 1s802ms 0ms 1s541ms 1s968ms 11 3 0 1s553ms 0ms 0ms 3s87ms 12 0 0 0ms 0ms 0ms 0ms 13 18 0 13s128ms 10s422ms 13s561ms 1m33s 14 11 0 1s504ms 1s259ms 3s60ms 3s794ms 15 0 0 0ms 0ms 0ms 0ms 16 0 0 0ms 0ms 0ms 0ms 17 0 0 0ms 0ms 0ms 0ms 18 0 0 0ms 0ms 0ms 0ms 19 0 0 0ms 0ms 0ms 0ms 20 0 0 0ms 0ms 0ms 0ms 21 0 0 0ms 0ms 0ms 0ms 22 0 0 0ms 0ms 0ms 0ms 23 0 0 0ms 0ms 0ms 0ms Day Hour INSERT UPDATE DELETE COPY FROM Average Duration Latency Percentile(90) Latency Percentile(95) Latency Percentile(99) Apr 01 00 0 0 0 0 0ms 0ms 0ms 0ms 01 0 0 0 0 0ms 0ms 0ms 0ms 02 0 0 0 0 0ms 0ms 0ms 0ms 03 0 0 0 0 0ms 0ms 0ms 0ms 04 0 0 0 0 0ms 0ms 0ms 0ms 05 0 0 0 0 0ms 0ms 0ms 0ms 06 0 0 0 0 0ms 0ms 0ms 0ms 07 0 0 0 0 0ms 0ms 0ms 0ms 08 0 0 0 0 0ms 0ms 0ms 0ms 09 0 0 0 0 0ms 0ms 0ms 0ms 10 0 0 0 0 0ms 0ms 0ms 0ms 11 0 0 0 0 0ms 0ms 0ms 0ms 12 0 0 0 0 0ms 0ms 0ms 0ms 13 0 0 0 0 0ms 0ms 0ms 0ms 14 3 0 1 0 2m20s 0ms 0ms 2m25s 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 Apr 01 00 0 0 0.00 0.00% 01 0 0 0.00 0.00% 02 0 0 0.00 0.00% 03 0 0 0.00 0.00% 04 0 0 0.00 0.00% 05 0 18 18.00 0.00% 06 0 0 0.00 0.00% 07 0 0 0.00 0.00% 08 0 0 0.00 0.00% 09 0 0 0.00 0.00% 10 1 0 0.00 33.33% 11 0 0 0.00 0.00% 12 0 0 0.00 0.00% 13 0 0 0.00 0.00% 14 0 0 0.00 0.00% 15 0 0 0.00 0.00% 16 0 0 0.00 0.00% 17 0 0 0.00 0.00% 18 0 0 0.00 0.00% 19 0 0 0.00 0.00% 20 0 0 0.00 0.00% 21 0 0 0.00 0.00% 22 0 0 0.00 0.00% 23 0 0 0.00 0.00% Day Hour Count Average / Second Apr 01 00 64 0.02/s 01 64 0.02/s 02 58 0.02/s 03 57 0.02/s 04 61 0.02/s 05 69 0.02/s 06 64 0.02/s 07 64 0.02/s 08 64 0.02/s 09 68 0.02/s 10 70 0.02/s 11 64 0.02/s 12 64 0.02/s 13 65 0.02/s 14 64 0.02/s 15 64 0.02/s 16 62 0.02/s 17 61 0.02/s 18 56 0.02/s 19 63 0.02/s 20 64 0.02/s 21 63 0.02/s 22 64 0.02/s 23 64 0.02/s Day Hour Count Average Duration Average idle time Apr 01 00 64 30m39s 30m39s 01 64 30m39s 30m39s 02 58 30m40s 30m40s 03 57 30m39s 30m39s 04 61 30m39s 30m39s 05 69 28m15s 28m14s 06 64 30m38s 30m38s 07 64 30m39s 30m39s 08 64 30m41s 30m41s 09 64 30m9s 30m9s 10 66 30m8s 30m5s 11 64 30m44s 30m44s 12 64 30m39s 30m39s 13 64 30m39s 30m35s 14 62 30m41s 30m31s 15 64 30m39s 30m39s 16 62 31m 31m 17 61 30m41s 30m41s 18 56 30m38s 30m38s 19 63 30m40s 30m40s 20 64 30m40s 30m40s 21 64 38m13s 38m13s 22 69 1h21m31s 1h21m31s 23 69 1h16m19s 1h16m19s -
Connections
Established Connections
Key values
- 9 connections Connection Peak
- 2025-04-01 05:45:08 Date
Connections per database
Key values
- ctddev51 Main Database
- 1,521 connections Total
Connections per user
Key values
- pubeu Main User
- 1,521 connections Total
-
Sessions
Simultaneous sessions
Key values
- 38 sessions Session Peak
- 2025-04-01 10:57:23 Date
Histogram of session times
Key values
- 1,496 1800000-3600000ms duration
Sessions per database
Key values
- ctddev51 Main Database
- 1,521 sessions Total
Sessions per user
Key values
- pubeu Main User
- 1,521 sessions Total
Sessions per host
Key values
- 10.12.5.53 Main Host
- 1,521 sessions Total
-
Checkpoints / Restartpoints
Checkpoints Buffers
Key values
- 18,147 buffers Checkpoint Peak
- 2025-04-01 17:12:46 Date
- 1619.178 seconds Highest write time
- 0.002 seconds Sync time
Checkpoints Wal files
Key values
- 326 files Wal files usage Peak
- 2025-04-01 11:16:01 Date
Checkpoints distance
Key values
- 10,414.49 Mo Distance Peak
- 2025-04-01 11:16:01 Date
Checkpoints Activity
↑ Back to the top of the Checkpoint Activity tableDay Hour Written buffers Write time Sync time Total time Apr 01 00 0 0s 0s 0s 01 0 0s 0s 0s 02 0 0s 0s 0s 03 0 0s 0s 0s 04 0 0s 0s 0s 05 0 0s 0s 0s 06 79 8.047s 0.001s 8.064s 07 0 0s 0s 0s 08 0 0s 0s 0s 09 0 0s 0s 0s 10 0 0s 0s 0s 11 105 10.591s 0.001s 14.633s 12 0 0s 0s 0s 13 0 0s 0s 0s 14 0 0s 0s 0s 15 10,075 1,008.419s 0.002s 1,008.65s 16 0 0s 0s 0s 17 18,147 1,619.178s 0.001s 1,619.194s 18 0 0s 0s 0s 19 0 0s 0s 0s 20 0 0s 0s 0s 21 0 0s 0s 0s 22 0 0s 0s 0s 23 0 0s 0s 0s Day Hour Added Removed Recycled Synced files Longest sync Average sync Apr 01 00 0 0 0 0 0s 0s 01 0 0 0 0 0s 0s 02 0 0 0 0 0s 0s 03 0 0 0 0 0s 0s 04 0 0 0 0 0s 0s 05 0 0 0 0 0s 0s 06 0 0 0 17 0.001s 0.001s 07 0 0 0 0 0s 0s 08 0 0 0 0 0s 0s 09 0 0 0 0 0s 0s 10 0 0 0 0 0s 0s 11 0 0 326 45 0.001s 0.001s 12 0 0 0 0 0s 0s 13 0 0 0 0 0s 0s 14 0 0 0 0 0s 0s 15 0 0 14 5 0.001s 0.001s 16 0 0 0 0 0s 0s 17 0 0 0 16 0.001s 0.001s 18 0 0 0 0 0s 0s 19 0 0 0 0 0s 0s 20 0 0 0 0 0s 0s 21 0 0 0 0 0s 0s 22 0 0 0 0 0s 0s 23 0 0 0 0 0s 0s Day Hour Count Avg time (sec) Apr 01 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 Apr 01 00 0.00 kB 0.00 kB 01 0.00 kB 0.00 kB 02 0.00 kB 0.00 kB 03 0.00 kB 0.00 kB 04 0.00 kB 0.00 kB 05 0.00 kB 0.00 kB 06 419.00 kB 100,100.00 kB 07 0.00 kB 0.00 kB 08 0.00 kB 0.00 kB 09 0.00 kB 0.00 kB 10 0.00 kB 0.00 kB 11 5,332,221.00 kB 5,332,221.00 kB 12 0.00 kB 0.00 kB 13 0.00 kB 0.00 kB 14 0.00 kB 0.00 kB 15 87,115.00 kB 4,807,711.00 kB 16 0.00 kB 0.00 kB 17 149,782.00 kB 4,341,918.00 kB 18 0.00 kB 0.00 kB 19 0.00 kB 0.00 kB 20 0.00 kB 0.00 kB 21 0.00 kB 0.00 kB 22 0.00 kB 0.00 kB 23 0.00 kB 0.00 kB -
Temporary Files
Size of temporary files
Key values
- 720.68 MiB Temp Files size Peak
- 2025-04-01 10:55:48 Date
Number of temporary files
Key values
- 5 per second Temp Files Peak
- 2025-04-01 10:55:48 Date
Temporary Files Activity
↑ Back to the top of the Temporary Files Activity tableDay Hour Count Total size Average size Apr 01 00 0 0 0 01 0 0 0 02 0 0 0 03 0 0 0 04 0 0 0 05 0 0 0 06 0 0 0 07 0 0 0 08 0 0 0 09 0 0 0 10 40 4.21 GiB 107.75 MiB 11 0 0 0 12 0 0 0 13 0 0 0 14 0 0 0 15 0 0 0 16 0 0 0 17 0 0 0 18 0 0 0 19 0 0 0 20 0 0 0 21 0 0 0 22 0 0 0 23 0 0 0 Queries generating the most temporary files (N)
Rank Count Total size Min size Max size Avg size Query 1 40 4.21 GiB 62.44 MiB 160.70 MiB 107.75 MiB vacuum full analyze db_link;-
vacuum FULL analyze db_link;
Date: 2025-04-01 10:57:23 Duration: 2m33s
-
vacuum FULL analyze db_link;
Date: 2025-04-01 10:55:05 Duration: 0ms
Queries generating the largest temporary files
Rank Size Query 1 160.70 MiB vacuum FULL analyze db_link;[ Date: 2025-04-01 10:55:48 ]
2 155.88 MiB vacuum FULL analyze db_link;[ Date: 2025-04-01 10:55:48 ]
3 146.84 MiB vacuum FULL analyze db_link;[ Date: 2025-04-01 10:55:26 ]
4 141.94 MiB vacuum FULL analyze db_link;[ Date: 2025-04-01 10:55:26 ]
5 141.77 MiB vacuum FULL analyze db_link;[ Date: 2025-04-01 10:55:26 ]
6 140.95 MiB vacuum FULL analyze db_link;[ Date: 2025-04-01 10:55:26 ]
7 137.78 MiB vacuum FULL analyze db_link;[ Date: 2025-04-01 10:55:26 ]
8 134.96 MiB vacuum FULL analyze db_link;[ Date: 2025-04-01 10:55:48 ]
9 134.86 MiB vacuum FULL analyze db_link;[ Date: 2025-04-01 10:55:48 ]
10 134.28 MiB vacuum FULL analyze db_link;[ Date: 2025-04-01 10:55:48 ]
11 123.57 MiB vacuum FULL analyze db_link;[ Date: 2025-04-01 10:55:05 ]
12 123.33 MiB vacuum FULL analyze db_link;[ Date: 2025-04-01 10:55:05 ]
13 122.30 MiB vacuum FULL analyze db_link;[ Date: 2025-04-01 10:56:04 ]
14 120.79 MiB vacuum FULL analyze db_link;[ Date: 2025-04-01 10:56:04 ]
15 120.05 MiB vacuum FULL analyze db_link;[ Date: 2025-04-01 10:56:04 ]
16 118.19 MiB vacuum FULL analyze db_link;[ Date: 2025-04-01 10:55:05 ]
17 117.98 MiB vacuum FULL analyze db_link;[ Date: 2025-04-01 10:56:04 ]
18 117.70 MiB vacuum FULL analyze db_link;[ Date: 2025-04-01 10:56:24 ]
19 117.62 MiB vacuum FULL analyze db_link;[ Date: 2025-04-01 10:55:05 ]
20 117.48 MiB vacuum FULL analyze db_link;[ Date: 2025-04-01 10:56:24 ]
-
Vacuums
Vacuums / Analyzes Distribution
Key values
- 0 sec Highest CPU-cost vacuum
Table
Database - Date
- 0 sec Highest CPU-cost analyze
Table
Database - Date
Average Autovacuum Duration
Key values
- 0 sec Highest CPU-cost vacuum
Table
Database - Date
Analyzes per table
Key values
- pubc.log_query_bots (1) Main table analyzed (database ctddev51)
- 2 analyzes Total
Vacuums per table
Key values
- unknown (0) Main table vacuumed on database
- 0 vacuums Total
Tuples removed per table
Key values
- unknown (0) Main table with removed tuples on database
- 0 tuples Total removed
Pages removed per table
Key values
- unknown (0) Main table with removed pages on database unknown
- 0 pages Total removed
Autovacuum Activity
↑ Back to the top of the Autovacuum Activity tableDay Hour VACUUMs ANALYZEs Apr 01 00 0 0 01 0 0 02 0 0 03 0 0 04 0 0 05 0 1 06 0 0 07 0 0 08 0 0 09 0 0 10 0 0 11 0 0 12 0 0 13 0 0 14 0 1 15 0 0 16 0 0 17 0 0 18 0 0 19 0 0 20 0 0 21 0 0 22 0 0 23 0 0 - 0 sec Highest CPU-cost vacuum
-
Locks
Locks by types
Key values
- AccessShareLock Main Lock Type
- 1 locks Total
Most frequent waiting queries (N)
Rank Count Total time Min time Max time Avg duration Query 1 1 2m17s 2m17s 2m17s 2m17s select coalesce(d.abbr_display, d.nm_display) dbnm, d.cd dbcd, d.id dbid, coalesce(d.abbr, d.nm) anchor, l.acc_txt acc, get_acc_sort_num (l.acc_txt) accsort, l.is_primary isprimary, dbrs.nm sitenm, dbr.nm reportnm, get_encoded_acc_url (dbrs.url, l.acc_txt) url, count(dbrs.id) over (partition by l.acc_txt, l.db_id, dbr.id) sitesperacccount, count(l.acc_txt) over (partition by l.db_id, dbrs.id) accsperdbcount from db_link l inner join db d on l.db_id = d.id inner join db_report dbr on d.id = dbr.db_id and dbr.object_type_id = ? inner join db_report_site dbrs on dbr.id = dbrs.db_report_id where l.object_id = ? and l.object_type_id = ? and l.type_cd = ? and dbr.type_cd in (...) order by ?, ? desc, ?, ?, ?;-
SELECT /* TermLinksDAO */ COALESCE(d.abbr_display, d.nm_display) dbnm, d.cd dbcd, d.id dbid, COALESCE(d.abbr, d.nm) anchor, l.acc_txt acc, get_acc_sort_num (l.acc_txt) accsort, l.is_primary isprimary, dbrs.nm sitenm, dbr.nm reportnm, get_encoded_acc_url (dbrs.url, l.acc_txt) url, COUNT(dbrs.id) OVER (PARTITION BY l.acc_txt, l.db_id, dbr.id) sitesPerAccCount, COUNT(l.acc_txt) OVER (PARTITION BY l.db_id, dbrs.id) accsPerDbCount FROM db_link l INNER JOIN db d ON l.db_id = d.id INNER JOIN db_report dbr ON d.id = dbr.db_id AND dbr.object_type_id = 2 INNER JOIN db_report_site dbrs ON dbr.id = dbrs.db_report_id WHERE l.object_id = $1 AND l.object_type_id = 2 AND l.type_cd = 'X' AND dbr.type_cd IN ('PAV', 'SAV') ORDER BY 1, 7 DESC, 6, 5, 8;
Date: 2025-04-01 10:57:20
Queries that waited the most
Rank Wait time Query 1 2m17s SELECT /* TermLinksDAO */ COALESCE(d.abbr_display, d.nm_display) dbnm, d.cd dbcd, d.id dbid, COALESCE(d.abbr, d.nm) anchor, l.acc_txt acc, get_acc_sort_num (l.acc_txt) accsort, l.is_primary isprimary, dbrs.nm sitenm, dbr.nm reportnm, get_encoded_acc_url (dbrs.url, l.acc_txt) url, COUNT(dbrs.id) OVER (PARTITION BY l.acc_txt, l.db_id, dbr.id) sitesPerAccCount, COUNT(l.acc_txt) OVER (PARTITION BY l.db_id, dbrs.id) accsPerDbCount FROM db_link l INNER JOIN db d ON l.db_id = d.id INNER JOIN db_report dbr ON d.id = dbr.db_id AND dbr.object_type_id = 2 INNER JOIN db_report_site dbrs ON dbr.id = dbrs.db_report_id WHERE l.object_id = $1 AND l.object_type_id = 2 AND l.type_cd = 'X' AND dbr.type_cd IN ('PAV', 'SAV') ORDER BY 1, 7 DESC, 6, 5, 8;[ Date: 2025-04-01 10:57:20 ]
-
Queries
Queries by type
Key values
- 53 Total read queries
- 5 Total write queries
Queries by database
Key values
- unknown Main database
- 52 Requests
- 16m47s (unknown)
- Main time consuming database
Queries by user
Key values
- unknown Main user
- 67 Requests
User Request type Count Duration pub2 Total 1 2s528ms select 1 2s528ms pubc Total 1 1s898ms select 1 1s898ms pubeu Total 9 25s990ms cte 2 7s108ms select 7 18s882ms unknown Total 67 17m22s cte 1 1s177ms delete 1 2m25s insert 3 6m58s others 1 2m33s select 61 5m23s Duration by user
Key values
- 17m22s (unknown) Main time consuming user
User Request type Count Duration pub2 Total 1 2s528ms select 1 2s528ms pubc Total 1 1s898ms select 1 1s898ms pubeu Total 9 25s990ms cte 2 7s108ms select 7 18s882ms unknown Total 67 17m22s cte 1 1s177ms delete 1 2m25s insert 3 6m58s others 1 2m33s select 61 5m23s Queries by host
Key values
- unknown Main host
- 78 Requests
- 17m53s (unknown)
- Main time consuming host
Queries by application
Key values
- unknown Main application
- 57 Requests
- 17m2s (unknown)
- Main time consuming application
Number of cancelled queries
Key values
- 0 per second Cancelled query Peak
- 2025-04-01 10:57:23 Date
Number of cancelled queries (5 minutes period)
NO DATASET
-
Top Queries
Histogram of query times
Key values
- 48 1000-10000ms duration
Slowest individual queries
Rank Duration Query 1 2m33s vacuum FULL analyze db_link;[ Date: 2025-04-01 10:57:23 ]
2 2m26s --select count(*) from log_query_bots -2776425 --begin transaction --rollback INSERT INTO log_query_bots (id, type_cd, query_tm, submission_qty, session_id, remote_addr, http_user_agent, server_nm, node_nm, results_qty, execution_ms, basic_query_type, basic_query_txt, gene_query_type, gene_txt, gene_form_type_txt, taxon_query_type, taxon_txt, chem_query_type, chem_txt, party_query_type, party_nm_txt, acc_txt, go_query_type, go_txt, disease_query_type, disease_txt, action_type_txt, action_degree_type_txt, from_yr, through_yr, title_abstract_txt, has_marray, pathway_query_type, pathway_txt, dag_txt, results_format_txt, batch_input_type_txt, gd_assn_type, p_val, p_val_type, input_term_qty, review_status) SELECT q.id, q.type_cd, q.query_tm, q.submission_qty, q.session_id, q.remote_addr, q.http_user_agent, q.server_nm, q.node_nm, q.results_qty, q.execution_ms, q.basic_query_type, q.basic_query_txt, q.gene_query_type, q.gene_txt, q.gene_form_type_txt, q.taxon_query_type, q.taxon_txt, q.chem_query_type, q.chem_txt, q.party_query_type, q.party_nm_txt, q.acc_txt, q.go_query_type, q.go_txt, q.disease_query_type, q.disease_txt, q.action_type_txt, q.action_degree_type_txt, q.from_yr, q.through_yr, q.title_abstract_txt, q.has_marray, q.pathway_query_type, q.pathway_txt, q.dag_txt, q.results_format_txt, q.batch_input_type_txt, q.gd_assn_type, q.p_val, q.p_val_type, q.input_term_qty, q.review_status FROM log_query_archive q WHERE upper(q.http_user_agent) LIKE upper('%ARCHIVE%') OR upper(q.http_user_agent) LIKE upper('%SPIDER%') OR upper(q.http_user_agent) LIKE upper('%FACEBOOK%') OR upper(q.http_user_agent) LIKE upper('%NUTCH%') OR upper(q.http_user_agent) LIKE upper('%TRACK%') OR upper(q.http_user_agent) LIKE upper('%SITESUCKER%') OR upper(q.http_user_agent) LIKE upper('%KNOWLEDGE AI%') OR upper(q.http_user_agent) LIKE upper('%MEGAINDEX%') OR upper(q.http_user_agent) LIKE upper('%YANDEX%') OR upper(q.http_user_agent) LIKE upper('%PANSCIENT%') OR upper(q.http_user_agent) LIKE upper('%GOOGLEOTHER%') OR upper(q.http_user_agent) LIKE upper('%QWANTIFY%') OR upper(q.http_user_agent) LIKE upper('%SEOKICKS%') OR upper(q.http_user_agent) LIKE upper('%ARQUIVO%') OR upper(q.http_user_agent) LIKE upper('%SEMANTIC%') OR upper(q.http_user_agent) LIKE upper('%BOT%') AND q.id NOT IN ( SELECT a.id FROM log_query_bots a);[ Date: 2025-04-01 14:45:41 ]
3 2m25s --begin transaction --rollback --commit DELETE from log_query_archive q WHERE upper(q.http_user_agent) LIKE upper('%ARCHIVE%') OR upper(q.http_user_agent) LIKE upper('%SPIDER%') OR upper(q.http_user_agent) LIKE upper('%FACEBOOK%') OR upper(q.http_user_agent) LIKE upper('%NUTCH%') OR upper(q.http_user_agent) LIKE upper('%TRACK%') OR upper(q.http_user_agent) LIKE upper('%SITESUCKER%') OR upper(q.http_user_agent) LIKE upper('%KNOWLEDGE AI%') OR upper(q.http_user_agent) LIKE upper('%MEGAINDEX%') OR upper(q.http_user_agent) LIKE upper('%YANDEX%') OR upper(q.http_user_agent) LIKE upper('%PANSCIENT%') OR upper(q.http_user_agent) LIKE upper('%GOOGLEOTHER%') OR upper(q.http_user_agent) LIKE upper('%QWANTIFY%') OR upper(q.http_user_agent) LIKE upper('%SEOKICKS%') OR upper(q.http_user_agent) LIKE upper('%ARQUIVO%') OR upper(q.http_user_agent) LIKE upper('%SEMANTIC%') OR upper(q.http_user_agent) LIKE upper('%BOT%');[ Date: 2025-04-01 14:50:12 ]
4 2m16s --begin transaction --rollback INSERT INTO log_query_bots (id, type_cd, query_tm, submission_qty, session_id, remote_addr, http_user_agent, server_nm, node_nm, results_qty, execution_ms, basic_query_type, basic_query_txt, gene_query_type, gene_txt, gene_form_type_txt, taxon_query_type, taxon_txt, chem_query_type, chem_txt, party_query_type, party_nm_txt, acc_txt, go_query_type, go_txt, disease_query_type, disease_txt, action_type_txt, action_degree_type_txt, from_yr, through_yr, title_abstract_txt, has_marray, pathway_query_type, pathway_txt, dag_txt, results_format_txt, batch_input_type_txt, gd_assn_type, p_val, p_val_type, input_term_qty, review_status) SELECT q.id, q.type_cd, q.query_tm, q.submission_qty, q.session_id, q.remote_addr, q.http_user_agent, q.server_nm, q.node_nm, q.results_qty, q.execution_ms, q.basic_query_type, q.basic_query_txt, q.gene_query_type, q.gene_txt, q.gene_form_type_txt, q.taxon_query_type, q.taxon_txt, q.chem_query_type, q.chem_txt, q.party_query_type, q.party_nm_txt, q.acc_txt, q.go_query_type, q.go_txt, q.disease_query_type, q.disease_txt, q.action_type_txt, q.action_degree_type_txt, q.from_yr, q.through_yr, q.title_abstract_txt, q.has_marray, q.pathway_query_type, q.pathway_txt, q.dag_txt, q.results_format_txt, q.batch_input_type_txt, q.gd_assn_type, q.p_val, q.p_val_type, q.input_term_qty, q.review_status FROM log_query_archive q WHERE upper(q.http_user_agent) LIKE upper('%ARCHIVE%') OR upper(q.http_user_agent) LIKE upper('%SPIDER%') OR upper(q.http_user_agent) LIKE upper('%FACEBOOK%') OR upper(q.http_user_agent) LIKE upper('%NUTCH%') OR upper(q.http_user_agent) LIKE upper('%TRACK%') OR upper(q.http_user_agent) LIKE upper('%SITESUCKER%') OR upper(q.http_user_agent) LIKE upper('%KNOWLEDGE AI%') OR upper(q.http_user_agent) LIKE upper('%MEGAINDEX%') OR upper(q.http_user_agent) LIKE upper('%YANDEX%') OR upper(q.http_user_agent) LIKE upper('%PANSCIENT%') OR upper(q.http_user_agent) LIKE upper('%GOOGLEOTHER%') OR upper(q.http_user_agent) LIKE upper('%QWANTIFY%') OR upper(q.http_user_agent) LIKE upper('%SEOKICKS%') OR upper(q.http_user_agent) LIKE upper('%ARQUIVO%') OR upper(q.http_user_agent) LIKE upper('%SEMANTIC%') AND q.id NOT IN ( SELECT a.id FROM log_query_bots a);[ Date: 2025-04-01 14:29:24 ]
5 2m16s --select count(*) from log_query_bots -2776425 --begin transaction --rollback INSERT INTO log_query_bots (id, type_cd, query_tm, submission_qty, session_id, remote_addr, http_user_agent, server_nm, node_nm, results_qty, execution_ms, basic_query_type, basic_query_txt, gene_query_type, gene_txt, gene_form_type_txt, taxon_query_type, taxon_txt, chem_query_type, chem_txt, party_query_type, party_nm_txt, acc_txt, go_query_type, go_txt, disease_query_type, disease_txt, action_type_txt, action_degree_type_txt, from_yr, through_yr, title_abstract_txt, has_marray, pathway_query_type, pathway_txt, dag_txt, results_format_txt, batch_input_type_txt, gd_assn_type, p_val, p_val_type, input_term_qty, review_status) SELECT q.id, q.type_cd, q.query_tm, q.submission_qty, q.session_id, q.remote_addr, q.http_user_agent, q.server_nm, q.node_nm, q.results_qty, q.execution_ms, q.basic_query_type, q.basic_query_txt, q.gene_query_type, q.gene_txt, q.gene_form_type_txt, q.taxon_query_type, q.taxon_txt, q.chem_query_type, q.chem_txt, q.party_query_type, q.party_nm_txt, q.acc_txt, q.go_query_type, q.go_txt, q.disease_query_type, q.disease_txt, q.action_type_txt, q.action_degree_type_txt, q.from_yr, q.through_yr, q.title_abstract_txt, q.has_marray, q.pathway_query_type, q.pathway_txt, q.dag_txt, q.results_format_txt, q.batch_input_type_txt, q.gd_assn_type, q.p_val, q.p_val_type, q.input_term_qty, q.review_status FROM log_query_archive q WHERE upper(q.http_user_agent) LIKE upper('%ARCHIVE%') OR upper(q.http_user_agent) LIKE upper('%SPIDER%') OR upper(q.http_user_agent) LIKE upper('%FACEBOOK%') OR upper(q.http_user_agent) LIKE upper('%NUTCH%') OR upper(q.http_user_agent) LIKE upper('%TRACK%') OR upper(q.http_user_agent) LIKE upper('%SITESUCKER%') OR upper(q.http_user_agent) LIKE upper('%KNOWLEDGE AI%') OR upper(q.http_user_agent) LIKE upper('%MEGAINDEX%') OR upper(q.http_user_agent) LIKE upper('%YANDEX%') OR upper(q.http_user_agent) LIKE upper('%PANSCIENT%') OR upper(q.http_user_agent) LIKE upper('%GOOGLEOTHER%') OR upper(q.http_user_agent) LIKE upper('%QWANTIFY%') OR upper(q.http_user_agent) LIKE upper('%SEOKICKS%') OR upper(q.http_user_agent) LIKE upper('%ARQUIVO%') OR upper(q.http_user_agent) LIKE upper('%SEMANTIC%') AND q.id NOT IN ( SELECT a.id FROM log_query_bots a);[ Date: 2025-04-01 14:35:00 ]
6 1m33s ( SELECT upper(http_user_agent) FROM log_query_bots l, excluded_user_agent2 ua WHERE UPPER(l.http_user_agent) LIKE UPPER(ua.user_agent_pattern));[ Date: 2025-04-01 13:20:38 ]
7 58s103ms select distinct (http_user_agent) from log_query_bots where upper(http_user_agent) not in ( SELECT distinct upper(http_user_agent) FROM log_query_bots l, excluded_user_agent2 ua WHERE UPPER(l.http_user_agent) LIKE UPPER(ua.user_agent_pattern));[ Date: 2025-04-01 13:22:46 ]
8 13s561ms select distinct (http_user_agent) from log_query_bots where upper(http_user_agent) not in ( SELECT http_user_agent FROM log_query_bots l, excluded_user_agent2 ua WHERE UPPER(l.http_user_agent) LIKE UPPER(ua.user_agent_pattern));[ Date: 2025-04-01 13:10:33 ]
9 13s276ms select distinct (http_user_agent) from log_query_bots where upper(http_user_agent) not in ( SELECT upper(http_user_agent) FROM log_query_bots l, excluded_user_agent2 ua WHERE UPPER(l.http_user_agent) LIKE UPPER(ua.user_agent_pattern));[ Date: 2025-04-01 13:12:41 ]
10 11s301ms select distinct (http_user_agent) from log_query_bots where http_user_agent not in ( SELECT http_user_agent FROM log_query_bots l, excluded_user_agent2 ua WHERE UPPER(l.http_user_agent) LIKE UPPER(ua.user_agent_pattern));[ Date: 2025-04-01 13:11:38 ]
11 10s422ms ( SELECT distinct upper(http_user_agent) FROM log_query_bots l, excluded_user_agent2 ua WHERE UPPER(l.http_user_agent) LIKE UPPER(ua.user_agent_pattern));[ Date: 2025-04-01 13:14:09 ]
12 9s945ms ( SELECT distinct upper(http_user_agent) FROM log_query_bots l, excluded_user_agent2 ua WHERE UPPER(l.http_user_agent) LIKE UPPER(ua.user_agent_pattern));[ Date: 2025-04-01 13:21:30 ]
13 6s262ms select distinct (http_user_agent) from log_query_archive --select distinct(http_user_agent) from log_query_bots where upper(http_user_agent) LIKE '%SEMANTIC%' OR upper(http_user_agent) LIKE '%QWANTIFY%' OR upper(http_user_agent) LIKE '%SEOKICKS%' OR upper(http_user_agent) LIKE '%ARQUIVO%';[ Date: 2025-04-01 13:59:19 ]
14 5s442ms select distinct (http_user_agent) from log_query_archive where upper(http_user_agent) LIKE '%SEMANTIC%' OR upper(http_user_agent) LIKE '%QWANTIFY%' OR upper(http_user_agent) LIKE '%SEOKICKS%' OR upper(http_user_agent) LIKE '%ARQUIVO%';[ Date: 2025-04-01 13:32:33 ]
15 4s693ms SELECT /* BatchChemGODAO */ 'ddt' "Input", sqi.chem_nm "ChemicalName", sqi.chem_acc_txt "ChemicalID", sqi.casRN "CasRN", sqi.gene_symbol "GeneSymbol", sqi.gene_acc_txt "GeneID", sqi.ontology_nm "Ontology", sqi.go_term_nm "GoTermName", sqi.go_acc_txt "GoTermID" FROM ( WITH sq AS ( SELECT DISTINCT c.id chem_id, c.nm chem_nm, c.acc_txt chem_acc_txt, c.secondary_nm casRN, c.nm_sort chem_nm_sort, gcr.gene_id, g.nm gene_symbol, g.acc_txt gene_acc_txt, g.nm_sort gene_symbol_sort FROM term c INNER JOIN gene_chem_reference gcr ON c.id = gcr.chem_id INNER JOIN term g ON gcr.gene_id = g.id WHERE (c.id = 1308127)) SELECT DISTINCT sq.chem_nm, sq.chem_acc_txt, sq.casRN, sq.gene_symbol, sq.gene_acc_txt, gt.nm go_term_nm, gt.acc_txt go_acc_txt, sq.chem_nm_sort, sq.gene_symbol_sort, gt.nm_sort, d.nm ontology_nm FROM sq INNER JOIN gene_go_annot gga ON sq.gene_id = gga.gene_id INNER JOIN dag_node gt ON gga.go_term_id = gt.object_id INNER JOIN dag d ON gt.dag_id = d.id WHERE gga.is_not = false AND (d.id = 5 OR d.id = 4) ORDER BY sq.chem_nm_sort, sq.gene_symbol_sort, d.nm, gt.nm_sort) sqi;[ Date: 2025-04-01 05:48:27 - Database: ctddev51 - User: pubeu - Bind query: yes ]
16 4s267ms SELECT /* CIQH.getIxnCacheQuery */ gcr.ixn_id, NULL, NULL, NULL FROM gene_chem_reference gcr WHERE /* CIQH.getIxnWhereCore */ gcr.gene_id = ANY (ARRAY (( SELECT /* IQH.getMasterDiseaseWhereEquals.Name.Gene */ gd.gene_id FROM term t INNER JOIN dag_path dp ON t.id = dp.ancestor_object_id INNER JOIN gene_disease gd ON dp.descendant_object_id = gd.disease_id WHERE UPPER(t.nm) LIKE 'ASTHMA' AND t.object_type_id = 3))) AND gcr.id IN ( SELECT gcra.gene_chem_reference_id FROM gene_chem_reference_axn gcra WHERE (gcra.action_degree_type_nm = 'increases'));[ Date: 2025-04-01 05:47:19 - Database: ctddev51 - User: pubeu - Bind query: yes ]
17 4s207ms 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 ptr.term_id IN ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 2 and upper(baseTerm.nm) LIKE 'ACETYLCYSTEINE')) and ptr.term_object_type_id = 2 and ptr.phenotype_id IN ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 5 and baseTerm.id in ( select object_id from db_link l where l.acc_txt = 'GO:0006979' AND l.type_cd = 'A' AND l.object_type_id = 5))) and i.id in ( select ixn_id from ixn_anatomy where anatomy_id IN ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 10 and upper(baseTerm.nm) LIKE 'CARDIOVASCULAR SYSTEM'))) and taxonTerm.id in ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 1 and baseTerm.id in ( select object_id from db_link l where l.acc_txt = '9605' AND l.type_cd = 'A' AND l.object_type_id = 1))) and i.id in ( select ixn_id from ixn_axn where action_type_nm = 'phenotype' and action_degree_type_nm in ('increases')) group by associatedTerm, associatedTermNmSort, phenotype, casRN, ixnId, ixnProseHtml, ixnProse, ixnSort, associatedTermId, phenotypeId, inferredCount ORDER BY associatedTermNmSort LIMIT 50;[ Date: 2025-04-01 05:48:52 - Bind query: yes ]
18 4s113ms SELECT /* AdvancedGeneQueryDAO.getData */ g.id geneId, g.acc_txt acc, g.nm nm, g.nm nmHtml, g.secondary_nm secondaryNm, g.has_chems hasChems, g.has_diseases hasDiseases, g.has_exposures hasExposures, g.has_phenotypes hasPhenotypes, COUNT(*) OVER () fullRowCount FROM term g WHERE g.id IN ( SELECT gcr.gene_id FROM gene_chem_reference gcr WHERE /* CIQH.getIxnWhereCore */ gcr.gene_id = ANY (ARRAY (( SELECT /* IQH.getMasterDiseaseWhereEquals.Name.Gene */ gd.gene_id FROM term t INNER JOIN dag_path dp ON t.id = dp.ancestor_object_id INNER JOIN gene_disease gd ON dp.descendant_object_id = gd.disease_id WHERE UPPER(t.nm) LIKE 'ASTHMA' AND t.object_type_id = 3))) AND gcr.id IN ( SELECT gcra.gene_chem_reference_id FROM gene_chem_reference_axn gcra WHERE (gcra.action_degree_type_nm = 'increases'))) ORDER BY g.nm_sort, g.id LIMIT 50;[ Date: 2025-04-01 05:47:24 - Bind query: yes ]
19 4s43ms 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 ptr.term_id IN ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 2 and upper(baseTerm.nm) LIKE 'ZINC')) and ptr.term_object_type_id = 2 and ptr.phenotype_id IN ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 5 and baseTerm.id in ( select object_id from db_link l where l.acc_txt = 'GO:0006915' AND l.type_cd = 'A' AND l.object_type_id = 5))) and taxonTerm.id in ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 1 and baseTerm.id in ( select object_id from db_link l where l.acc_txt = '9606' AND l.type_cd = 'A' AND l.object_type_id = 1))) and i.id in ( select ixn_id from ixn_axn where action_type_nm = 'phenotype' and action_degree_type_nm in ('increases')) group by associatedTerm, associatedTermNmSort, phenotype, casRN, ixnId, ixnProseHtml, ixnProse, ixnSort, associatedTermId, phenotypeId, inferredCount ORDER BY associatedTermNmSort LIMIT 50;[ Date: 2025-04-01 05:48:47 - Bind query: yes ]
20 3s662ms SELECT /* AdvancedIxnQueryDAO.getData */ g.nm geneSymbol, g.id geneId, g.acc_txt geneAcc, g.acc_db_cd geneAccDbCd, c.nm chemNm, c.nm_html chemNmhtml, c.acc_txt chemAcc, c.secondary_nm casRN, c.id chemId, i.id ixnId, i.ixn_prose_txt ixnProse, i.ixn_prose_html ixnProseHtml, i.actions_txt ixnActions, COUNT(DISTINCT gcr.reference_id) refCount, COUNT(DISTINCT gcr.taxon_id) taxonCount, COUNT(*) OVER () fullRowCount FROM gene_chem_reference gcr INNER JOIN ixn i ON gcr.ixn_id = i.id INNER JOIN term g ON gcr.gene_id = g.id INNER JOIN term c ON gcr.chem_id = c.id WHERE /* CIQH.getIxnWhereCore */ gcr.gene_id = ANY (ARRAY (( SELECT /* IQH.getMasterPathwayWhereEquals.Name */ tp.term_id FROM term_pathway tp WHERE UPPER(tp.pathway_nm) LIKE 'METABOLISM' AND tp.object_type_id = 4))) AND gcr.id IN ( SELECT gcra.gene_chem_reference_id FROM gene_chem_reference_axn gcra WHERE (gcra.action_degree_type_nm = 'increases')) GROUP BY g.nm, g.nm_sort, g.acc_txt, g.acc_db_cd, g.id, c.nm, c.nm_html, c.nm_sort, c.acc_txt, c.secondary_nm, c.id, i.ixn_prose_txt, i.ixn_prose_html, i.sort_txt, i.actions_txt, i.id ORDER BY g.nm_sort, c.nm_sort, i.sort_txt LIMIT 50;[ Date: 2025-04-01 05:47:33 - Bind query: yes ]
Time consuming queries (N)
Rank Total duration Times executed Min duration Max duration Avg duration Query 1 4m32s 2 2m16s 2m16s 2m16s insert into log_query_bots (id, type_cd, query_tm, submission_qty, session_id, remote_addr, http_user_agent, server_nm, node_nm, results_qty, execution_ms, basic_query_type, basic_query_txt, gene_query_type, gene_txt, gene_form_type_txt, taxon_query_type, taxon_txt, chem_query_type, chem_txt, party_query_type, party_nm_txt, acc_txt, go_query_type, go_txt, disease_query_type, disease_txt, action_type_txt, action_degree_type_txt, from_yr, through_yr, title_abstract_txt, has_marray, pathway_query_type, pathway_txt, dag_txt, results_format_txt, batch_input_type_txt, gd_assn_type, p_val, p_val_type, input_term_qty, review_status) select q.id, q.type_cd, q.query_tm, q.submission_qty, q.session_id, q.remote_addr, q.http_user_agent, q.server_nm, q.node_nm, q.results_qty, q.execution_ms, q.basic_query_type, q.basic_query_txt, q.gene_query_type, q.gene_txt, q.gene_form_type_txt, q.taxon_query_type, q.taxon_txt, q.chem_query_type, q.chem_txt, q.party_query_type, q.party_nm_txt, q.acc_txt, q.go_query_type, q.go_txt, q.disease_query_type, q.disease_txt, q.action_type_txt, q.action_degree_type_txt, q.from_yr, q.through_yr, q.title_abstract_txt, q.has_marray, q.pathway_query_type, q.pathway_txt, q.dag_txt, q.results_format_txt, q.batch_input_type_txt, q.gd_assn_type, q.p_val, q.p_val_type, q.input_term_qty, q.review_status from log_query_archive q where upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) and q.id not in ( select a.id from log_query_bots a);Times Reported Time consuming queries #1
Day Hour Count Duration Avg duration Apr 01 14 2 4m32s 2m16s -
--begin transaction --rollback INSERT INTO log_query_bots (id, type_cd, query_tm, submission_qty, session_id, remote_addr, http_user_agent, server_nm, node_nm, results_qty, execution_ms, basic_query_type, basic_query_txt, gene_query_type, gene_txt, gene_form_type_txt, taxon_query_type, taxon_txt, chem_query_type, chem_txt, party_query_type, party_nm_txt, acc_txt, go_query_type, go_txt, disease_query_type, disease_txt, action_type_txt, action_degree_type_txt, from_yr, through_yr, title_abstract_txt, has_marray, pathway_query_type, pathway_txt, dag_txt, results_format_txt, batch_input_type_txt, gd_assn_type, p_val, p_val_type, input_term_qty, review_status) SELECT q.id, q.type_cd, q.query_tm, q.submission_qty, q.session_id, q.remote_addr, q.http_user_agent, q.server_nm, q.node_nm, q.results_qty, q.execution_ms, q.basic_query_type, q.basic_query_txt, q.gene_query_type, q.gene_txt, q.gene_form_type_txt, q.taxon_query_type, q.taxon_txt, q.chem_query_type, q.chem_txt, q.party_query_type, q.party_nm_txt, q.acc_txt, q.go_query_type, q.go_txt, q.disease_query_type, q.disease_txt, q.action_type_txt, q.action_degree_type_txt, q.from_yr, q.through_yr, q.title_abstract_txt, q.has_marray, q.pathway_query_type, q.pathway_txt, q.dag_txt, q.results_format_txt, q.batch_input_type_txt, q.gd_assn_type, q.p_val, q.p_val_type, q.input_term_qty, q.review_status FROM log_query_archive q WHERE upper(q.http_user_agent) LIKE upper('%ARCHIVE%') OR upper(q.http_user_agent) LIKE upper('%SPIDER%') OR upper(q.http_user_agent) LIKE upper('%FACEBOOK%') OR upper(q.http_user_agent) LIKE upper('%NUTCH%') OR upper(q.http_user_agent) LIKE upper('%TRACK%') OR upper(q.http_user_agent) LIKE upper('%SITESUCKER%') OR upper(q.http_user_agent) LIKE upper('%KNOWLEDGE AI%') OR upper(q.http_user_agent) LIKE upper('%MEGAINDEX%') OR upper(q.http_user_agent) LIKE upper('%YANDEX%') OR upper(q.http_user_agent) LIKE upper('%PANSCIENT%') OR upper(q.http_user_agent) LIKE upper('%GOOGLEOTHER%') OR upper(q.http_user_agent) LIKE upper('%QWANTIFY%') OR upper(q.http_user_agent) LIKE upper('%SEOKICKS%') OR upper(q.http_user_agent) LIKE upper('%ARQUIVO%') OR upper(q.http_user_agent) LIKE upper('%SEMANTIC%') AND q.id NOT IN ( SELECT a.id FROM log_query_bots a);
Date: 2025-04-01 14:29:24 Duration: 2m16s
-
--select count(*) from log_query_bots -2776425 --begin transaction --rollback INSERT INTO log_query_bots (id, type_cd, query_tm, submission_qty, session_id, remote_addr, http_user_agent, server_nm, node_nm, results_qty, execution_ms, basic_query_type, basic_query_txt, gene_query_type, gene_txt, gene_form_type_txt, taxon_query_type, taxon_txt, chem_query_type, chem_txt, party_query_type, party_nm_txt, acc_txt, go_query_type, go_txt, disease_query_type, disease_txt, action_type_txt, action_degree_type_txt, from_yr, through_yr, title_abstract_txt, has_marray, pathway_query_type, pathway_txt, dag_txt, results_format_txt, batch_input_type_txt, gd_assn_type, p_val, p_val_type, input_term_qty, review_status) SELECT q.id, q.type_cd, q.query_tm, q.submission_qty, q.session_id, q.remote_addr, q.http_user_agent, q.server_nm, q.node_nm, q.results_qty, q.execution_ms, q.basic_query_type, q.basic_query_txt, q.gene_query_type, q.gene_txt, q.gene_form_type_txt, q.taxon_query_type, q.taxon_txt, q.chem_query_type, q.chem_txt, q.party_query_type, q.party_nm_txt, q.acc_txt, q.go_query_type, q.go_txt, q.disease_query_type, q.disease_txt, q.action_type_txt, q.action_degree_type_txt, q.from_yr, q.through_yr, q.title_abstract_txt, q.has_marray, q.pathway_query_type, q.pathway_txt, q.dag_txt, q.results_format_txt, q.batch_input_type_txt, q.gd_assn_type, q.p_val, q.p_val_type, q.input_term_qty, q.review_status FROM log_query_archive q WHERE upper(q.http_user_agent) LIKE upper('%ARCHIVE%') OR upper(q.http_user_agent) LIKE upper('%SPIDER%') OR upper(q.http_user_agent) LIKE upper('%FACEBOOK%') OR upper(q.http_user_agent) LIKE upper('%NUTCH%') OR upper(q.http_user_agent) LIKE upper('%TRACK%') OR upper(q.http_user_agent) LIKE upper('%SITESUCKER%') OR upper(q.http_user_agent) LIKE upper('%KNOWLEDGE AI%') OR upper(q.http_user_agent) LIKE upper('%MEGAINDEX%') OR upper(q.http_user_agent) LIKE upper('%YANDEX%') OR upper(q.http_user_agent) LIKE upper('%PANSCIENT%') OR upper(q.http_user_agent) LIKE upper('%GOOGLEOTHER%') OR upper(q.http_user_agent) LIKE upper('%QWANTIFY%') OR upper(q.http_user_agent) LIKE upper('%SEOKICKS%') OR upper(q.http_user_agent) LIKE upper('%ARQUIVO%') OR upper(q.http_user_agent) LIKE upper('%SEMANTIC%') AND q.id NOT IN ( SELECT a.id FROM log_query_bots a);
Date: 2025-04-01 14:35:00 Duration: 2m16s
2 2m33s 1 2m33s 2m33s 2m33s vacuum full analyze db_link;Times Reported Time consuming queries #2
Day Hour Count Duration Avg duration Apr 01 10 1 2m33s 2m33s -
vacuum FULL analyze db_link;
Date: 2025-04-01 10:57:23 Duration: 2m33s
-
vacuum FULL analyze db_link;
Date: 2025-04-01 10:55:05 Duration: 0ms
3 2m26s 1 2m26s 2m26s 2m26s insert into log_query_bots (id, type_cd, query_tm, submission_qty, session_id, remote_addr, http_user_agent, server_nm, node_nm, results_qty, execution_ms, basic_query_type, basic_query_txt, gene_query_type, gene_txt, gene_form_type_txt, taxon_query_type, taxon_txt, chem_query_type, chem_txt, party_query_type, party_nm_txt, acc_txt, go_query_type, go_txt, disease_query_type, disease_txt, action_type_txt, action_degree_type_txt, from_yr, through_yr, title_abstract_txt, has_marray, pathway_query_type, pathway_txt, dag_txt, results_format_txt, batch_input_type_txt, gd_assn_type, p_val, p_val_type, input_term_qty, review_status) select q.id, q.type_cd, q.query_tm, q.submission_qty, q.session_id, q.remote_addr, q.http_user_agent, q.server_nm, q.node_nm, q.results_qty, q.execution_ms, q.basic_query_type, q.basic_query_txt, q.gene_query_type, q.gene_txt, q.gene_form_type_txt, q.taxon_query_type, q.taxon_txt, q.chem_query_type, q.chem_txt, q.party_query_type, q.party_nm_txt, q.acc_txt, q.go_query_type, q.go_txt, q.disease_query_type, q.disease_txt, q.action_type_txt, q.action_degree_type_txt, q.from_yr, q.through_yr, q.title_abstract_txt, q.has_marray, q.pathway_query_type, q.pathway_txt, q.dag_txt, q.results_format_txt, q.batch_input_type_txt, q.gd_assn_type, q.p_val, q.p_val_type, q.input_term_qty, q.review_status from log_query_archive q where upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) and q.id not in ( select a.id from log_query_bots a);Times Reported Time consuming queries #3
Day Hour Count Duration Avg duration Apr 01 14 1 2m26s 2m26s -
--select count(*) from log_query_bots -2776425 --begin transaction --rollback INSERT INTO log_query_bots (id, type_cd, query_tm, submission_qty, session_id, remote_addr, http_user_agent, server_nm, node_nm, results_qty, execution_ms, basic_query_type, basic_query_txt, gene_query_type, gene_txt, gene_form_type_txt, taxon_query_type, taxon_txt, chem_query_type, chem_txt, party_query_type, party_nm_txt, acc_txt, go_query_type, go_txt, disease_query_type, disease_txt, action_type_txt, action_degree_type_txt, from_yr, through_yr, title_abstract_txt, has_marray, pathway_query_type, pathway_txt, dag_txt, results_format_txt, batch_input_type_txt, gd_assn_type, p_val, p_val_type, input_term_qty, review_status) SELECT q.id, q.type_cd, q.query_tm, q.submission_qty, q.session_id, q.remote_addr, q.http_user_agent, q.server_nm, q.node_nm, q.results_qty, q.execution_ms, q.basic_query_type, q.basic_query_txt, q.gene_query_type, q.gene_txt, q.gene_form_type_txt, q.taxon_query_type, q.taxon_txt, q.chem_query_type, q.chem_txt, q.party_query_type, q.party_nm_txt, q.acc_txt, q.go_query_type, q.go_txt, q.disease_query_type, q.disease_txt, q.action_type_txt, q.action_degree_type_txt, q.from_yr, q.through_yr, q.title_abstract_txt, q.has_marray, q.pathway_query_type, q.pathway_txt, q.dag_txt, q.results_format_txt, q.batch_input_type_txt, q.gd_assn_type, q.p_val, q.p_val_type, q.input_term_qty, q.review_status FROM log_query_archive q WHERE upper(q.http_user_agent) LIKE upper('%ARCHIVE%') OR upper(q.http_user_agent) LIKE upper('%SPIDER%') OR upper(q.http_user_agent) LIKE upper('%FACEBOOK%') OR upper(q.http_user_agent) LIKE upper('%NUTCH%') OR upper(q.http_user_agent) LIKE upper('%TRACK%') OR upper(q.http_user_agent) LIKE upper('%SITESUCKER%') OR upper(q.http_user_agent) LIKE upper('%KNOWLEDGE AI%') OR upper(q.http_user_agent) LIKE upper('%MEGAINDEX%') OR upper(q.http_user_agent) LIKE upper('%YANDEX%') OR upper(q.http_user_agent) LIKE upper('%PANSCIENT%') OR upper(q.http_user_agent) LIKE upper('%GOOGLEOTHER%') OR upper(q.http_user_agent) LIKE upper('%QWANTIFY%') OR upper(q.http_user_agent) LIKE upper('%SEOKICKS%') OR upper(q.http_user_agent) LIKE upper('%ARQUIVO%') OR upper(q.http_user_agent) LIKE upper('%SEMANTIC%') OR upper(q.http_user_agent) LIKE upper('%BOT%') AND q.id NOT IN ( SELECT a.id FROM log_query_bots a);
Date: 2025-04-01 14:45:41 Duration: 2m26s
4 2m25s 1 2m25s 2m25s 2m25s delete from log_query_archive q where upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?);Times Reported Time consuming queries #4
Day Hour Count Duration Avg duration Apr 01 14 1 2m25s 2m25s -
--begin transaction --rollback --commit DELETE from log_query_archive q WHERE upper(q.http_user_agent) LIKE upper('%ARCHIVE%') OR upper(q.http_user_agent) LIKE upper('%SPIDER%') OR upper(q.http_user_agent) LIKE upper('%FACEBOOK%') OR upper(q.http_user_agent) LIKE upper('%NUTCH%') OR upper(q.http_user_agent) LIKE upper('%TRACK%') OR upper(q.http_user_agent) LIKE upper('%SITESUCKER%') OR upper(q.http_user_agent) LIKE upper('%KNOWLEDGE AI%') OR upper(q.http_user_agent) LIKE upper('%MEGAINDEX%') OR upper(q.http_user_agent) LIKE upper('%YANDEX%') OR upper(q.http_user_agent) LIKE upper('%PANSCIENT%') OR upper(q.http_user_agent) LIKE upper('%GOOGLEOTHER%') OR upper(q.http_user_agent) LIKE upper('%QWANTIFY%') OR upper(q.http_user_agent) LIKE upper('%SEOKICKS%') OR upper(q.http_user_agent) LIKE upper('%ARQUIVO%') OR upper(q.http_user_agent) LIKE upper('%SEMANTIC%') OR upper(q.http_user_agent) LIKE upper('%BOT%');
Date: 2025-04-01 14:50:12 Duration: 2m25s
5 1m33s 1 1m33s 1m33s 1m33s ( select upper(http_user_agent) from log_query_bots l, excluded_user_agent2 ua where upper(l.http_user_agent) like upper(ua.user_agent_pattern));Times Reported Time consuming queries #5
Day Hour Count Duration Avg duration Apr 01 13 1 1m33s 1m33s -
( SELECT upper(http_user_agent) FROM log_query_bots l, excluded_user_agent2 ua WHERE UPPER(l.http_user_agent) LIKE UPPER(ua.user_agent_pattern));
Date: 2025-04-01 13:20:38 Duration: 1m33s
6 58s103ms 1 58s103ms 58s103ms 58s103ms select distinct (http_user_agent) from log_query_bots where upper(http_user_agent) not in ( select distinct upper(http_user_agent) from log_query_bots l, excluded_user_agent2 ua where upper(l.http_user_agent) like upper(ua.user_agent_pattern));Times Reported Time consuming queries #6
Day Hour Count Duration Avg duration Apr 01 13 1 58s103ms 58s103ms -
select distinct (http_user_agent) from log_query_bots where upper(http_user_agent) not in ( SELECT distinct upper(http_user_agent) FROM log_query_bots l, excluded_user_agent2 ua WHERE UPPER(l.http_user_agent) LIKE UPPER(ua.user_agent_pattern));
Date: 2025-04-01 13:22:46 Duration: 58s103ms
7 20s368ms 2 9s945ms 10s422ms 10s184ms ( select distinct upper(http_user_agent) from log_query_bots l, excluded_user_agent2 ua where upper(l.http_user_agent) like upper(ua.user_agent_pattern));Times Reported Time consuming queries #7
Day Hour Count Duration Avg duration Apr 01 13 2 20s368ms 10s184ms -
( SELECT distinct upper(http_user_agent) FROM log_query_bots l, excluded_user_agent2 ua WHERE UPPER(l.http_user_agent) LIKE UPPER(ua.user_agent_pattern));
Date: 2025-04-01 13:14:09 Duration: 10s422ms
-
( SELECT distinct upper(http_user_agent) FROM log_query_bots l, excluded_user_agent2 ua WHERE UPPER(l.http_user_agent) LIKE UPPER(ua.user_agent_pattern));
Date: 2025-04-01 13:21:30 Duration: 9s945ms
8 18s973ms 12 1s507ms 1s968ms 1s581ms select distinct (http_user_agent) from log_query_archive where upper(http_user_agent) like ?;Times Reported Time consuming queries #8
Day Hour Count Duration Avg duration Apr 01 10 2 3s509ms 1s754ms 11 3 4s659ms 1s553ms 13 5 7s675ms 1s535ms 14 2 3s128ms 1s564ms -
select distinct (http_user_agent) from log_query_archive where upper(http_user_agent) LIKE '%GOOGLE%';
Date: 2025-04-01 10:54:43 Duration: 1s968ms
-
select distinct (http_user_agent) from log_query_archive where upper(http_user_agent) LIKE '%BOT%';
Date: 2025-04-01 14:39:45 Duration: 1s577ms
-
select distinct (http_user_agent) from log_query_archive where upper(http_user_agent) LIKE '%EASY%';
Date: 2025-04-01 11:06:44 Duration: 1s572ms
9 13s561ms 1 13s561ms 13s561ms 13s561ms select distinct (http_user_agent) from log_query_bots where upper(http_user_agent) not in ( select http_user_agent from log_query_bots l, excluded_user_agent2 ua where upper(l.http_user_agent) like upper(ua.user_agent_pattern));Times Reported Time consuming queries #9
Day Hour Count Duration Avg duration Apr 01 13 1 13s561ms 13s561ms -
select distinct (http_user_agent) from log_query_bots where upper(http_user_agent) not in ( SELECT http_user_agent FROM log_query_bots l, excluded_user_agent2 ua WHERE UPPER(l.http_user_agent) LIKE UPPER(ua.user_agent_pattern));
Date: 2025-04-01 13:10:33 Duration: 13s561ms
10 13s276ms 1 13s276ms 13s276ms 13s276ms select distinct (http_user_agent) from log_query_bots where upper(http_user_agent) not in ( select upper(http_user_agent) from log_query_bots l, excluded_user_agent2 ua where upper(l.http_user_agent) like upper(ua.user_agent_pattern));Times Reported Time consuming queries #10
Day Hour Count Duration Avg duration Apr 01 13 1 13s276ms 13s276ms -
select distinct (http_user_agent) from log_query_bots where upper(http_user_agent) not in ( SELECT upper(http_user_agent) FROM log_query_bots l, excluded_user_agent2 ua WHERE UPPER(l.http_user_agent) LIKE UPPER(ua.user_agent_pattern));
Date: 2025-04-01 13:12:41 Duration: 13s276ms
11 11s704ms 2 5s442ms 6s262ms 5s852ms select distinct (http_user_agent) from log_query_archive where upper(http_user_agent) like ? or upper(http_user_agent) like ? or upper(http_user_agent) like ? or upper(http_user_agent) like ?;Times Reported Time consuming queries #11
Day Hour Count Duration Avg duration Apr 01 13 2 11s704ms 5s852ms -
select distinct (http_user_agent) from log_query_archive --select distinct(http_user_agent) from log_query_bots where upper(http_user_agent) LIKE '%SEMANTIC%' OR upper(http_user_agent) LIKE '%QWANTIFY%' OR upper(http_user_agent) LIKE '%SEOKICKS%' OR upper(http_user_agent) LIKE '%ARQUIVO%';
Date: 2025-04-01 13:59:19 Duration: 6s262ms
-
select distinct (http_user_agent) from log_query_archive where upper(http_user_agent) LIKE '%SEMANTIC%' OR upper(http_user_agent) LIKE '%QWANTIFY%' OR upper(http_user_agent) LIKE '%SEOKICKS%' OR upper(http_user_agent) LIKE '%ARQUIVO%';
Date: 2025-04-01 13:32:33 Duration: 5s442ms
12 11s301ms 1 11s301ms 11s301ms 11s301ms select distinct (http_user_agent) from log_query_bots where http_user_agent not in ( select http_user_agent from log_query_bots l, excluded_user_agent2 ua where upper(l.http_user_agent) like upper(ua.user_agent_pattern));Times Reported Time consuming queries #12
Day Hour Count Duration Avg duration Apr 01 13 1 11s301ms 11s301ms -
select distinct (http_user_agent) from log_query_bots where http_user_agent not in ( SELECT http_user_agent FROM log_query_bots l, excluded_user_agent2 ua WHERE UPPER(l.http_user_agent) LIKE UPPER(ua.user_agent_pattern));
Date: 2025-04-01 13:11:38 Duration: 11s301ms
13 9s569ms 6 1s512ms 1s898ms 1s594ms select count(*) from log_query_archive where upper(http_user_agent) like ?;Times Reported Time consuming queries #13
Day Hour Count Duration Avg duration Apr 01 10 1 1s898ms 1s898ms 13 1 1s530ms 1s530ms 14 4 6s140ms 1s535ms [ User: pubc - Total duration: 1s898ms - Times executed: 1 ]
[ Application: pgAdmin 4 - CONN:9715397 - Total duration: 1s898ms - Times executed: 1 ]
-
select count(*) from log_query_archive where upper(http_user_agent) LIKE '%GOOGLE%';
Date: 2025-04-01 10:53:48 Duration: 1s898ms Database: ctddev51 User: pubc Application: pgAdmin 4 - CONN:9715397
-
select count(*) from log_query_archive where upper(http_user_agent) LIKE '%CRAWL%';
Date: 2025-04-01 14:39:04 Duration: 1s550ms
-
select count(*) from log_query_archive where upper(http_user_agent) LIKE '%GPTBOT%';
Date: 2025-04-01 14:41:55 Duration: 1s548ms
14 5s329ms 3 1s534ms 1s910ms 1s776ms select * from log_query_archive where upper(http_user_agent) like ?;Times Reported Time consuming queries #14
Day Hour Count Duration Avg duration Apr 01 13 1 1s534ms 1s534ms 14 2 3s794ms 1s897ms -
select * from log_query_archive where upper(http_user_agent) LIKE '%SEMANTIC%';
Date: 2025-04-01 14:06:30 Duration: 1s910ms
-
select * from log_query_archive where upper(http_user_agent) LIKE '%QWANTIFY%';
Date: 2025-04-01 14:06:00 Duration: 1s884ms
-
select * from log_query_archive where upper(http_user_agent) LIKE '%SEMANTIC%';
Date: 2025-04-01 13:42:16 Duration: 1s534ms
15 4s693ms 1 4s693ms 4s693ms 4s693ms select ? "Input", sqi.chem_nm "ChemicalName", sqi.chem_acc_txt "ChemicalID", sqi.casrn "CasRN", sqi.gene_symbol "GeneSymbol", sqi.gene_acc_txt "GeneID", sqi.ontology_nm "Ontology", sqi.go_term_nm "GoTermName", sqi.go_acc_txt "GoTermID" from ( with sq as ( select distinct c.id chem_id, c.nm chem_nm, c.acc_txt chem_acc_txt, c.secondary_nm casrn, c.nm_sort chem_nm_sort, gcr.gene_id, g.nm gene_symbol, g.acc_txt gene_acc_txt, g.nm_sort gene_symbol_sort from term c inner join gene_chem_reference gcr on c.id = gcr.chem_id inner join term g on gcr.gene_id = g.id where (c.id = ?)) select distinct sq.chem_nm, sq.chem_acc_txt, sq.casrn, sq.gene_symbol, sq.gene_acc_txt, gt.nm go_term_nm, gt.acc_txt go_acc_txt, sq.chem_nm_sort, sq.gene_symbol_sort, gt.nm_sort, d.nm ontology_nm from sq inner join gene_go_annot gga on sq.gene_id = gga.gene_id inner join dag_node gt on gga.go_term_id = gt.object_id inner join dag d on gt.dag_id = d.id where gga.is_not = false and (d.id = ? or d.id = ?) order by sq.chem_nm_sort, sq.gene_symbol_sort, d.nm, gt.nm_sort) sqi;Times Reported Time consuming queries #15
Day Hour Count Duration Avg duration Apr 01 05 1 4s693ms 4s693ms [ User: pubeu - Total duration: 4s693ms - Times executed: 1 ]
-
SELECT /* BatchChemGODAO */ 'ddt' "Input", sqi.chem_nm "ChemicalName", sqi.chem_acc_txt "ChemicalID", sqi.casRN "CasRN", sqi.gene_symbol "GeneSymbol", sqi.gene_acc_txt "GeneID", sqi.ontology_nm "Ontology", sqi.go_term_nm "GoTermName", sqi.go_acc_txt "GoTermID" FROM ( WITH sq AS ( SELECT DISTINCT c.id chem_id, c.nm chem_nm, c.acc_txt chem_acc_txt, c.secondary_nm casRN, c.nm_sort chem_nm_sort, gcr.gene_id, g.nm gene_symbol, g.acc_txt gene_acc_txt, g.nm_sort gene_symbol_sort FROM term c INNER JOIN gene_chem_reference gcr ON c.id = gcr.chem_id INNER JOIN term g ON gcr.gene_id = g.id WHERE (c.id = 1308127)) SELECT DISTINCT sq.chem_nm, sq.chem_acc_txt, sq.casRN, sq.gene_symbol, sq.gene_acc_txt, gt.nm go_term_nm, gt.acc_txt go_acc_txt, sq.chem_nm_sort, sq.gene_symbol_sort, gt.nm_sort, d.nm ontology_nm FROM sq INNER JOIN gene_go_annot gga ON sq.gene_id = gga.gene_id INNER JOIN dag_node gt ON gga.go_term_id = gt.object_id INNER JOIN dag d ON gt.dag_id = d.id WHERE gga.is_not = false AND (d.id = 5 OR d.id = 4) ORDER BY sq.chem_nm_sort, sq.gene_symbol_sort, d.nm, gt.nm_sort) sqi;
Date: 2025-04-01 05:48:27 Duration: 4s693ms Database: ctddev51 User: pubeu Bind query: yes
16 4s267ms 1 4s267ms 4s267ms 4s267ms select gcr.ixn_id, null, null, null from gene_chem_reference gcr where gcr.gene_id = any (array (( select gd.gene_id from term t inner join dag_path dp on t.id = dp.ancestor_object_id inner join gene_disease gd on dp.descendant_object_id = gd.disease_id where upper(t.nm) like ? and t.object_type_id = ?))) and gcr.id in ( select gcra.gene_chem_reference_id from gene_chem_reference_axn gcra where (gcra.action_degree_type_nm = ?));Times Reported Time consuming queries #16
Day Hour Count Duration Avg duration Apr 01 05 1 4s267ms 4s267ms [ User: pubeu - Total duration: 4s267ms - Times executed: 1 ]
-
SELECT /* CIQH.getIxnCacheQuery */ gcr.ixn_id, NULL, NULL, NULL FROM gene_chem_reference gcr WHERE /* CIQH.getIxnWhereCore */ gcr.gene_id = ANY (ARRAY (( SELECT /* IQH.getMasterDiseaseWhereEquals.Name.Gene */ gd.gene_id FROM term t INNER JOIN dag_path dp ON t.id = dp.ancestor_object_id INNER JOIN gene_disease gd ON dp.descendant_object_id = gd.disease_id WHERE UPPER(t.nm) LIKE 'ASTHMA' AND t.object_type_id = 3))) AND gcr.id IN ( SELECT gcra.gene_chem_reference_id FROM gene_chem_reference_axn gcra WHERE (gcra.action_degree_type_nm = 'increases'));
Date: 2025-04-01 05:47:19 Duration: 4s267ms Database: ctddev51 User: pubeu Bind query: yes
17 4s207ms 1 4s207ms 4s207ms 4s207ms 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 ptr.term_id in ( select distinct dp.descendant_object_id from dag_path dp where dp.ancestor_object_id in ( select distinct id from term baseterm where object_type_id = ? and upper(baseterm.nm) like ?)) and ptr.term_object_type_id = ? and ptr.phenotype_id in ( select distinct dp.descendant_object_id from dag_path dp where dp.ancestor_object_id in ( select distinct id from term baseterm where object_type_id = ? and baseterm.id in ( select object_id from db_link l where l.acc_txt = ? and l.type_cd = ? and l.object_type_id = ?))) and i.id in ( select ixn_id from ixn_anatomy where anatomy_id in ( select distinct dp.descendant_object_id from dag_path dp where dp.ancestor_object_id in ( select distinct id from term baseterm where object_type_id = ? and upper(baseterm.nm) like ?))) and taxonterm.id in ( select distinct dp.descendant_object_id from dag_path dp where dp.ancestor_object_id in ( select distinct id from term baseterm where object_type_id = ? and baseterm.id in ( select object_id from db_link l where l.acc_txt = ? and l.type_cd = ? and l.object_type_id = ?))) and i.id in ( select ixn_id from ixn_axn where action_type_nm = ? and action_degree_type_nm in (...)) group by associatedterm, associatedtermnmsort, phenotype, casrn, ixnid, ixnprosehtml, ixnprose, ixnsort, associatedtermid, phenotypeid, inferredcount order by associatedtermnmsort limit ?;Times Reported Time consuming queries #17
Day Hour Count Duration Avg duration Apr 01 05 1 4s207ms 4s207ms -
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 ptr.term_id IN ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 2 and upper(baseTerm.nm) LIKE 'ACETYLCYSTEINE')) and ptr.term_object_type_id = 2 and ptr.phenotype_id IN ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 5 and baseTerm.id in ( select object_id from db_link l where l.acc_txt = 'GO:0006979' AND l.type_cd = 'A' AND l.object_type_id = 5))) and i.id in ( select ixn_id from ixn_anatomy where anatomy_id IN ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 10 and upper(baseTerm.nm) LIKE 'CARDIOVASCULAR SYSTEM'))) and taxonTerm.id in ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 1 and baseTerm.id in ( select object_id from db_link l where l.acc_txt = '9605' AND l.type_cd = 'A' AND l.object_type_id = 1))) and i.id in ( select ixn_id from ixn_axn where action_type_nm = 'phenotype' and action_degree_type_nm in ('increases')) group by associatedTerm, associatedTermNmSort, phenotype, casRN, ixnId, ixnProseHtml, ixnProse, ixnSort, associatedTermId, phenotypeId, inferredCount ORDER BY associatedTermNmSort LIMIT 50;
Date: 2025-04-01 05:48:52 Duration: 4s207ms Bind query: yes
18 4s113ms 1 4s113ms 4s113ms 4s113ms select g.id geneid, g.acc_txt acc, g.nm nm, g.nm nmhtml, g.secondary_nm secondarynm, g.has_chems haschems, g.has_diseases hasdiseases, g.has_exposures hasexposures, g.has_phenotypes hasphenotypes, count(*) over () fullrowcount from term g where g.id in ( select gcr.gene_id from gene_chem_reference gcr where gcr.gene_id = any (array (( select gd.gene_id from term t inner join dag_path dp on t.id = dp.ancestor_object_id inner join gene_disease gd on dp.descendant_object_id = gd.disease_id where upper(t.nm) like ? and t.object_type_id = ?))) and gcr.id in ( select gcra.gene_chem_reference_id from gene_chem_reference_axn gcra where (gcra.action_degree_type_nm = ?))) order by g.nm_sort, g.id limit ?;Times Reported Time consuming queries #18
Day Hour Count Duration Avg duration Apr 01 05 1 4s113ms 4s113ms -
SELECT /* AdvancedGeneQueryDAO.getData */ g.id geneId, g.acc_txt acc, g.nm nm, g.nm nmHtml, g.secondary_nm secondaryNm, g.has_chems hasChems, g.has_diseases hasDiseases, g.has_exposures hasExposures, g.has_phenotypes hasPhenotypes, COUNT(*) OVER () fullRowCount FROM term g WHERE g.id IN ( SELECT gcr.gene_id FROM gene_chem_reference gcr WHERE /* CIQH.getIxnWhereCore */ gcr.gene_id = ANY (ARRAY (( SELECT /* IQH.getMasterDiseaseWhereEquals.Name.Gene */ gd.gene_id FROM term t INNER JOIN dag_path dp ON t.id = dp.ancestor_object_id INNER JOIN gene_disease gd ON dp.descendant_object_id = gd.disease_id WHERE UPPER(t.nm) LIKE 'ASTHMA' AND t.object_type_id = 3))) AND gcr.id IN ( SELECT gcra.gene_chem_reference_id FROM gene_chem_reference_axn gcra WHERE (gcra.action_degree_type_nm = 'increases'))) ORDER BY g.nm_sort, g.id LIMIT 50;
Date: 2025-04-01 05:47:24 Duration: 4s113ms Bind query: yes
19 4s43ms 1 4s43ms 4s43ms 4s43ms 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 ptr.term_id in ( select distinct dp.descendant_object_id from dag_path dp where dp.ancestor_object_id in ( select distinct id from term baseterm where object_type_id = ? and upper(baseterm.nm) like ?)) and ptr.term_object_type_id = ? and ptr.phenotype_id in ( select distinct dp.descendant_object_id from dag_path dp where dp.ancestor_object_id in ( select distinct id from term baseterm where object_type_id = ? and baseterm.id in ( select object_id from db_link l where l.acc_txt = ? and l.type_cd = ? and l.object_type_id = ?))) and taxonterm.id in ( select distinct dp.descendant_object_id from dag_path dp where dp.ancestor_object_id in ( select distinct id from term baseterm where object_type_id = ? and baseterm.id in ( select object_id from db_link l where l.acc_txt = ? and l.type_cd = ? and l.object_type_id = ?))) and i.id in ( select ixn_id from ixn_axn where action_type_nm = ? and action_degree_type_nm in (...)) group by associatedterm, associatedtermnmsort, phenotype, casrn, ixnid, ixnprosehtml, ixnprose, ixnsort, associatedtermid, phenotypeid, inferredcount order by associatedtermnmsort limit ?;Times Reported Time consuming queries #19
Day Hour Count Duration Avg duration Apr 01 05 1 4s43ms 4s43ms -
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 ptr.term_id IN ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 2 and upper(baseTerm.nm) LIKE 'ZINC')) and ptr.term_object_type_id = 2 and ptr.phenotype_id IN ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 5 and baseTerm.id in ( select object_id from db_link l where l.acc_txt = 'GO:0006915' AND l.type_cd = 'A' AND l.object_type_id = 5))) and taxonTerm.id in ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 1 and baseTerm.id in ( select object_id from db_link l where l.acc_txt = '9606' AND l.type_cd = 'A' AND l.object_type_id = 1))) and i.id in ( select ixn_id from ixn_axn where action_type_nm = 'phenotype' and action_degree_type_nm in ('increases')) group by associatedTerm, associatedTermNmSort, phenotype, casRN, ixnId, ixnProseHtml, ixnProse, ixnSort, associatedTermId, phenotypeId, inferredCount ORDER BY associatedTermNmSort LIMIT 50;
Date: 2025-04-01 05:48:47 Duration: 4s43ms Bind query: yes
20 3s821ms 2 1s745ms 2s75ms 1s910ms select distinct (http_user_agent) from log_query_bots where upper(http_user_agent) like ? or upper(http_user_agent) like ? or upper(http_user_agent) like ? or upper(http_user_agent) like ?;Times Reported Time consuming queries #20
Day Hour Count Duration Avg duration Apr 01 13 2 3s821ms 1s910ms -
select distinct (http_user_agent) from log_query_bots where upper(http_user_agent) LIKE '%SEMANTIC%' OR upper(http_user_agent) LIKE '%QWANTIFY%' OR upper(http_user_agent) LIKE '%SEOKICKS%' OR upper(http_user_agent) LIKE '%ARQUIVO%';
Date: 2025-04-01 13:59:40 Duration: 2s75ms
-
select distinct (http_user_agent) from log_query_bots where upper(http_user_agent) LIKE '%SEMANTIC%' OR upper(http_user_agent) LIKE '%QWANTIFY%' OR upper(http_user_agent) LIKE '%SEOKICKS%' OR upper(http_user_agent) LIKE '%ARQUIVO%';
Date: 2025-04-01 13:36:01 Duration: 1s745ms
Most frequent queries (N)
Rank Times executed Total duration Min duration Max duration Avg duration Query 1 12 18s973ms 1s507ms 1s968ms 1s581ms select distinct (http_user_agent) from log_query_archive where upper(http_user_agent) like ?;Times Reported Time consuming queries #1
Day Hour Count Duration Avg duration Apr 01 10 2 3s509ms 1s754ms 11 3 4s659ms 1s553ms 13 5 7s675ms 1s535ms 14 2 3s128ms 1s564ms -
select distinct (http_user_agent) from log_query_archive where upper(http_user_agent) LIKE '%GOOGLE%';
Date: 2025-04-01 10:54:43 Duration: 1s968ms
-
select distinct (http_user_agent) from log_query_archive where upper(http_user_agent) LIKE '%BOT%';
Date: 2025-04-01 14:39:45 Duration: 1s577ms
-
select distinct (http_user_agent) from log_query_archive where upper(http_user_agent) LIKE '%EASY%';
Date: 2025-04-01 11:06:44 Duration: 1s572ms
2 6 9s569ms 1s512ms 1s898ms 1s594ms select count(*) from log_query_archive where upper(http_user_agent) like ?;Times Reported Time consuming queries #2
Day Hour Count Duration Avg duration Apr 01 10 1 1s898ms 1s898ms 13 1 1s530ms 1s530ms 14 4 6s140ms 1s535ms [ User: pubc - Total duration: 1s898ms - Times executed: 1 ]
[ Application: pgAdmin 4 - CONN:9715397 - Total duration: 1s898ms - Times executed: 1 ]
-
select count(*) from log_query_archive where upper(http_user_agent) LIKE '%GOOGLE%';
Date: 2025-04-01 10:53:48 Duration: 1s898ms Database: ctddev51 User: pubc Application: pgAdmin 4 - CONN:9715397
-
select count(*) from log_query_archive where upper(http_user_agent) LIKE '%CRAWL%';
Date: 2025-04-01 14:39:04 Duration: 1s550ms
-
select count(*) from log_query_archive where upper(http_user_agent) LIKE '%GPTBOT%';
Date: 2025-04-01 14:41:55 Duration: 1s548ms
3 3 5s329ms 1s534ms 1s910ms 1s776ms select * from log_query_archive where upper(http_user_agent) like ?;Times Reported Time consuming queries #3
Day Hour Count Duration Avg duration Apr 01 13 1 1s534ms 1s534ms 14 2 3s794ms 1s897ms -
select * from log_query_archive where upper(http_user_agent) LIKE '%SEMANTIC%';
Date: 2025-04-01 14:06:30 Duration: 1s910ms
-
select * from log_query_archive where upper(http_user_agent) LIKE '%QWANTIFY%';
Date: 2025-04-01 14:06:00 Duration: 1s884ms
-
select * from log_query_archive where upper(http_user_agent) LIKE '%SEMANTIC%';
Date: 2025-04-01 13:42:16 Duration: 1s534ms
4 3 3s486ms 1s26ms 1s259ms 1s162ms select * from log_query_bots where upper(http_user_agent) like ?;Times Reported Time consuming queries #4
Day Hour Count Duration Avg duration Apr 01 14 3 3s486ms 1s162ms -
select * from log_query_bots where upper(http_user_agent) LIKE '%SEMANTICS%';
Date: 2025-04-01 14:03:28 Duration: 1s259ms
-
select * from log_query_bots where upper(http_user_agent) LIKE '%SEMANTICS%';
Date: 2025-04-01 14:09:09 Duration: 1s200ms
-
select * from log_query_bots where upper(http_user_agent) LIKE '%SEOKICKS%';
Date: 2025-04-01 14:01:53 Duration: 1s26ms
5 2 4m32s 2m16s 2m16s 2m16s insert into log_query_bots (id, type_cd, query_tm, submission_qty, session_id, remote_addr, http_user_agent, server_nm, node_nm, results_qty, execution_ms, basic_query_type, basic_query_txt, gene_query_type, gene_txt, gene_form_type_txt, taxon_query_type, taxon_txt, chem_query_type, chem_txt, party_query_type, party_nm_txt, acc_txt, go_query_type, go_txt, disease_query_type, disease_txt, action_type_txt, action_degree_type_txt, from_yr, through_yr, title_abstract_txt, has_marray, pathway_query_type, pathway_txt, dag_txt, results_format_txt, batch_input_type_txt, gd_assn_type, p_val, p_val_type, input_term_qty, review_status) select q.id, q.type_cd, q.query_tm, q.submission_qty, q.session_id, q.remote_addr, q.http_user_agent, q.server_nm, q.node_nm, q.results_qty, q.execution_ms, q.basic_query_type, q.basic_query_txt, q.gene_query_type, q.gene_txt, q.gene_form_type_txt, q.taxon_query_type, q.taxon_txt, q.chem_query_type, q.chem_txt, q.party_query_type, q.party_nm_txt, q.acc_txt, q.go_query_type, q.go_txt, q.disease_query_type, q.disease_txt, q.action_type_txt, q.action_degree_type_txt, q.from_yr, q.through_yr, q.title_abstract_txt, q.has_marray, q.pathway_query_type, q.pathway_txt, q.dag_txt, q.results_format_txt, q.batch_input_type_txt, q.gd_assn_type, q.p_val, q.p_val_type, q.input_term_qty, q.review_status from log_query_archive q where upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) and q.id not in ( select a.id from log_query_bots a);Times Reported Time consuming queries #5
Day Hour Count Duration Avg duration Apr 01 14 2 4m32s 2m16s -
--begin transaction --rollback INSERT INTO log_query_bots (id, type_cd, query_tm, submission_qty, session_id, remote_addr, http_user_agent, server_nm, node_nm, results_qty, execution_ms, basic_query_type, basic_query_txt, gene_query_type, gene_txt, gene_form_type_txt, taxon_query_type, taxon_txt, chem_query_type, chem_txt, party_query_type, party_nm_txt, acc_txt, go_query_type, go_txt, disease_query_type, disease_txt, action_type_txt, action_degree_type_txt, from_yr, through_yr, title_abstract_txt, has_marray, pathway_query_type, pathway_txt, dag_txt, results_format_txt, batch_input_type_txt, gd_assn_type, p_val, p_val_type, input_term_qty, review_status) SELECT q.id, q.type_cd, q.query_tm, q.submission_qty, q.session_id, q.remote_addr, q.http_user_agent, q.server_nm, q.node_nm, q.results_qty, q.execution_ms, q.basic_query_type, q.basic_query_txt, q.gene_query_type, q.gene_txt, q.gene_form_type_txt, q.taxon_query_type, q.taxon_txt, q.chem_query_type, q.chem_txt, q.party_query_type, q.party_nm_txt, q.acc_txt, q.go_query_type, q.go_txt, q.disease_query_type, q.disease_txt, q.action_type_txt, q.action_degree_type_txt, q.from_yr, q.through_yr, q.title_abstract_txt, q.has_marray, q.pathway_query_type, q.pathway_txt, q.dag_txt, q.results_format_txt, q.batch_input_type_txt, q.gd_assn_type, q.p_val, q.p_val_type, q.input_term_qty, q.review_status FROM log_query_archive q WHERE upper(q.http_user_agent) LIKE upper('%ARCHIVE%') OR upper(q.http_user_agent) LIKE upper('%SPIDER%') OR upper(q.http_user_agent) LIKE upper('%FACEBOOK%') OR upper(q.http_user_agent) LIKE upper('%NUTCH%') OR upper(q.http_user_agent) LIKE upper('%TRACK%') OR upper(q.http_user_agent) LIKE upper('%SITESUCKER%') OR upper(q.http_user_agent) LIKE upper('%KNOWLEDGE AI%') OR upper(q.http_user_agent) LIKE upper('%MEGAINDEX%') OR upper(q.http_user_agent) LIKE upper('%YANDEX%') OR upper(q.http_user_agent) LIKE upper('%PANSCIENT%') OR upper(q.http_user_agent) LIKE upper('%GOOGLEOTHER%') OR upper(q.http_user_agent) LIKE upper('%QWANTIFY%') OR upper(q.http_user_agent) LIKE upper('%SEOKICKS%') OR upper(q.http_user_agent) LIKE upper('%ARQUIVO%') OR upper(q.http_user_agent) LIKE upper('%SEMANTIC%') AND q.id NOT IN ( SELECT a.id FROM log_query_bots a);
Date: 2025-04-01 14:29:24 Duration: 2m16s
-
--select count(*) from log_query_bots -2776425 --begin transaction --rollback INSERT INTO log_query_bots (id, type_cd, query_tm, submission_qty, session_id, remote_addr, http_user_agent, server_nm, node_nm, results_qty, execution_ms, basic_query_type, basic_query_txt, gene_query_type, gene_txt, gene_form_type_txt, taxon_query_type, taxon_txt, chem_query_type, chem_txt, party_query_type, party_nm_txt, acc_txt, go_query_type, go_txt, disease_query_type, disease_txt, action_type_txt, action_degree_type_txt, from_yr, through_yr, title_abstract_txt, has_marray, pathway_query_type, pathway_txt, dag_txt, results_format_txt, batch_input_type_txt, gd_assn_type, p_val, p_val_type, input_term_qty, review_status) SELECT q.id, q.type_cd, q.query_tm, q.submission_qty, q.session_id, q.remote_addr, q.http_user_agent, q.server_nm, q.node_nm, q.results_qty, q.execution_ms, q.basic_query_type, q.basic_query_txt, q.gene_query_type, q.gene_txt, q.gene_form_type_txt, q.taxon_query_type, q.taxon_txt, q.chem_query_type, q.chem_txt, q.party_query_type, q.party_nm_txt, q.acc_txt, q.go_query_type, q.go_txt, q.disease_query_type, q.disease_txt, q.action_type_txt, q.action_degree_type_txt, q.from_yr, q.through_yr, q.title_abstract_txt, q.has_marray, q.pathway_query_type, q.pathway_txt, q.dag_txt, q.results_format_txt, q.batch_input_type_txt, q.gd_assn_type, q.p_val, q.p_val_type, q.input_term_qty, q.review_status FROM log_query_archive q WHERE upper(q.http_user_agent) LIKE upper('%ARCHIVE%') OR upper(q.http_user_agent) LIKE upper('%SPIDER%') OR upper(q.http_user_agent) LIKE upper('%FACEBOOK%') OR upper(q.http_user_agent) LIKE upper('%NUTCH%') OR upper(q.http_user_agent) LIKE upper('%TRACK%') OR upper(q.http_user_agent) LIKE upper('%SITESUCKER%') OR upper(q.http_user_agent) LIKE upper('%KNOWLEDGE AI%') OR upper(q.http_user_agent) LIKE upper('%MEGAINDEX%') OR upper(q.http_user_agent) LIKE upper('%YANDEX%') OR upper(q.http_user_agent) LIKE upper('%PANSCIENT%') OR upper(q.http_user_agent) LIKE upper('%GOOGLEOTHER%') OR upper(q.http_user_agent) LIKE upper('%QWANTIFY%') OR upper(q.http_user_agent) LIKE upper('%SEOKICKS%') OR upper(q.http_user_agent) LIKE upper('%ARQUIVO%') OR upper(q.http_user_agent) LIKE upper('%SEMANTIC%') AND q.id NOT IN ( SELECT a.id FROM log_query_bots a);
Date: 2025-04-01 14:35:00 Duration: 2m16s
6 2 20s368ms 9s945ms 10s422ms 10s184ms ( select distinct upper(http_user_agent) from log_query_bots l, excluded_user_agent2 ua where upper(l.http_user_agent) like upper(ua.user_agent_pattern));Times Reported Time consuming queries #6
Day Hour Count Duration Avg duration Apr 01 13 2 20s368ms 10s184ms -
( SELECT distinct upper(http_user_agent) FROM log_query_bots l, excluded_user_agent2 ua WHERE UPPER(l.http_user_agent) LIKE UPPER(ua.user_agent_pattern));
Date: 2025-04-01 13:14:09 Duration: 10s422ms
-
( SELECT distinct upper(http_user_agent) FROM log_query_bots l, excluded_user_agent2 ua WHERE UPPER(l.http_user_agent) LIKE UPPER(ua.user_agent_pattern));
Date: 2025-04-01 13:21:30 Duration: 9s945ms
7 2 11s704ms 5s442ms 6s262ms 5s852ms select distinct (http_user_agent) from log_query_archive where upper(http_user_agent) like ? or upper(http_user_agent) like ? or upper(http_user_agent) like ? or upper(http_user_agent) like ?;Times Reported Time consuming queries #7
Day Hour Count Duration Avg duration Apr 01 13 2 11s704ms 5s852ms -
select distinct (http_user_agent) from log_query_archive --select distinct(http_user_agent) from log_query_bots where upper(http_user_agent) LIKE '%SEMANTIC%' OR upper(http_user_agent) LIKE '%QWANTIFY%' OR upper(http_user_agent) LIKE '%SEOKICKS%' OR upper(http_user_agent) LIKE '%ARQUIVO%';
Date: 2025-04-01 13:59:19 Duration: 6s262ms
-
select distinct (http_user_agent) from log_query_archive where upper(http_user_agent) LIKE '%SEMANTIC%' OR upper(http_user_agent) LIKE '%QWANTIFY%' OR upper(http_user_agent) LIKE '%SEOKICKS%' OR upper(http_user_agent) LIKE '%ARQUIVO%';
Date: 2025-04-01 13:32:33 Duration: 5s442ms
8 2 3s821ms 1s745ms 2s75ms 1s910ms select distinct (http_user_agent) from log_query_bots where upper(http_user_agent) like ? or upper(http_user_agent) like ? or upper(http_user_agent) like ? or upper(http_user_agent) like ?;Times Reported Time consuming queries #8
Day Hour Count Duration Avg duration Apr 01 13 2 3s821ms 1s910ms -
select distinct (http_user_agent) from log_query_bots where upper(http_user_agent) LIKE '%SEMANTIC%' OR upper(http_user_agent) LIKE '%QWANTIFY%' OR upper(http_user_agent) LIKE '%SEOKICKS%' OR upper(http_user_agent) LIKE '%ARQUIVO%';
Date: 2025-04-01 13:59:40 Duration: 2s75ms
-
select distinct (http_user_agent) from log_query_bots where upper(http_user_agent) LIKE '%SEMANTIC%' OR upper(http_user_agent) LIKE '%QWANTIFY%' OR upper(http_user_agent) LIKE '%SEOKICKS%' OR upper(http_user_agent) LIKE '%ARQUIVO%';
Date: 2025-04-01 13:36:01 Duration: 1s745ms
9 2 2s873ms 1s416ms 1s457ms 1s436ms select fg.nm fromgenesymbol, fg.acc_txt fromgeneacc, tg.nm togenesymbol, tg.acc_txt togeneacc, ft.nm fromtaxonnm, ft.secondary_nm fromtaxoncommonnm, ft.acc_txt fromtaxonacc, tt.nm totaxonnm, tt.secondary_nm totaxoncommonnm, tt.acc_txt totaxonacc, ggr.experimental_sys_nm, ggr.experimental_sys_type, ( select string_agg(ggt.throughput_txt, ? order by ggt.throughput_txt) from gene_gene_ref_throughput ggt where ggt.gene_gene_reference_id = ggr.id) throughput, count(*) over () fullrowcount from gene_gene_reference ggr inner join term fg on ggr.from_gene_id = fg.id inner join term tg on ggr.to_gene_id = tg.id inner join term ft on ggr.from_taxon_id = ft.id inner join term tt on ggr.to_taxon_id = tt.id where ggr.reference_id = ? order by fg.nm_sort, tg.nm_sort limit ?;Times Reported Time consuming queries #9
Day Hour Count Duration Avg duration Apr 01 05 2 2s873ms 1s436ms -
SELECT /* ReferenceGeneGeneIxnsDAO */ fg.nm fromGeneSymbol, fg.acc_txt fromGeneAcc, tg.nm toGeneSymbol, tg.acc_txt toGeneAcc, ft.nm fromTaxonNm, ft.secondary_nm fromTaxonCommonNm, ft.acc_txt fromTaxonAcc, tt.nm toTaxonNm, tt.secondary_nm toTaxonCommonNm, tt.acc_txt toTaxonAcc, ggr.experimental_sys_nm, ggr.experimental_sys_type, ( SELECT STRING_AGG(ggt.throughput_txt, ', ' ORDER BY ggt.throughput_txt) FROM gene_gene_ref_throughput ggt WHERE ggt.gene_gene_reference_id = ggr.id) throughput, COUNT(*) OVER () fullRowCount FROM gene_gene_reference ggr INNER JOIN term fg ON ggr.from_gene_id = fg.id INNER JOIN term tg ON ggr.to_gene_id = tg.id INNER JOIN term ft ON ggr.from_taxon_id = ft.id INNER JOIN term tt ON ggr.to_taxon_id = tt.id WHERE ggr.reference_id = '111363' ORDER BY fg.nm_sort, tg.nm_sort LIMIT 50;
Date: 2025-04-01 05:48:07 Duration: 1s457ms Bind query: yes
-
SELECT /* ReferenceGeneGeneIxnsDAO */ fg.nm fromGeneSymbol, fg.acc_txt fromGeneAcc, tg.nm toGeneSymbol, tg.acc_txt toGeneAcc, ft.nm fromTaxonNm, ft.secondary_nm fromTaxonCommonNm, ft.acc_txt fromTaxonAcc, tt.nm toTaxonNm, tt.secondary_nm toTaxonCommonNm, tt.acc_txt toTaxonAcc, ggr.experimental_sys_nm, ggr.experimental_sys_type, ( SELECT STRING_AGG(ggt.throughput_txt, ', ' ORDER BY ggt.throughput_txt) FROM gene_gene_ref_throughput ggt WHERE ggt.gene_gene_reference_id = ggr.id) throughput, COUNT(*) OVER () fullRowCount FROM gene_gene_reference ggr INNER JOIN term fg ON ggr.from_gene_id = fg.id INNER JOIN term tg ON ggr.to_gene_id = tg.id INNER JOIN term ft ON ggr.from_taxon_id = ft.id INNER JOIN term tt ON ggr.to_taxon_id = tt.id WHERE ggr.reference_id = '111363' ORDER BY fg.nm_sort, tg.nm_sort LIMIT 50;
Date: 2025-04-01 05:48:06 Duration: 1s416ms Bind query: yes
10 1 2m33s 2m33s 2m33s 2m33s vacuum full analyze db_link;Times Reported Time consuming queries #10
Day Hour Count Duration Avg duration Apr 01 10 1 2m33s 2m33s -
vacuum FULL analyze db_link;
Date: 2025-04-01 10:57:23 Duration: 2m33s
-
vacuum FULL analyze db_link;
Date: 2025-04-01 10:55:05 Duration: 0ms
11 1 2m26s 2m26s 2m26s 2m26s insert into log_query_bots (id, type_cd, query_tm, submission_qty, session_id, remote_addr, http_user_agent, server_nm, node_nm, results_qty, execution_ms, basic_query_type, basic_query_txt, gene_query_type, gene_txt, gene_form_type_txt, taxon_query_type, taxon_txt, chem_query_type, chem_txt, party_query_type, party_nm_txt, acc_txt, go_query_type, go_txt, disease_query_type, disease_txt, action_type_txt, action_degree_type_txt, from_yr, through_yr, title_abstract_txt, has_marray, pathway_query_type, pathway_txt, dag_txt, results_format_txt, batch_input_type_txt, gd_assn_type, p_val, p_val_type, input_term_qty, review_status) select q.id, q.type_cd, q.query_tm, q.submission_qty, q.session_id, q.remote_addr, q.http_user_agent, q.server_nm, q.node_nm, q.results_qty, q.execution_ms, q.basic_query_type, q.basic_query_txt, q.gene_query_type, q.gene_txt, q.gene_form_type_txt, q.taxon_query_type, q.taxon_txt, q.chem_query_type, q.chem_txt, q.party_query_type, q.party_nm_txt, q.acc_txt, q.go_query_type, q.go_txt, q.disease_query_type, q.disease_txt, q.action_type_txt, q.action_degree_type_txt, q.from_yr, q.through_yr, q.title_abstract_txt, q.has_marray, q.pathway_query_type, q.pathway_txt, q.dag_txt, q.results_format_txt, q.batch_input_type_txt, q.gd_assn_type, q.p_val, q.p_val_type, q.input_term_qty, q.review_status from log_query_archive q where upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) and q.id not in ( select a.id from log_query_bots a);Times Reported Time consuming queries #11
Day Hour Count Duration Avg duration Apr 01 14 1 2m26s 2m26s -
--select count(*) from log_query_bots -2776425 --begin transaction --rollback INSERT INTO log_query_bots (id, type_cd, query_tm, submission_qty, session_id, remote_addr, http_user_agent, server_nm, node_nm, results_qty, execution_ms, basic_query_type, basic_query_txt, gene_query_type, gene_txt, gene_form_type_txt, taxon_query_type, taxon_txt, chem_query_type, chem_txt, party_query_type, party_nm_txt, acc_txt, go_query_type, go_txt, disease_query_type, disease_txt, action_type_txt, action_degree_type_txt, from_yr, through_yr, title_abstract_txt, has_marray, pathway_query_type, pathway_txt, dag_txt, results_format_txt, batch_input_type_txt, gd_assn_type, p_val, p_val_type, input_term_qty, review_status) SELECT q.id, q.type_cd, q.query_tm, q.submission_qty, q.session_id, q.remote_addr, q.http_user_agent, q.server_nm, q.node_nm, q.results_qty, q.execution_ms, q.basic_query_type, q.basic_query_txt, q.gene_query_type, q.gene_txt, q.gene_form_type_txt, q.taxon_query_type, q.taxon_txt, q.chem_query_type, q.chem_txt, q.party_query_type, q.party_nm_txt, q.acc_txt, q.go_query_type, q.go_txt, q.disease_query_type, q.disease_txt, q.action_type_txt, q.action_degree_type_txt, q.from_yr, q.through_yr, q.title_abstract_txt, q.has_marray, q.pathway_query_type, q.pathway_txt, q.dag_txt, q.results_format_txt, q.batch_input_type_txt, q.gd_assn_type, q.p_val, q.p_val_type, q.input_term_qty, q.review_status FROM log_query_archive q WHERE upper(q.http_user_agent) LIKE upper('%ARCHIVE%') OR upper(q.http_user_agent) LIKE upper('%SPIDER%') OR upper(q.http_user_agent) LIKE upper('%FACEBOOK%') OR upper(q.http_user_agent) LIKE upper('%NUTCH%') OR upper(q.http_user_agent) LIKE upper('%TRACK%') OR upper(q.http_user_agent) LIKE upper('%SITESUCKER%') OR upper(q.http_user_agent) LIKE upper('%KNOWLEDGE AI%') OR upper(q.http_user_agent) LIKE upper('%MEGAINDEX%') OR upper(q.http_user_agent) LIKE upper('%YANDEX%') OR upper(q.http_user_agent) LIKE upper('%PANSCIENT%') OR upper(q.http_user_agent) LIKE upper('%GOOGLEOTHER%') OR upper(q.http_user_agent) LIKE upper('%QWANTIFY%') OR upper(q.http_user_agent) LIKE upper('%SEOKICKS%') OR upper(q.http_user_agent) LIKE upper('%ARQUIVO%') OR upper(q.http_user_agent) LIKE upper('%SEMANTIC%') OR upper(q.http_user_agent) LIKE upper('%BOT%') AND q.id NOT IN ( SELECT a.id FROM log_query_bots a);
Date: 2025-04-01 14:45:41 Duration: 2m26s
12 1 2m25s 2m25s 2m25s 2m25s delete from log_query_archive q where upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?);Times Reported Time consuming queries #12
Day Hour Count Duration Avg duration Apr 01 14 1 2m25s 2m25s -
--begin transaction --rollback --commit DELETE from log_query_archive q WHERE upper(q.http_user_agent) LIKE upper('%ARCHIVE%') OR upper(q.http_user_agent) LIKE upper('%SPIDER%') OR upper(q.http_user_agent) LIKE upper('%FACEBOOK%') OR upper(q.http_user_agent) LIKE upper('%NUTCH%') OR upper(q.http_user_agent) LIKE upper('%TRACK%') OR upper(q.http_user_agent) LIKE upper('%SITESUCKER%') OR upper(q.http_user_agent) LIKE upper('%KNOWLEDGE AI%') OR upper(q.http_user_agent) LIKE upper('%MEGAINDEX%') OR upper(q.http_user_agent) LIKE upper('%YANDEX%') OR upper(q.http_user_agent) LIKE upper('%PANSCIENT%') OR upper(q.http_user_agent) LIKE upper('%GOOGLEOTHER%') OR upper(q.http_user_agent) LIKE upper('%QWANTIFY%') OR upper(q.http_user_agent) LIKE upper('%SEOKICKS%') OR upper(q.http_user_agent) LIKE upper('%ARQUIVO%') OR upper(q.http_user_agent) LIKE upper('%SEMANTIC%') OR upper(q.http_user_agent) LIKE upper('%BOT%');
Date: 2025-04-01 14:50:12 Duration: 2m25s
13 1 1m33s 1m33s 1m33s 1m33s ( select upper(http_user_agent) from log_query_bots l, excluded_user_agent2 ua where upper(l.http_user_agent) like upper(ua.user_agent_pattern));Times Reported Time consuming queries #13
Day Hour Count Duration Avg duration Apr 01 13 1 1m33s 1m33s -
( SELECT upper(http_user_agent) FROM log_query_bots l, excluded_user_agent2 ua WHERE UPPER(l.http_user_agent) LIKE UPPER(ua.user_agent_pattern));
Date: 2025-04-01 13:20:38 Duration: 1m33s
14 1 58s103ms 58s103ms 58s103ms 58s103ms select distinct (http_user_agent) from log_query_bots where upper(http_user_agent) not in ( select distinct upper(http_user_agent) from log_query_bots l, excluded_user_agent2 ua where upper(l.http_user_agent) like upper(ua.user_agent_pattern));Times Reported Time consuming queries #14
Day Hour Count Duration Avg duration Apr 01 13 1 58s103ms 58s103ms -
select distinct (http_user_agent) from log_query_bots where upper(http_user_agent) not in ( SELECT distinct upper(http_user_agent) FROM log_query_bots l, excluded_user_agent2 ua WHERE UPPER(l.http_user_agent) LIKE UPPER(ua.user_agent_pattern));
Date: 2025-04-01 13:22:46 Duration: 58s103ms
15 1 13s561ms 13s561ms 13s561ms 13s561ms select distinct (http_user_agent) from log_query_bots where upper(http_user_agent) not in ( select http_user_agent from log_query_bots l, excluded_user_agent2 ua where upper(l.http_user_agent) like upper(ua.user_agent_pattern));Times Reported Time consuming queries #15
Day Hour Count Duration Avg duration Apr 01 13 1 13s561ms 13s561ms -
select distinct (http_user_agent) from log_query_bots where upper(http_user_agent) not in ( SELECT http_user_agent FROM log_query_bots l, excluded_user_agent2 ua WHERE UPPER(l.http_user_agent) LIKE UPPER(ua.user_agent_pattern));
Date: 2025-04-01 13:10:33 Duration: 13s561ms
16 1 13s276ms 13s276ms 13s276ms 13s276ms select distinct (http_user_agent) from log_query_bots where upper(http_user_agent) not in ( select upper(http_user_agent) from log_query_bots l, excluded_user_agent2 ua where upper(l.http_user_agent) like upper(ua.user_agent_pattern));Times Reported Time consuming queries #16
Day Hour Count Duration Avg duration Apr 01 13 1 13s276ms 13s276ms -
select distinct (http_user_agent) from log_query_bots where upper(http_user_agent) not in ( SELECT upper(http_user_agent) FROM log_query_bots l, excluded_user_agent2 ua WHERE UPPER(l.http_user_agent) LIKE UPPER(ua.user_agent_pattern));
Date: 2025-04-01 13:12:41 Duration: 13s276ms
17 1 11s301ms 11s301ms 11s301ms 11s301ms select distinct (http_user_agent) from log_query_bots where http_user_agent not in ( select http_user_agent from log_query_bots l, excluded_user_agent2 ua where upper(l.http_user_agent) like upper(ua.user_agent_pattern));Times Reported Time consuming queries #17
Day Hour Count Duration Avg duration Apr 01 13 1 11s301ms 11s301ms -
select distinct (http_user_agent) from log_query_bots where http_user_agent not in ( SELECT http_user_agent FROM log_query_bots l, excluded_user_agent2 ua WHERE UPPER(l.http_user_agent) LIKE UPPER(ua.user_agent_pattern));
Date: 2025-04-01 13:11:38 Duration: 11s301ms
18 1 4s693ms 4s693ms 4s693ms 4s693ms select ? "Input", sqi.chem_nm "ChemicalName", sqi.chem_acc_txt "ChemicalID", sqi.casrn "CasRN", sqi.gene_symbol "GeneSymbol", sqi.gene_acc_txt "GeneID", sqi.ontology_nm "Ontology", sqi.go_term_nm "GoTermName", sqi.go_acc_txt "GoTermID" from ( with sq as ( select distinct c.id chem_id, c.nm chem_nm, c.acc_txt chem_acc_txt, c.secondary_nm casrn, c.nm_sort chem_nm_sort, gcr.gene_id, g.nm gene_symbol, g.acc_txt gene_acc_txt, g.nm_sort gene_symbol_sort from term c inner join gene_chem_reference gcr on c.id = gcr.chem_id inner join term g on gcr.gene_id = g.id where (c.id = ?)) select distinct sq.chem_nm, sq.chem_acc_txt, sq.casrn, sq.gene_symbol, sq.gene_acc_txt, gt.nm go_term_nm, gt.acc_txt go_acc_txt, sq.chem_nm_sort, sq.gene_symbol_sort, gt.nm_sort, d.nm ontology_nm from sq inner join gene_go_annot gga on sq.gene_id = gga.gene_id inner join dag_node gt on gga.go_term_id = gt.object_id inner join dag d on gt.dag_id = d.id where gga.is_not = false and (d.id = ? or d.id = ?) order by sq.chem_nm_sort, sq.gene_symbol_sort, d.nm, gt.nm_sort) sqi;Times Reported Time consuming queries #18
Day Hour Count Duration Avg duration Apr 01 05 1 4s693ms 4s693ms [ User: pubeu - Total duration: 4s693ms - Times executed: 1 ]
-
SELECT /* BatchChemGODAO */ 'ddt' "Input", sqi.chem_nm "ChemicalName", sqi.chem_acc_txt "ChemicalID", sqi.casRN "CasRN", sqi.gene_symbol "GeneSymbol", sqi.gene_acc_txt "GeneID", sqi.ontology_nm "Ontology", sqi.go_term_nm "GoTermName", sqi.go_acc_txt "GoTermID" FROM ( WITH sq AS ( SELECT DISTINCT c.id chem_id, c.nm chem_nm, c.acc_txt chem_acc_txt, c.secondary_nm casRN, c.nm_sort chem_nm_sort, gcr.gene_id, g.nm gene_symbol, g.acc_txt gene_acc_txt, g.nm_sort gene_symbol_sort FROM term c INNER JOIN gene_chem_reference gcr ON c.id = gcr.chem_id INNER JOIN term g ON gcr.gene_id = g.id WHERE (c.id = 1308127)) SELECT DISTINCT sq.chem_nm, sq.chem_acc_txt, sq.casRN, sq.gene_symbol, sq.gene_acc_txt, gt.nm go_term_nm, gt.acc_txt go_acc_txt, sq.chem_nm_sort, sq.gene_symbol_sort, gt.nm_sort, d.nm ontology_nm FROM sq INNER JOIN gene_go_annot gga ON sq.gene_id = gga.gene_id INNER JOIN dag_node gt ON gga.go_term_id = gt.object_id INNER JOIN dag d ON gt.dag_id = d.id WHERE gga.is_not = false AND (d.id = 5 OR d.id = 4) ORDER BY sq.chem_nm_sort, sq.gene_symbol_sort, d.nm, gt.nm_sort) sqi;
Date: 2025-04-01 05:48:27 Duration: 4s693ms Database: ctddev51 User: pubeu Bind query: yes
19 1 4s267ms 4s267ms 4s267ms 4s267ms select gcr.ixn_id, null, null, null from gene_chem_reference gcr where gcr.gene_id = any (array (( select gd.gene_id from term t inner join dag_path dp on t.id = dp.ancestor_object_id inner join gene_disease gd on dp.descendant_object_id = gd.disease_id where upper(t.nm) like ? and t.object_type_id = ?))) and gcr.id in ( select gcra.gene_chem_reference_id from gene_chem_reference_axn gcra where (gcra.action_degree_type_nm = ?));Times Reported Time consuming queries #19
Day Hour Count Duration Avg duration Apr 01 05 1 4s267ms 4s267ms [ User: pubeu - Total duration: 4s267ms - Times executed: 1 ]
-
SELECT /* CIQH.getIxnCacheQuery */ gcr.ixn_id, NULL, NULL, NULL FROM gene_chem_reference gcr WHERE /* CIQH.getIxnWhereCore */ gcr.gene_id = ANY (ARRAY (( SELECT /* IQH.getMasterDiseaseWhereEquals.Name.Gene */ gd.gene_id FROM term t INNER JOIN dag_path dp ON t.id = dp.ancestor_object_id INNER JOIN gene_disease gd ON dp.descendant_object_id = gd.disease_id WHERE UPPER(t.nm) LIKE 'ASTHMA' AND t.object_type_id = 3))) AND gcr.id IN ( SELECT gcra.gene_chem_reference_id FROM gene_chem_reference_axn gcra WHERE (gcra.action_degree_type_nm = 'increases'));
Date: 2025-04-01 05:47:19 Duration: 4s267ms Database: ctddev51 User: pubeu Bind query: yes
20 1 4s207ms 4s207ms 4s207ms 4s207ms 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 ptr.term_id in ( select distinct dp.descendant_object_id from dag_path dp where dp.ancestor_object_id in ( select distinct id from term baseterm where object_type_id = ? and upper(baseterm.nm) like ?)) and ptr.term_object_type_id = ? and ptr.phenotype_id in ( select distinct dp.descendant_object_id from dag_path dp where dp.ancestor_object_id in ( select distinct id from term baseterm where object_type_id = ? and baseterm.id in ( select object_id from db_link l where l.acc_txt = ? and l.type_cd = ? and l.object_type_id = ?))) and i.id in ( select ixn_id from ixn_anatomy where anatomy_id in ( select distinct dp.descendant_object_id from dag_path dp where dp.ancestor_object_id in ( select distinct id from term baseterm where object_type_id = ? and upper(baseterm.nm) like ?))) and taxonterm.id in ( select distinct dp.descendant_object_id from dag_path dp where dp.ancestor_object_id in ( select distinct id from term baseterm where object_type_id = ? and baseterm.id in ( select object_id from db_link l where l.acc_txt = ? and l.type_cd = ? and l.object_type_id = ?))) and i.id in ( select ixn_id from ixn_axn where action_type_nm = ? and action_degree_type_nm in (...)) group by associatedterm, associatedtermnmsort, phenotype, casrn, ixnid, ixnprosehtml, ixnprose, ixnsort, associatedtermid, phenotypeid, inferredcount order by associatedtermnmsort limit ?;Times Reported Time consuming queries #20
Day Hour Count Duration Avg duration Apr 01 05 1 4s207ms 4s207ms -
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 ptr.term_id IN ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 2 and upper(baseTerm.nm) LIKE 'ACETYLCYSTEINE')) and ptr.term_object_type_id = 2 and ptr.phenotype_id IN ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 5 and baseTerm.id in ( select object_id from db_link l where l.acc_txt = 'GO:0006979' AND l.type_cd = 'A' AND l.object_type_id = 5))) and i.id in ( select ixn_id from ixn_anatomy where anatomy_id IN ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 10 and upper(baseTerm.nm) LIKE 'CARDIOVASCULAR SYSTEM'))) and taxonTerm.id in ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 1 and baseTerm.id in ( select object_id from db_link l where l.acc_txt = '9605' AND l.type_cd = 'A' AND l.object_type_id = 1))) and i.id in ( select ixn_id from ixn_axn where action_type_nm = 'phenotype' and action_degree_type_nm in ('increases')) group by associatedTerm, associatedTermNmSort, phenotype, casRN, ixnId, ixnProseHtml, ixnProse, ixnSort, associatedTermId, phenotypeId, inferredCount ORDER BY associatedTermNmSort LIMIT 50;
Date: 2025-04-01 05:48:52 Duration: 4s207ms Bind query: yes
Normalized slowest queries (N)
Rank Min duration Max duration Avg duration Times executed Total duration Query 1 2m33s 2m33s 2m33s 1 2m33s vacuum full analyze db_link;Times Reported Time consuming queries #1
Day Hour Count Duration Avg duration Apr 01 10 1 2m33s 2m33s -
vacuum FULL analyze db_link;
Date: 2025-04-01 10:57:23 Duration: 2m33s
-
vacuum FULL analyze db_link;
Date: 2025-04-01 10:55:05 Duration: 0ms
2 2m26s 2m26s 2m26s 1 2m26s insert into log_query_bots (id, type_cd, query_tm, submission_qty, session_id, remote_addr, http_user_agent, server_nm, node_nm, results_qty, execution_ms, basic_query_type, basic_query_txt, gene_query_type, gene_txt, gene_form_type_txt, taxon_query_type, taxon_txt, chem_query_type, chem_txt, party_query_type, party_nm_txt, acc_txt, go_query_type, go_txt, disease_query_type, disease_txt, action_type_txt, action_degree_type_txt, from_yr, through_yr, title_abstract_txt, has_marray, pathway_query_type, pathway_txt, dag_txt, results_format_txt, batch_input_type_txt, gd_assn_type, p_val, p_val_type, input_term_qty, review_status) select q.id, q.type_cd, q.query_tm, q.submission_qty, q.session_id, q.remote_addr, q.http_user_agent, q.server_nm, q.node_nm, q.results_qty, q.execution_ms, q.basic_query_type, q.basic_query_txt, q.gene_query_type, q.gene_txt, q.gene_form_type_txt, q.taxon_query_type, q.taxon_txt, q.chem_query_type, q.chem_txt, q.party_query_type, q.party_nm_txt, q.acc_txt, q.go_query_type, q.go_txt, q.disease_query_type, q.disease_txt, q.action_type_txt, q.action_degree_type_txt, q.from_yr, q.through_yr, q.title_abstract_txt, q.has_marray, q.pathway_query_type, q.pathway_txt, q.dag_txt, q.results_format_txt, q.batch_input_type_txt, q.gd_assn_type, q.p_val, q.p_val_type, q.input_term_qty, q.review_status from log_query_archive q where upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) and q.id not in ( select a.id from log_query_bots a);Times Reported Time consuming queries #2
Day Hour Count Duration Avg duration Apr 01 14 1 2m26s 2m26s -
--select count(*) from log_query_bots -2776425 --begin transaction --rollback INSERT INTO log_query_bots (id, type_cd, query_tm, submission_qty, session_id, remote_addr, http_user_agent, server_nm, node_nm, results_qty, execution_ms, basic_query_type, basic_query_txt, gene_query_type, gene_txt, gene_form_type_txt, taxon_query_type, taxon_txt, chem_query_type, chem_txt, party_query_type, party_nm_txt, acc_txt, go_query_type, go_txt, disease_query_type, disease_txt, action_type_txt, action_degree_type_txt, from_yr, through_yr, title_abstract_txt, has_marray, pathway_query_type, pathway_txt, dag_txt, results_format_txt, batch_input_type_txt, gd_assn_type, p_val, p_val_type, input_term_qty, review_status) SELECT q.id, q.type_cd, q.query_tm, q.submission_qty, q.session_id, q.remote_addr, q.http_user_agent, q.server_nm, q.node_nm, q.results_qty, q.execution_ms, q.basic_query_type, q.basic_query_txt, q.gene_query_type, q.gene_txt, q.gene_form_type_txt, q.taxon_query_type, q.taxon_txt, q.chem_query_type, q.chem_txt, q.party_query_type, q.party_nm_txt, q.acc_txt, q.go_query_type, q.go_txt, q.disease_query_type, q.disease_txt, q.action_type_txt, q.action_degree_type_txt, q.from_yr, q.through_yr, q.title_abstract_txt, q.has_marray, q.pathway_query_type, q.pathway_txt, q.dag_txt, q.results_format_txt, q.batch_input_type_txt, q.gd_assn_type, q.p_val, q.p_val_type, q.input_term_qty, q.review_status FROM log_query_archive q WHERE upper(q.http_user_agent) LIKE upper('%ARCHIVE%') OR upper(q.http_user_agent) LIKE upper('%SPIDER%') OR upper(q.http_user_agent) LIKE upper('%FACEBOOK%') OR upper(q.http_user_agent) LIKE upper('%NUTCH%') OR upper(q.http_user_agent) LIKE upper('%TRACK%') OR upper(q.http_user_agent) LIKE upper('%SITESUCKER%') OR upper(q.http_user_agent) LIKE upper('%KNOWLEDGE AI%') OR upper(q.http_user_agent) LIKE upper('%MEGAINDEX%') OR upper(q.http_user_agent) LIKE upper('%YANDEX%') OR upper(q.http_user_agent) LIKE upper('%PANSCIENT%') OR upper(q.http_user_agent) LIKE upper('%GOOGLEOTHER%') OR upper(q.http_user_agent) LIKE upper('%QWANTIFY%') OR upper(q.http_user_agent) LIKE upper('%SEOKICKS%') OR upper(q.http_user_agent) LIKE upper('%ARQUIVO%') OR upper(q.http_user_agent) LIKE upper('%SEMANTIC%') OR upper(q.http_user_agent) LIKE upper('%BOT%') AND q.id NOT IN ( SELECT a.id FROM log_query_bots a);
Date: 2025-04-01 14:45:41 Duration: 2m26s
3 2m25s 2m25s 2m25s 1 2m25s delete from log_query_archive q where upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?);Times Reported Time consuming queries #3
Day Hour Count Duration Avg duration Apr 01 14 1 2m25s 2m25s -
--begin transaction --rollback --commit DELETE from log_query_archive q WHERE upper(q.http_user_agent) LIKE upper('%ARCHIVE%') OR upper(q.http_user_agent) LIKE upper('%SPIDER%') OR upper(q.http_user_agent) LIKE upper('%FACEBOOK%') OR upper(q.http_user_agent) LIKE upper('%NUTCH%') OR upper(q.http_user_agent) LIKE upper('%TRACK%') OR upper(q.http_user_agent) LIKE upper('%SITESUCKER%') OR upper(q.http_user_agent) LIKE upper('%KNOWLEDGE AI%') OR upper(q.http_user_agent) LIKE upper('%MEGAINDEX%') OR upper(q.http_user_agent) LIKE upper('%YANDEX%') OR upper(q.http_user_agent) LIKE upper('%PANSCIENT%') OR upper(q.http_user_agent) LIKE upper('%GOOGLEOTHER%') OR upper(q.http_user_agent) LIKE upper('%QWANTIFY%') OR upper(q.http_user_agent) LIKE upper('%SEOKICKS%') OR upper(q.http_user_agent) LIKE upper('%ARQUIVO%') OR upper(q.http_user_agent) LIKE upper('%SEMANTIC%') OR upper(q.http_user_agent) LIKE upper('%BOT%');
Date: 2025-04-01 14:50:12 Duration: 2m25s
4 2m16s 2m16s 2m16s 2 4m32s insert into log_query_bots (id, type_cd, query_tm, submission_qty, session_id, remote_addr, http_user_agent, server_nm, node_nm, results_qty, execution_ms, basic_query_type, basic_query_txt, gene_query_type, gene_txt, gene_form_type_txt, taxon_query_type, taxon_txt, chem_query_type, chem_txt, party_query_type, party_nm_txt, acc_txt, go_query_type, go_txt, disease_query_type, disease_txt, action_type_txt, action_degree_type_txt, from_yr, through_yr, title_abstract_txt, has_marray, pathway_query_type, pathway_txt, dag_txt, results_format_txt, batch_input_type_txt, gd_assn_type, p_val, p_val_type, input_term_qty, review_status) select q.id, q.type_cd, q.query_tm, q.submission_qty, q.session_id, q.remote_addr, q.http_user_agent, q.server_nm, q.node_nm, q.results_qty, q.execution_ms, q.basic_query_type, q.basic_query_txt, q.gene_query_type, q.gene_txt, q.gene_form_type_txt, q.taxon_query_type, q.taxon_txt, q.chem_query_type, q.chem_txt, q.party_query_type, q.party_nm_txt, q.acc_txt, q.go_query_type, q.go_txt, q.disease_query_type, q.disease_txt, q.action_type_txt, q.action_degree_type_txt, q.from_yr, q.through_yr, q.title_abstract_txt, q.has_marray, q.pathway_query_type, q.pathway_txt, q.dag_txt, q.results_format_txt, q.batch_input_type_txt, q.gd_assn_type, q.p_val, q.p_val_type, q.input_term_qty, q.review_status from log_query_archive q where upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) or upper(q.http_user_agent) like upper(?) and q.id not in ( select a.id from log_query_bots a);Times Reported Time consuming queries #4
Day Hour Count Duration Avg duration Apr 01 14 2 4m32s 2m16s -
--begin transaction --rollback INSERT INTO log_query_bots (id, type_cd, query_tm, submission_qty, session_id, remote_addr, http_user_agent, server_nm, node_nm, results_qty, execution_ms, basic_query_type, basic_query_txt, gene_query_type, gene_txt, gene_form_type_txt, taxon_query_type, taxon_txt, chem_query_type, chem_txt, party_query_type, party_nm_txt, acc_txt, go_query_type, go_txt, disease_query_type, disease_txt, action_type_txt, action_degree_type_txt, from_yr, through_yr, title_abstract_txt, has_marray, pathway_query_type, pathway_txt, dag_txt, results_format_txt, batch_input_type_txt, gd_assn_type, p_val, p_val_type, input_term_qty, review_status) SELECT q.id, q.type_cd, q.query_tm, q.submission_qty, q.session_id, q.remote_addr, q.http_user_agent, q.server_nm, q.node_nm, q.results_qty, q.execution_ms, q.basic_query_type, q.basic_query_txt, q.gene_query_type, q.gene_txt, q.gene_form_type_txt, q.taxon_query_type, q.taxon_txt, q.chem_query_type, q.chem_txt, q.party_query_type, q.party_nm_txt, q.acc_txt, q.go_query_type, q.go_txt, q.disease_query_type, q.disease_txt, q.action_type_txt, q.action_degree_type_txt, q.from_yr, q.through_yr, q.title_abstract_txt, q.has_marray, q.pathway_query_type, q.pathway_txt, q.dag_txt, q.results_format_txt, q.batch_input_type_txt, q.gd_assn_type, q.p_val, q.p_val_type, q.input_term_qty, q.review_status FROM log_query_archive q WHERE upper(q.http_user_agent) LIKE upper('%ARCHIVE%') OR upper(q.http_user_agent) LIKE upper('%SPIDER%') OR upper(q.http_user_agent) LIKE upper('%FACEBOOK%') OR upper(q.http_user_agent) LIKE upper('%NUTCH%') OR upper(q.http_user_agent) LIKE upper('%TRACK%') OR upper(q.http_user_agent) LIKE upper('%SITESUCKER%') OR upper(q.http_user_agent) LIKE upper('%KNOWLEDGE AI%') OR upper(q.http_user_agent) LIKE upper('%MEGAINDEX%') OR upper(q.http_user_agent) LIKE upper('%YANDEX%') OR upper(q.http_user_agent) LIKE upper('%PANSCIENT%') OR upper(q.http_user_agent) LIKE upper('%GOOGLEOTHER%') OR upper(q.http_user_agent) LIKE upper('%QWANTIFY%') OR upper(q.http_user_agent) LIKE upper('%SEOKICKS%') OR upper(q.http_user_agent) LIKE upper('%ARQUIVO%') OR upper(q.http_user_agent) LIKE upper('%SEMANTIC%') AND q.id NOT IN ( SELECT a.id FROM log_query_bots a);
Date: 2025-04-01 14:29:24 Duration: 2m16s
-
--select count(*) from log_query_bots -2776425 --begin transaction --rollback INSERT INTO log_query_bots (id, type_cd, query_tm, submission_qty, session_id, remote_addr, http_user_agent, server_nm, node_nm, results_qty, execution_ms, basic_query_type, basic_query_txt, gene_query_type, gene_txt, gene_form_type_txt, taxon_query_type, taxon_txt, chem_query_type, chem_txt, party_query_type, party_nm_txt, acc_txt, go_query_type, go_txt, disease_query_type, disease_txt, action_type_txt, action_degree_type_txt, from_yr, through_yr, title_abstract_txt, has_marray, pathway_query_type, pathway_txt, dag_txt, results_format_txt, batch_input_type_txt, gd_assn_type, p_val, p_val_type, input_term_qty, review_status) SELECT q.id, q.type_cd, q.query_tm, q.submission_qty, q.session_id, q.remote_addr, q.http_user_agent, q.server_nm, q.node_nm, q.results_qty, q.execution_ms, q.basic_query_type, q.basic_query_txt, q.gene_query_type, q.gene_txt, q.gene_form_type_txt, q.taxon_query_type, q.taxon_txt, q.chem_query_type, q.chem_txt, q.party_query_type, q.party_nm_txt, q.acc_txt, q.go_query_type, q.go_txt, q.disease_query_type, q.disease_txt, q.action_type_txt, q.action_degree_type_txt, q.from_yr, q.through_yr, q.title_abstract_txt, q.has_marray, q.pathway_query_type, q.pathway_txt, q.dag_txt, q.results_format_txt, q.batch_input_type_txt, q.gd_assn_type, q.p_val, q.p_val_type, q.input_term_qty, q.review_status FROM log_query_archive q WHERE upper(q.http_user_agent) LIKE upper('%ARCHIVE%') OR upper(q.http_user_agent) LIKE upper('%SPIDER%') OR upper(q.http_user_agent) LIKE upper('%FACEBOOK%') OR upper(q.http_user_agent) LIKE upper('%NUTCH%') OR upper(q.http_user_agent) LIKE upper('%TRACK%') OR upper(q.http_user_agent) LIKE upper('%SITESUCKER%') OR upper(q.http_user_agent) LIKE upper('%KNOWLEDGE AI%') OR upper(q.http_user_agent) LIKE upper('%MEGAINDEX%') OR upper(q.http_user_agent) LIKE upper('%YANDEX%') OR upper(q.http_user_agent) LIKE upper('%PANSCIENT%') OR upper(q.http_user_agent) LIKE upper('%GOOGLEOTHER%') OR upper(q.http_user_agent) LIKE upper('%QWANTIFY%') OR upper(q.http_user_agent) LIKE upper('%SEOKICKS%') OR upper(q.http_user_agent) LIKE upper('%ARQUIVO%') OR upper(q.http_user_agent) LIKE upper('%SEMANTIC%') AND q.id NOT IN ( SELECT a.id FROM log_query_bots a);
Date: 2025-04-01 14:35:00 Duration: 2m16s
5 1m33s 1m33s 1m33s 1 1m33s ( select upper(http_user_agent) from log_query_bots l, excluded_user_agent2 ua where upper(l.http_user_agent) like upper(ua.user_agent_pattern));Times Reported Time consuming queries #5
Day Hour Count Duration Avg duration Apr 01 13 1 1m33s 1m33s -
( SELECT upper(http_user_agent) FROM log_query_bots l, excluded_user_agent2 ua WHERE UPPER(l.http_user_agent) LIKE UPPER(ua.user_agent_pattern));
Date: 2025-04-01 13:20:38 Duration: 1m33s
6 58s103ms 58s103ms 58s103ms 1 58s103ms select distinct (http_user_agent) from log_query_bots where upper(http_user_agent) not in ( select distinct upper(http_user_agent) from log_query_bots l, excluded_user_agent2 ua where upper(l.http_user_agent) like upper(ua.user_agent_pattern));Times Reported Time consuming queries #6
Day Hour Count Duration Avg duration Apr 01 13 1 58s103ms 58s103ms -
select distinct (http_user_agent) from log_query_bots where upper(http_user_agent) not in ( SELECT distinct upper(http_user_agent) FROM log_query_bots l, excluded_user_agent2 ua WHERE UPPER(l.http_user_agent) LIKE UPPER(ua.user_agent_pattern));
Date: 2025-04-01 13:22:46 Duration: 58s103ms
7 13s561ms 13s561ms 13s561ms 1 13s561ms select distinct (http_user_agent) from log_query_bots where upper(http_user_agent) not in ( select http_user_agent from log_query_bots l, excluded_user_agent2 ua where upper(l.http_user_agent) like upper(ua.user_agent_pattern));Times Reported Time consuming queries #7
Day Hour Count Duration Avg duration Apr 01 13 1 13s561ms 13s561ms -
select distinct (http_user_agent) from log_query_bots where upper(http_user_agent) not in ( SELECT http_user_agent FROM log_query_bots l, excluded_user_agent2 ua WHERE UPPER(l.http_user_agent) LIKE UPPER(ua.user_agent_pattern));
Date: 2025-04-01 13:10:33 Duration: 13s561ms
8 13s276ms 13s276ms 13s276ms 1 13s276ms select distinct (http_user_agent) from log_query_bots where upper(http_user_agent) not in ( select upper(http_user_agent) from log_query_bots l, excluded_user_agent2 ua where upper(l.http_user_agent) like upper(ua.user_agent_pattern));Times Reported Time consuming queries #8
Day Hour Count Duration Avg duration Apr 01 13 1 13s276ms 13s276ms -
select distinct (http_user_agent) from log_query_bots where upper(http_user_agent) not in ( SELECT upper(http_user_agent) FROM log_query_bots l, excluded_user_agent2 ua WHERE UPPER(l.http_user_agent) LIKE UPPER(ua.user_agent_pattern));
Date: 2025-04-01 13:12:41 Duration: 13s276ms
9 11s301ms 11s301ms 11s301ms 1 11s301ms select distinct (http_user_agent) from log_query_bots where http_user_agent not in ( select http_user_agent from log_query_bots l, excluded_user_agent2 ua where upper(l.http_user_agent) like upper(ua.user_agent_pattern));Times Reported Time consuming queries #9
Day Hour Count Duration Avg duration Apr 01 13 1 11s301ms 11s301ms -
select distinct (http_user_agent) from log_query_bots where http_user_agent not in ( SELECT http_user_agent FROM log_query_bots l, excluded_user_agent2 ua WHERE UPPER(l.http_user_agent) LIKE UPPER(ua.user_agent_pattern));
Date: 2025-04-01 13:11:38 Duration: 11s301ms
10 9s945ms 10s422ms 10s184ms 2 20s368ms ( select distinct upper(http_user_agent) from log_query_bots l, excluded_user_agent2 ua where upper(l.http_user_agent) like upper(ua.user_agent_pattern));Times Reported Time consuming queries #10
Day Hour Count Duration Avg duration Apr 01 13 2 20s368ms 10s184ms -
( SELECT distinct upper(http_user_agent) FROM log_query_bots l, excluded_user_agent2 ua WHERE UPPER(l.http_user_agent) LIKE UPPER(ua.user_agent_pattern));
Date: 2025-04-01 13:14:09 Duration: 10s422ms
-
( SELECT distinct upper(http_user_agent) FROM log_query_bots l, excluded_user_agent2 ua WHERE UPPER(l.http_user_agent) LIKE UPPER(ua.user_agent_pattern));
Date: 2025-04-01 13:21:30 Duration: 9s945ms
11 5s442ms 6s262ms 5s852ms 2 11s704ms select distinct (http_user_agent) from log_query_archive where upper(http_user_agent) like ? or upper(http_user_agent) like ? or upper(http_user_agent) like ? or upper(http_user_agent) like ?;Times Reported Time consuming queries #11
Day Hour Count Duration Avg duration Apr 01 13 2 11s704ms 5s852ms -
select distinct (http_user_agent) from log_query_archive --select distinct(http_user_agent) from log_query_bots where upper(http_user_agent) LIKE '%SEMANTIC%' OR upper(http_user_agent) LIKE '%QWANTIFY%' OR upper(http_user_agent) LIKE '%SEOKICKS%' OR upper(http_user_agent) LIKE '%ARQUIVO%';
Date: 2025-04-01 13:59:19 Duration: 6s262ms
-
select distinct (http_user_agent) from log_query_archive where upper(http_user_agent) LIKE '%SEMANTIC%' OR upper(http_user_agent) LIKE '%QWANTIFY%' OR upper(http_user_agent) LIKE '%SEOKICKS%' OR upper(http_user_agent) LIKE '%ARQUIVO%';
Date: 2025-04-01 13:32:33 Duration: 5s442ms
12 4s693ms 4s693ms 4s693ms 1 4s693ms select ? "Input", sqi.chem_nm "ChemicalName", sqi.chem_acc_txt "ChemicalID", sqi.casrn "CasRN", sqi.gene_symbol "GeneSymbol", sqi.gene_acc_txt "GeneID", sqi.ontology_nm "Ontology", sqi.go_term_nm "GoTermName", sqi.go_acc_txt "GoTermID" from ( with sq as ( select distinct c.id chem_id, c.nm chem_nm, c.acc_txt chem_acc_txt, c.secondary_nm casrn, c.nm_sort chem_nm_sort, gcr.gene_id, g.nm gene_symbol, g.acc_txt gene_acc_txt, g.nm_sort gene_symbol_sort from term c inner join gene_chem_reference gcr on c.id = gcr.chem_id inner join term g on gcr.gene_id = g.id where (c.id = ?)) select distinct sq.chem_nm, sq.chem_acc_txt, sq.casrn, sq.gene_symbol, sq.gene_acc_txt, gt.nm go_term_nm, gt.acc_txt go_acc_txt, sq.chem_nm_sort, sq.gene_symbol_sort, gt.nm_sort, d.nm ontology_nm from sq inner join gene_go_annot gga on sq.gene_id = gga.gene_id inner join dag_node gt on gga.go_term_id = gt.object_id inner join dag d on gt.dag_id = d.id where gga.is_not = false and (d.id = ? or d.id = ?) order by sq.chem_nm_sort, sq.gene_symbol_sort, d.nm, gt.nm_sort) sqi;Times Reported Time consuming queries #12
Day Hour Count Duration Avg duration Apr 01 05 1 4s693ms 4s693ms [ User: pubeu - Total duration: 4s693ms - Times executed: 1 ]
-
SELECT /* BatchChemGODAO */ 'ddt' "Input", sqi.chem_nm "ChemicalName", sqi.chem_acc_txt "ChemicalID", sqi.casRN "CasRN", sqi.gene_symbol "GeneSymbol", sqi.gene_acc_txt "GeneID", sqi.ontology_nm "Ontology", sqi.go_term_nm "GoTermName", sqi.go_acc_txt "GoTermID" FROM ( WITH sq AS ( SELECT DISTINCT c.id chem_id, c.nm chem_nm, c.acc_txt chem_acc_txt, c.secondary_nm casRN, c.nm_sort chem_nm_sort, gcr.gene_id, g.nm gene_symbol, g.acc_txt gene_acc_txt, g.nm_sort gene_symbol_sort FROM term c INNER JOIN gene_chem_reference gcr ON c.id = gcr.chem_id INNER JOIN term g ON gcr.gene_id = g.id WHERE (c.id = 1308127)) SELECT DISTINCT sq.chem_nm, sq.chem_acc_txt, sq.casRN, sq.gene_symbol, sq.gene_acc_txt, gt.nm go_term_nm, gt.acc_txt go_acc_txt, sq.chem_nm_sort, sq.gene_symbol_sort, gt.nm_sort, d.nm ontology_nm FROM sq INNER JOIN gene_go_annot gga ON sq.gene_id = gga.gene_id INNER JOIN dag_node gt ON gga.go_term_id = gt.object_id INNER JOIN dag d ON gt.dag_id = d.id WHERE gga.is_not = false AND (d.id = 5 OR d.id = 4) ORDER BY sq.chem_nm_sort, sq.gene_symbol_sort, d.nm, gt.nm_sort) sqi;
Date: 2025-04-01 05:48:27 Duration: 4s693ms Database: ctddev51 User: pubeu Bind query: yes
13 4s267ms 4s267ms 4s267ms 1 4s267ms select gcr.ixn_id, null, null, null from gene_chem_reference gcr where gcr.gene_id = any (array (( select gd.gene_id from term t inner join dag_path dp on t.id = dp.ancestor_object_id inner join gene_disease gd on dp.descendant_object_id = gd.disease_id where upper(t.nm) like ? and t.object_type_id = ?))) and gcr.id in ( select gcra.gene_chem_reference_id from gene_chem_reference_axn gcra where (gcra.action_degree_type_nm = ?));Times Reported Time consuming queries #13
Day Hour Count Duration Avg duration Apr 01 05 1 4s267ms 4s267ms [ User: pubeu - Total duration: 4s267ms - Times executed: 1 ]
-
SELECT /* CIQH.getIxnCacheQuery */ gcr.ixn_id, NULL, NULL, NULL FROM gene_chem_reference gcr WHERE /* CIQH.getIxnWhereCore */ gcr.gene_id = ANY (ARRAY (( SELECT /* IQH.getMasterDiseaseWhereEquals.Name.Gene */ gd.gene_id FROM term t INNER JOIN dag_path dp ON t.id = dp.ancestor_object_id INNER JOIN gene_disease gd ON dp.descendant_object_id = gd.disease_id WHERE UPPER(t.nm) LIKE 'ASTHMA' AND t.object_type_id = 3))) AND gcr.id IN ( SELECT gcra.gene_chem_reference_id FROM gene_chem_reference_axn gcra WHERE (gcra.action_degree_type_nm = 'increases'));
Date: 2025-04-01 05:47:19 Duration: 4s267ms Database: ctddev51 User: pubeu Bind query: yes
14 4s207ms 4s207ms 4s207ms 1 4s207ms 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 ptr.term_id in ( select distinct dp.descendant_object_id from dag_path dp where dp.ancestor_object_id in ( select distinct id from term baseterm where object_type_id = ? and upper(baseterm.nm) like ?)) and ptr.term_object_type_id = ? and ptr.phenotype_id in ( select distinct dp.descendant_object_id from dag_path dp where dp.ancestor_object_id in ( select distinct id from term baseterm where object_type_id = ? and baseterm.id in ( select object_id from db_link l where l.acc_txt = ? and l.type_cd = ? and l.object_type_id = ?))) and i.id in ( select ixn_id from ixn_anatomy where anatomy_id in ( select distinct dp.descendant_object_id from dag_path dp where dp.ancestor_object_id in ( select distinct id from term baseterm where object_type_id = ? and upper(baseterm.nm) like ?))) and taxonterm.id in ( select distinct dp.descendant_object_id from dag_path dp where dp.ancestor_object_id in ( select distinct id from term baseterm where object_type_id = ? and baseterm.id in ( select object_id from db_link l where l.acc_txt = ? and l.type_cd = ? and l.object_type_id = ?))) and i.id in ( select ixn_id from ixn_axn where action_type_nm = ? and action_degree_type_nm in (...)) 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 Apr 01 05 1 4s207ms 4s207ms -
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 ptr.term_id IN ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 2 and upper(baseTerm.nm) LIKE 'ACETYLCYSTEINE')) and ptr.term_object_type_id = 2 and ptr.phenotype_id IN ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 5 and baseTerm.id in ( select object_id from db_link l where l.acc_txt = 'GO:0006979' AND l.type_cd = 'A' AND l.object_type_id = 5))) and i.id in ( select ixn_id from ixn_anatomy where anatomy_id IN ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 10 and upper(baseTerm.nm) LIKE 'CARDIOVASCULAR SYSTEM'))) and taxonTerm.id in ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 1 and baseTerm.id in ( select object_id from db_link l where l.acc_txt = '9605' AND l.type_cd = 'A' AND l.object_type_id = 1))) and i.id in ( select ixn_id from ixn_axn where action_type_nm = 'phenotype' and action_degree_type_nm in ('increases')) group by associatedTerm, associatedTermNmSort, phenotype, casRN, ixnId, ixnProseHtml, ixnProse, ixnSort, associatedTermId, phenotypeId, inferredCount ORDER BY associatedTermNmSort LIMIT 50;
Date: 2025-04-01 05:48:52 Duration: 4s207ms Bind query: yes
15 4s113ms 4s113ms 4s113ms 1 4s113ms select g.id geneid, g.acc_txt acc, g.nm nm, g.nm nmhtml, g.secondary_nm secondarynm, g.has_chems haschems, g.has_diseases hasdiseases, g.has_exposures hasexposures, g.has_phenotypes hasphenotypes, count(*) over () fullrowcount from term g where g.id in ( select gcr.gene_id from gene_chem_reference gcr where gcr.gene_id = any (array (( select gd.gene_id from term t inner join dag_path dp on t.id = dp.ancestor_object_id inner join gene_disease gd on dp.descendant_object_id = gd.disease_id where upper(t.nm) like ? and t.object_type_id = ?))) and gcr.id in ( select gcra.gene_chem_reference_id from gene_chem_reference_axn gcra where (gcra.action_degree_type_nm = ?))) order by g.nm_sort, g.id limit ?;Times Reported Time consuming queries #15
Day Hour Count Duration Avg duration Apr 01 05 1 4s113ms 4s113ms -
SELECT /* AdvancedGeneQueryDAO.getData */ g.id geneId, g.acc_txt acc, g.nm nm, g.nm nmHtml, g.secondary_nm secondaryNm, g.has_chems hasChems, g.has_diseases hasDiseases, g.has_exposures hasExposures, g.has_phenotypes hasPhenotypes, COUNT(*) OVER () fullRowCount FROM term g WHERE g.id IN ( SELECT gcr.gene_id FROM gene_chem_reference gcr WHERE /* CIQH.getIxnWhereCore */ gcr.gene_id = ANY (ARRAY (( SELECT /* IQH.getMasterDiseaseWhereEquals.Name.Gene */ gd.gene_id FROM term t INNER JOIN dag_path dp ON t.id = dp.ancestor_object_id INNER JOIN gene_disease gd ON dp.descendant_object_id = gd.disease_id WHERE UPPER(t.nm) LIKE 'ASTHMA' AND t.object_type_id = 3))) AND gcr.id IN ( SELECT gcra.gene_chem_reference_id FROM gene_chem_reference_axn gcra WHERE (gcra.action_degree_type_nm = 'increases'))) ORDER BY g.nm_sort, g.id LIMIT 50;
Date: 2025-04-01 05:47:24 Duration: 4s113ms Bind query: yes
16 4s43ms 4s43ms 4s43ms 1 4s43ms 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 ptr.term_id in ( select distinct dp.descendant_object_id from dag_path dp where dp.ancestor_object_id in ( select distinct id from term baseterm where object_type_id = ? and upper(baseterm.nm) like ?)) and ptr.term_object_type_id = ? and ptr.phenotype_id in ( select distinct dp.descendant_object_id from dag_path dp where dp.ancestor_object_id in ( select distinct id from term baseterm where object_type_id = ? and baseterm.id in ( select object_id from db_link l where l.acc_txt = ? and l.type_cd = ? and l.object_type_id = ?))) and taxonterm.id in ( select distinct dp.descendant_object_id from dag_path dp where dp.ancestor_object_id in ( select distinct id from term baseterm where object_type_id = ? and baseterm.id in ( select object_id from db_link l where l.acc_txt = ? and l.type_cd = ? and l.object_type_id = ?))) and i.id in ( select ixn_id from ixn_axn where action_type_nm = ? and action_degree_type_nm in (...)) group by associatedterm, associatedtermnmsort, phenotype, casrn, ixnid, ixnprosehtml, ixnprose, ixnsort, associatedtermid, phenotypeid, inferredcount order by associatedtermnmsort limit ?;Times Reported Time consuming queries #16
Day Hour Count Duration Avg duration Apr 01 05 1 4s43ms 4s43ms -
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 ptr.term_id IN ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 2 and upper(baseTerm.nm) LIKE 'ZINC')) and ptr.term_object_type_id = 2 and ptr.phenotype_id IN ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 5 and baseTerm.id in ( select object_id from db_link l where l.acc_txt = 'GO:0006915' AND l.type_cd = 'A' AND l.object_type_id = 5))) and taxonTerm.id in ( select /* DBConstants.getDAGTermSQL */ distinct dp.descendant_object_id from dag_path dp WHERE dp.ancestor_object_id in ( select distinct id from term baseTerm where object_type_id = 1 and baseTerm.id in ( select object_id from db_link l where l.acc_txt = '9606' AND l.type_cd = 'A' AND l.object_type_id = 1))) and i.id in ( select ixn_id from ixn_axn where action_type_nm = 'phenotype' and action_degree_type_nm in ('increases')) group by associatedTerm, associatedTermNmSort, phenotype, casRN, ixnId, ixnProseHtml, ixnProse, ixnSort, associatedTermId, phenotypeId, inferredCount ORDER BY associatedTermNmSort LIMIT 50;
Date: 2025-04-01 05:48:47 Duration: 4s43ms Bind query: yes
17 1s745ms 2s75ms 1s910ms 2 3s821ms select distinct (http_user_agent) from log_query_bots where upper(http_user_agent) like ? or upper(http_user_agent) like ? or upper(http_user_agent) like ? or upper(http_user_agent) like ?;Times Reported Time consuming queries #17
Day Hour Count Duration Avg duration Apr 01 13 2 3s821ms 1s910ms -
select distinct (http_user_agent) from log_query_bots where upper(http_user_agent) LIKE '%SEMANTIC%' OR upper(http_user_agent) LIKE '%QWANTIFY%' OR upper(http_user_agent) LIKE '%SEOKICKS%' OR upper(http_user_agent) LIKE '%ARQUIVO%';
Date: 2025-04-01 13:59:40 Duration: 2s75ms
-
select distinct (http_user_agent) from log_query_bots where upper(http_user_agent) LIKE '%SEMANTIC%' OR upper(http_user_agent) LIKE '%QWANTIFY%' OR upper(http_user_agent) LIKE '%SEOKICKS%' OR upper(http_user_agent) LIKE '%ARQUIVO%';
Date: 2025-04-01 13:36:01 Duration: 1s745ms
18 1s534ms 1s910ms 1s776ms 3 5s329ms select * from log_query_archive where upper(http_user_agent) like ?;Times Reported Time consuming queries #18
Day Hour Count Duration Avg duration Apr 01 13 1 1s534ms 1s534ms 14 2 3s794ms 1s897ms -
select * from log_query_archive where upper(http_user_agent) LIKE '%SEMANTIC%';
Date: 2025-04-01 14:06:30 Duration: 1s910ms
-
select * from log_query_archive where upper(http_user_agent) LIKE '%QWANTIFY%';
Date: 2025-04-01 14:06:00 Duration: 1s884ms
-
select * from log_query_archive where upper(http_user_agent) LIKE '%SEMANTIC%';
Date: 2025-04-01 13:42:16 Duration: 1s534ms
19 1s512ms 1s898ms 1s594ms 6 9s569ms select count(*) from log_query_archive where upper(http_user_agent) like ?;Times Reported Time consuming queries #19
Day Hour Count Duration Avg duration Apr 01 10 1 1s898ms 1s898ms 13 1 1s530ms 1s530ms 14 4 6s140ms 1s535ms [ User: pubc - Total duration: 1s898ms - Times executed: 1 ]
[ Application: pgAdmin 4 - CONN:9715397 - Total duration: 1s898ms - Times executed: 1 ]
-
select count(*) from log_query_archive where upper(http_user_agent) LIKE '%GOOGLE%';
Date: 2025-04-01 10:53:48 Duration: 1s898ms Database: ctddev51 User: pubc Application: pgAdmin 4 - CONN:9715397
-
select count(*) from log_query_archive where upper(http_user_agent) LIKE '%CRAWL%';
Date: 2025-04-01 14:39:04 Duration: 1s550ms
-
select count(*) from log_query_archive where upper(http_user_agent) LIKE '%GPTBOT%';
Date: 2025-04-01 14:41:55 Duration: 1s548ms
20 1s507ms 1s968ms 1s581ms 12 18s973ms select distinct (http_user_agent) from log_query_archive where upper(http_user_agent) like ?;Times Reported Time consuming queries #20
Day Hour Count Duration Avg duration Apr 01 10 2 3s509ms 1s754ms 11 3 4s659ms 1s553ms 13 5 7s675ms 1s535ms 14 2 3s128ms 1s564ms -
select distinct (http_user_agent) from log_query_archive where upper(http_user_agent) LIKE '%GOOGLE%';
Date: 2025-04-01 10:54:43 Duration: 1s968ms
-
select distinct (http_user_agent) from log_query_archive where upper(http_user_agent) LIKE '%BOT%';
Date: 2025-04-01 14:39:45 Duration: 1s577ms
-
select distinct (http_user_agent) from log_query_archive where upper(http_user_agent) LIKE '%EASY%';
Date: 2025-04-01 11:06:44 Duration: 1s572ms
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 NO DATASET
-
Events
Log levels
Key values
- 6,282 Event entries
- (EVENTLOG entries are formaly LOG level entries that are not queries)
Events distribution (except queries)
Key values
- 0 PANIC entries
- 0 FATAL entries
- 10 ERROR entries
- 1 WARNING entries
- 12 EVENTLOG entries
Most Frequent Errors/Events
Key values
- 11 Max number of times the same event was reported
- 23 Total events found
Rank Times reported Error 1 11 LOG: could not receive data from client: Connection timed out
Times Reported Most Frequent Error / Event #1
Day Hour Count Apr 01 21 1 22 5 23 5 2 5 ERROR: syntax error at or near "..."
Times Reported Most Frequent Error / Event #2
Day Hour Count Apr 01 13 1 14 4 - ERROR: syntax error at or near "where" at character 107
- ERROR: syntax error at or near "records" at character 82
- ERROR: syntax error at or near "records" at character 81
Statement: select distinct(http_user_agent) from log_query_archive where upper(http_user_agent) LIKE '%SEMANTIC%' OR where upper(http_user_agent) LIKE '%QWANTIFY%' OR where upper(http_user_agent) LIKE '%SEOKICKS%' OR where upper(http_user_agent) LIKE '%ARQUIVO%'
Date: 2025-04-01 13:32:05
Statement: select * from log_query_bots where upper(http_user_agent) LIKE '%SEOKICKS%' - 4 records 2021-2022
Date: 2025-04-01 14:01:40
Statement: select * from log_query_bots where upper(http_user_agent) LIKE '%ARQUIVO%' - 4 records 2021-2022
Date: 2025-04-01 14:07:42
3 2 ERROR: current transaction is aborted, commands ignored until end of transaction block
Times Reported Most Frequent Error / Event #3
Day Hour Count Apr 01 14 2 - ERROR: current transaction is aborted, commands ignored until end of transaction block
- ERROR: current transaction is aborted, commands ignored until end of transaction block
Statement: begin transaction
Date: 2025-04-01 14:26:26
Statement: --begin transaction INSERT INTO log_query_bots ( id ,type_cd ,query_tm ,submission_qty ,session_id ,remote_addr ,http_user_agent ,server_nm ,node_nm ,results_qty ,execution_ms ,basic_query_type ,basic_query_txt ,gene_query_type ,gene_txt ,gene_form_type_txt ,taxon_query_type ,taxon_txt ,chem_query_type ,chem_txt ,party_query_type ,party_nm_txt ,acc_txt ,go_query_type ,go_txt ,disease_query_type ,disease_txt ,action_type_txt ,action_degree_type_txt ,from_yr ,through_yr ,title_abstract_txt ,has_marray ,pathway_query_type ,pathway_txt ,dag_txt ,results_format_txt ,batch_input_type_txt ,gd_assn_type ,p_val ,p_val_type ,input_term_qty ,review_status ) SELECT q.id ,q.type_cd ,q.query_tm ,q.submission_qty ,q.session_id ,q.remote_addr ,q.http_user_agent ,q.server_nm ,q.node_nm ,q.results_qty ,q.execution_ms ,q.basic_query_type ,q.basic_query_txt ,q.gene_query_type ,q.gene_txt ,q.gene_form_type_txt ,q.taxon_query_type ,q.taxon_txt ,q.chem_query_type ,q.chem_txt ,q.party_query_type ,q.party_nm_txt ,q.acc_txt ,q.go_query_type ,q.go_txt ,q.disease_query_type ,q.disease_txt ,q.action_type_txt ,q.action_degree_type_txt ,q.from_yr ,q.through_yr ,q.title_abstract_txt ,q.has_marray ,q.pathway_query_type ,q.pathway_txt ,q.dag_txt ,q.results_format_txt ,q.batch_input_type_txt ,q.gd_assn_type ,q.p_val ,q.p_val_type ,q.input_term_qty ,q.review_status FROM log_query_archive q WHERE upper(q.http_user_agent) LIKE upper('%ARCHIVE%') OR upper(q.http_user_agent) LIKE upper('%SPIDER%') OR upper(q.http_user_agent) LIKE upper('%FACEBOOK%') OR upper(q.http_user_agent) LIKE upper('%NUTCH%') OR upper(q.http_user_agent) LIKE upper('%TRACK%') OR upper(q.http_user_agent) LIKE upper('%SITESUCKER%') OR upper(q.http_user_agent) LIKE upper('%KNOWLEDGE AI%') OR upper(q.http_user_agent) LIKE upper('%MEGAINDEX%') OR upper(q.http_user_agent) LIKE upper('%YANDEX%') OR upper(q.http_user_agent) LIKE upper('%PANSCIENT%') OR upper(q.http_user_agent) LIKE upper('%GOOGLEOTHER%') OR upper(q.http_user_agent) LIKE upper('%QWANTIFY%') OR upper(q.http_user_agent) LIKE upper('%SEOKICKS%') OR upper(q.http_user_agent) LIKE upper('%ARQUIVO%') OR upper(q.http_user_agent) LIKE upper('%SEMANTIC%') AND q.id NOT IN (SELECT a.id FROM log_query_bots a);
Date: 2025-04-01 14:26:36
4 1 ERROR: relation "..." does not exist
Times Reported Most Frequent Error / Event #4
Day Hour Count Apr 01 13 1 - ERROR: relation "exlcuded_user_agent2" does not exist at character 15
Statement: select * from exlcuded_user_agent2
Date: 2025-04-01 13:03:54 Database: ctddev51 Application: pgAdmin 4 - CONN:8082113 User: pubc Remote:
5 1 WARNING: is not a PostgreSQL server process
Times Reported Most Frequent Error / Event #5
Day Hour Count Apr 01 09 1 6 1 ERROR: canceling statement due to user request
Times Reported Most Frequent Error / Event #6
Day Hour Count Apr 01 13 1 - ERROR: canceling statement due to user request
Statement: (SELECT distinct upper(http_user_agent) FROM log_query_bots l, excluded_user_agent2 ua WHERE UPPER(l.http_user_agent) NOT LIKE UPPER(ua.user_agent_pattern));
Date: 2025-04-01 13:18:07
7 1 LOG: process ... still waiting for AccessShareLock on relation ... of database ... after ... ms
Times Reported Most Frequent Error / Event #7
Day Hour Count Apr 01 10 1 - LOG: process 2562081 still waiting for AccessShareLock on relation 7511475 of database 484829 after 1000.057 ms at character 454
Detail: Process holding the lock: 2558983. Wait queue: 2562081.
Statement: SELECT /* TermLinksDAO */ COALESCE(d.abbr_display, d.nm_display) dbnm ,d.cd dbcd ,d.id dbid ,COALESCE(d.abbr, d.nm) anchor ,l.acc_txt acc ,get_acc_sort_num(l.acc_txt) accsort ,l.is_primary isprimary ,dbrs.nm sitenm ,dbr.nm reportnm ,get_encoded_acc_url(dbrs.url, l.acc_txt) url ,COUNT(dbrs.id) OVER(PARTITION BY l.acc_txt,l.db_id,dbr.id) sitesPerAccCount ,COUNT(l.acc_txt) OVER(PARTITION BY l.db_id,dbrs.id) accsPerDbCount FROM db_link l INNER JOIN db d ON l.db_id = d.id INNER JOIN db_report dbr ON d.id = dbr.db_id AND dbr.object_type_id = 2 INNER JOIN db_report_site dbrs ON dbr.id = dbrs.db_report_id WHERE l.object_id = $1 AND l.object_type_id = 2 AND l.type_cd = 'X' AND dbr.type_cd IN ('PAV', 'SAV') ORDER BY 1 ,7 DESC ,6 ,5 ,8Date: 2025-04-01 10:55:04 Database: ctddev51 Application: User: pubeu Remote:
8 1 ERROR: function distint(...) does not exist
Times Reported Most Frequent Error / Event #8
Day Hour Count Apr 01 14 1 - ERROR: function distint(character varying) does not exist at character 8
Hint: No function matches the given name and argument types. You might need to add explicit type casts.
Statement: select distint(http_user_agent) from log_query_archive where upper(http_user_agent) LIKE '%BOT%'Date: 2025-04-01 14:34:49