Project

General

Profile

Actions

Bug #14149

closed

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

Added by Nicolas CHARLES about 5 years ago. Updated about 5 years ago.

Status:
Released
Priority:
N/A
Category:
Performance and scalability
Target version:
Severity:
UX impact:
User visibility:
Effort required:
Priority:
0
Name check:
Fix check:
Regression:

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 2 (0 open2 closed)

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

Also available in: Atom PDF