我有一个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
这个查询运行良好，但我相信使用智能索引会更快。表中大约有250000行，我总是将current = F作为谓词。我正在将输入数组与存储的数组进行比较，它也将有1-9个整数。other_id可以变化。但通常，在限制之前，扫描将在0-25000行之间匹配。
下面是一个示例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文档建议可以添加一个复合索引来提高性能。我已经在[other_id, current]上有一个了。我在很多地方也读到，除了WHERE子句之外，索引还可以提高ORDER BY的性能。
用于此查询的复合索引的正确类型是什么？我根本不在乎空间。
我如何订购
WHERE条款中的条款有多重要？
- 用于此查询的复合索引的正确类型是什么？我根本不在乎空间
这取决于整体情况。无论哪种方式，在您的情况下，您已经拥有的GIN索引很可能优于GiST索引：
- GiST和GIN索引之间的差异
一旦安装了额外的模块btree_gin（或btree_gist），就可以将其中一个与integer列组合。
- 具有异构数据类型的3个字段的多列索引
但是，这并不包括boolean数据类型，它通常作为索引列一开始就没有意义。只有两个（包括NULL在内的三个）可能的值，这是不够选择性的。
对于CCD_ 12，普通btree索引更有效。虽然在两个integer列上使用多列btree索引肯定会有所帮助，但您必须仔细测试在多列GIN索引中组合(other_id, dn_ids)是否物有所值。可能不会。Postgres可以在位图索引扫描中高效地组合多个索引。
最后，虽然索引可以用于排序输出，但这可能不会像您显示的那样应用查询（除非您选择了表的大部分）
不适用于更新的问题。
部分索引可能是一个选项。除此之外，您已经拥有了所需的所有索引。
我会完全删除boolean列current上的无意义索引，而仅rank上的索引可能从未用于此查询。
- 我如何订购
WHERE条款中的条款有多重要
CCD_ 19条件的阶数是完全无关的。
问题更新后的补遗
索引的效用与选择性标准有关。如果选择了大约5%以上的表（取决于各种因素），则对整个表的顺序扫描通常比处理任何索引的开销更快——除了预排序输出之外，这是索引在这种情况下仍然有用的一件事。
对于一个获取250000行中的25000行的查询，索引主要用于此目的——如果附加一个LIMIT子句，则会变得更加有趣。一旦满足LIMIT，Postgres就可以停止从索引中获取行。
请注意，Postgres总是需要读取OFFSET+LIMIT行，因此性能会随着两者的总和而恶化。
即使添加了您的信息，许多相关信息仍处于未知状态。我将假设：
- 谓词
NOT (ARRAY[100,200] && dn_ids)的选择性很强。排除1到10个ID值通常应该保留大多数行，除非dn_ids中有非常少的不同元素 - 最具选择性的谓词是
other_id = 5 - 用CCD_ 27消除了相当一部分行
旁白：current = FNOT current或current = FALSE
虽然GIN索引可以比任何其他索引类型更快地识别具有匹配数组的少数行，但这似乎与查询无关。我的最佳猜测是这个部分多列btree索引：
CREATE INDEX foo ON mytable (other_id, rank, dn_ids)
WHERE NOT current;
btree索引中的数组列dn_ids无法支持&&运算符，我只是在访问堆（表）之前将其包含在内，以允许仅进行索引扫描和筛选行。如果索引中没有dn_ids，甚至可能更快：
CREATE INDEX foo ON mytable (other_id, rank) WHERE NOT current;
由于这个新功能：，GiST索引在Postgres9.5中可能会变得更有趣
允许GiST索引执行仅索引扫描（Anastasia Lubennikova，Heikki Linnakangas，Andreas Karlsson）
旁白：current在标准SQL中是一个保留字，即使它在Postgres中被允许作为标识符
旁白2：我假设id是一个实际的serial列，并设置了列默认值。只是创建一个序列，就像你演示的那样，不会有任何作用。
- 自动递增SQL函数
不幸的是，我不认为你可以将BTree和GIN/GIST索引组合成一个单一的复合索引，所以规划者必须在使用other_id索引和dn_ids索引之间做出选择。正如您所指出的，使用other_id的一个优点是可以使用多列索引来提高排序性能。这样做的方法是
CREATE INDEX index_mytable_on_other_id_and_current
ON mytable (other_id, rank) WHERE current = F;
这使用了部分索引，当您按秩排序并查询other_id时，可以跳过排序步骤。
根据other_id的基数，这样做的唯一好处可能是排序。因为你的计划有一个限制条款，所以很难判断。如果您使用的是表的1/5以上，那么SEQ扫描可能是最快的选择，尤其是如果您使用标准HDD而不是固态硬盘。如果你是规划师，当你知道IDX扫描更快时（你已经用enable_seqscan false测试过了，你可能想尝试微调你的random_page_cost或effective_cache_size。
最后，我建议不要保留所有这些索引。找到你需要的，然后剔除剩下的。索引会导致插入的性能大幅下降（尤其是mutli列和GIN/GIST索引）。
查询最简单的索引是mytable(other_id, current)。这将处理前两种情况。这将是一个普通的b-tree类型索引。
您可以使用mytable(dn_ids)上的GIST索引来满足数组条件。
然而，我认为不能在一个索引中混合不同的数据类型，至少不能没有扩展。