I've been trying to COUNT(*)
a table with 150,000 rows that has a Primary key. It tool about 5 minutes, so I figured out this is an indexing problem.
Citing the PostgreSQL manual:
REINDEX is similar to a drop and recreate of the index in that the index contents are rebuilt from scratch. However, the locking considerations are rather different. REINDEX locks out writes but not reads of the index's parent table. It also takes an exclusive lock on the specific index being processed, which will block reads that attempt to use that index (...) The subsequent CREATE INDEX locks out writes but not reads; since the index is not there, no read will attempt to use it, meaning that there will be no blocking but reads might be forced into expensive sequential scans.
From your own experience, can you tell:
- is
REINDEXING
dangerous? Can it harm the data consistency? - Can it take a lot of time?
- Is it a probable solution to my scenario?
Update:
The solution that worked for us was recreating the same index with a different name, then deleting the old index.
The index creation is very fast, and we've reduced the index size from 650 MB to 8 MB. Using a COUNT(*)
with between
takes only 3 seconds.