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

Commit82679b9

Browse files
Fixed unused_indexes report
1 parentc9e9531 commit82679b9

File tree

1 file changed

+34
-40
lines changed

1 file changed

+34
-40
lines changed

‎reporter/postgres_reports.py‎

Lines changed: 34 additions & 40 deletions
Original file line numberDiff line numberDiff line change
@@ -301,24 +301,24 @@ def generate_h001_invalid_indexes_report(self, cluster: str = "local", node_name
301301

302302
defgenerate_h002_unused_indexes_report(self,cluster:str="local",node_name:str="node-01")->Dict[str,Any]:
303303
"""
304-
Generate H002 Unusedand rarely usedIndexes report.
304+
Generate H002 Unused Indexes report.
305305
306306
Args:
307307
cluster: Cluster name
308308
node_name: Node name
309309
310310
Returns:
311-
Dictionary containing unusedand rarely usedindexes information
311+
Dictionary containing unused indexes information
312312
"""
313-
print("Generating H002 Unusedand rarely usedIndexes report...")
313+
print("Generating H002 Unused Indexes report...")
314314

315315
# Get all databases
316316
databases=self.get_all_databases(cluster,node_name)
317317

318318
unused_indexes_by_db= {}
319319
fordb_nameindatabases:
320-
# Query unused indexes for each database
321-
unused_indexes_query=f'pgwatch_unused_indexes_index_size_bytes{{cluster="{cluster}", node_name="{node_name}",datname="{db_name}"}}'
320+
# Query unused indexes for each database using last_over_time to get most recent value
321+
unused_indexes_query=f'last_over_time(pgwatch_unused_indexes_index_size_bytes{{cluster="{cluster}", node_name="{node_name}",dbname="{db_name}"}}[10h])'
322322
unused_result=self.query_instant(unused_indexes_query)
323323

324324
unused_indexes= []
@@ -333,7 +333,7 @@ def generate_h002_unused_indexes_report(self, cluster: str = "local", node_name:
333333
index_size_bytes=float(item['value'][1])ifitem.get('value')else0
334334

335335
# Query other related metrics for this index
336-
idx_scan_query=f'pgwatch_unused_indexes_idx_scan{{cluster="{cluster}", node_name="{node_name}",datname="{db_name}", schema_name="{schema_name}", table_name="{table_name}", index_name="{index_name}"}}'
336+
idx_scan_query=f'last_over_time(pgwatch_unused_indexes_idx_scan{{cluster="{cluster}", node_name="{node_name}",dbname="{db_name}", schema_name="{schema_name}", table_name="{table_name}", index_name="{index_name}"}}[10h])'
337337
idx_scan_result=self.query_instant(idx_scan_query)
338338
idx_scan=float(idx_scan_result['data']['result'][0]['value'][1])ifidx_scan_result.get('data',
339339
{}).get(
@@ -346,41 +346,24 @@ def generate_h002_unused_indexes_report(self, cluster: str = "local", node_name:
346346
"reason":reason,
347347
"idx_scan":idx_scan,
348348
"index_size_bytes":index_size_bytes,
349-
"idx_is_btree":item['metric'].get('opclasses','').startswith('btree'),
349+
"idx_is_btree":item['metric'].get('idx_is_btree','false')=='true',
350350
"supports_fk":bool(int(item['metric'].get('supports_fk',0)))
351351
}
352352

353353
index_data['index_size_pretty']=self.format_bytes(index_data['index_size_bytes'])
354354

355355
unused_indexes.append(index_data)
356356

357-
# Query rarely used indexes (note: logs show 0 rows, but we'll include the structure)
358-
rarely_used_indexes= []# Currently empty as per logs
359-
360-
# Combine and calculate totals
361-
all_indexes=unused_indexes+rarely_used_indexes
362-
total_unused_size=sum(idx['index_size_bytes']foridxinunused_indexes)
363-
total_rarely_used_size=sum(idx['index_size_bytes']foridxinrarely_used_indexes)
364-
total_size=total_unused_size+total_rarely_used_size
365-
366357
# Sort by index size descending
367-
all_indexes.sort(key=lambdax:x['index_size_bytes'],reverse=True)
358+
unused_indexes.sort(key=lambdax:x['index_size_bytes'],reverse=True)
359+
360+
total_unused_size=sum(idx['index_size_bytes']foridxinunused_indexes)
368361

369362
unused_indexes_by_db[db_name]= {
370363
"unused_indexes":unused_indexes,
371-
"rarely_used_indexes":rarely_used_indexes,
372-
"all_indexes":all_indexes,
373-
"summary": {
374-
"total_unused_count":len(unused_indexes),
375-
"total_rarely_used_count":len(rarely_used_indexes),
376-
"total_count":len(all_indexes),
377-
"total_unused_size_bytes":total_unused_size,
378-
"total_rarely_used_size_bytes":total_rarely_used_size,
379-
"total_size_bytes":total_size,
380-
"total_unused_size_pretty":self.format_bytes(total_unused_size),
381-
"total_rarely_used_size_pretty":self.format_bytes(total_rarely_used_size),
382-
"total_size_pretty":self.format_bytes(total_size)
383-
}
364+
"total_count":len(unused_indexes),
365+
"total_size_bytes":total_unused_size,
366+
"total_size_pretty":self.format_bytes(total_unused_size)
384367
}
385368

386369
returnself.format_report_data("H002",unused_indexes_by_db,node_name)
@@ -404,8 +387,8 @@ def generate_h004_redundant_indexes_report(self, cluster: str = "local", node_na
404387

405388
redundant_indexes_by_db= {}
406389
fordb_nameindatabases:
407-
# Query redundant indexes for each database
408-
redundant_indexes_query=f'pgwatch_redundant_indexes_index_size_bytes{{cluster="{cluster}", node_name="{node_name}",datname="{db_name}"}}'
390+
# Query redundant indexes for each database using last_over_time to get most recent value
391+
redundant_indexes_query=f'last_over_time(pgwatch_redundant_indexes_index_size_bytes{{cluster="{cluster}", node_name="{node_name}",dbname="{db_name}"}}[10h])'
409392
result=self.query_instant(redundant_indexes_query)
410393

411394
redundant_indexes= []
@@ -424,18 +407,18 @@ def generate_h004_redundant_indexes_report(self, cluster: str = "local", node_na
424407
index_size_bytes=float(item['value'][1])ifitem.get('value')else0
425408

426409
# Query other related metrics for this index
427-
table_size_query=f'pgwatch_redundant_indexes_table_size_bytes{{cluster="{cluster}", node_name="{node_name}",datname="{db_name}", schema_name="{schema_name}", table_name="{table_name}", index_name="{index_name}"}}'
410+
table_size_query=f'last_over_time(pgwatch_redundant_indexes_table_size_bytes{{cluster="{cluster}", node_name="{node_name}",dbname="{db_name}", schema_name="{schema_name}", table_name="{table_name}", index_name="{index_name}"}}[10h])'
428411
table_size_result=self.query_instant(table_size_query)
429412
table_size_bytes=float(
430413
table_size_result['data']['result'][0]['value'][1])iftable_size_result.get('data', {}).get(
431414
'result')else0
432415

433-
index_usage_query=f'pgwatch_redundant_indexes_index_usage{{cluster="{cluster}", node_name="{node_name}",datname="{db_name}", schema_name="{schema_name}", table_name="{table_name}", index_name="{index_name}"}}'
416+
index_usage_query=f'last_over_time(pgwatch_redundant_indexes_index_usage{{cluster="{cluster}", node_name="{node_name}",dbname="{db_name}", schema_name="{schema_name}", table_name="{table_name}", index_name="{index_name}"}}[10h])'
434417
index_usage_result=self.query_instant(index_usage_query)
435418
index_usage=float(index_usage_result['data']['result'][0]['value'][1])ifindex_usage_result.get(
436419
'data', {}).get('result')else0
437420

438-
supports_fk_query=f'pgwatch_redundant_indexes_supports_fk{{cluster="{cluster}", node_name="{node_name}",datname="{db_name}", schema_name="{schema_name}", table_name="{table_name}", index_name="{index_name}"}}'
421+
supports_fk_query=f'last_over_time(pgwatch_redundant_indexes_supports_fk{{cluster="{cluster}", node_name="{node_name}",dbname="{db_name}", schema_name="{schema_name}", table_name="{table_name}", index_name="{index_name}"}}[10h])'
439422
supports_fk_result=self.query_instant(supports_fk_query)
440423
supports_fk=bool(
441424
int(supports_fk_result['data']['result'][0]['value'][1]))ifsupports_fk_result.get('data',
@@ -1612,17 +1595,28 @@ def get_all_databases(self, cluster: str = "local", node_name: str = "node-01")
16121595
Returns:
16131596
List of database names
16141597
"""
1615-
#Query for all databasesusing pg_stat_databasemetrics
1616-
db_query=f'pgwatch_pg_database_wraparound_age_datfrozenxid{{cluster="{cluster}", node_name="{node_name}", datname!="template1"}}'
1598+
#Try to get databasesfrom metrics that use 'dbname' label (custommetrics)
1599+
db_query=f'last_over_time(pgwatch_unused_indexes_index_size_bytes{{cluster="{cluster}", node_name="{node_name}"}}[10h])'
16171600
result=self.query_instant(db_query)
1618-
1601+
16191602
databases= []
16201603
ifresult.get('status')=='success'andresult.get('data', {}).get('result'):
16211604
foriteminresult['data']['result']:
1622-
db_name=item['metric'].get('datname','')
1605+
db_name=item['metric'].get('dbname','')
16231606
ifdb_nameanddb_namenotindatabases:
16241607
databases.append(db_name)
1625-
# If no databases found, try alternative query
1608+
1609+
# If no databases found using dbname, try using datname (catalog metrics)
1610+
ifnotdatabases:
1611+
db_query=f'pgwatch_pg_database_wraparound_age_datfrozenxid{{cluster="{cluster}", node_name="{node_name}", datname!="template1"}}'
1612+
result=self.query_instant(db_query)
1613+
ifresult.get('status')=='success'andresult.get('data', {}).get('result'):
1614+
foriteminresult['data']['result']:
1615+
db_name=item['metric'].get('datname','')
1616+
ifdb_nameanddb_namenotindatabases:
1617+
databases.append(db_name)
1618+
1619+
# If still no databases found, try another alternative query
16261620
ifnotdatabases:
16271621
db_query=f'pgwatch_pg_database_size_bytes{{cluster="{cluster}", node_name="{node_name}"}}'
16281622
result=self.query_instant(db_query)

0 commit comments

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