关于Postgresql中索引的工作,我有几个问题.
@H_404_1@我有一个带有以下索引的Friends表：
@H_301_2@Friends ( user_id1,user_id2)
user_id1和user_id2是用户表的外键
>这些是等价的吗？如果不是那么为什么？
@H_301_2@Index(user_id1,user_id2) and Index(user_id2,user_id1)>如果我创建主键(user_id1,user_id2),它是否会自动为其创建索引
如果第一个问题中的索引不相等,那么在上面的主键命令上创建哪个索引？
以下是在多列索引的第二列上查询表的结果.
@H_404_1@这些效果很容易为任何人重现.试试吧吧.
我使用中型大小的23322行数据库在Debian上使用Postgresql 9.0.5进行了测试.它实现了表adr(地址)和att(属性)之间的n：m关系,但这与此无关.简化架构：
@H_301_2@CREATE TABLE adratt ( adratt_id serial PRIMARY KEY,adr_id integer NOT NULL,att_id integer NOT NULL,log_up timestamp(0) NOT NULL DEFAULT (now())::timestamp(0),CONSTRAINT adratt_uni UNIQUE (adr_id,att_id) );UNIQUE约束有效地实现了唯一索引.我用一个简单的索引重复测试,以确保并获得与预期相同的结果.
@H_301_2@CREATE INDEX adratt_idx ON adratt(adr_id,att_id)该表聚集在adratt_uni索引上,在运行测试之前：
@H_301_2@CLUSTER adratt; ANALYZE adratt;(adr_id,att_id)上的查询的顺序扫描速度尽可能快.多列索引仍将仅用于第二个索引列上的查询条件.
我运行了几次查询来填充缓存,并从十次运行中挑选出最好的以获得可比较的结果.
1.使用两列进行查询
@H_301_2@SELECT * FROM adratt WHERE att_id = 90 AND adr_id = 10; adratt_id | adr_id | att_id | log_up -----------+--------+--------+--------------------- 123 | 10 | 90 | 2008-07-29 09:35:54 (1 row)EXPLAIN ANALYZE的输出：
06004
2.使用第一列查询
@H_301_2@SELECT * FROM adratt WHERE adr_id = 10 adratt_id | adr_id | att_id | log_up -----------+--------+--------+--------------------- 126 | 10 | 10 | 2008-07-29 09:35:54 125 | 10 | 13 | 2008-07-29 09:35:54 4711 | 10 | 21 | 2008-07-29 09:35:54 29322 | 10 | 22 | 2011-06-06 15:50:38 29321 | 10 | 30 | 2011-06-06 15:47:17 124 | 10 | 62 | 2008-07-29 09:35:54 21913 | 10 | 78 | 2008-07-29 09:35:54 123 | 10 | 90 | 2008-07-29 09:35:54 28352 | 10 | 106 | 2010-11-22 12:37:50 (9 rows)EXPLAIN ANALYZE的输出：
06006
3.使用第二列查询
@H_301_2@SELECT * FROM adratt WHERE att_id = 90 adratt_id | adr_id | att_id | log_up -----------+--------+--------+--------------------- 123 | 10 | 90 | 2008-07-29 09:35:54 180 | 39 | 90 | 2008-08-29 15:46:07 ... (83 rows)EXPLAIN ANALYZE的输出：
06008
4.禁用indexscan& bitmapscan
@H_301_2@SET enable_indexscan = off; SELECT * FROM adratt WHERE att_id = 90EXPLAIN ANALYZE的输出：
@H_301_2@SET enable_bitmapscan = off SELECT * FROM adratt WHERE att_id = 90060010
EXPLAIN ANALYZE的输出：
060012
结论
正如所料,多列索引仅用于第二列的查询.正如预期的那样,效果较差,但查询仍然比没有索引快3倍.禁用索引扫描后,查询计划程序会选择位图堆扫描,其执行速度几乎一样快.只有在禁用它之后,它才会回退到顺序扫描.
