5

I am using Postgres with the following query:

select count(*) from image;

The primary key on this table is non-incrementing; it's a unique serial number for the images stored in the table. Our app often attempts to ingest images that have already been recorded in the database, so the primary key/serial number ensures they are only recorded once.

Now we are wondering if we should have gone with an incrementing primary key instead. We have 1,259,369 images in the database and it takes about 7 minutes for the count query to run.

Our app will never delete images from this table - so an incrementing primary key would allow us to check the value of the last ID which would equal the number of rows in the table.

  • 3
    7 minutes to count 1.2 million rows seems extremely slow. Which exact Postgres version are you using? That should only take seconds. Can you post the output of explain (analyze, verbose, buffers) select count(*) from image. And please post the definition of the table (create table image (...)) – a_horse_with_no_name Mar 17 '15 at 12:07
6

Generally, if you don't need an exact count, there is a much faster way:

SELECT reltuples::bigint AS estimate
FROM   pg_class
WHERE  oid = 'image'::regclass;

As a matter of fact, in a DB with concurrent write access every count is an estimate, because the number may be outdated the instant you get it.

But, like @a_horse commented, there is something off in your DB. Counting a million should not take more than a few seconds in the worst case.

That your app will never delete images from this table makes this even more suspicious, because there shouldn't be many dead rows then. (Or are you updating a lot?) A huge amount of dead tuples could slow you down - and call for VACUUM. Normally, autovacuum takes care of this. Did you enable it? (It's the default in modern Postgres.)

Check for dead tuples:

All the usual advice for performance optimization applies.

  • 1
    Thanks for all your comments. Turning on autovacuum seems to have done the trick - a full count now takes about 6 seconds or so. I also learned through the process of tuning Postgres that I am running 32-bit instead of 64-bit, which means I will have to rebuild my Lubuntu install so I can take full advantage of my 16GB of RAM. Thanks for helping out this recent convert from MySQL! – andrewniesen Mar 23 '15 at 3:45
0

Taken from my blog:

Using pg_stat_user_tables you can find row count of a PostgreSQL Table:

SELECT 
    schemaname
    ,relname
    ,n_live_tup AS EstimatedCount 
FROM pg_stat_user_tables 
ORDER BY n_live_tup DESC;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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