Why COUNT(*) Is Slow on Large PostgreSQL Tables

Resumo

Pixel shares why PostgreSQL COUNT(*) can be slow on large tables: MVCC requires checking which rows are visible to the current transaction. The announcement explains how index-only scans and the visibility map can help, why recent updates or infrequent vacuuming reduce their benefit, and what to investigate with EXPLAIN.

DEV Community

Why Is COUNT(*) So Slow on Large Tables?

COUNT() feels like it should be instant. The table already exists, an index already exists, so why does Postgres sometimes scan a large table just to answer how many rows it holds? The short answer is that in Postgres, a count is not a stored number. It depends on what the current transaction can see. Why Postgres checks rows instead of reading a stored count Postgres uses MVCC, which means different transactions can see different versions of the same table at the same time. A row inserted by an uncommitted transaction, or deleted but not yet vacuumed, may be visible to one transaction and invisible to another. That means there is no single correct count for a table in isolation. The count depends on the snapshot of the transaction running the query. Postgres therefore has to check visibility, row by row or index entry by index entry, rather than returning a cached total. SELECT COUNT() FROM orders;
On a small table this is fast. On a table with millions of rows, that visibility work adds up, even when the query itself looks trivial. Why an index does not always save the query An index can help, but only in the right conditions. Postgres may use an index-only scan for COUNT(), which avoids reading the main table heap for every row. Even then, it still needs to confirm visibility. Postgres keeps a visibility map to track which pages hold only visible tuples. If a page is marked all-visible, an index-only scan can skip heap checks for that page. If the table has not been vacuumed recently, or has a lot of recent updates and deletes, many pages will not be all-visible. The scan then falls back to heap fetches to check visibility, and much of the benefit disappears. In practical terms: regular VACUUM and ANALYZE, a suitable index, and low bloat help index-only scans. Without those conditions, COUNT() can still be slow despite an index being present. How to see what is happening Start with EXPLAIN, and add ANALYZE and BUFFERS when checking a slow query in a safe environment: EXPLAIN (ANALYZE, BUFFERS) SELECT COUNT() FROM orders;
Look for the scan type, whether heap fetches appear on an index-only scan, and how many buffers were read. A sequential scan on a large table, or a high heap fetch count, usually points to visibility work rather than a missing index alone. Avoid treating one plan as proof for every case. Table size, write patterns, vacuum history, and Postgres settings all change the result. Faster options when an exact count is not required Many uses of COUNT(
) do not need an exact number. Dashboards, admin totals, and rough progress indicators often work well with an estimate. 1. Use the planner estimate from pg_class Postgres stores an estimated row count in pg_class.reltuples, updated by VACUUM and ANALYZE: SELECT reltuples::bigint AS estimated_rows
FROM pg_class
WHERE relname = 'orders';
This is fast because it reads catalog metadata, not the table. The trade-off is accuracy. The estimate can be stale after bulk loads, large deletes, or before the next analyze run. Use it when “about right” is good enough, and say so in the interface if users will see the number. 2. Read the estimate from EXPLAIN A plain EXPLAIN without ANALYZE shows a row estimate for the query without executing it. It comes from the same planner statistics, so it shares the same staleness trade-off. It is useful for a quick check, but it is not a substitute for an exact count when exactness matters. 3. Maintain a counter for hot totals For a total that is read often and written often, a separate counter can help. The usual pattern is a small table holding one row per counted thing, updated when rows are inserted or deleted, or refreshed on a schedule from a background job. This moves the cost from read time to write time or to a periodic refresh. It also introduces new trade-offs: a single counter row can become a contention point under heavy writes, and a scheduled refresh means the displayed total lags behind reality. It suits dashboar...
Why COUNT(*) Is Slow on Large PostgreSQL Tables
Powered by Readybot.io

As últimas novidades de Pixel

Incident Response: Look Beyond the Red Alert

Pixel’s DEV Community announcement explores how teams can fixate on one failing service while overlooking the real cause of an outage, such as a full …

Sach: AI Fact-Checks WhatsApp Forwards in India

Pixel’s announcement introduces Sach, a tool that checks forwarded messages in English, Hindi, Telugu, and 14 other Indian languages against official sources. It gives cited …

Java 25 Removes SecurityManager: What to Fix

Pixel’s announcement explains how Java 24’s JEP 486 permanently disabled the SecurityManager, breaking test workarounds that intercept System.exit. It recommends returning exit codes or throwing …