Revisions to What is the most efficient way to count the number of rows in a table?

Copyedit, links

Source Link

edited Jun 29 at 22:28

182.1k
28
457
620

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;

See:

Fast way to discover the row count of a table

As a matter of factStrictly speaking, 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 rows should not take more than a few seconds in the worst case. (Much less with modern Postgres on modern hardware.)

That your app will never delete images from this table"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 autovacuum takes care of this. Did you enable it? (It's theIt's on by default in modern Postgres.)

Are regular VACUUM ANALYZE still recommended under 9.1?

Check for dead tuples:

Measure the size of a PostgreSQL table row

All the usual advice for performance optimization applies.

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;

Fast way to discover the row count of a table

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.)

Are regular VACUUM ANALYZE still recommended under 9.1?

Check for dead tuples:

Measure the size of a PostgreSQL table row

All the usual advice for performance optimization applies.

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;

See:

Fast way to discover the row count of a table

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

But there is something off in your DB. Counting a million rows should not take more than a few seconds in the worst case. (Much less with modern Postgres on modern hardware.)

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 on by default in modern Postgres.

Are regular VACUUM ANALYZE still recommended under 9.1?

Check for dead tuples:

Measure the size of a PostgreSQL table row

All the usual advice for performance optimization applies.

replaced http://stackoverflow.com/ with https://stackoverflow.com/

Source Link

edited May 23, 2017 at 12:40

Community Bot

1

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;

Fast way to discover the row count of a table Fast way to discover the row count of a table

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.)

Are regular VACUUM ANALYZE still recommended under 9.1?

Check for dead tuples:

Measure the size of a PostgreSQL table row

All the usual advice for performance optimization applies.

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;

Fast way to discover the row count of a table

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.)

Are regular VACUUM ANALYZE still recommended under 9.1?

Check for dead tuples:

Measure the size of a PostgreSQL table row

All the usual advice for performance optimization applies.

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;

Fast way to discover the row count of a table

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.)

Are regular VACUUM ANALYZE still recommended under 9.1?

Check for dead tuples:

Measure the size of a PostgreSQL table row

All the usual advice for performance optimization applies.

replaced http://dba.stackexchange.com/ with https://dba.stackexchange.com/

Source Link

edited Apr 13, 2017 at 12:42

Community Bot

1

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;

Fast way to discover the row count of a table

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 @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.)

Are regular VACUUM ANALYZE still recommended under 9.1?Are regular VACUUM ANALYZE still recommended under 9.1?

Check for dead tuples:

Measure the size of a PostgreSQL table row Measure the size of a PostgreSQL table row

All the usual advice for performance optimization applies.

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;

Fast way to discover the row count of a table

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.)

Are regular VACUUM ANALYZE still recommended under 9.1?

Check for dead tuples:

Measure the size of a PostgreSQL table row

All the usual advice for performance optimization applies.

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;

Fast way to discover the row count of a table

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.)