SQL 如何加快 PostgreSQL 表中的行计数？

How do I speed up counting rows in a PostgreSQL table?

提问by Juan Carlos Coto

We need to count the number of rows in a PostgreSQL table. In our case, no conditions need to be met, and it would be perfectly acceptable to get a row estimate if that significantly improved query speed.

我们需要计算 PostgreSQL 表中的行数。在我们的例子中，不需要满足任何条件，如果能显着提高查询速度，那么获得行估计是完全可以接受的。

Basically, we want select count(id) from <table>to run as fast as possible, even if that implies not getting exact results.

基本上，我们希望select count(id) from <table>尽可能快地运行，即使这意味着无法获得准确的结果。

Thanks! Any suggestions will be appreciated!

谢谢！任何建议将不胜感激！

回答by Erwin Brandstetter

For a quick estimate:

快速估算：

SELECT reltuples FROM pg_class WHERE oid = 'my_schema.my_table'::regclass;

This is superior to the queries presented so far - including the advice in the Postgres Wiki on slow counting.(Updated that by now.):
relnameis not unique in pg_class. There can be multiple tables with the same relnamein multiple schemas of the database. That's regularly the case in my installations.

这优于目前提出的查询 - 包括Postgres Wiki 中关于慢计数的建议。（现在已更新。）：
relname在pg_class. relname在数据库的多个模式中可以有多个相同的表。在我的装置中经常出现这种情况。

And a query on pg_stat_user_tablesis many times slower, as that's a view on a couple of tables.

并且查询pg_stat_user_tables要慢很多倍，因为这是对几个表的视图。

If you do not schema-qualify the table name, a cast to regclassobserves the current search_pathto pick the best match. And if the table does not exist (or cannot be seen) in any of the schemas in the search_pathyou get an error message.

如果您没有对表名进行模式限定，则强制转换为regclass观察当前search_path以选择最佳匹配。如果该表在任何架构中不存在（或无法看到），search_path您将收到一条错误消息。

Details on Object Identifier Typesin the manual.

手册中有关对象标识符类型的详细信息。

Related answer with new options:

带有新选项的相关答案：

• Fast way to discover the row count of a table in PostgreSQL

• 在 PostgreSQL 中发现表的行数的快速方法

回答by Ariel Grabijas

Count is slow for big tables, so you can get a close estimate this way:

对于大表，计数很慢，因此您可以通过以下方式进行近似估计：

SELECT reltuples::bigint AS estimate 
FROM pg_class 
WHERE relname='tableName';

and its extremely fast, results are not float, but still a close estimate.

而且它的速度极快，结果不是浮动的，但仍然是一个接近的估计。

• reltuplesis a column from pg_classtable, it holds data about "number of rows in the table. This is only an estimate used by the planner. It is updated by VACUUM, ANALYZE, and a few DDL commands such as CREATE INDEX" (manual)
• The catalog pg_classcatalogs tables and most everything else that has columns or is otherwise similar to a table. This includes indexes (but see also pg_index), sequences, views, composite types, and some kinds of special relation (manual)
• "Why is "SELECT count(*) FROM bigtable;" slow?" : http://wiki.postgresql.org/wiki/FAQ#Why_is_.22SELECT_count.28.2A.29_FROM_bigtable.3B.22_slow.3F

• reltuples是pg_class表中的一列，它保存有关“表中的行数”的数据。这只是规划器使用的估计值。它由 VACUUM、ANALYZE 和一些 DDL 命令（例如 CREATE INDEX）更新（手动）
• pg_class目录对表格和大多数其他具有列或与表格相似的所有内容进行编目。这包括索引（但另见 pg_index）、序列、视图、复合类型和某些特殊关系（手册）
• “为什么“SELECT count(*) FROM bigtable;”很慢？：http: //wiki.postgresql.org/wiki/FAQ#Why_is_.22SELECT_count.28.2A.29_FROM_bigtable.3B.22_slow.3F

回答by PinnyM

Aside from running COUNT() on an indexed field (which hopefully 'id' is) - the next best thing would be to actually cache the row count in some table using a trigger on INSERT. Naturally, you'll be checking the cache instead.

除了在索引字段上运行 COUNT()（希望是 'id' ） - 下一个最好的事情是使用 INSERT 上的触发器实际缓存某个表中的行数。当然，您将改为检查缓存。

For an approximation you can try this (from https://wiki.postgresql.org/wiki/Count_estimate):

对于近似值，您可以尝试这个（来自https://wiki.postgresql.org/wiki/Count_estimate）：

select reltuples from pg_class where relname='tablename';

回答by Maryna Krasnova

You can ask for the exact value of the count in the table by simply using trigger AFTER INSERT OR DELETE Something like this

您可以通过简单地使用触发器 AFTER INSERT OR DELETE 来询问表中计数的确切值

CREATE TABLE  tcounter(id serial primary key,table_schema text, table_name text, count serial);

insert into tcounter(table_schema, table_name,count) select 'my_schema', 'my_table', count(*) from my_schema.my_table;

and use trigger

并使用触发器

CREATE OR REPLACE FUNCTION ex_count()
RETURNS trigger AS
$BODY$
BEGIN
    IF (TG_OP='INSERT') THEN
      UPDATE tcounter set count = count + 1 where table_schema = TG_TABLE_SCHEMA::TEXT and table_name = TG_TABLE_NAME::TEXT;
    ELSIF  (TG_OP='DELETE') THEN
      UPDATE tcounter set count = count - 1 where table_schema = TG_TABLE_SCHEMA::TEXT and table_name = TG_TABLE_NAME::TEXT;
    END IF;
RETURN NEW;
END$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

CREATE TRIGGER tg_counter  AFTER INSERT OR DELETE
  ON my_schema.my_table  FOR EACH ROW  EXECUTE PROCEDURE ex_count();

And ask for count

并要求计数

select * from tcounter where table_schema =  'my_schema' and table_name = 'my_table'

it means you select count(*) once for initialize first record

这意味着您选择 count(*) 一次来初始化第一条记录

回答by Mike Sherrill 'Cat Recall'

You can get an estimate from the system table "pg_stat_user_tables".

您可以从系统表“pg_stat_user_tables”中获得估计值。

select schemaname, relname, n_live_tup 
from pg_stat_user_tables 
where schemaname = 'your_schema_name'
and relname = 'your_table_name';

回答by Hyman

If your database is small, you can get an estimate of all your tables like @mike-sherrill-cat-recall suggested. This command will list all the tables though.

如果你的数据库很小，你可以像@mike-sherrill-cat-recall 建议的那样估计你所有的表。但是，此命令将列出所有表。

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

Output would be something like this:

输出将是这样的：

 schemaname |      relname       | n_live_tup
------------+--------------------+------------
 public     | items              |      21806
 public     | tags               |      11213
 public     | sessions           |       3269
 public     | users              |        266
 public     | shops              |        259
 public     | quantities         |         34
 public     | schema_migrations  |         30
 public     | locations          |          8
(8 rows)