Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Do you run ANALYSE regularly? –  Hendrik Brummermann Mar 29 '11 at 6:28

2 Answers 2

up vote 10 down vote accepted

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 will 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(*) likely 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(*).

share|improve this answer
    
I read somewhere that COUNT(*) performance issues in PostgreSQL can be caused by the need for the server to check the permissions on every tuple during the table scan (to determine which rows are visible to the entity performing the query). –  Jim Dennis Mar 29 '11 at 0:14
    
@Jim: I would expect row or field level security restrictions, if used, would add overhead. Overhead would depend on the criteria. –  BillThor Mar 29 '11 at 5:04
    
I'm thinking I was mistaken about it being row level security. Further reading suggests that it's more about the MVCC "visibility" (are some of those rows still locked in transactions which have yet to be committed). –  Jim Dennis Mar 29 '11 at 10:44

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.