postgresql - Postgres 多列索引(整数、 bool 值和数组)

Question

我有一个带有如下表的 Postgres 9.4 数据库:

| id | other_id | current | dn_ids                                | rank |
|----|----------|---------|---------------------------------------|------|
| 1  | 5        | F       | {123,234,345,456,111,222,333,444,555} | 1    |
| 2  | 7        | F       | {123,100,200,900,800,700,600,400,323} | 2    |

(更新)我已经定义了几个索引。这是 CREATE TABLE 语法:

CREATE TABLE mytable (
    id integer NOT NULL,
    other_id integer,
    rank integer,
    current boolean DEFAULT false,
    dn_ids integer[] DEFAULT '{}'::integer[]
);

CREATE SEQUENCE mytable_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;

ALTER TABLE ONLY mytable ALTER COLUMN id SET DEFAULT nextval('mytable_id_seq'::regclass);
ALTER TABLE ONLY mytable ADD CONSTRAINT mytable_pkey PRIMARY KEY (id);

CREATE INDEX ind_dn_ids ON mytable USING gin (dn_ids);
CREATE INDEX index_mytable_on_current ON mytable USING btree (current);
CREATE INDEX index_mytable_on_other_id ON mytable USING btree (other_id);
CREATE INDEX index_mytable_on_other_id_and_current ON mytable USING btree (other_id, current);

我需要优化这样的查询:

SELECT id, dn_ids
FROM mytable
WHERE other_id = 5 AND current = F AND NOT (ARRAY[100,200] && dn_ids)
ORDER BY rank ASC
LIMIT 500 OFFSET 1000

此查询工作正常，但我确信使用智能索引可以更快。表中有大约 250,000 行，我总是将 current = F 作为谓词。我与存储数组进行比较的输入数组也将有 1-9 个整数。 other_id 可以变化。但通常，在限制之前，扫描会匹配 0-25,000 行。

这是一个示例 EXPLAIN :

Limit  (cost=36944.53..36945.78 rows=500 width=65)
  ->  Sort  (cost=36942.03..37007.42 rows=26156 width=65)
        Sort Key: rank
        ->  Seq Scan on mytable  (cost=0.00..35431.42 rows=26156 width=65)
              Filter: ((NOT current) AND (NOT ('{-1,35257,35314}'::integer[] && dn_ids)) AND (other_id = 193))

该站点和 Postgres docs 上的其他答案表明可以添加复合索引以提高性能。我已经有一个 [other_id, current] 。除了 ORDER BY 子句之外，我还在各个地方读到索引可以提高 WHERE 的性能。

用于此查询的正确复合索引类型是什么？我根本不在乎空间。

我如何对 WHERE 子句中的术语进行排序是否重要？

Answer 1

1. What's the right type of compound index to use for this query? I don't care about space at all.

这取决于完整的情况。无论哪种方式，在您的情况下，您已经拥有的 GIN 索引很可能优于 GiST 索引:

Difference between GiST and GIN index

您可以与 integer 结合使用。安装附加模块后的列 btree_gin (或 btree_gist ，分别)。

Multicolumn index on 3 fields with heterogenous data types

但是，这不包括 boolean数据类型，通常作为索引列开始时没有意义。只有两个(三个包括 NULL)可能的值，它的选择性不够。

对于 integer 来说，普通的 btree 索引更有效。 .而在两个 integer 上的多列 btree 索引列肯定会有所帮助，如果组合 (other_id, dn_ids)，则必须仔细测试在多列 GIN 索引中，它的值(value)超过它的成本。可能不是。 Postgres 可以相当有效地在位图索引扫描中组合多个索引。

最后，虽然索引可用于已排序的输出，但这可能不会像您显示的那样申请查询(除非您选择表的大部分)。
不适用于更新的问题。

部分索引可能是一种选择。除此之外， 您已经拥有所需的所有索引 .

我会删除 boolean 上的无意义索引专栏 current完全，而索引只是 rank可能从未用于此查询。

2. Does it matter much how I order the terms in the WHERE clause?

WHERE的顺序条件完全无关。

问题更新后的附录

索引的效用绑定(bind)到 选择性标准。如果选择了超过大约 5%(取决于各种因素)的表，则对整个表的顺序扫描通常比处理任何索引上的开销要快 - 除外预分拣输出 ，这是索引在这种情况下仍然有用的一件事。

对于获取 的查询25,000 个中的 25,000 个 行，索引主要是为了这个——如果你附加一个 会更有趣。 LIMIT 条款。一旦 LIMIT，Postgres 可以停止从索引中获取行。很满意。

请注意 Postgres 始终需要读取 OFFSET + LIMIT行，因此性能随着两者的总和而下降。

即使有了您添加的信息，许多相关的内容仍然一无所知。我要去 假设 那:

您的谓词 NOT (ARRAY[100,200] && dn_ids)是 不是 非常有选择性。排除 1 到 10 个 ID 值通常应该保留大部分行，除非 dn_ids 中的不同元素非常少。 .

最具选择性的谓词是other_id = 5 .

NOT current 消除了大部分行。 .
旁白:current = F不是标准 Postgres 中的有效语法。必须是 NOT current或 current = FALSE ;

虽然 GIN 索引可以比任何其他索引类型更快地识别具有匹配数组的几行，但这似乎与您的查询几乎无关。我最好的猜测是 部分、多列 btree 索引 :

CREATE INDEX foo ON mytable (other_id, rank, dn_ids)
WHERE NOT current;

数组列 dn_ids在 btree 索引中不支持 &&运算符，我只是包含它以允许 index-only scans并在访问堆(表)之前过滤行。没有 dn_ids 甚至可能更快在索引中:

CREATE INDEX foo ON mytable (other_id, rank) WHERE NOT current;

GiST 索引在 Postgres 9.5 due to this new feature 中可能会变得更有趣:

Allow GiST indexes to perform index-only scans (Anastasia Lubennikova, Heikki Linnakangas, Andreas Karlsson)

旁白: current is a reserved word在标准 SQL 中，即使它在 Postgres 中被允许作为标识符。
除了 2:我假设 id是一个实际的 serial具有列默认设置的列。只是像你演示的那样创建一个序列，不会做任何事情。

Auto increment SQL function