Is REINDEX dangerous?

Question

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.

Answer 1

Reindexing is not dangerous and can not harm data consistency. However, if you have time critical writes, you may loose data if the table is locked and the DML is aborted.

Reindexing should not take a lot of time, but will usually involve reading the whole table, sorting the index fields and writing a new index. Given the time for COUNT(*) it will likely take five minutes or more.

It is unlikely this is an indexing problem. COUNT(*) should use a table scan in which case no index is read. I would expect you have an IO problem of some sort.

Try using COUNT(1) or COUNT(pk_field) which may use the index.

If you are running on a Unix or Linux platform you may want to monitor disk activity with sar. You might also have a failing disk which can cut IO rates dramatically.

Tables with large objects can also increase IO significantly to construct the records for COUNT(*).

Answer 2

I'm not sure of the best answer for you. However this thread seems to offer some good suggestions:n http://postgresql.1045698.n5.nabble.com/count-performance-issue-td2067873.html

One note is that you could implement a TRIGGER to maintain row counts in a separate table (if COUNT(*) would be called frequently by your applications).

A few of the responses suggest that this is symptomatic of a database that hasn't been vacuumed recently enough (suggesting that autovacuum is disabled on your server or for that database in particular)?

Another suggestion looks like:

ANALYZE tablename;
SELECT reltuple FROM pg_class WHERE relname = 'tablename';

And someone identified as A. Kretschmer notes:

No. The current index-implementation contains no information about the row-visibility within the current transaction. You need to scan the whole data-table to obtain if the current row are visible within the current transaction.

... supporting my comment about row-level permissions being a performance concern.

My search also turned up WikiVS: MySQL vs. PostgreSQL: COUNT(*).

You can peruse the other results I found by using Google:postgresql count(*) performance