PostgreSQL计数查询的优化

Question

我在postgresql中有一张表，其中包含一个经常更新的数组。

在我的应用程序中，我需要获取特定参数不存在于该数组列中的行数。我的查询如下：

select count(id) 
from table 
where not (ARRAY['parameter value'] <@ table.array_column)

然而，当增加查询的行数和执行次数（每秒数百或数千次）时，性能会大幅降低。我认为在PostgreSQL中计数可能具有线性执行顺序（我并不完全确定）。

基本上我的问题是：

是否存在我不知道的适用于这种情况的现有模式？最佳方法是什么？

如果您能给我任何建议，我将非常感激。

- jeruki

---

我不确定，但是我认为在表格.array_column上创建一个GIN索引可以帮助加快速度。您需要运行EXPLAIN来找出答案。请参见此处：http://dba.stackexchange.com/a/27505/1822 - user330315

当表变得庞大时，在Postgres中使其高效将会很困难。当测试“包含在”而不是您的谓词中的“不包含在”时，GIN索引只有帮助作用。如果计数不需要100％准确，您可以尝试在应用程序层使用一些TTL进行缓存。如果表的写入速率不太高，您可以合理地使用触发器来更新另一个包含当前计数的表。 - dbenhur

最好展示你的版本和explain analyze; 请参见http://stackoverflow.com/tags/postgresql-performance/info - Craig Ringer

我认为属性列表可能是固定的。如果这样有助于解决问题，那么它肯定可以被视为固定的。 - Juan Carlos Coto

有人在这里问了同样的问题。我建议使用一个辅助表来存储表的计数，该计数将由触发器更新。请参阅此处以获取有关缓慢计数的更多信息。 - didierc

Answer 1

实际上，PostgreSQL支持在数组列上创建GIN索引。不幸的是，似乎无法将其用于NOT ARRAY[...] <@ indexed_col，而且对于经常更新的表，GIN索引也不适合。

Demo:

CREATE TABLE arrtable (id integer primary key, array_column integer[]);

INSERT INTO arrtable(1, ARRAY[1,2,3,4]);

CREATE INDEX arrtable_arraycolumn_gin_arr_idx
ON arrtable USING GIN(array_column);

-- Use the following *only* for testing whether Pg can use an index
-- Do not use it in production.
SET enable_seqscan = off;

explain (buffers, analyze) select count(id) 
from arrtable 
where not (ARRAY[1] <@ arrtable.array_column);

很遗憾，这表明按照现有的写法我们不能使用索引。如果不否定条件，则可以使用它，因此您可以搜索并计算包含搜索元素的行数（通过删除“NOT”）。您可以使用索引计算包含目标值的条目数量，然后从所有条目的计数中减去该结果。由于在 PostgreSQL（9.1及更早版本）中计算表中所有行的数量相当缓慢且需要顺序扫描，因此这实际上比您当前的查询更慢。如果您在id上有一个b树索引，则在9.2上可能会使用仅索引扫描来计算行数，那么这可能是可以接受的：

SELECT (
  SELECT count(id) FROM arrtable
) - (
  SELECT count(id) FROM arrtable 
  WHERE (ARRAY[1] <@ arrtable.array_column)
);

保证在Pg 9.1及以下版本中，其性能肯定比您的原始版本差，因为除了需要seqscan之外，原始版本还需要GIN索引扫描。我已经在9.2上进行了测试，它确实会使用索引进行计数，因此值得在9.2上探索。对于一些不太琐碎的虚拟数据：

drop index arrtable_arraycolumn_gin_arr_idx ;
truncate table arrtable;
insert into arrtable (id, array_column)
select s, ARRAY[1,2,s,s*2,s*3,s/2,s/4] FROM generate_series(1,1000000) s;
CREATE INDEX arrtable_arraycolumn_gin_arr_idx
ON arrtable USING GIN(array_column);

请注意，像这样的GIN索引会显著减慢更新速度，并且在创建时相当缓慢。它不适合那些更新频繁的表格 - 就像你的表格一样。

更糟糕的是，使用该索引的查询时间最长可达到原始查询的两倍，并且在相同数据集上最多需要一半的时间。例如使用ARRAY[1]时性能最差，原始查询需要2秒而该索引则需要4秒。当索引高度具有选择性（即匹配较少，例如ARRAY[199]）时，它运行时间约为1.2秒，而原始查询则需要3秒。对于此查询，这个索引根本不值得拥有。

教训是什么？有时，正确答案就是进行顺序扫描。

既然这对于您的命中率不够用，请尝试像@maniek建议的那样将数组反转为entry没有的参数列表，这样可以使用GiST索引；或者像@debenhur建议的那样维护一个包含触发器的物化视图。

- Craig Ringer

Answer 2

有没有我不知道的适用于这种情况的现有模式？对此最好的方法是什么？

在这种情况下，您最好将模式规范化。将数组拆分为表格。在属性表上添加B树索引，或按照property_id使主键有序，以便进行高效搜索。

CREATE TABLE demo( id integer primary key );
INSERT INTO demo (id) SELECT id FROM arrtable;
CREATE TABLE properties (
  demo_id integer not null references demo(id),
  property integer not null,
  primary key (demo_id, property)
);
CREATE INDEX properties_property_idx ON properties(property);

然后您可以查询属性：

SELECT count(id) 
FROM demo 
WHERE NOT EXISTS (
  SELECT 1 FROM properties WHERE demo.id = properties.demo_id AND property = 1
)

我原以为这个查询比原来的要快得多，但实际上在相同的样本数据下，它与原始查询花费的时间大致相同；它运行的时间范围是2秒到3秒，与您的原始查询相同。这是同样的问题，寻找不存在的内容比寻找存在的内容要慢得多；如果我们正在寻找包含某个属性的行，则可以避免对demo进行顺序扫描，直接扫描properties以查找匹配的ID。

同样，在包含数组的表上进行顺序扫描同样可以完成工作。

- Craig Ringer

---

非常感谢您提供的详细解释，看起来在我目前的情况下，最好进行顺序计数或考虑其他存储信息的方式以加快搜索速度。再次非常感谢，这真的非常有用。 - jeruki

Answer 3

我认为你当前的数据模型无法胜任。尝试思考一下数据库必须执行的算法来处理你的查询。如果没有对数据进行顺序扫描，就无法实现。

你能否调整列，使其存储数据的倒数（这样查询将是select count(id) from table where ARRAY[‘parameter value’] <@ table.array_column）？这个查询将使用gin/gist索引。

- maniek