Project

General

Profile

Bug #14149

Index nodeid_idx is not used and consumes a lot of disk space for nothing

Added by Nicolas CHARLES 3 months ago. Updated 3 months ago.

Status:
Released
Priority:
N/A
Category:
Performance and scalability
Target version:
Severity:
User visibility:
Effort required:
Priority:
0

Description

Index nodeid_idx on table RudderSysEvents is not used at all, as the result

SELECT
 schemaname || '.' || relname AS table,
 indexrelname AS index,
 pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size,
 idx_scan as index_scans
FROM pg_stat_user_indexes ui
JOIN pg_index i ON ui.indexrelid = i.indexrelid
WHERE NOT indisunique AND pg_relation_size(relid) > 5 * 8192
ORDER BY pg_relation_size(i.indexrelid) / nullif(idx_scan, 0) DESC NULLS FIRST,
pg_relation_size(i.indexrelid) DESC;

             table              |                  index                   | index_size | index_scans 
--------------------------------+------------------------------------------+------------+-------------
 public.ruddersysevents         | nodeid_idx                               | 3302 MB    |           0
 public.ruddersysevents         | ruleid_idx                               | 3300 MB    |           0
 public.ruddersysevents         | component_idx                            | 2269 MB    |           0
 public.ruddersysevents         | keyvalue_idx                             | 2205 MB    |           0
 public.nodecompliancelevels    | nodecompliancelevels_ruleid              | 171 MB     |           0
 public.nodecompliancelevels    | nodecompliancelevels_nodeid              | 170 MB     |           0

Note: here index ruleid_idx, keyvalue_idx and component_idx are not used either, but on some installs they widely use)

droping it will save some space, and relieve a bit I/O


Subtasks

User story #14151: create migration script to remove index nodeid_idxReleasedNicolas CHARLESActions
Bug #14213: Wrong condition on deletion of index_idxReleasedAlexis MOUSSETActions

Associated revisions

Revision d5832213 (diff)
Added by Nicolas CHARLES 3 months ago

Fixes #14149: Index nodeid_idx is not used and consumes a lot of disk space for nothing

History

#1

Updated by Nicolas CHARLES 3 months ago

  • Status changed from New to In progress
  • Assignee set to Nicolas CHARLES
#2

Updated by Nicolas CHARLES 3 months ago

  • Status changed from In progress to Pending technical review
  • Assignee changed from Nicolas CHARLES to Fran├žois ARMAND
  • Pull Request set to https://github.com/Normation/rudder/pull/2114
#3

Updated by Nicolas CHARLES 3 months ago

  • Status changed from Pending technical review to Pending release
#4

Updated by Alexis MOUSSET 3 months ago

  • Status changed from Pending release to Released
This bug has been fixed in Rudder 4.1.19, 4.3.9 and 5.0.5 which were released today.
Changelog
Changelog
Changelog

Also available in: Atom PDF