创建索引

postgre建索引方法：
单字段索引：

CREATE INDEX index_name ON table_name (field1);

联合索引：

CREATE INDEX index_name ON table_name (field1,field2);

如果创建单列索引中列值有很多重复，而联合查询的and列没有重复项，最好创建这两个字段的联合索引，提高查询速率

在postgre里，联合索引的使用，在select里的where条件是要求有序的，比如where field1=100和where field1=100 and field2=1000都可以利用到上面这个组合索引（多条件尽量不要用or，至少用UNION代替OR，用or也用不到联合索引），但使用where field2=1000就利用不到索引了。

例如

mytest=# \d tbl_real_net
      Table "public.tbl_real_net"
 Column  |  Type   |     Modifiers      
---------+---------+--------------------
 real_id | integer | not null default 0
 net_id  | integer | not null default 0
Indexes:
    "tbl_real_net_pkey" PRIMARY KEY, btree (real_id, net_id)
    "index_real_net" btree (real_id, net_id)


mytest=# explain SELECT * from tbl_real_net where  net_id = 4 ;
                             QUERY PLAN                              
---------------------------------------------------------------------
 Seq Scan on tbl_real_net  (cost=0.00..174020.00 rows=50000 width=8)
   Filter: (net_id = 4)
(2 rows)

没有用到索引

mytest=# explain SELECT * from tbl_real_net where  net_id = 4 and real_id = 3;
                                    QUERY PLAN                                    
----------------------------------------------------------------------------------
 Bitmap Heap Scan on tbl_real_net  (cost=5.42..955.61 rows=250 width=8)
   Recheck Cond: ((real_id = 3) AND (net_id = 4))
   ->  Bitmap Index Scan on tbl_real_net_pkey  (cost=0.00..5.36 rows=250 width=0)
         Index Cond: ((real_id = 3) AND (net_id = 4))
(4 rows)

用到联合索引

mytest=# explain SELECT * from tbl_real_net where real_id = 3;
                                      QUERY PLAN                                      
--------------------------------------------------------------------------------------
 Bitmap Heap Scan on tbl_real_net  (cost=940.85..52372.32 rows=50000 width=8)
   Recheck Cond: (real_id = 3)
   ->  Bitmap Index Scan on tbl_real_net_pkey  (cost=0.00..928.35 rows=50000 width=0)
         Index Cond: (real_id = 3)

用到单个real_id的索引

mytest=# explain SELECT * from tbl_real_net where  net_id = 4 or real_id = 3;
                             QUERY PLAN                              
---------------------------------------------------------------------
 Seq Scan on tbl_real_net  (cost=0.00..199020.00 rows=99750 width=8)
   Filter: ((net_id = 4) OR (real_id = 3))
(2 rows)

未用到联合索引
如果创建的是单列索引，上述情况都能用到