pgBadger :: PostgreSQL Log Analyzer

Global information

Generated on Fri Apr 26 04:15:05 2024
Log file: /project/archive/log/postgres/dbprd51/postgresql.log-20240425
Parsed 28,095 log entries in 3s
Log start from 2024-04-25 00:00:01 to 2024-04-25 23:59:29

Global Stats

162 Number of unique normalized queries
869 Number of queries
7h39m52s Total query duration
2024-04-25 00:01:04 First query
2024-04-25 23:59:29 Last query
2 queries/s at 2024-04-25 09:09:48 Query peak

7h39m52s Total query duration
0ms Prepare/parse total duration
0ms Bind total duration
7h39m52s Execute total duration

1,333 Number of events
11 Number of unique normalized events
1,026 Max number of times the same event was reported
0 Number of cancellation

32 Total number of automatic vacuums
45 Total number of automatic analyzes

987 Number temporary file
1.00 GiB Max size of temporary file
162.03 MiB Average size of temporary file

2,096 Total number of sessions
159 sessions at 2024-04-25 03:06:46 Session peak
53d7h29m10s Total duration of sessions
36m37s Average duration of sessions
0 Average queries per session
13s164ms Average queries duration per session
36m24s Average idle time per session

2,097 Total number of connections
9 connections/s at 2024-04-25 13:06:04 Connection peak
2 Total number of databases

SQL Traffic

Key values

2 queries/s Query Peak
2024-04-25 09:09:48 Date

SELECT Traffic

Key values

2 queries/s Query Peak
2024-04-25 09:09:48 Date

INSERT/UPDATE/DELETE Traffic

Key values

1 queries/s Query Peak
2024-04-25 14:05:34 Date

Queries duration

Key values

7h39m52s 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

Day	Hour	Count	Min duration	Max duration	Avg duration	Latency Percentile(90)	Latency Percentile(95)	Latency Percentile(99)
Apr 25	00	28	0ms	24m9s	1m39s	1m9s	2m29s	24m9s
	01	20	0ms	1h33m49s	4m51s	4s880ms	6s310ms	1h33m49s
	02	39	0ms	1h1m35s	1m44s	18s76ms	1m31s	1h1m46s
	03	21	0ms	45m15s	2m12s	5s741ms	6s271ms	45m15s
	04	23	0ms	10s875ms	3s273ms	4s229ms	10s116ms	11s44ms
	05	61	0ms	1h53m13s	2m4s	19s217ms	1m8s	1h53m48s
	06	21	0ms	34m27s	1m44s	5s775ms	38s974ms	34m27s
	07	15	0ms	12s121ms	2s969ms	3s682ms	3s768ms	12s121ms
	08	27	0ms	17s104ms	5s100ms	6s695ms	9s507ms	50s90ms
	09	32	0ms	5s292ms	2s530ms	5s292ms	9s577ms	14s906ms
	10	62	0ms	32s4ms	4s233ms	11s384ms	42s278ms	52s315ms
	11	48	0ms	10s917ms	3s13ms	9s679ms	11s818ms	23s764ms
	12	41	0ms	8s77ms	2s767ms	9s265ms	12s819ms	15s762ms
	13	66	0ms	6s795ms	2s741ms	9s625ms	20s20ms	29s581ms
	14	54	0ms	32s7ms	3s699ms	9s488ms	18s611ms	54s400ms
	15	35	0ms	8s325ms	2s922ms	8s325ms	11s946ms	16s577ms
	16	27	0ms	12s121ms	3s237ms	10s737ms	10s949ms	14s894ms
	17	22	0ms	5s911ms	3s786ms	10s696ms	11s9ms	12s117ms
	18	38	0ms	31s991ms	4s844ms	10s881ms	15s521ms	52s256ms
	19	59	0ms	8s648ms	3s337ms	10s988ms	34s579ms	49s634ms
	20	19	0ms	5s913ms	3s90ms	2s553ms	10s783ms	12s872ms
	21	28	0ms	15s278ms	4s139ms	8s723ms	14s565ms	15s278ms
	22	43	0ms	12s61ms	3s377ms	11s834ms	14s856ms	17s68ms
	23	40	0ms	34s196ms	3s569ms	6s534ms	10s545ms	34s196ms

Day	Hour	SELECT	COPY TO	Average Duration	Latency Percentile(90)	Latency Percentile(95)	Latency Percentile(99)
Apr 25	00	18	0	56s630ms	1s785ms	3s915ms	15m51s
	01	18	0	5m14s	1s261ms	4s372ms	1h33m49s
	02	29	0	2m14s	3s801ms	4s853ms	1h1m35s
	03	16	0	3s50ms	1s193ms	4s25ms	5s927ms
	04	22	0	3s236ms	1s697ms	4s229ms	10s875ms
	05	57	0	2m12s	5s65ms	19s217ms	1h53m48s
	06	11	10	1m44s	1s894ms	5s775ms	34m27s
	07	15	0	2s969ms	1s487ms	3s682ms	12s121ms
	08	27	0	5s100ms	3s867ms	6s695ms	15s579ms
	09	32	0	2s530ms	2s147ms	5s292ms	9s723ms
	10	52	10	4s233ms	8s635ms	11s384ms	42s431ms
	11	48	0	3s13ms	4s784ms	9s679ms	13s525ms
	12	41	0	2s767ms	4s858ms	9s265ms	15s762ms
	13	64	0	2s755ms	7s257ms	9s625ms	26s469ms
	14	44	10	3s699ms	6s340ms	9s488ms	52s304ms
	15	35	0	2s922ms	5s22ms	8s325ms	16s577ms
	16	27	0	3s237ms	4s843ms	10s737ms	14s894ms
	17	22	0	3s786ms	4s907ms	10s696ms	12s117ms
	18	28	10	4s844ms	5s135ms	10s881ms	52s256ms
	19	59	0	3s337ms	10s667ms	10s988ms	49s634ms
	20	18	0	3s162ms	1s74ms	2s553ms	12s872ms
	21	28	0	4s139ms	4s873ms	8s723ms	15s278ms
	22	42	0	3s410ms	6s500ms	11s834ms	17s68ms
	23	39	0	3s606ms	4s465ms	6s534ms	34s196ms

Day	Hour	INSERT	UPDATE	Average Duration	Latency Percentile(90)	Latency Percentile(95)	Latency Percentile(99)
Apr 25	00	5	4	3m15s	0ms	0ms	2m5s
	01	1	0	2m41s	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	0	0	0ms	0ms	0ms	0ms
	06	0	0	0ms	0ms	0ms	0ms
	07	0	0	0ms	0ms	0ms	0ms
	08	0	0	0ms	0ms	0ms	0ms
	09	0	0	0ms	0ms	0ms	0ms
	10	0	0	0ms	0ms	0ms	0ms
	11	0	0	0ms	0ms	0ms	0ms
	12	0	0	0ms	0ms	0ms	0ms
	13	0	0	0ms	0ms	0ms	0ms
	14	0	0	0ms	0ms	0ms	0ms
	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	Bind	Bind/Prepare	Percentage of prepare
Apr 25	00	26	26.00	0.00%
	01	20	20.00	0.00%
	02	39	39.00	0.00%
	03	21	21.00	0.00%
	04	24	24.00	0.00%
	05	67	67.00	0.00%
	06	11	11.00	0.00%
	07	15	15.00	0.00%
	08	29	29.00	0.00%
	09	32	32.00	0.00%
	10	36	36.00	0.00%
	11	44	44.00	0.00%
	12	41	41.00	0.00%
	13	67	67.00	0.00%
	14	44	44.00	0.00%
	15	35	35.00	0.00%
	16	28	28.00	0.00%
	17	22	22.00	0.00%
	18	28	28.00	0.00%
	19	59	59.00	0.00%
	20	19	19.00	0.00%
	21	28	28.00	0.00%
	22	43	43.00	0.00%
	23	40	40.00	0.00%

Day	Hour	Count	Average / Second
Apr 25	00	88	0.02/s
	01	78	0.02/s
	02	90	0.03/s
	03	95	0.03/s
	04	86	0.02/s
	05	93	0.03/s
	06	71	0.02/s
	07	77	0.02/s
	08	81	0.02/s
	09	89	0.02/s
	10	88	0.02/s
	11	122	0.03/s
	12	82	0.02/s
	13	89	0.02/s
	14	103	0.03/s
	15	69	0.02/s
	16	76	0.02/s
	17	80	0.02/s
	18	82	0.02/s
	19	83	0.02/s
	20	79	0.02/s
	21	81	0.02/s
	22	91	0.03/s
	23	124	0.03/s

Day	Hour	Count	Average Duration	Average idle time
Apr 25	00	88	28m25s	27m53s
	01	78	30m38s	29m24s
	02	90	28m58s	28m13s
	03	95	24m18s	23m48s
	04	86	28m35s	28m34s
	05	93	24m32s	23m11s
	06	72	36m6s	35m36s
	07	77	32m18s	32m17s
	08	81	29m22s	29m20s
	09	85	28m49s	28m48s
	10	82	28m56s	28m53s
	11	122	20m33s	20m32s
	12	82	29m10s	29m8s
	13	89	25m55s	25m53s
	14	95	27m32s	27m29s
	15	69	30m39s	30m37s
	16	76	33m15s	33m14s
	17	80	30m1s	30m
	18	82	29m20s	29m18s
	19	84	51m	50m58s
	20	88	1h47m34s	1h47m33s
	21	87	2h18m3s	2h18m2s
	22	91	27m31s	27m30s
	23	124	19m13s	19m12s

↑ Back to the top of the General Activity table

Established Connections

Key values

9 connections Connection Peak
2024-04-25 13:06:04 Date

Connections per database

Key values

ctdprd51 Main Database
2,097 connections Total

Chart
Table

Database	User	Count
ctdprd51		2,095
	edit	7
	editeu	388
	load	4
	postgres	8
	pub1	1
	pub2	4
	pubc	1
	pubeu	910
	qaeu	772
postgres		2
	edit	1
	editeu	1

Connections per user

Key values

pubeu Main User
2,097 connections Total

Chart
Table

User	Count
edit	8
editeu	389
load	4
postgres	8
pub1	1
pub2	4
pubc	1
pubeu	910
qaeu	772

Connections per host

Key values

10.12.5.37 Main host with 538 connections
2,097 Total connections

Chart
Table

Host	Count
10.12.5.122	12
10.12.5.37	538
10.12.5.38	373
10.12.5.39	370
10.12.5.40	7
10.12.5.45	394
10.12.5.46	378
192.168.201.10	6
192.168.202.5	7
192.168.202.6	4
::1	8

Simultaneous sessions

Key values

159 sessions Session Peak
2024-04-25 03:06:46 Date

Histogram of session times

Key values

1,695 1800000-3600000ms duration

Chart
Table

Range	Count	Percentage
0ms-500ms	35	1.67%
500ms-1s	3	0.14%
1s-30s	17	0.81%
30s-1m	69	3.29%
1m-10m	147	7.01%
10m-30m	91	4.34%
30m-1h	1,695	80.87%
1h-8h	23	1.10%
> 8h	16	0.76%

Sessions per database

Key values

ctdprd51 Main Database
2,096 sessions Total

Chart
Table

Database	Count	Total Duration	Average Duration
ctdprd51	2,092	50d24m19s	34m25s
postgres	4	3d7h4m51s	19h46m12s

Sessions per user

Key values

pubeu Main User
2,096 sessions Total

Chart
Table

User	Count	Total Duration	Average Duration
edit	11	7d6h17m49s	15h50m42s
editeu	381	8d1h33m22s	30m28s
load	7	5d13h54m22s	19h7m46s
postgres	8	7m18s	54s789ms
pub1	1	20s50ms	20s50ms
pub2	5	6h43m36s	1h20m43s
pubc	1	15m52s	15m52s
pubeu	910	16d36m39s	25m21s
qaeu	772	16d1h59m50s	29m59s

Sessions per host

Key values

10.12.5.37 Main Host
2,096 sessions Total

Chart
Table

Host	Count	Total Duration	Average Duration
10.12.5.122	4	1h30m30s	22m37s
10.12.5.37	538	8d55m40s	21m30s
10.12.5.38	373	7d23h56m51s	30m52s
10.12.5.39	370	7d23h40m20s	31m4s
10.12.5.40	8	6h44m24s	50m33s
10.12.5.45	394	8d2h11m27s	29m34s
10.12.5.46	378	7d23h48m22s	30m26s
192.168.201.10	12	11d57m46s	22h4m48s
192.168.202.5	7	22m31s	3m13s
192.168.202.6	4	1d19h13m56s	10h48m29s
::1	8	7m18s	54s789ms

Sessions per application

Key values

unknown Main Application
2,096 sessions Total

Chart
Table

Application	Count	Total Duration	Average Duration
pgAdmin 4 - CONN:288486	2	6m35s	3m17s
pgAdmin 4 - CONN:8770850	2	6s929ms	3s464ms
pgAdmin 4 - DB:ctdprd51	2	7m54s	3m57s
pgAdmin 4 - DB:postgres	1	7m55s	7m55s
psql	4	2s244ms	561ms
unknown	2,085	53d7h6m36s	36m48s

Checkpoints Buffers

Key values

1,483,053 buffers Checkpoint Peak
2024-04-25 00:47:22 Date
1620.018 seconds Highest write time
1.003 seconds Sync time

Checkpoints Wal files

Key values

869 files Wal files usage Peak
2024-04-25 03:17:20 Date

Checkpoints distance

Key values

17,688.71 Mo Distance Peak
2024-04-25 03:10:05 Date

Checkpoints Activity

Day	Hour	Written buffers	Write time	Sync time	Total time
Apr 25	00	2,606,534	2,740.708s	0.074s	2,760.583s
	01	252,382	1,627.923s	0.002s	1,628.592s
	02	570,219	3,733.103s	0.045s	3,737.256s
	03	284,257	775.354s	5.791s	936.38s
	04	488,007	1,619.552s	0.007s	1,625.51s
	05	114,064	3,239.321s	0.005s	3,239.95s
	06	1,111,331	3,238.851s	0.005s	3,248.823s
	07	2,876	288.254s	0.003s	288.444s
	08	6,568	657.984s	0.004s	658.029s
	09	103	10.828s	0.001s	10.844s
	10	83,434	3,350.701s	0.005s	3,350.965s
	11	456	45.895s	0.002s	45.925s
	12	212	21.423s	0.002s	21.453s
	13	25,354	2,175.542s	0.003s	2,175.655s
	14	198	20.027s	0.002s	20.058s
	15	69	7.094s	0.002s	7.124s
	16	228	23.037s	0.002s	23.11s
	17	90	9.203s	0.002s	9.233s
	18	78	8.149s	0.002s	8.179s
	19	286	28.857s	0.002s	28.889s
	20	82	8.389s	0.002s	8.419s
	21	107	10.898s	0.002s	10.928s
	22	639	64.243s	0.002s	64.274s
	23	393	39.64s	0.003s	39.671s

Day	Hour	Removed	Recycled	Synced files	Longest sync	Average sync
Apr 25	00	0	1,614	288	0.032s	0.004s
	01	0	41	48	0.001s	0.002s
	02	255	251	188	0.015s	0.003s
	03	296	10,919	992	0.946s	0.165s
	04	0	494	115	0.001s	0.001s
	05	33	43	104	0.001s	0.002s
	06	0	817	194	0.001s	0.002s
	07	0	1	111	0.001s	0.002s
	08	4	0	153	0.001s	0.002s
	09	0	0	53	0.001s	0.001s
	10	46	0	213	0.001s	0.003s
	11	0	0	117	0.001s	0.002s
	12	0	0	31	0.001s	0.002s
	13	13	0	91	0.001s	0.002s
	14	0	0	80	0.001s	0.002s
	15	0	0	18	0.001s	0.002s
	16	1	0	73	0.001s	0.002s
	17	0	0	19	0.001s	0.002s
	18	0	0	22	0.001s	0.002s
	19	0	0	31	0.001s	0.002s
	20	0	0	22	0.001s	0.002s
	21	0	0	21	0.001s	0.002s
	22	0	0	29	0.001s	0.002s
	23	0	0	39	0.001s	0.002s

Day	Hour	Avg time (sec)
Apr 25	00	0s
	01	0s
	02	0s
	03	0s
	04	0s
	05	0s
	06	0s
	07	0s
	08	0s
	09	0s
	10	0s
	11	0s
	12	0s
	13	0s
	14	0s
	15	0s
	16	0s
	17	0s
	18	0s
	19	0s
	20	0s
	21	0s
	22	0s
	23	0s

Day	Hour	Mean distance	Mean estimate
Apr 25	00	8,814,501.33 kB	8,853,543.67 kB
	01	599,092.50 kB	7,680,142.00 kB
	02	2,588,830.33 kB	6,360,937.33 kB
	03	8,838,605.81 kB	8,940,163.24 kB
	04	6,236,211.00 kB	8,672,093.00 kB
	05	617,861.50 kB	7,521,000.00 kB
	06	6,956,362.50 kB	8,630,439.50 kB
	07	9,454.00 kB	7,221,600.00 kB
	08	32,385.00 kB	5,853,179.00 kB
	09	528.00 kB	4,993,453.00 kB
	10	253,218.67 kB	4,122,513.33 kB
	11	735.00 kB	3,166,759.00 kB
	12	444.00 kB	2,565,180.50 kB
	13	106,394.50 kB	2,095,514.00 kB
	14	524.50 kB	1,699,970.00 kB
	15	138.00 kB	1,377,002.00 kB
	16	598.50 kB	1,115,465.50 kB
	17	146.00 kB	903,574.50 kB
	18	141.00 kB	731,922.00 kB
	19	582.00 kB	592,966.50 kB
	20	147.50 kB	480,329.00 kB
	21	232.50 kB	389,108.00 kB
	22	1,868.00 kB	315,533.50 kB
	23	1,023.50 kB	255,724.50 kB

↑ Back to the top of the Checkpoint Activity table

Checkpoint causes

Key values

time (43) Main checkpoint cause
67 checkpoints Total

Chart
Table

Cause	Number of checkpoints
time	43
wal	24
Total	67

Size of temporary files

Key values

9.43 GiB Temp Files size Peak
2024-04-25 03:43:08 Date

Number of temporary files

Key values

21 per second Temp Files Peak
2024-04-25 03:09:31 Date

Temporary Files Activity

Temp Files

Day	Hour	Count	Total size	Average size
Apr 25	00	0	0	0
	01	0	0	0
	02	87	2.84 GiB	33.43 MiB
	03	892	145.59 GiB	167.14 MiB
	04	0	0	0
	05	0	0	0
	06	8	7.75 GiB	991.53 MiB
	07	0	0	0
	08	0	0	0
	09	0	0	0
	10	0	0	0
	11	0	0	0
	12	0	0	0
	13	0	0	0
	14	0	0	0
	15	0	0	0
	16	0	0	0
	17	0	0	0
	18	0	0	0
	19	0	0	0
	20	0	0	0
	21	0	0	0
	22	0	0	0
	23	0	0	0

↑ Back to the top of the Temporary Files Activity table

Queries generating the most temporary files (N)

Rank	Count	Total size	Min size	Max size	Avg size	Query
1	897	145.82 GiB	128.00 KiB	1.00 GiB	166.47 MiB	vacuum full analyze; VACUUM FULL ANALYZE; Date: 2024-04-25 03:44:59 Duration: 45m15s VACUUM FULL ANALYZE; Date: 2024-04-25 02:59:53 Duration: 0ms
2	62	1.97 GiB	7.21 MiB	1.00 GiB	32.48 MiB	cluster pub2.term; CLUSTER pub2.TERM; Date: 2024-04-25 02:59:09 Duration: 59s17ms CLUSTER pub2.TERM; Date: 2024-04-25 02:58:19 Duration: 0ms
3	20	656.61 MiB	19.27 MiB	55.12 MiB	32.83 MiB	cluster pub2.term_label; CLUSTER pub2.TERM_LABEL; Date: 2024-04-25 02:59:42 Duration: 32s580ms CLUSTER pub2.TERM_LABEL; Date: 2024-04-25 02:59:14 Duration: 0ms
4	8	7.75 GiB	764.27 MiB	1.00 GiB	991.53 MiB	select pub2.maint_cached_value_refresh_data_metrics (); select pub2.maint_cached_value_refresh_data_metrics (); Date: 2024-04-25 06:23:18 Duration: 34m27s select pub2.maint_cached_value_refresh_data_metrics (); Date: 2024-04-25 06:19:03 Duration: 0ms

Queries generating the largest temporary files

Rank	Size	Query
1	1.00 GiB	VACUUM FULL ANALYZE; [ Date: 2024-04-25 03:15:08 ]
2	1.00 GiB	VACUUM FULL ANALYZE; [ Date: 2024-04-25 03:15:08 ]
3	1.00 GiB	VACUUM FULL ANALYZE; [ Date: 2024-04-25 03:15:08 ]
4	1.00 GiB	VACUUM FULL ANALYZE; [ Date: 2024-04-25 03:15:08 ]
5	1.00 GiB	VACUUM FULL ANALYZE; [ Date: 2024-04-25 03:15:08 ]
6	1.00 GiB	VACUUM FULL ANALYZE; [ Date: 2024-04-25 03:18:35 ]
7	1.00 GiB	VACUUM FULL ANALYZE; [ Date: 2024-04-25 03:18:35 ]
8	1.00 GiB	VACUUM FULL ANALYZE; [ Date: 2024-04-25 03:18:35 ]
9	1.00 GiB	VACUUM FULL ANALYZE; [ Date: 2024-04-25 03:18:35 ]
10	1.00 GiB	VACUUM FULL ANALYZE; [ Date: 2024-04-25 03:18:35 ]
11	1.00 GiB	VACUUM FULL ANALYZE; [ Date: 2024-04-25 03:18:35 ]
12	1.00 GiB	VACUUM FULL ANALYZE; [ Date: 2024-04-25 03:18:35 ]
13	1.00 GiB	VACUUM FULL ANALYZE; [ Date: 2024-04-25 03:18:36 ]
14	1.00 GiB	VACUUM FULL ANALYZE; [ Date: 2024-04-25 03:18:36 ]
15	1.00 GiB	VACUUM FULL ANALYZE; [ Date: 2024-04-25 03:18:36 ]
16	1.00 GiB	VACUUM FULL ANALYZE; [ Date: 2024-04-25 03:19:24 ]
17	1.00 GiB	VACUUM FULL ANALYZE; [ Date: 2024-04-25 03:19:24 ]
18	1.00 GiB	VACUUM FULL ANALYZE; [ Date: 2024-04-25 03:19:25 ]
19	1.00 GiB	VACUUM FULL ANALYZE; [ Date: 2024-04-25 03:19:25 ]
20	1.00 GiB	VACUUM FULL ANALYZE; [ Date: 2024-04-25 03:19:25 ]

Vacuums / Analyzes Distribution

Key values

285.44 sec Highest CPU-cost vacuum
Table pub2.gene_disease
Database ctdprd51
2024-04-25 00:21:37 Date
0 sec Highest CPU-cost analyze
Table
Database ctdprd51
Date

Average Autovacuum Duration

Key values

285.44 sec Highest CPU-cost vacuum
Table pub2.gene_disease
Database ctdprd51
2024-04-25 00:21:37 Date

Analyzes per table

Key values

pubc.log_query (18) Main table analyzed (database ctdprd51)
45 analyzes Total

Chart
Table

Table	Number of analyzes
ctdprd51.pubc.log_query	18
ctdprd51.pub2.term_comp_agent	2
ctdprd51.pub2.term_set_enrichment	2
ctdprd51.pub2.term_set_enrichment_agent	2
ctdprd51.pub2.phenotype_term	2
ctdprd51.pg_catalog.pg_type	2
ctdprd51.pg_catalog.pg_shdepend	1
ctdprd51.pub2.ixn	1
ctdprd51.pub2.gene_gene_ref_throughput	1
ctdprd51.pg_catalog.pg_class	1
ctdprd51.pub2.gene_chem_ref_gene_form	1
ctdprd51.pub2.term_comp	1
ctdprd51.pub2.reference	1
ctdprd51.pub2.gene_disease	1
ctdprd51.pg_catalog.pg_attribute	1
ctdprd51.pub2.gene_gene	1
ctdprd51.pub2.slim_term_mapping	1
ctdprd51.pub2.term_reference	1
ctdprd51.pub2.term	1
ctdprd51.load.data_load	1
ctdprd51.pg_catalog.pg_index	1
ctdprd51.pub2.gene_gene_reference	1
ctdprd51.pg_catalog.pg_depend	1
Total	45

Vacuums per table

Key values

pub2.phenotype_term (5) Main table vacuumed on database ctdprd51
32 vacuums Total

Chart
Table

		Index	Buffer usage			Skipped		WAL usage
Table	Vacuums	scans	hits	misses	dirtied	pins	frozen	records	full page	bytes
ctdprd51.pub2.phenotype_term	5	2	1,293,095	0	44,008	0	0	746,015	132,484	302,709,692
ctdprd51.pub2.gene_disease	5	1	6,602,934	0	1,193,599	0	0	1,576,094	825,486	2,042,310,192
ctdprd51.pubc.log_query	3	3	1,246	0	333	0	0	638	89	609,616
ctdprd51.pg_catalog.pg_statistic	2	2	1,141	0	301	0	52	662	207	965,818
ctdprd51.pub2.gene_gene	1	0	11,609	0	4	0	0	1	1	6,333
ctdprd51.pub2.slim_term_mapping	1	0	600	0	3	0	0	1	1	5,173
ctdprd51.pub2.term	1	1	861,520	0	294,887	0	34	523,072	250,695	1,143,284,420
ctdprd51.pub2.term_reference	1	0	35,868	0	5	0	0	17,878	2	1,066,565
ctdprd51.pg_toast.pg_toast_2619	1	1	3,757	0	1,165	0	10,367	2,916	559	442,069
ctdprd51.pub2.gene_gene_reference	1	0	29,346	0	3	0	0	1	1	5,273
ctdprd51.pg_toast.pg_toast_5031308	1	1	90	0	3	0	0	50	1	11,614
ctdprd51.pub2.term_comp_agent	1	0	160	0	4	0	0	36	2	15,543
ctdprd51.pub2.term_set_enrichment	1	0	569	0	4	0	0	244	2	27,931
ctdprd51.pub2.ixn	1	1	1,417,865	0	83	0	0	1,208,031	17	83,648,716
ctdprd51.pg_catalog.pg_shdepend	1	1	132	0	67	0	0	87	43	162,512
ctdprd51.pub2.gene_gene_ref_throughput	1	0	14,053	0	2	0	0	1	0	281
ctdprd51.pg_catalog.pg_class	1	1	286	0	51	0	0	186	50	278,241
ctdprd51.pub2.term_set_enrichment_agent	1	0	11,097	0	3	0	0	5,531	1	334,748
ctdprd51.pub2.gene_chem_ref_gene_form	1	0	31,299	0	5	0	0	15,600	2	935,523
ctdprd51.pg_catalog.pg_attribute	1	1	537	0	177	0	55	325	124	562,942
ctdprd51.pub2.reference	1	1	365,714	0	125,914	0	0	209,994	51,618	217,974,952
Total	32	16	10,682,918	171,304	1,660,621	0	10,508	4,307,363	1,261,385	3,795,358,154

Tuples removed per table

Key values

pub2.gene_disease (32612953) Main table with removed tuples on database ctdprd51
54803670 tuples Total removed

Chart
Table

		Index	Tuples			Pages
Table	Vacuums	scans	removed	remain	not yet removable	removed	remain
ctdprd51.pub2.gene_disease	5	1	32,612,953	293,516,577	130,451,812	0	2,398,015
ctdprd51.pub2.phenotype_term	5	2	19,513,459	25,981,280	9,742,980	0	425,185
ctdprd51.pub2.ixn	1	1	2,244,898	2,191,575	0	0	518,173
ctdprd51.pub2.term	1	1	425,024	2,082,142	0	0	228,471
ctdprd51.pg_toast.pg_toast_2619	1	1	3,011	20,516	0	0	12,592
ctdprd51.pubc.log_query	3	3	2,251	4,750	0	0	286
ctdprd51.pg_catalog.pg_attribute	1	1	875	8,745	0	0	244
ctdprd51.pg_catalog.pg_statistic	2	2	538	4,655	0	0	580
ctdprd51.pg_catalog.pg_shdepend	1	1	428	2,057	0	0	24
ctdprd51.pg_catalog.pg_class	1	1	162	1,782	0	0	61
ctdprd51.pg_toast.pg_toast_5031308	1	1	71	69	0	0	21
ctdprd51.pub2.gene_gene	1	0	0	1,069,096	0	0	5,779
ctdprd51.pub2.slim_term_mapping	1	0	0	32,792	0	0	261
ctdprd51.pub2.term_reference	1	0	0	3,307,119	0	0	17,877
ctdprd51.pub2.gene_gene_reference	1	0	0	1,341,638	0	0	14,610
ctdprd51.pub2.term_comp_agent	1	0	0	3,636	0	0	35
ctdprd51.pub2.term_set_enrichment	1	0	0	14,634	0	0	243
ctdprd51.pub2.gene_gene_ref_throughput	1	0	0	1,347,229	0	0	7,003
ctdprd51.pub2.term_set_enrichment_agent	1	0	0	486,635	0	0	5,530
ctdprd51.pub2.gene_chem_ref_gene_form	1	0	0	2,879,676	0	0	15,599
ctdprd51.pub2.reference	1	1	0	196,034	0	0	85,143
Total	32	16	54,803,670	334,492,637	140,194,792	0	3,735,732

Pages removed per table

Key values

unknown (0) Main table with removed pages on database unknown
0 pages Total removed

Chart
Table

Pages removed per tables

NO DATASET

Table	Number of vacuums	Index scans	Tuples removed
ctdprd51.pub2.gene_gene	1	0	0
ctdprd51.pub2.slim_term_mapping	1	0	0
ctdprd51.pub2.term	1	1	425024
ctdprd51.pub2.term_reference	1	0	0
ctdprd51.pubc.log_query	3	3	2251
ctdprd51.pg_toast.pg_toast_2619	1	1	3011
ctdprd51.pub2.gene_gene_reference	1	0	0
ctdprd51.pg_toast.pg_toast_5031308	1	1	71
ctdprd51.pub2.term_comp_agent	1	0	0
ctdprd51.pub2.term_set_enrichment	1	0	0
ctdprd51.pub2.ixn	1	1	2244898
ctdprd51.pg_catalog.pg_shdepend	1	1	428
ctdprd51.pub2.gene_gene_ref_throughput	1	0	0
ctdprd51.pg_catalog.pg_class	1	1	162
ctdprd51.pub2.term_set_enrichment_agent	1	0	0
ctdprd51.pub2.gene_chem_ref_gene_form	1	0	0
ctdprd51.pub2.phenotype_term	5	2	19513459
ctdprd51.pg_catalog.pg_statistic	2	2	538
ctdprd51.pg_catalog.pg_attribute	1	1	875
ctdprd51.pub2.gene_disease	5	1	32612953
ctdprd51.pub2.reference	1	1	0
Total	32	16	54,803,670

Autovacuum Activity

Vacuums / Analyzes

Day	Hour	ANALYZEs
Apr 25	00	7
	01	3
	02	2
	03	9
	04	1
	05	7
	06	1
	07	1
	08	0
	09	1
	10	1
	11	0
	12	1
	13	8
	14	0
	15	0
	16	1
	17	0
	18	0
	19	1
	20	0
	21	0
	22	1
	23	0

↑ Back to the top of the Autovacuum Activity table

Locks by types

Key values

unknown Main Lock Type
0 locks Total

Chart
Table

Type of locks

NO DATASET

Type	Object	Count	Total Duration	Average Duration (s)
NO DATASET

Most frequent waiting queries (N)

Rank	Count	Total time	Min time	Max time	Avg duration	Query
NO DATASET

Queries that waited the most

Rank	Wait time	Query
NO DATASET

Queries by type

Key values

792 Total read queries
63 Total write queries

Chart
Table

Type	Count	Percentage
SELECT	792	91.14%
INSERT	6	0.69%
UPDATE	4	0.46%
DELETE	0	0.00%
COPY FROM	0	0.00%
COPY TO	40	4.60%
CTE	10	1.15%
DDL	3	0.35%
TCL	0	0.00%
CURSOR	0	0.00%
OTHERS	14	1.61%

Queries by database

Key values

unknown Main database
560 Requests
7h21m2s (unknown)
Main time consuming database

Chart
Table

Database	Request type	Count	Duration
ctdprd51	Total	309	18m49s
	copy to	8	3m1s
	cte	6	18s542ms
	insert	1	19s745ms
	select	294	15m9s
unknown	Total	560	7h21m2s
	copy to	32	3m40s
	cte	4	5s229ms
	ddl	3	55s252ms
	insert	5	4m17s
	others	14	48m
	select	498	5h56m38s
	update	4	27m24s

Queries by user

Key values

unknown Main user
982 Requests

Queries Chart
Table

User	Request type	Count	Duration
edit	Total	2	13s452ms
	insert	1	10s832ms
	select	1	2s619ms
editeu	Total	2	6s117ms
	select	2	6s117ms
load	Total	33	24m25s
	others	1	2s613ms
	select	32	24m22s
postgres	Total	16	6m
	copy to	16	6m
pub1	Total	1	4s9ms
	select	1	4s9ms
pub2	Total	8	11m35s
	ddl	1	4s245ms
	insert	3	11m14s
	select	4	17s5ms
pubeu	Total	475	29m10s
	cte	5	14s944ms
	select	470	28m55s
qaeu	Total	15	38s265ms
	cte	3	10s386ms
	select	12	27s879ms
unknown	Total	982	11h26m12s
	copy to	61	7m13s
	cte	7	9s171ms
	ddl	52	38m14s
	insert	26	38m44s
	others	26	52m35s
	select	801	8h36m53s
	update	9	32m22s
zbx_monitor	Total	2	2s822ms
	select	2	2s822ms

Duration by user

Key values

11h26m12s (unknown) Main time consuming user

Duration Chart
Table

User	Request type	Count	Duration
edit	Total	2	13s452ms
	insert	1	10s832ms
	select	1	2s619ms
editeu	Total	2	6s117ms
	select	2	6s117ms
load	Total	33	24m25s
	others	1	2s613ms
	select	32	24m22s
postgres	Total	16	6m
	copy to	16	6m
pub1	Total	1	4s9ms
	select	1	4s9ms
pub2	Total	8	11m35s
	ddl	1	4s245ms
	insert	3	11m14s
	select	4	17s5ms
pubeu	Total	475	29m10s
	cte	5	14s944ms
	select	470	28m55s
qaeu	Total	15	38s265ms
	cte	3	10s386ms
	select	12	27s879ms
unknown	Total	982	11h26m12s
	copy to	61	7m13s
	cte	7	9s171ms
	ddl	52	38m14s
	insert	26	38m44s
	others	26	52m35s
	select	801	8h36m53s
	update	9	32m22s
zbx_monitor	Total	2	2s822ms
	select	2	2s822ms

Queries by host

Key values

unknown Main host
1,536 Requests
12h38m29s (unknown)
Main time consuming host

Chart
Table

Host	Request type	Count	Duration
unknown	Total	1,536	12h38m29s
	copy to	77	13m13s
	cte	15	34s502ms
	ddl	53	38m18s
	insert	30	50m9s
	others	27	52m38s
	select	1,325	9h31m11s
	update	9	32m22s

Queries by application

Key values

unknown Main application
856 Requests
7h36m34s (unknown)
Main time consuming application

Chart
Table

Application	Request type	Count	Duration
pgAdmin 4 - CONN:8436271	Total	1	2s619ms
	select	1	2s619ms
pg_dump	Total	8	3m1s
	copy to	8	3m1s
psql	Total	4	13s542ms
	select	4	13s542ms
unknown	Total	856	7h36m34s
	copy to	32	3m40s
	cte	10	23s772ms
	ddl	3	55s252ms
	insert	6	4m37s
	others	14	48m
	select	787	6h11m32s
	update	4	27m24s

Number of cancelled queries

Key values

0 per second Cancelled query Peak
2024-04-25 22:45:46 Date

Number of cancelled queries (5 minutes period)

NO DATASET

Histogram of query times

Key values

827 1000-10000ms duration

Chart
Table

Range	Count	Percentage
0-1ms	0	0.00%
1-5ms	0	0.00%
5-10ms	0	0.00%
10-25ms	0	0.00%
25-50ms	0	0.00%
50-100ms	0	0.00%
100-500ms	0	0.00%
500-1000ms	0	0.00%
1000-10000ms	827	93.34%
> 10000ms	59	6.66%

Slowest individual queries

Rank	Duration	Query
1	1h53m13s	select pub2.maint_term_derive_data (); [ Date: 2024-04-25 05:38:37 - Bind query: yes ]
2	1h33m49s	select pub2.maint_gene_chem_ref_gene_form_refresh (); [ Date: 2024-04-25 01:49:58 - Bind query: yes ]
3	1h1m35s	SELECT maint_term_derive_nm_fts (); [ Date: 2024-04-25 02:54:24 - Bind query: yes ]
4	45m15s	VACUUM FULL ANALYZE; [ Date: 2024-04-25 03:44:59 - Bind query: yes ]
5	34m27s	select pub2.maint_cached_value_refresh_data_metrics (); [ Date: 2024-04-25 06:23:18 - Bind query: yes ]
6	24m9s	update pub2.GENE_DISEASE gd set exposure_reference_qty = ( select count(distinct reference_id) from pub2.GENE_DISEASE_REFERENCE gdr where gd.gene_id = gdr.gene_id and gd.disease_id = gdr.disease_id and reference_id in ( select id from pub2.REFERENCE r where has_exposures = true)); [ Date: 2024-04-25 00:10:57 - Bind query: yes ]
7	15m46s	/* * Run daily to prune LOG_QUERY, archive old queries to LOG_QUERY_ARCHIVE * and vacuum/analyze the tables. * * $Id: archive_query_logs.sql 10832 2012-03-19 15:27:11Z mcr $ / SELECT maint_query_logs_archive (); [ Date:* 2024-04-25 00:15:47 ]
8	9m2s	select pub2.maint_phenotype_term_derive_data (); [ Date: 2024-04-25 05:48:50 - Bind query: yes ]
9	2m41s	INSERT INTO pub2.TERM_REFERENCE (term_id, object_type_id, reference_id, ixn_type_id) SELECT DISTINCT term_id, object_type_id, reference_id, ixn_type_id FROM ( SELECT gene_id as term_id, ( SELECT object_type_id FROM pub2.TERM WHERE id = gene_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'CHEMICAL-GENE') as ixn_type_id FROM pub2.GENE_CHEM_REFERENCE UNION SELECT chem_id as term_id, ( SELECT object_type_id FROM pub2.TERM WHERE id = chem_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'CHEMICAL-GENE') as ixn_type_id FROM pub2.GENE_CHEM_REFERENCE UNION SELECT taxon_id as term_id, ( SELECT object_type_id FROM pub2.TERM WHERE id = taxon_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'CHEMICAL-GENE') as ixn_type_id FROM pub2.GENE_CHEM_REFERENCE WHERE taxon_id IS NOT NULL UNION SELECT chem_id as term_id, ( SELECT object_type_id FROM pub2.TERM WHERE id = chem_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'CHEMICAL-DISEASE') as ixn_type_id FROM pub2.CHEM_DISEASE_REFERENCE WHERE source_cd = 'C' UNION SELECT disease_id as term_id, ( SELECT object_type_id FROM pub2.TERM WHERE id = disease_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'CHEMICAL-DISEASE') as ixn_type_id FROM pub2.CHEM_DISEASE_REFERENCE WHERE source_cd = 'C' UNION SELECT gene_id as term_id, ( SELECT object_type_id FROM pub2.TERM WHERE id = gene_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'GENE-DISEASE') as ixn_type_id FROM pub2.GENE_DISEASE_REFERENCE WHERE source_cd = 'C' UNION SELECT disease_id as term_id, ( SELECT object_type_id FROM pub2.TERM WHERE id = disease_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'GENE-DISEASE') as ixn_type_id FROM pub2.GENE_DISEASE_REFERENCE WHERE source_cd = 'C' UNION SELECT ee.exp_marker_term_id as term_id, ( SELECT object_type_id FROM term WHERE id = exp_marker_term_id), e.reference_id, ( SELECT id FROM edit.ixn_type WHERE nm = 'EXPOSURE') as ixn_type_id FROM pub2.EXPOSURE e, pub2.EXP_EVENT ee WHERE e.exp_event_id = ee.id AND exp_marker_term_id IS NOT NULL UNION SELECT er.term_id, ( SELECT object_type_id FROM term WHERE id = er.term_id), e.reference_id, ( SELECT id FROM edit.ixn_type WHERE nm = 'EXPOSURE') as ixn_type_id FROM pub2.EXPOSURE e, pub2.EXP_RECEPTOR er WHERE e.exp_receptor_id = er.id AND er.term_id IS NOT NULL UNION SELECT chem_id as term_id, ( SELECT object_type_id FROM term WHERE id = chem_id), e.reference_id, ( SELECT id FROM edit.ixn_type WHERE nm = 'EXPOSURE') as ixn_type_id FROM pub2.EXPOSURE e, pub2.EXP_STRESSOR es WHERE e.exp_stressor_id = es.id AND chem_id IS NOT NULL UNION SELECT phenotype_id as term_id, ( SELECT object_type_id FROM term WHERE id = phenotype_id), e.reference_id, ( SELECT id FROM edit.ixn_type WHERE nm = 'EXPOSURE') as ixn_type_id FROM pub2.EXPOSURE e, pub2.EXP_OUTCOME eo WHERE e.exp_outcome_id = eo.id AND phenotype_id IS NOT NULL UNION SELECT disease_id as term_id, ( SELECT object_type_id FROM term WHERE id = disease_id), e.reference_id, ( SELECT id FROM edit.ixn_type WHERE nm = 'EXPOSURE') as ixn_type_id FROM pub2.EXPOSURE e, pub2.EXP_OUTCOME eo WHERE e.exp_outcome_id = eo.id AND disease_id IS NOT NULL UNION SELECT phenotype_id as term_id, ( SELECT object_type_id FROM pub2.TERM WHERE id = phenotype_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'PHENOTYPE') as ixn_type_id FROM pub2.PHENOTYPE_TERM_REFERENCE WHERE source_cd = 'C' UNION SELECT term_id, ( SELECT object_type_id FROM pub2.TERM WHERE id = term_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'PHENOTYPE') as ixn_type_id FROM pub2.PHENOTYPE_TERM_REFERENCE WHERE source_cd = 'C' UNION SELECT taxon_id as term_id, ( SELECT object_type_id FROM pub2.TERM WHERE id = taxon_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'PHENOTYPE') as ixn_type_id FROM pub2.PHENOTYPE_TERM_REFERENCE WHERE source_cd = 'C' AND taxon_id IS NOT NULL UNION SELECT from_gene_id as term_id, ( SELECT object_type_id FROM pub2.TERM WHERE id = from_gene_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'GENE-GENE') as ixn_type_id FROM pub2.GENE_GENE_REFERENCE UNION SELECT to_gene_id as term_id, ( SELECT object_type_id FROM pub2.TERM WHERE id = to_gene_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'GENE-GENE') as ixn_type_id FROM pub2.GENE_GENE_REFERENCE UNION SELECT from_taxon_id as term_id, ( SELECT object_type_id FROM pub2.TERM WHERE id = from_taxon_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'GENE-GENE') as ixn_type_id FROM pub2.GENE_GENE_REFERENCE UNION SELECT to_taxon_id as term_id, ( SELECT object_type_id FROM pub2.TERM WHERE id = to_taxon_id), reference_id, ( SELECT id FROM edit.IXN_TYPE WHERE nm = 'GENE-GENE') as ixn_type_id FROM pub2.GENE_GENE_REFERENCE UNION SELECT DISTINCT t.id as taxon_id, t.object_type_id as taxon_object_id, r.id as reference_id, i.ixn_type_id as ixn_type_id FROM edit.REFERENCE_IXN ri, pub2.TERM t, edit.IXN i, pub2.REFERENCE r WHERE ri.taxon_acc_txt = t.acc_txt AND t.object_type_id = ( SELECT id FROM pub2.OBJECT_TYPE WHERE cd = 'taxon') AND ri.ixn_id = i.root_id AND i.ixn_type_id in ( SELECT id FROM edit.IXN_TYPE WHERE nm in ('CHEMICAL-DISEASE', 'GENE-DISEASE')) AND ri.reference_acc_txt = r.acc_txt AND ri.taxon_acc_txt IS NOT NULL AND ri.taxon_acc_txt <> '') as test UNION select ea.anatomy_id as term_id, ( select id from object_type where cd = 'anatomy') as object_type_id, r.id as reference_id, ( select id from ixn_type where nm = 'EXPOSURE') as ixn_type_id from pub2.EXP_ANATOMY ea, pub2.EXP_OUTCOME eo, pub2.EXPOSURE e, pub2.REFERENCE r where ea.exp_outcome_id = eo.id and eo.id = e.exp_outcome_id and e.reference_acc_txt = r.acc_txt UNION select anatomy_id as term_id, ( select id from object_type where cd = 'anatomy') as object_type_id, r.id as reference_id, ( select id from ixn_type where nm = 'PHENOTYPE') as ixn_type_id from pub2.IXN i, pub2.IXN_ANATOMY ia, edit.REFERENCE_IXN ri, pub2.REFERENCE r where i.id = ri.ixn_id and ri.reference_acc_txt = r.acc_txt and i.id = ia.ixn_id UNION select medium_term_id as term_id, ( select id from object_type where cd = 'anatomy') as object_type_id, r.id, ( select id from ixn_type where nm = 'EXPOSURE') as ixn_type_id from pub2.EXP_EVENT ee, pub2.EXPOSURE e, pub2.REFERENCE r where ee.id = e.exp_event_id and e.reference_acc_txt = r.acc_txt and medium_term_id is not null; [ Date: 2024-04-25 01:52:40 - Bind query: yes ]
10	2m15s	SELECT maint_term_label_derive_nm_fts (); [ Date: 2024-04-25 02:56:50 - Bind query: yes ]
11	2m5s	update pub2.PHENOTYPE_TERM pt set exposure_reference_qty = ( select count(distinct reference_id) from pub2.PHENOTYPE_TERM_REFERENCE ptr where pt.phenotype_id = ptr.phenotype_id and pt.term_id = ptr.term_id and reference_id in ( select id from pub2.REFERENCE r where has_exposures = true)); [ Date: 2024-04-25 00:13:03 - Bind query: yes ]
12	1m5s	update pub2.IXN set ixn_xml = replace(ixn_xml, '''', '"'); [ Date: 2024-04-25 00:16:05 - Bind query: yes ]
13	1m4s	INSERT INTO pub2.GENE_GENE_REFERENCE (id, from_gene_id, to_gene_id, reference_id, from_taxon_id, to_taxon_id, src_db_id, experimental_sys_nm, experimental_sys_type) select ggr. id, ggr.from_gene_id, ggr.to_gene_id, l.object_id, ggr.from_taxon_id, ggr.to_taxon_id, ggr.src_db_id, ggr.experimental_sys_nm, ggr.experimental_sys_type from load.GENE_GENE_REFERENCE ggr, edit.DB_LINK l where ggr.reference_acc_txt = l.acc_txt and l.object_type_id = 7 and l.db_id = 16; [ Date: 2024-04-25 00:14:28 - Bind query: yes ]
14	59s17ms	CLUSTER pub2.TERM; [ Date: 2024-04-25 02:59:09 - Bind query: yes ]
15	50s420ms	select pub2.maint_reference_derive_flags (); [ Date: 2024-04-25 05:39:48 - Bind query: yes ]
16	34s196ms	SELECT /* DiseaseGeneAssnsDAO / d.nm diseaseNm, d.acc_txt diseaseAcc, d.acc_db_cd diseaseAccDbCd, d.id diseaseId, g.nm geneSymbol, g.acc_txt geneAcc, g.acc_db_cd geneAccDbCd, g.id geneId, gd.network_score networkScore, gd.indirect_chem_qty inferredCount, gd.reference_qty referenceCount, gd.exposure_reference_qty exposureReferenceCount, CASE WHEN gd.curated_reference_qty > 0 THEN ( SELECT STRING_AGG(a.action_type_cd \|\| '^' \|\| a.action_type_nm, '\|') FROM gene_disease_axn a WHERE a.gene_id = gd.gene_id AND a.disease_id = gd.disease_id) ELSE NULL END actionTypes FROM gene_disease gd INNER JOIN term g ON gd.gene_id = g.id INNER JOIN term d ON gd.disease_id = d.id WHERE gd.disease_id IN ( SELECT p.descendant_object_id FROM dag_path p WHERE p.ancestor_object_id = '2063367') ORDER BY actionTypes, gd.network_score DESC NULLS LAST, g.nm_sort, d.nm_sort; [ Date:* 2024-04-25 23:19:32 - Bind query: yes ]
17	32s580ms	CLUSTER pub2.TERM_LABEL; [ Date: 2024-04-25 02:59:42 - Bind query: yes ]
18	32s245ms	COPY pubc.log_query_archive (id, type_cd, query_tm, submission_qty, session_id, remote_addr, http_user_agent, server_nm, node_nm, results_qty, execution_ms, basic_query_type, basic_query_txt, gene_query_type, gene_txt, gene_form_type_txt, taxon_query_type, taxon_txt, chem_query_type, chem_txt, party_query_type, party_nm_txt, acc_txt, go_query_type, go_txt, disease_query_type, disease_txt, action_type_txt, action_degree_type_txt, from_yr, through_yr, title_abstract_txt, has_marray, gene_set_txt, molecule_type_txt, volume_txt, first_page_txt, journal_query_type, journal_txt, is_review, pathway_query_type, pathway_txt, dag_txt, results_format_txt, batch_input_type_txt, gd_assn_type, p_val, p_val_type, input_term_qty, review_status) TO stdout; [ Date: 2024-04-25 06:05:34 - Database: ctdprd51 - User: postgres - Application: pg_dump ]
19	32s7ms	COPY pubc.log_query_archive (id, type_cd, query_tm, submission_qty, session_id, remote_addr, http_user_agent, server_nm, node_nm, results_qty, execution_ms, basic_query_type, basic_query_txt, gene_query_type, gene_txt, gene_form_type_txt, taxon_query_type, taxon_txt, chem_query_type, chem_txt, party_query_type, party_nm_txt, acc_txt, go_query_type, go_txt, disease_query_type, disease_txt, action_type_txt, action_degree_type_txt, from_yr, through_yr, title_abstract_txt, has_marray, gene_set_txt, molecule_type_txt, volume_txt, first_page_txt, journal_query_type, journal_txt, is_review, pathway_query_type, pathway_txt, dag_txt, results_format_txt, batch_input_type_txt, gd_assn_type, p_val, p_val_type, input_term_qty, review_status) TO stdout; [ Date: 2024-04-25 14:05:34 - Database: ctdprd51 - User: postgres - Application: pg_dump ]
20	32s4ms	COPY pubc.log_query_archive (id, type_cd, query_tm, submission_qty, session_id, remote_addr, http_user_agent, server_nm, node_nm, results_qty, execution_ms, basic_query_type, basic_query_txt, gene_query_type, gene_txt, gene_form_type_txt, taxon_query_type, taxon_txt, chem_query_type, chem_txt, party_query_type, party_nm_txt, acc_txt, go_query_type, go_txt, disease_query_type, disease_txt, action_type_txt, action_degree_type_txt, from_yr, through_yr, title_abstract_txt, has_marray, gene_set_txt, molecule_type_txt, volume_txt, first_page_txt, journal_query_type, journal_txt, is_review, pathway_query_type, pathway_txt, dag_txt, results_format_txt, batch_input_type_txt, gd_assn_type, p_val, p_val_type, input_term_qty, review_status) TO stdout; [ Date: 2024-04-25 10:05:34 - Database: ctdprd51 - User: postgres - Application: pg_dump ]

Time consuming queries (N)


Date: 2024-04-25 05:38:37
Duration: 1h53m13s
Bind query: yes
Date: 2024-04-25 01:49:58
Duration: 1h33m49s
Bind query: yes
Date: 2024-04-25 02:54:24
Duration: 1h1m35s
Bind query: yes
Date: 2024-04-25 03:44:59
Duration: 45m15s
Bind query: yes
Date: 2024-04-25 06:23:18
Duration: 34m27s
Bind query: yes
Date: 2024-04-25 00:10:57
Duration: 24m9s
Bind query: yes
Date: 2024-04-25 00:15:47
Duration: 15m46s
[ User: pubeu - Total duration: 4m16s - Times executed: 74 ]
Date: 2024-04-25 05:48:50
Duration: 9m2s
Bind query: yes
[ User: pubeu - Total duration: 1m26s - Times executed: 28 ]
[ User: pubeu - Total duration: 1m56s - Times executed: 30 ]
[ User: pubeu - Total duration: 1m20s - Times executed: 15 ]
Date: 2024-04-25 01:52:40
Duration: 2m41s
Bind query: yes
Date: 2024-04-25 02:56:50
Duration: 2m15s
Bind query: yes
[ User: postgres - Total duration: 2m8s - Times executed: 4 ]
Date: 2024-04-25 00:13:03
Duration: 2m5s
Bind query: yes
[ User: pubeu - Total duration: 23s321ms - Times executed: 22 ]
[ User: pubeu - Total duration: 23s528ms - Times executed: 6 ]
Date: 2024-04-25 00:16:05
Duration: 1m5s
Bind query: yes
Date: 2024-04-25 00:14:28
Duration: 1m4s
Bind query: yes

Most frequent queries (N)


[ User: pubeu - Total duration: 4m16s - Times executed: 74 ]
[ User: pubeu - Total duration: 1m26s - Times executed: 28 ]
[ User: pubeu - Total duration: 23s321ms - Times executed: 22 ]
[ User: pubeu - Total duration: 1m56s - Times executed: 30 ]
[ User: pubeu - Total duration: 1m20s - Times executed: 15 ]
[ User: pubeu - Total duration: 9s659ms - Times executed: 8 ]
[ User: pubeu - Total duration: 14s41ms - Times executed: 10 ]
[ User: pubeu - Total duration: 8s238ms - Times executed: 6 ]
[ User: pubeu - Total duration: 9s405ms - Times executed: 8 ]
[ User: pubeu - Total duration: 23s367ms - Times executed: 5 ]
[ User: pubeu - Total duration: 15s584ms - Times executed: 7 ]
[ User: pubeu - Total duration: 15s244ms - Times executed: 7 ]
[ User: pubeu - Total duration: 23s528ms - Times executed: 6 ]
[ User: pubeu - Total duration: 11s488ms - Times executed: 4 ]
[ User: pubeu - Total duration: 5s520ms - Times executed: 4 ]
[ User: pubeu - Total duration: 6s344ms - Times executed: 2 ]
[ User: pubeu - Total duration: 3s836ms - Times executed: 3 ]
[ User: qaeu - Total duration: 4s37ms - Times executed: 3 ]
[ User: pubeu - Total duration: 5s741ms - Times executed: 1 ]
[ User: pubeu - Total duration: 1s552ms - Times executed: 1 ]

Normalized slowest queries (N)


Date: 2024-04-25 05:38:37
Duration: 1h53m13s
Bind query: yes
Date: 2024-04-25 01:49:58
Duration: 1h33m49s
Bind query: yes
Date: 2024-04-25 02:54:24
Duration: 1h1m35s
Bind query: yes
Date: 2024-04-25 03:44:59
Duration: 45m15s
Bind query: yes
Date: 2024-04-25 06:23:18
Duration: 34m27s
Bind query: yes
Date: 2024-04-25 00:10:57
Duration: 24m9s
Bind query: yes
Date: 2024-04-25 00:15:47
Duration: 15m46s
Date: 2024-04-25 05:48:50
Duration: 9m2s
Bind query: yes
Date: 2024-04-25 01:52:40
Duration: 2m41s
Bind query: yes
Date: 2024-04-25 02:56:50
Duration: 2m15s
Bind query: yes
Date: 2024-04-25 00:13:03
Duration: 2m5s
Bind query: yes
Date: 2024-04-25 00:16:05
Duration: 1m5s
Bind query: yes
Date: 2024-04-25 00:14:28
Duration: 1m4s
Bind query: yes
[ User: postgres - Total duration: 2m8s - Times executed: 4 ]
[ User: pubeu - Total duration: 5s741ms - Times executed: 1 ]
[ User: pubeu - Total duration: 23s528ms - Times executed: 6 ]
[ User: pubeu - Total duration: 1m20s - Times executed: 15 ]
[ User: pubeu - Total duration: 23s367ms - Times executed: 5 ]
[ User: pubeu - Total duration: 1m56s - Times executed: 30 ]
[ User: pubeu - Total duration: 6s344ms - Times executed: 2 ]

Time consuming prepare

Rank	Total duration	Times executed	Min duration	Max duration	Avg duration	Query
NO DATASET

Time consuming bind

Rank

Total duration

Times executed

Min duration

Max duration

Avg duration

Query

1

1s906ms

1

Details

1s906ms

select distinct ptr.phenotype_id, ptr.term_id, i.action_type_nm, i.action_type_cd, i.action_degree_type_nm, i.action_degree_type_cd from pub2.PHENOTYPE_TERM_REFERENCE ptr, pub2.IXN_AXN i where ptr.ixn_id = i.ixn_id;

Times Reported Time consuming bind #1

Day	Hour	Count	Duration	Avg duration
Apr 25	23	1	1s906ms	1s906ms

select distinct ptr.phenotype_id, ptr.term_id, i.action_type_nm, i.action_type_cd, i.action_degree_type_nm, i.action_degree_type_cd from pub2.PHENOTYPE_TERM_REFERENCE ptr, pub2.IXN_AXN i where ptr.ixn_id = i.ixn_id;

Date: 2024-04-24 23:39:37 Duration: 1s906ms Database: postgres

2

0ms

23

Details

0ms

;

Times Reported Time consuming bind #2

Day	Hour	Count	Duration	Avg duration
Apr 24	06	2	0ms	0ms
	07	3	0ms	0ms
	08	2	0ms	0ms
	10	2	0ms	0ms
Apr 25	04	1	0ms	0ms
	05	4	0ms	0ms
	07	1	0ms	0ms
	08	1	0ms	0ms
	10	2	0ms	0ms
	11	3	0ms	0ms
	13	1	0ms	0ms
	16	1	0ms	0ms

[ User: pubeu - Total duration: 43s66ms - Times executed: 13 ]

;

Date: Duration: 0ms Database: postgres parameters: $1 = '1420577', $2 = '1420577', $3 = 'chem%'

Log levels

Key values

15,163 Log entries

Chart
Table

Type	Count	Percentage
CONTEXT	1,629	10.74%
DETAIL	706	4.66%
ERROR	34	0.22%
HINT	2	0.01%
LOG	10,488	69.17%
STATEMENT	1,005	6.63%
WARNING	1,299	8.57%

Events distribution

Key values

0 PANIC entries
0 FATAL entries
34 ERROR entries
1299 WARNING entries

Most Frequent Errors/Events

Key values

1,026 Max number of times the same event was reported
1,333 Total events found

Rank

Times reported

Error

1

1,026

Details

WARNING: skipping "..." --- only table or database owner can vacuum it

Times Reported Most Frequent Error / Event #1

Day	Hour	Count
Apr 25	02	1,026

2

224

Details

WARNING: skipping "..." --- only superuser or database owner can vacuum it

Times Reported Most Frequent Error / Event #2

Day	Hour	Count
Apr 25	02	224

3

43

Details

WARNING: skipping "..." --- only superuser can vacuum it

Times Reported Most Frequent Error / Event #3

Day	Hour	Count
Apr 25	02	43

4

16

Details

LOG: could not receive data from client: Connection timed out

Times Reported Most Frequent Error / Event #4

Day	Hour	Count
Apr 25	19	1
	20	9
	21	6

5

8

Details

ERROR: column "..." does not exist

Times Reported Most Frequent Error / Event #5

Day	Hour	Count
Apr 25	10	8

ERROR: column "exp_event_id" does not exist at character 59
ERROR: column "exp_stressor_id" does not exist at character 93
ERROR: column "exp_event_id" does not exist at character 93

6

Details

WARNING: there is no transaction in progress

Times Reported Most Frequent Error / Event #6

Day	Hour	Count
Apr 25	02	2
	05	4

7

4

Details

ERROR: relation "..." does not exist

Times Reported Most Frequent Error / Event #7

Day	Hour	Count
Apr 25	10	4

ERROR: relation "exposure" does not exist at character 15
ERROR: relation "edit.event" does not exist at character 80
ERROR: relation "exposure" does not exist at character 40

8

2

Details

ERROR: column reference "..." is ambiguous

Times Reported Most Frequent Error / Event #8

Day	Hour	Count
Apr 25	10	2

ERROR: column reference "id" is ambiguous at character 70
ERROR: column reference "id" is ambiguous at character 107

9

2

Details

ERROR: syntax error at or near "..."

Times Reported Most Frequent Error / Event #9

Day	Hour	Count
Apr 25	10	2

ERROR: syntax error at or near "between" at character 49
ERROR: syntax error at or near "select" at character 146

10

1

Details

ERROR: function get_ixn_prose(...) does not exist

Times Reported Most Frequent Error / Event #10

Day	Hour	Count
Apr 25	10	1

ERROR: function get_ixn_prose(integer) does not exist at character 8

11

1

Details

ERROR: missing FROM-clause entry for table "..."

Times Reported Most Frequent Error / Event #11

Day	Hour	Count
Apr 25	10	1

ERROR: missing FROM-clause entry for table "i" at character 23

Global information

Overview

Global Stats

SQL Traffic

Key values

SELECT Traffic

Key values

INSERT/UPDATE/DELETE Traffic

Key values

Queries duration

Key values

Prepared queries ratio

Key values

General Activity

Connections

Established Connections

Key values

Connections per database

Key values

Connections per user

Key values

Connections per host

Key values

Sessions

Simultaneous sessions

Key values

Histogram of session times

Key values

Sessions per database

Key values

Sessions per user

Key values

Sessions per host

Key values

Sessions per application

Key values

Checkpoints / Restartpoints

Checkpoints Buffers

Key values

Checkpoints Wal files

Key values

Checkpoints distance

Key values

Checkpoints Activity

Checkpoint causes

Key values

Temporary Files

Size of temporary files

Key values

Number of temporary files

Key values

Temporary Files Activity

Queries generating the most temporary files (N)

Queries generating the largest temporary files

Vacuums

Vacuums / Analyzes Distribution

Key values

Average Autovacuum Duration

Key values

Analyzes per table

Key values

Vacuums per table

Key values

Tuples removed per table

Key values

Pages removed per table

Key values

Pages removed per tables

Autovacuum Activity

Locks

Locks by types

Key values

Type of locks

Most frequent waiting queries (N)

Queries that waited the most

Queries

Queries by type

Key values

Queries by database

Key values