lndhub.go

lndhub.go copied to clipboard

Unused indezes

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 idx_scan < 50 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;

Does this index hurt? querying tx entries by user id is still something valuable imo. (at least from an admin point.)

It doesn't hurt, but since the index has never been scanned according to the PostgreSQL query above I thought it would make sense to remove it.

Running a query on transaction_entires and filtering by user_id is pretty fast even without an index. (< 1 s)