Skip to content

Instantly share code, notes, and snippets.

@justnoise

justnoise/gist:e94f542e782cc084c7caec12cdf4c4fd

Created March 23, 2025 20:21
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save justnoise/e94f542e782cc084c7caec12cdf4c4fd to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save justnoise/e94f542e782cc084c7caec12cdf4c4fd to your computer and use it in GitHub Desktop.
Download ZIP
PG concurrent index commands
Raw
gistfile1.txt
-- check on creation status
SELECT * FROM pg_stat_progress_create_index;
-- look for broken indexes
SELECT * FROM pg_class, pg_index WHERE pg_index.indisvalid = false AND pg_index.indexrelid = pg_class.oid;
-- fix
REINDEX INDEX CONCURRENTLY idx_users_email_2019;
-- find unused indexes
select
indexrelid::regclass as index, relid::regclass as table
from
pg_stat_user_indexes
JOIN pg_index USING (indexrelid)
where
idx_scan = 0 and indisunique is false;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment