All quizzesHard

Index Internals & Bloat

Preview — 3 of 10 questions

What happens during a B-tree page split?

AThe entire index is rebuilt from scratch

BThe index temporarily degrades to a sequential scan

CThe overflow rows are stored in a separate TOAST table

DWhen an index page is full, it is split into two pages and a new separator key is added to the parent page

Under what condition does PostgreSQL perform a HOT update instead of a full tuple update with index maintenance?

AWhen the table has no indexes

BWhen the updated row fits within the same heap page and no indexed column is modified

CWhen fillfactor = 100 is set on the table

DWhen the update is performed inside a transaction

Which query identifies unused indexes that should be considered for removal?

javascript

SELECT
  schemaname,
  tablename,
  indexrelname,
  idx_scan,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE schemaname = 'public' AND idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

ASELECT indexrelname, idx_scan FROM pg_stat_user_indexes WHERE idx_scan = 0 AND schemaname = 'public'

BSELECT * FROM pg_indexes WHERE indexname LIKE '%_unused%'

CSELECT * FROM pg_index WHERE indisunique = false

DEXPLAIN SELECT * FROM pg_stat_activity WHERE state = 'idle'

Answer all 10 questions (7 more), see explanations for every answer, and track your score.