Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitec4376d

Browse files
author
Dementii Priadko
committed
Improve unused indexes report: index definition (to support UNDO steps), better stats age (use uptime if stats_reset is NULL) , and misc. minor improvements
1 parentb9035eb commitec4376d

File tree

7 files changed

+480
-81
lines changed

7 files changed

+480
-81
lines changed

‎config/pgwatch-postgres/metrics.yml‎

Lines changed: 179 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -14,8 +14,186 @@ metrics:
1414
gauges:
1515
-'*'
1616

17+
index_definitions:
18+
description:"Index definitions for unused and redundant indexes only"
19+
sqls:
20+
11:|-
21+
with fk_indexes as (
22+
select
23+
n.nspname as schema_name,
24+
ci.relname as index_name,
25+
cr.relname as table_name,
26+
(confrelid::regclass)::text as fk_table_ref,
27+
array_to_string(indclass, ', ') as opclasses
28+
from pg_index i
29+
join pg_class ci on ci.oid = i.indexrelid and ci.relkind = 'i'
30+
join pg_class cr on cr.oid = i.indrelid and cr.relkind = 'r'
31+
join pg_namespace n on n.oid = ci.relnamespace
32+
join pg_constraint cn on cn.conrelid = cr.oid
33+
left join pg_stat_all_indexes as si on si.indexrelid = i.indexrelid
34+
where
35+
contype = 'f'
36+
and not i.indisunique
37+
and conkey is notnull
38+
and ci.relpages > 5
39+
and si.idx_scan < 10
40+
),
41+
-- Unused indexes
42+
table_scans as (
43+
select relid,
44+
tables.idx_scan + tables.seq_scan as all_scans,
45+
(tables.n_tup_ins + tables.n_tup_upd + tables.n_tup_del) as writes,
46+
pg_relation_size(relid) as table_size
47+
from pg_stat_all_tables as tables
48+
join pg_class c on c.oid = relid
49+
where c.relpages > 5
50+
),
51+
indexes as (
52+
select
53+
i.indrelid,
54+
i.indexrelid,
55+
n.nspname as schema_name,
56+
cr.relname as table_name,
57+
ci.relname as index_name,
58+
si.idx_scan,
59+
pg_relation_size(i.indexrelid) as index_bytes,
60+
ci.relpages,
61+
(a.amname = 'btree') as idx_is_btree,
62+
array_to_string(i.indclass, ', ') as opclasses
63+
from pg_index i
64+
join pg_class ci on ci.oid = i.indexrelid and ci.relkind = 'i'
65+
join pg_class cr on cr.oid = i.indrelid and cr.relkind = 'r'
66+
join pg_namespace n on n.oid = ci.relnamespace
67+
join pg_am a on ci.relam = a.oid
68+
left join pg_stat_all_indexes as si on si.indexrelid = i.indexrelid
69+
where
70+
not i.indisunique
71+
and i.indisvalid
72+
and ci.relpages > 5
73+
),
74+
unused_index_ids as (
75+
select
76+
i.indexrelid as index_id,
77+
i.schema_name,
78+
i.table_name,
79+
i.index_name
80+
from indexes i
81+
join table_scans ts on ts.relid = i.indrelid
82+
where
83+
i.idx_scan = 0
84+
and i.idx_is_btree
85+
order by i.index_bytes desc
86+
limit 10000
87+
),
88+
-- Redundant indexes
89+
index_data as (
90+
select
91+
*,
92+
indkey::text as columns,
93+
array_to_string(indclass, ', ') as opclasses
94+
from pg_index i
95+
join pg_class ci on ci.oid = i.indexrelid and ci.relkind = 'i'
96+
where
97+
indisvalid
98+
and ci.relpages > 5
99+
),
100+
redundant_index_pairs as (
101+
select
102+
i2.indexrelid as redundant_index_id,
103+
i1.indexrelid as reason_index_id,
104+
tnsp.nspname as schema_name,
105+
trel.relname as table_name,
106+
irel.relname as index_name,
107+
pg_relation_size(i2.indexrelid) as index_size_bytes
108+
from (
109+
select indrelid, indexrelid, opclasses, indclass, indexprs, indpred, indisprimary, indisunique, columns
110+
from index_data
111+
order by indexrelid
112+
) as i1
113+
join index_data as i2 on
114+
i1.indrelid = i2.indrelid
115+
and i1.indexrelid <> i2.indexrelid
116+
inner join pg_opclass op1 on i1.indclass[0] = op1.oid
117+
inner join pg_opclass op2 on i2.indclass[0] = op2.oid
118+
inner join pg_am am1 on op1.opcmethod = am1.oid
119+
inner join pg_am am2 on op2.opcmethod = am2.oid
120+
join pg_stat_all_indexes as s on s.indexrelid = i2.indexrelid
121+
join pg_class as trel on trel.oid = i2.indrelid
122+
join pg_namespace as tnsp on trel.relnamespace = tnsp.oid
123+
join pg_class as irel on irel.oid = i2.indexrelid
124+
where
125+
not i2.indisprimary
126+
and not i2.indisunique
127+
and am1.amname = am2.amname
128+
and i1.columns like (i2.columns || '%')
129+
and i1.opclasses like (i2.opclasses || '%')
130+
and pg_get_expr(i1.indexprs, i1.indrelid) is not distinct from pg_get_expr(i2.indexprs, i2.indrelid)
131+
and pg_get_expr(i1.indpred, i1.indrelid) is not distinct from pg_get_expr(i2.indpred, i2.indrelid)
132+
),
133+
redundant_indexes_tmp_num as (
134+
select row_number() over () num, rip.*
135+
from redundant_index_pairs rip
136+
),
137+
redundant_indexes_tmp_links as (
138+
select
139+
ri1.*,
140+
ri2.num as r_num
141+
from redundant_indexes_tmp_num ri1
142+
left join redundant_indexes_tmp_num ri2 on
143+
ri2.reason_index_id = ri1.redundant_index_id
144+
and ri1.reason_index_id = ri2.redundant_index_id
145+
),
146+
redundant_index_ids as (
147+
select distinct
148+
index_id,
149+
schema_name,
150+
table_name,
151+
index_name
152+
from (
153+
select
154+
redundant_index_id as index_id,
155+
schema_name,
156+
table_name,
157+
index_name,
158+
index_size_bytes
159+
from redundant_indexes_tmp_links
160+
where num < r_num or r_num isnull
161+
union all
162+
select
163+
reason_index_id as index_id,
164+
schema_name,
165+
table_name,
166+
index_name,
167+
index_size_bytes
168+
from redundant_indexes_tmp_links
169+
where
170+
num < r_num
171+
or r_num isnull
172+
) as combined
173+
order by index_size_bytes desc
174+
limit 10000
175+
),
176+
-- Combine unused and redundant index IDs
177+
all_target_indexes as (
178+
select distinct index_id, schema_name, table_name, index_name
179+
from unused_index_ids
180+
union
181+
select distinct index_id, schema_name, table_name, index_name
182+
from redundant_index_ids
183+
)
184+
select /* pgwatch_generated */
185+
ati.index_name as indexrelname,
186+
ati.schema_name as schemaname,
187+
ati.table_name as relname,
188+
pg_get_indexdef(ati.index_id) as index_definition
189+
from all_target_indexes ati
190+
order by schemaname, relname, indexrelname;
191+
gauges:
192+
-'*'
193+
17194
presets:
18195
full:
19196
description:"Full metrics for PostgreSQL storage"
20197
metrics:
21-
pgss_queryid_queries:300
198+
pgss_queryid_queries:300
199+
index_definitions:3600

‎config/pgwatch-prometheus/metrics.yml‎

Lines changed: 1 addition & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1575,9 +1575,7 @@ metrics:
15751575
join pg_class as irel on irel.oid = i2.indexrelid
15761576
where
15771577
not i2.indisprimary -- index 1 is not primary
1578-
and not ( -- skip if index1 is (primary or uniq) and is NOT (primary and uniq)
1579-
i2.indisunique and not i1.indisprimary
1580-
)
1578+
and not i2.indisunique -- index 1 is not unique (unique indexes serve constraint purpose)
15811579
and am1.amname = am2.amname -- same access type
15821580
and i1.columns like (i2.columns || '%') -- index 2 includes all columns from index 1
15831581
and i1.opclasses like (i2.opclasses || '%')
Lines changed: 63 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,63 @@
1+
#!/bin/bash
2+
# Configure pg_hba.conf to allow trust authentication from Docker networks
3+
#
4+
# SECURITY NOTE: This configuration uses trust authentication, which is appropriate
5+
# for this use case because:
6+
# 1. The sink-postgres container runs in an isolated Docker network
7+
# 2. No ports are exposed to the host or external networks
8+
# 3. Only other containers in the same Docker Compose network can connect
9+
# 4. This is an internal data collection service, not a production database
10+
# 5. The container network provides the security boundary
11+
#
12+
# This approach simplifies container-to-container communication while maintaining
13+
# appropriate security isolation from external access.
14+
15+
cat>${PGDATA}/pg_hba.conf<<EOF
16+
# PostgreSQL Client Authentication Configuration File
17+
# Custom configuration for sink-postgres container
18+
#
19+
# SECURITY CONTEXT:
20+
# This configuration uses trust authentication for connections within Docker networks.
21+
# This is safe because:
22+
# - The container is NOT exposed to external networks (no published ports)
23+
# - Only containers within the same Docker Compose network can connect
24+
# - The Docker network itself provides the security boundary
25+
# - This simplifies internal service communication without compromising security
26+
#
27+
# If you expose this container's ports to the host or internet, you MUST change
28+
# the authentication method to 'scram-sha-256' or 'md5' and use strong passwords.
29+
30+
# TYPE DATABASE USER ADDRESS METHOD
31+
32+
# "local" is for Unix domain socket connections only
33+
# Safe: only processes within the same container can connect via socket
34+
local all all trust
35+
36+
# IPv4 local connections:
37+
# Safe: only connections from within the container itself
38+
host all all 127.0.0.1/32 trust
39+
40+
# IPv6 local connections:
41+
# Safe: only connections from within the container itself
42+
host all all ::1/128 trust
43+
44+
# Allow replication connections from localhost
45+
# Safe: only for internal container operations
46+
local replication all trust
47+
host replication all 127.0.0.1/32 trust
48+
host replication all ::1/128 trust
49+
50+
# Allow all connections from Docker networks without password
51+
# Safe: these are private Docker network ranges used by Docker Compose
52+
# External networks cannot reach these addresses
53+
# 172.16.0.0/12 - Default Docker bridge networks
54+
# 192.168.0.0/16 - User-defined bridge networks
55+
# 10.0.0.0/8 - Additional private network range
56+
host all all 172.16.0.0/12 trust
57+
host all all 192.168.0.0/16 trust
58+
host all all 10.0.0.0/8 trust
59+
EOF
60+
61+
# Reload PostgreSQL configuration
62+
pg_ctl reload -D${PGDATA}
63+

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp