在PostgreSQL中使用索引

前端之家收集整理的这篇文章主要介绍了在PostgreSQL中使用索引前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。
关于Postgresql中索引的工作,我有几个问题.
我有一个带有以下索引的Friends表:
  1. Friends ( user_id1,user_id2)

user_id1和user_id2是用户表的外键

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

  1. 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关系,但这与此无关.简化架构:

  1. CREATE TABLE adratt (
  2. 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)
  3. );

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

  1. CREATE INDEX adratt_idx ON adratt(adr_id,att_id)

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

  1. CLUSTER adratt;
  2. ANALYZE adratt;

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

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

1.使用两列进行查询

  1. SELECT *
  2. FROM adratt
  3. WHERE att_id = 90
  4. AND adr_id = 10;
  5.  
  6. adratt_id | adr_id | att_id | log_up
  7. -----------+--------+--------+---------------------
  8. 123 | 10 | 90 | 2008-07-29 09:35:54
  9. (1 row)

EXPLAIN ANALYZE的输出

06004

2.使用第一列查询

  1. SELECT * FROM adratt WHERE adr_id = 10
  2.  
  3. adratt_id | adr_id | att_id | log_up
  4. -----------+--------+--------+---------------------
  5. 126 | 10 | 10 | 2008-07-29 09:35:54
  6. 125 | 10 | 13 | 2008-07-29 09:35:54
  7. 4711 | 10 | 21 | 2008-07-29 09:35:54
  8. 29322 | 10 | 22 | 2011-06-06 15:50:38
  9. 29321 | 10 | 30 | 2011-06-06 15:47:17
  10. 124 | 10 | 62 | 2008-07-29 09:35:54
  11. 21913 | 10 | 78 | 2008-07-29 09:35:54
  12. 123 | 10 | 90 | 2008-07-29 09:35:54
  13. 28352 | 10 | 106 | 2010-11-22 12:37:50
  14. (9 rows)

EXPLAIN ANALYZE的输出

06006

3.使用第二列查询

  1. SELECT * FROM adratt WHERE att_id = 90
  2.  
  3. adratt_id | adr_id | att_id | log_up
  4. -----------+--------+--------+---------------------
  5. 123 | 10 | 90 | 2008-07-29 09:35:54
  6. 180 | 39 | 90 | 2008-08-29 15:46:07
  7. ...
  8. (83 rows)

EXPLAIN ANALYZE的输出

06008

4.禁用indexscan& bitmapscan

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

EXPLAIN ANALYZE的输出

060010

  1. SET enable_bitmapscan = off
  2. SELECT * FROM adratt WHERE att_id = 90

EXPLAIN ANALYZE的输出

060012

结论

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

猜你在找的Postgre SQL相关文章