在PostgreSQL中使用索引

关于Postgresql中索引的工作,我有几个问题.
我有一个带有以下索引的Friends表：

Friends ( user_id1,user_id2)

user_id1和user_id2是用户表的外键

>这些是等价的吗？如果不是那么为什么？

Index(user_id1,user_id2) and Index(user_id2,user_id1)

>如果我创建主键(user_id1,user_id2),它是否会自动为其创建索引

如果第一个问题中的索引不相等,那么在上面的主键命令上创建哪个索引？

以下是在多列索引的第二列上查询表的结果.
这些效果很容易为任何人重现.试试吧吧.

我使用中型大小的23322行数据库在Debian上使用Postgresql 9.0.5进行了测试.它实现了表adr(地址)和att(属性)之间的n：m关系,但这与此无关.简化架构：

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约束有效地实现了唯一索引.我用一个简单的索引重复测试,以确保并获得与预期相同的结果.

CREATE INDEX adratt_idx ON adratt(adr_id,att_id)

该表聚集在adratt_uni索引上,在运行测试之前：

CLUSTER adratt;
ANALYZE adratt;

(adr_id,att_id)上的查询的顺序扫描速度尽可能快.多列索引仍将仅用于第二个索引列上的查询条件.

我运行了几次查询来填充缓存,并从十次运行中挑选出最好的以获得可比较的结果.

1.使用两列进行查询

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.使用第一列查询

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.使用第二列查询

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

SET enable_indexscan = off;
SELECT * FROM adratt WHERE att_id = 90

EXPLAIN ANALYZE的输出：

060010

SET enable_bitmapscan = off
SELECT * FROM adratt WHERE att_id = 90

EXPLAIN ANALYZE的输出：

060012

结论

正如所料,多列索引仅用于第二列的查询.正如预期的那样,效果较差,但查询仍然比没有索引快3倍.禁用索引扫描后,查询计划程序会选择位图堆扫描,其执行速度几乎一样快.只有在禁用它之后,它才会回退到顺序扫描.

在PostgreSQL中使用索引

猜你在找的Postgre SQL相关文章