#
exclusive
inclusive
rows x
rows
loops
read
written
node
1.
0.007
4,003.546
↓
22.0
22
1
0
0
→
Limit
(cost=998.53..998.58
rows=1
width=232)
(actual
time=4,003.365..4,003.546
rows=22
loops=1)
Memory: used=462kB allocated=512kB
Serialization: time=0.210 ms output=14kB format=text
Buffers: shared hit=478,049 read=418, temp read=34,033 written=34,037
I/O Timings: shared read=1.346, temp read=251.850 write=513.695
2.
0.179
4,003.539
↓
22.0
22
1
0
0
→
GroupAggregate
(cost=998.53..998.58
rows=1
width=232)
(actual
time=4,003.363..4,003.539
rows=22
loops=1)
Group Key: (sum("*SELECT* 1".occurences)), "*SELECT* 1".qualid, (sum("*SELECT* 1".execution_count)), (((to_json(pqnh.quals)))::jsonb), ((sum("*SELECT* 1".nbfiltered) / sum("*SELECT* 1".occurences))), (CASE WHEN (sum("*SELECT* 1".execution_count) = '0'::numeric) THEN '0'::numeric ELSE ((sum("*SELECT* 1".nbfiltered) / sum("*SELECT* 1".execution_count)) * '100'::numeric) END)
Buffers: shared hit=478,049 read=418, temp read=34,033 written=34,037
I/O Timings: shared read=1.346, temp read=251.850 write=513.695
3.
0.369
4,003.360
↓
47.0
47
1
0
0
→
Sort
(cost=998.53..998.54
rows=1
width=724)
(actual
time=4,003.342..4,003.360
rows=47
loops=1)
Sort Key: (sum("*SELECT* 1".occurences)) DESC, "*SELECT* 1".qualid, (sum("*SELECT* 1".execution_count)), (((to_json(pqnh.quals)))::jsonb), ((sum("*SELECT* 1".nbfiltered) / sum("*SELECT* 1".occurences))), (CASE WHEN (sum("*SELECT* 1".execution_count) = '0'::numeric) THEN '0'::numeric ELSE ((sum("*SELECT* 1".nbfiltered) / sum("*SELECT* 1".execution_count)) * '100'::numeric) END)
Sort Method: quicksort Memory: 44kB
Buffers: shared hit=478,049 read=418, temp read=34,033 written=34,037
I/O Timings: shared read=1.346, temp read=251.850 write=513.695
4.
0.542
4,002.991
↓
47.0
47
1
0
0
→
Nested Loop
(cost=993.94..998.52
rows=1
width=724)
(actual
time=2,653.876..4,002.991
rows=47
loops=1)
Buffers: shared hit=478,038 read=418, temp read=34,033 written=34,037
I/O Timings: shared read=1.346, temp read=251.850 write=513.695
5.
411.976
4,001.745
↓
64.0
64
1
0
0
→
GroupAggregate
(cost=993.79..993.91
rows=1
width=764)
(actual
time=2,653.641..4,001.745
rows=64
loops=1)
Group Key: "*SELECT* 1".qualid, ps.queryid, ps.dbid, ps.query, pqnh.quals
Filter: (((sum("*SELECT* 1".nbfiltered) / sum("*SELECT* 1".occurences)) > '1000'::numeric) AND (CASE WHEN (sum("*SELECT* 1".execution_count) = '0'::numeric) THEN '0'::numeric ELSE ((sum("*SELECT* 1".nbfiltered) / sum("*SELECT* 1".execution_count)) * '100'::numeric) END > 0.3))
Rows Removed by Filter: 67
Buffers: shared hit=477,910 read=418, temp read=34,033 written=34,037
I/O Timings: shared read=1.346, temp read=251.850 write=513.695
6.
2,867.985
3,589.769
↓
177,397.5
354,795
1
266 MB
266 MB
→
Sort
(cost=993.79..993.79
rows=2
width=628)
(actual
time=2,653.484..3,589.769
rows=354,795
loops=1)
Sort Key: "*SELECT* 1".qualid, ps.queryid, ps.dbid, ps.query, pqnh.quals
Sort Method: external merge Disk: 272,264kB
Buffers: shared hit=477,907 read=418, temp read=34,033 written=34,037
I/O Timings: shared read=1.346, temp read=251.850 write=513.695
7.
157.318
721.784
↓
177,397.5
354,795
1
0
0
→
Nested Loop
(cost=33.32..993.78
rows=2
width=628)
(actual
time=1.252..721.784
rows=354,795
loops=1)
Buffers: shared hit=477,896 read=418
I/O Timings: shared read=1.346
8.
44.014
75.762
↓
61,088.0
61,088
1
0
0
→
Hash Join
(cost=33.04..992.37
rows=1
width=84)
(actual
time=1.203..75.762
rows=61,088
loops=1)
Hash Cond: (("*SELECT* 1".queryid = pqnh.queryid) AND ("*SELECT* 1".qualid = pqnh.qualid))
Buffers: shared hit=274 read=418
I/O Timings: shared read=1.346
9.
3.008
30.874
↓
8.6
15,679
1
0
0
→
Append
(cost=5.62..951.28
rows=1,822
width=44)
(actual
time=0.283..30.874
rows=15,679
loops=1)
Buffers: shared hit=185 read=418
I/O Timings: shared read=1.346
10.
3.494
22.853
↓
293.0
13,771
1
0
0
→
Subquery Scan
on *SELECT* 1
(cost=5.62..346.41
rows=47
width=44)
(actual
time=0.282..22.853
rows=13,771
loops=1)
11.
9.755
19.359
↓
293.0
13,771
1
0
0
→
Subquery Scan
on unnested
(cost=5.62..345.94
rows=47
width=76)
(actual
time=0.280..19.359
rows=13,771
loops=1)
Filter: (((unnested.records).ts >= '2025-01-17 16:52:15+01'::timestamp with time zone) AND ((unnested.records).ts <= '2025-01-17 19:52:15+01'::timestamp with time zone))
Rows Removed by Filter: 2,775
Buffers: shared hit=92
12.
8.916
9.604
↓
1.8
16,546
1
0
0
→
ProjectSet
(cost=5.62..206.02
rows=9,328
width=92)
(actual
time=0.274..9.604
rows=16,546
loops=1)
13.
0.451
0.688
↓
1.2
257
1
0
0
→
Bitmap Heap Scan
on powa_qualstats_quals_history pqnh_1
(cost=5.62..157.79
rows=212
width=648)
(actual
time=0.267..0.688
rows=257
loops=1)
Recheck Cond: ((srvid = 1) AND (coalesce_range && '["2025-01-17 16:52:15+01","2025-01-17 19:52:15+01"]'::tstzrange))
Heap Blocks: exact=41
Buffers: shared hit=57
14.
0.237
0.237
↓
1.2
257
1
0
0
→
Bitmap Index Scan
on powa_qualstats_quals_history_query_ts
(cost=0.00..5.56
rows=212
width=0)
(actual
time=0.237..0.237
rows=257
loops=1)
Index Cond: ((srvid = 1) AND (coalesce_range && '["2025-01-17 16:52:15+01","2025-01-17 19:52:15+01"]'::tstzrange))
Buffers: shared hit=16
15.
0.456
5.013
↓
1.1
1,908
1
0
0
→
Subquery Scan
on *SELECT* 2
(cost=0.00..595.76
rows=1,775
width=44)
(actual
time=0.276..5.013
rows=1,908
loops=1)
Buffers: shared hit=93 read=418
I/O Timings: shared read=1.346
16.
4.557
4.557
↓
1.1
1,908
1
3.3 MB
0
→
Seq Scan
on powa_qualstats_quals_history_current pqnc
(cost=0.00..578.01
rows=1,775
width=76)
(actual
time=0.275..4.557
rows=1,908
loops=1)
Filter: ((ts >= '2025-01-17 16:52:15+01'::timestamp with time zone) AND (ts <= '2025-01-17 19:52:15+01'::timestamp with time zone) AND (srvid = 1))
Rows Removed by Filter: 2,512
Buffers: shared hit=93 read=418
I/O Timings: shared read=1.346
17.
0.083
0.874
↓
118.0
118
1
0
0
→
Hash
(cost=27.40..27.40
rows=1
width=52)
(actual
time=0.870..0.874
rows=118
loops=1)
Buckets: 1,024 Batches: 1 Memory Usage: 23kB
Buffers: shared hit=89
18.
0.027
0.791
↓
118.0
118
1
0
0
→
Subquery Scan
on pqnh
(cost=27.36..27.40
rows=1
width=52)
(actual
time=0.575..0.791
rows=118
loops=1)
19.
0.185
0.764
↓
118.0
118
1
0
0
→
GroupAggregate
(cost=27.36..27.39
rows=1
width=60)
(actual
time=0.575..0.764
rows=118
loops=1)
Group Key: expanded.qualid, expanded.queryid, expanded.dbid, expanded.userid
Buffers: shared hit=89
20.
0.151
0.579
↓
144.0
144
1
0
0
→
Sort
(cost=27.36..27.37
rows=1
width=60)
(actual
time=0.564..0.579
rows=144
loops=1)
Sort Key: expanded.qualid, expanded.queryid, expanded.dbid, expanded.userid
Sort Method: quicksort Memory: 36kB
Buffers: shared hit=89
21.
0.061
0.428
↓
144.0
144
1
0
0
→
Subquery Scan
on expanded
(cost=0.28..27.35
rows=1
width=60)
(actual
time=0.047..0.428
rows=144
loops=1)
Filter: ((expanded.qual).eval_type = 'f'::"char")
Rows Removed by Filter: 118
Buffers: shared hit=89
22.
0.197
0.367
↓
1.3
262
1
0
0
→
ProjectSet
(cost=0.28..24.90
rows=196
width=60)
(actual
time=0.044..0.367
rows=262
loops=1)
23.
0.170
0.170
↑
1.0
196
1
0
0
→
Index Scan
using powa_qualstats_quals_srvid_queryid_idx on powa_qualstats_quals
(cost=0.28..22.45
rows=196
width=106)
(actual
time=0.034..0.170
rows=196
loops=1)
Index Cond: (srvid = 1)
Buffers: shared hit=89
24.
488.704
488.704
↓
3.0
366,528
61,088
0
0
→
Index Scan
using powa_statements_pkey on powa_statements ps
(cost=0.29..1.39
rows=2
width=564)
(actual
time=0.004..0.008
rows=6
loops=61,088)
Index Cond: ((srvid = 1) AND (queryid = "*SELECT* 1".queryid))
Buffers: shared hit=477,622
25.
0.704
0.704
↑
1.0
64
64
0
0
→
Index Scan
using powa_databases_pkey on powa_databases pd
(cost=0.15..2.37
rows=1
width=8)
(actual
time=0.011..0.011
rows=1
loops=64)
Index Cond: ((srvid = 1) AND (oid = ps.dbid))
Filter: (datname = 'tpc'::name)
Rows Removed by Filter: 0
Buffers: shared hit=128
Planning I/O
:
Buffers: shared hit=557
Planning time
:
4.972 ms
Execution time
:
4,077.035 ms
Custom settings:
effective_cache_size
?
500MB
effective_io_concurrency
?
200
jit_above_cost
?
5000
maintenance_io_concurrency
?
400
random_page_cost
?
1.1
work_mem
?
64MB
Limit (cost=998.53..998.58 rows=1 width=232) (actual time=4003.365..4003.546 rows=22 loops=1)
Buffers: shared hit=478049 read=418, temp read=34033 written=34037
I/O Timings: shared read=1.346, temp read=251.850 write=513.695
-> GroupAggregate (cost=998.53..998.58 rows=1 width=232) (actual time=4003.363..4003.539 rows=22 loops=1)
Group Key: (sum("*SELECT* 1".occurences)), "*SELECT* 1".qualid, (sum("*SELECT* 1".execution_count)), (((to_json(pqnh.quals)))::jsonb), ((sum("*SELECT* 1".nbfiltered) / sum("*SELECT* 1".occurences))), (CASE WHEN (sum("*SELECT* 1".execution_count) = '0'::numeric) THEN '0'::numeric ELSE ((sum("*SELECT* 1".nbfiltered) / sum("*SELECT* 1".execution_count)) * '100'::numeric) END)
Buffers: shared hit=478049 read=418, temp read=34033 written=34037
I/O Timings: shared read=1.346, temp read=251.850 write=513.695
-> Sort (cost=998.53..998.54 rows=1 width=724) (actual time=4003.342..4003.360 rows=47 loops=1)
Sort Key: (sum("*SELECT* 1".occurences)) DESC, "*SELECT* 1".qualid, (sum("*SELECT* 1".execution_count)), (((to_json(pqnh.quals)))::jsonb), ((sum("*SELECT* 1".nbfiltered) / sum("*SELECT* 1".occurences))), (CASE WHEN (sum("*SELECT* 1".execution_count) = '0'::numeric) THEN '0'::numeric ELSE ((sum("*SELECT* 1".nbfiltered) / sum("*SELECT* 1".execution_count)) * '100'::numeric) END)
Sort Method: quicksort Memory: 44kB
Buffers: shared hit=478049 read=418, temp read=34033 written=34037
I/O Timings: shared read=1.346, temp read=251.850 write=513.695
-> Nested Loop (cost=993.94..998.52 rows=1 width=724) (actual time=2653.876..4002.991 rows=47 loops=1)
Buffers: shared hit=478038 read=418, temp read=34033 written=34037
I/O Timings: shared read=1.346, temp read=251.850 write=513.695
-> GroupAggregate (cost=993.79..993.91 rows=1 width=764) (actual time=2653.641..4001.745 rows=64 loops=1)
Group Key: "*SELECT* 1".qualid, ps.queryid, ps.dbid, ps.query, pqnh.quals
Filter: (((sum("*SELECT* 1".nbfiltered) / sum("*SELECT* 1".occurences)) > '1000'::numeric) AND (CASE WHEN (sum("*SELECT* 1".execution_count) = '0'::numeric) THEN '0'::numeric ELSE ((sum("*SELECT* 1".nbfiltered) / sum("*SELECT* 1".execution_count)) * '100'::numeric) END > 0.3))
Rows Removed by Filter: 67
Buffers: shared hit=477910 read=418, temp read=34033 written=34037
I/O Timings: shared read=1.346, temp read=251.850 write=513.695
-> Sort (cost=993.79..993.79 rows=2 width=628) (actual time=2653.484..3589.769 rows=354795 loops=1)
Sort Key: "*SELECT* 1".qualid, ps.queryid, ps.dbid, ps.query, pqnh.quals
Sort Method: external merge Disk: 272264kB
Buffers: shared hit=477907 read=418, temp read=34033 written=34037
I/O Timings: shared read=1.346, temp read=251.850 write=513.695
-> Nested Loop (cost=33.32..993.78 rows=2 width=628) (actual time=1.252..721.784 rows=354795 loops=1)
Buffers: shared hit=477896 read=418
I/O Timings: shared read=1.346
-> Hash Join (cost=33.04..992.37 rows=1 width=84) (actual time=1.203..75.762 rows=61088 loops=1)
Hash Cond: (("*SELECT* 1".queryid = pqnh.queryid) AND ("*SELECT* 1".qualid = pqnh.qualid))
Buffers: shared hit=274 read=418
I/O Timings: shared read=1.346
-> Append (cost=5.62..951.28 rows=1822 width=44) (actual time=0.283..30.874 rows=15679 loops=1)
Buffers: shared hit=185 read=418
I/O Timings: shared read=1.346
-> Subquery Scan on "*SELECT* 1" (cost=5.62..346.41 rows=47 width=44) (actual time=0.282..22.853 rows=13771 loops=1)
Buffers: shared hit=92
-> Subquery Scan on unnested (cost=5.62..345.94 rows=47 width=76) (actual time=0.280..19.359 rows=13771 loops=1)
Filter: (((unnested.records).ts >= '2025-01-17 16:52:15+01'::timestamp with time zone) AND ((unnested.records).ts <= '2025-01-17 19:52:15+01'::timestamp with time zone))
Rows Removed by Filter: 2775
Buffers: shared hit=92
-> ProjectSet (cost=5.62..206.02 rows=9328 width=92) (actual time=0.274..9.604 rows=16546 loops=1)
Buffers: shared hit=92
-> Bitmap Heap Scan on powa_qualstats_quals_history pqnh_1 (cost=5.62..157.79 rows=212 width=648) (actual time=0.267..0.688 rows=257 loops=1)
Recheck Cond: ((srvid = 1) AND (coalesce_range && '["2025-01-17 16:52:15+01","2025-01-17 19:52:15+01"]'::tstzrange))
Heap Blocks: exact=41
Buffers: shared hit=57
-> Bitmap Index Scan on powa_qualstats_quals_history_query_ts (cost=0.00..5.56 rows=212 width=0) (actual time=0.237..0.237 rows=257 loops=1)
Index Cond: ((srvid = 1) AND (coalesce_range && '["2025-01-17 16:52:15+01","2025-01-17 19:52:15+01"]'::tstzrange))
Buffers: shared hit=16
-> Subquery Scan on "*SELECT* 2" (cost=0.00..595.76 rows=1775 width=44) (actual time=0.276..5.013 rows=1908 loops=1)
Buffers: shared hit=93 read=418
I/O Timings: shared read=1.346
-> Seq Scan on powa_qualstats_quals_history_current pqnc (cost=0.00..578.01 rows=1775 width=76) (actual time=0.275..4.557 rows=1908 loops=1)
Filter: ((ts >= '2025-01-17 16:52:15+01'::timestamp with time zone) AND (ts <= '2025-01-17 19:52:15+01'::timestamp with time zone) AND (srvid = 1))
Rows Removed by Filter: 2512
Buffers: shared hit=93 read=418
I/O Timings: shared read=1.346
-> Hash (cost=27.40..27.40 rows=1 width=52) (actual time=0.870..0.874 rows=118 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 23kB
Buffers: shared hit=89
-> Subquery Scan on pqnh (cost=27.36..27.40 rows=1 width=52) (actual time=0.575..0.791 rows=118 loops=1)
Buffers: shared hit=89
-> GroupAggregate (cost=27.36..27.39 rows=1 width=60) (actual time=0.575..0.764 rows=118 loops=1)
Group Key: expanded.qualid, expanded.queryid, expanded.dbid, expanded.userid
Buffers: shared hit=89
-> Sort (cost=27.36..27.37 rows=1 width=60) (actual time=0.564..0.579 rows=144 loops=1)
Sort Key: expanded.qualid, expanded.queryid, expanded.dbid, expanded.userid
Sort Method: quicksort Memory: 36kB
Buffers: shared hit=89
-> Subquery Scan on expanded (cost=0.28..27.35 rows=1 width=60) (actual time=0.047..0.428 rows=144 loops=1)
Filter: ((expanded.qual).eval_type = 'f'::"char")
Rows Removed by Filter: 118
Buffers: shared hit=89
-> ProjectSet (cost=0.28..24.90 rows=196 width=60) (actual time=0.044..0.367 rows=262 loops=1)
Buffers: shared hit=89
-> Index Scan using powa_qualstats_quals_srvid_queryid_idx on powa_qualstats_quals (cost=0.28..22.45 rows=196 width=106) (actual time=0.034..0.170 rows=196 loops=1)
Index Cond: (srvid = 1)
Buffers: shared hit=89
-> Index Scan using powa_statements_pkey on powa_statements ps (cost=0.29..1.39 rows=2 width=564) (actual time=0.004..0.008 rows=6 loops=61088)
Index Cond: ((srvid = 1) AND (queryid = "*SELECT* 1".queryid))
Buffers: shared hit=477622
-> Index Scan using powa_databases_pkey on powa_databases pd (cost=0.15..2.37 rows=1 width=8) (actual time=0.011..0.011 rows=1 loops=64)
Index Cond: ((srvid = 1) AND (oid = ps.dbid))
Filter: (datname = 'tpc'::name)
Rows Removed by Filter: 0
Buffers: shared hit=128
Settings: work_mem = '64MB', effective_io_concurrency = '200', maintenance_io_concurrency = '400', random_page_cost = '1.1', effective_cache_size = '500MB', jit_above_cost = '5000'
Planning:
Buffers: shared hit=557
Memory: used=462kB allocated=512kB
Planning Time: 4.972 ms
Serialization: time=0.210 ms output=14kB format=text
Execution Time: 4077.035 ms
(95 rows)
Copy source to clipboard
I have one hint for you:
You have sort node (#6) that is using disk space to sort.
This is because your work_mem setting is too low.
Increasing it can make the sort run in memory, or, at least, use less of disk. Sort used 272264kB, so you would need to set your work_mem to at least that much to have a chance at sorting in memory only.
SELECT array_agg(cast(queryid AS text)) AS queryids, qualid, quals::jsonb AS quals, occurences, execution_count, array_agg(query) AS queries, avg_filter, filter_ratio
FROM (
SELECT
ps.srvid, qualid, ps.queryid, query, ps.dbid,
to_json(quals) AS quals,
sum(execution_count) AS execution_count,
sum(occurences) AS occurences,
(sum(nbfiltered) / sum(occurences)) AS avg_filter,
CASE
WHEN sum(execution_count) = 0 THEN 0
ELSE sum(nbfiltered) / sum(execution_count)::numeric * 100
END AS filter_ratio
FROM
(
SELECT srvid, qualid, queryid, dbid, userid, (unnested.records).*
FROM (
SELECT pqnh.srvid, pqnh.qualid, pqnh.queryid, pqnh.dbid, pqnh.userid,
pqnh.coalesce_range, unnest(records) AS records
FROM public.powa_qualstats_quals_history pqnh
WHERE coalesce_range && tstzrange('2025-01-17 16:52:15+0100', '2025-01-17 19:52:15+0100', '[]')
AND pqnh.srvid = '1'
) AS unnested
WHERE (records).ts <@ tstzrange('2025-01-17 16:52:15+0100', '2025-01-17 19:52:15+0100', '[]')
UNION ALL
SELECT pqnc.srvid, qualid, queryid, dbid, userid, pqnc.ts, pqnc.occurences,
pqnc.execution_count, pqnc.nbfiltered,
pqnc.mean_err_estimate_ratio, pqnc.mean_err_estimate_num
FROM public.powa_qualstats_quals_history_current pqnc
WHERE pqnc.ts <@ tstzrange('2025-01-17 16:52:15+0100', '2025-01-17 19:52:15+0100', '[]')
AND pqnc.srvid = '1'
) h
JOIN (
SELECT srvid, qualid, queryid, dbid, userid, array_agg(qual) AS quals
FROM (
SELECT srvid, qualid, queryid, dbid, userid, unnest(quals) AS qual
FROM public.powa_qualstats_quals
) expanded
WHERE (qual).eval_type = 'f'
GROUP BY srvid, qualid, queryid, dbid, userid
) AS pqnh USING (srvid, queryid, qualid)
JOIN public.powa_statements ps USING(queryid, srvid)
WHERE h.srvid = '1'
GROUP BY ps.srvid, qualid, ps.queryid, ps.dbid, ps.query, quals
) AS sub
JOIN public.powa_databases pd ON pd.oid = sub.dbid
AND pd.srvid = sub.srvid
WHERE pd.datname = 'tpc'
AND pd.srvid = '1'
AND sub.avg_filter > 1000
AND sub.filter_ratio > 0.3
GROUP BY sub.qualid, sub.execution_count, sub.occurences,
sub.quals::jsonb, sub.avg_filter, sub.filter_ratio
ORDER BY sub.occurences DESC
LIMIT 200 Copy query to clipboard
SELECT
array_agg(cast(queryid AS text)) AS queryids,
qualid,
quals::jsonb AS quals,
occurences,
execution_count,
array_agg(query) AS queries,
avg_filter,
filter_ratio
FROM (
SELECT
ps.srvid,
qualid,
ps.queryid,
query,
ps.dbid,
to_json(quals) AS quals,
sum(execution_count) AS execution_count,
sum(occurences) AS occurences,
(sum(nbfiltered) / sum(occurences)) AS avg_filter,
CASE WHEN sum(execution_count) = 0 THEN
0
ELSE
sum(nbfiltered) / sum(execution_count)::numeric * 100
END AS filter_ratio
FROM (
SELECT
srvid,
qualid,
queryid,
dbid,
userid,
(unnested.records).*
FROM (
SELECT
pqnh.srvid,
pqnh.qualid,
pqnh.queryid,
pqnh.dbid,
pqnh.userid,
pqnh.coalesce_range,
unnest(records) AS records
FROM
public.powa_qualstats_quals_history pqnh
WHERE
coalesce_range && tstzrange('2025-01-17 16:52:15+0100', '2025-01-17 19:52:15+0100', '[]')
AND pqnh.srvid = '1') AS unnested
WHERE (records).ts <@ tstzrange('2025-01-17 16:52:15+0100', '2025-01-17 19:52:15+0100', '[]')
UNION ALL
SELECT
pqnc.srvid,
qualid,
queryid,
dbid,
userid,
pqnc.ts,
pqnc.occurences,
pqnc.execution_count,
pqnc.nbfiltered,
pqnc.mean_err_estimate_ratio,
pqnc.mean_err_estimate_num
FROM
public.powa_qualstats_quals_history_current pqnc
WHERE
pqnc.ts <@ tstzrange('2025-01-17 16:52:15+0100', '2025-01-17 19:52:15+0100', '[]')
AND pqnc.srvid = '1') h
JOIN (
SELECT
srvid,
qualid,
queryid,
dbid,
userid,
array_agg(qual) AS quals
FROM (
SELECT
srvid,
qualid,
queryid,
dbid,
userid,
unnest(quals) AS qual
FROM
public.powa_qualstats_quals) expanded
WHERE (qual).eval_type = 'f'
GROUP BY
srvid,
qualid,
queryid,
dbid,
userid) AS pqnh USING (srvid, queryid, qualid)
JOIN public.powa_statements ps USING (queryid, srvid)
WHERE
h.srvid = '1'
GROUP BY
ps.srvid, qualid, ps.queryid, ps.dbid, ps.query, quals) AS sub
JOIN public.powa_databases pd ON pd.oid = sub.dbid
AND pd.srvid = sub.srvid
WHERE
pd.datname = 'tpc'
AND pd.srvid = '1'
AND sub.avg_filter > 1000
AND sub.filter_ratio > 0.3
GROUP BY
sub.qualid,
sub.execution_count,
sub.occurences,
sub.quals::jsonb,
sub.avg_filter,
sub.filter_ratio
ORDER BY
sub.occurences DESC
LIMIT 200
-- Formatted by pgFormatter::Beautify
Copy query to clipboard
I/O stats
Query read 270 MB bytes from disk (or system disk cache)
in 253.196 ms, at ~ 1.1 GB/s
Query wrote 266 MB bytes to disk
in 513.695 ms, at ~ 518 MB/s
Per node type stats
node type count sum of times % of query
Append
1
3.008 ms
0.1 %
Bitmap Heap Scan
1
0.451 ms
0.0 %
Bitmap Index Scan
1
0.237 ms
0.0 %
GroupAggregate
3
412.340 ms
10.3 %
Hash
1
0.083 ms
0.0 %
Hash Join
1
44.014 ms
1.1 %
Index Scan
3
489.578 ms
12.2 %
Limit
1
0.007 ms
0.0 %
Nested Loop
2
157.860 ms
3.9 %
ProjectSet
2
9.113 ms
0.2 %
Seq Scan
1
4.557 ms
0.1 %
Sort
3
2,868.505 ms
71.6 %
Subquery Scan
5
13.793 ms
0.3 %
Per table stats
Table name Scan count Total time % of query scan type count sum of times % of table
powa_databases
1
0.704 ms
0.0 %
Index Scan
1
0.704 ms
100.0 %
powa_qualstats_quals
1
0.170 ms
0.0 %
Index Scan
1
0.170 ms
100.0 %
powa_qualstats_quals_history
1
0.451 ms
0.0 %
Bitmap Heap Scan
1
0.451 ms
100.0 %
powa_qualstats_quals_history_current
1
4.557 ms
0.1 %
Seq Scan
1
4.557 ms
100.0 %
powa_statements
1
488.704 ms
12.2 %
Index Scan
1
488.704 ms
100.0 %