Which indexes is nobody using?

Sql

Lists indexes that have never been scanned, which are pure write cost. Check against a long uptime before dropping any.

SELECT
  relname AS table_name,
  indexrelname AS index_name,
  idx_scan,
  pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

Download

Databases postgres indexes performance chris

More in Databases

Delete duplicate rows, keeping the oldest Databases Uses a window function to number the duplicates and removes everything but the first of each group. Group rows into a JSON array Databases Returns one row per author with their posts nested, instead of a join that repeats the author on every row. Add a column without locking the table Databases On Postgres 11 and later a nullable column with a default is a metadata-only change. Adding NOT NULL afterwards is the part that needs care. Table sizes, largest first Databases Where the disk actually went, including indexes and TOAST. Find slow queries in Postgres Databases Ranks statements by total time spent. Needs the pg_stat_statements extension enabled. Upsert a row in SQLite Databases Inserts, or updates the existing row when the unique column already holds that value. Reset a Postgres sequence after a bulk import Databases Copying rows with explicit ids leaves the identity sequence behind, so the next insert collides with an existing row.

Random picks

Visually hidden, still read aloud Web & CSS Hides a label from sight while leaving it in the accessibility tree. display:none would remove it from both. Recover a branch you deleted Git The reflog remembers where HEAD has been for about ninety days, so a deleted branch is almost never really gone. Run a command on every file changed in a commit Shell Lints only what a commit touched, which is usually all you need in a pre-commit hook. Iterate and collect in Rust Systems Filter and map fuse into a single pass; collect decides what comes out the other end. Centre a box, two ways Web & CSS Grid is one line. Flex is three and gives you control over the axes separately. Freeze time in a test Testing Node's mock timers make anything date-dependent deterministic without injecting a clock everywhere. Show what a merge will do before doing it Git Lists the commits and the files a merge would bring in, so a surprise is less likely. Walk a directory tree lazily Python pathlib keeps the paths as objects rather than strings, so joining and suffix checks stay readable.