Skip navigation

Category Archives: PostgreSQL

PostgreSQL查询优化 – 函数索引

nodes表是我用来存储硬件设备的一个表。由于各种硬件设备的属性数量不同，我 采用了HSTORE来进行存储。 

                                  Table "public.nodes"
  Column  |          Type          |                     Modifiers
----------+------------------------+----------------------------------------------------
 id       | integer                | not null default nextval('nodes_id_seq'::regclass)
 manifest | character varying(60)  | not null
 cn       | character varying(124) | not null
 value    | hstore                 |
 depends  | integer[]              |

其中，value->'node_tag' 用来存储设备的“应用标签”。为了前段程序处理方便， 他是一个字符串类型的数据。我们采用“|”来分隔不同的标签。例如： 

    ?column?
-----------------
 |java|pub|

表示该设备拥有两个“应用标签”java 和 pub。

我们的应用，需要通过“应用标签”来进行设备查询。采用如下的语句： 

select value->'node_tag' from node_cache where regexp_split_to_array(value->'node_tag', '\|') @> ARRAY['pub'];

调用查询分析器可以看出，因为使用了顺序搜索，他的性能很差。 

database=> EXPLAIN ANALYZE  select value->'node_tag' from node_cache where regexp_split_to_array(value->'node_tag', '\|') @> ARRAY['pub'];
                                                  QUERY PLAN
---------------------------------------------------------------------------------------------------------------
 Seq Scan on node_cache  (cost=0.00..5505.88 rows=53 width=593) (actual time=106.390..135.642 rows=39 loops=1)
   Filter: (regexp_split_to_array((value -> 'node_tag'::text), '\|'::text) @> '{pub}'::text[])
 Total runtime: 135.677 ms
(3 rows)

万幸的是PostgreSQL提供了可定制的函数索引，我们可以针对 regexp_split_to_array 的结果进行索引。例如： 

CREATE index idx_test_splitted_node_tag on node_cache using gin(regexp_split_to_array(value->'node_tag', '\|'));

采用GIN倒排索引，查询效率有了显著的提升（135ms vs 0。175ms）, 

database=> explain ANALYZE  select value->'node_tag' from node_cache where regexp_split_to_array(value->'node_tag', '\|') @> ARRAY['pub'];
                                                              QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on node_cache  (cost=12.42..208.36 rows=53 width=593) (actual time=0.055..0.137 rows=39 loops=1)
   Recheck Cond: (regexp_split_to_array((value -> 'node_tag'::text), '\|'::text) @> '{pub}'::text[])
   ->  Bitmap Index Scan on idx_test_splitted_node_tag  (cost=0.00..12.41 rows=53 width=0) (actual time=0.034..0.034 rows=39 loops=1)
         Index Cond: (regexp_split_to_array((value -> 'node_tag'::text), '\|'::text) @> '{pub}'::text[])
 Total runtime: 0.175 ms
(5 rows)