在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_ 关于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   adrattWHERE  att_ID = 90AND    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 = offSELECT * FROM adratt WHERE att_ID = 90

EXPLAIN ANALYZE的输出：

060012

结论

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

总结

以上是内存溢出为你收集整理的在PostgreSQL中使用索引全部内容，希望文章能够帮你解决在PostgreSQL中使用索引所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。