获取只读（不可变）PostgreSQL表的行数的索引？ [英] Index to get row count of read-only (immutable) PostgreSQL table?

问题描述

我有一个脚本，每天运行几次，该脚本记录几个PostgreSQL表的行数。

I have a script that runs several times a day, which records the row counts of several PostgreSQL tables.

某些表虽然是只读的，但从不更改。 （不添加或删除行，也不更改任何值。）

Some of the tables though are read-only and never change. (No rows are added or removed, nor are any values changed.)

有没有一种方法可以快速从PostgreSQL获取行数？例如。我可以在选择来自some_table的count（*）上创建索引吗？？

Is there a way I could quickly get the row count from PostgreSQL? Eg. Could I create an index on select count(*) from some_table;?

我不想缓存这在脚本中。如果要缓存脚本，自从上次运行脚本以来，我还没有找到一种可靠的方法来确定表是否已更改。

I'd prefer not to cache this in the script. If I were to cache in the script, I haven't found a reliably way to determine if a table has been changed since the last time the script has run.

推荐答案

不幸的是，在PostgreSQL中，SELECT COUNT（*）通常比经常被使用的mysql 慢。

Unfortunately, in postgresql SELECT COUNT(*) is often slower than mysql to which it often get's compared to.

您可以使用以下查询代替SELECT COUNT（*）。

You can use the following query as an alternative to SELECT COUNT(*).

 SELECT reltuples FROM pg_class WHERE relname = 'mytable';

这并不总是100％更新，但对于不可变的表，每次都将是准确的。瞬间。对于非常大的表，百分比误差将非常小，因此值得节省大量时间。

This is not always 100% upto date but for immutable tables it will be accurate every time. And instant. For very large tables the percentage error will be very small and thus well worth the massive saving in time.

如果这很重要，并且该表不包含空值，则可以使用

If it does matter and the table does not contain nulls, you can use

 SELECT COUNT(primary_key_column) FROM table

中选择COUNT（primary_key_column）

，这将大大快于SELECT COUNT（*）

and this will be significantly faster than SELECT COUNT(*)

这篇关于获取只读（不可变）PostgreSQL表的行数的索引？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！