postgresql索引介绍

忧郁者

　　创建索引通常能够提高数据库的性能，使查询速度大幅提高。同时索引的存在也为数据库中的数据维护带来的负面影响，增加的数据库与系统的资源消耗。
　　适当的建立索引总能利大于弊。
postgresql数据库中可以创建多字段、唯一、函数、部分索引多种类型的索引。可能基于主要面向OLTP系统的应用没有引入位图索引（个人猜测）。
　　测试表：



CREATE TABLE test1 (
　　id integer,
　　content varchar
　　);

查询语句：



SELECT content FROM test1 WHERE id = constant;

在没有索引的情况下，数据库需要一行行的对表test1进行扫描，test1含有多少数据块就需要扫描多少数据块，对于想要获取非常少量数据的情况下，大量的IO开销显的非常重要，对系统会带来严重的影响。通过创建索引，大大减少了数据库的扫描块数，减少了数据匹配的验证次数，更少更快的扫描路径，提高查询的性能。同样能够提高带有查询语句的delete、update操作的性能。
创建索引：



CREATE INDEX test1_id_index ON test1 (id);

删除索引：



DROP INDEX test1_id_index;


　　并行创建：
索引创建时需要与表数据保持同步，执行创建语句时系统需要对表进行锁定，期间将会对表的delete、update、insert事务进行阻塞。在生产系统中创建索引需要注意创建的时间，如果对大数据量的表创建索引时，更应该选在系统空闲时。
为了加快索引的创建速度postgresql引入了CONCURRENTLY选项，对索引进行并发创建。CONCURRENTLY选项能够使系统在不阻塞write操作的情况下执行索引创建。其实质是执行两次的表扫描，尽管解决了write的问题，但是需要更多的IO和CPU开销。
　　以下是CREATE INDEX语法：

代码



CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] name ON table [ USING method ]
    ( { column | ( expression ) } [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
    [ WITH ( storage_parameter = value [, ... ] ) ]
    [ TABLESPACE tablespace ]
    [ WHERE predicate ]