如何在PostgreSQL中使用GIST或GIN索引与hstore列？

Question

如何在PostgreSQL中使用GIST或GIN索引与hstore列？

sqlpostgresqlindexinghstorepostgresql-9.3

3

我是一名有用的助手，可以为您翻译文本。

我正在尝试使用postgresql 9.3的hstore。我尝试像文档中所述那样为hstore列使用索引 just like documentation states。我的问题是索引似乎没有被使用。让我给你举个例子：

我创建了一个名为“Person”的表：

=# CREATE TABLE Person (Id BIGSERIAL PRIMARY KEY NOT NULL, Values hstore);

并插入了一个测试值：

=# INSERT INTO Person (Values, 'a=>1,b=>3');

如果我解释一个在“Values”列上使用运算符“@>”的SELECT查询，那么毫不意外地会得到以下结果：

=# EXPLAIN SELECT P.* FROM Person AS P WHERE P.Values @> hstore('a', '1');
                        QUERY PLAN                        
----------------------------------------------------------
 Seq Scan on person p  (cost=0.00..24.50 rows=1 width=40)
   Filter: ("values" @> '"a"=>"1"'::hstore)

没有索引 < - > 顺序扫描。有道理。无论我创建GIN或GIST索引，解释器都在谈论顺序扫描：

=# CREATE INDEX IX_GIN_VALUES ON Person USING GIN (values);
CREATE INDEX

=# EXPLAIN SELECT P.* FROM Person P WHERE P.values @> hstore('a', '1');

                        QUERY PLAN                        
----------------------------------------------------------
 Seq Scan on person p  (cost=0.00..1.01 rows=1 width=246)
   Filter: ("values" @> '"age"=>"2"'::hstore)

也许我错过了一些显而易见的东西？

- Fabzter

2个回答

3

简而言之：当表中只有少量页面时，Postgres的计划程序更喜欢跳过索引，直接加载并扫描行。

CREATE SCHEMA stackoverflow20589058;
--- CREATE SCHEMA

SET search_path TO stackoverflow20589058,"$user",public;
--- SET

CREATE EXTENSION hstore;
--- CREATE EXTENSION

CREATE TABLE Person (Id BIGSERIAL PRIMARY KEY NOT NULL, Values hstore);
--- CREATE TABLE

WITH Vals(n) AS (SELECT * FROM generate_series(1,10))
INSERT INTO Person (
  SELECT n AS Id, hstore('a=>'||n||', b=>'||n) AS Values FROM Vals
);
--- INSERT 0 10

EXPLAIN SELECT P.* FROM Person AS P WHERE P.Values @> hstore('a', '1');
---                         QUERY PLAN                        
--- ----------------------------------------------------------
---  Seq Scan on person p  (cost=0.00..24.50 rows=1 width=40)
---    Filter: ("values" @> '"a"=>"1"'::hstore)
--- (2 rows)

CREATE INDEX IX_GIN_VALUES ON Person USING GIN (values);
--- CREATE INDEX

------------------------- When there are few values, a sequential scan is
------------------------- often the best search strategy. Grabbing a few
------------------------- pages in sequence can be cheaper than making an
------------------------- extra disk seek to load the index.
EXPLAIN SELECT P.* FROM Person AS P WHERE P.Values @> hstore('a', '1');
---                        QUERY PLAN                        
--- ---------------------------------------------------------
---  Seq Scan on person p  (cost=0.00..1.12 rows=1 width=40)
---    Filter: ("values" @> '"a"=>"1"'::hstore)
--- (2 rows)

TRUNCATE Person;
--- TRUNCATE TABLE

WITH Vals(n) AS (SELECT * FROM generate_series(1,100000))
INSERT INTO Person (
  SELECT n AS Id, hstore('a=>'||n||', b=>'||n) AS Values FROM Vals
);
--- INSERT 0 100000

------------------------- When there are many rows, using the index can
------------------------- allow us to skip quite a lot of I/O; so
------------------------- Postgres's planner makes use of the index.
EXPLAIN SELECT P.* FROM Person AS P WHERE P.Values @> hstore('a', '1');
---                                    QUERY PLAN                                   
--- --------------------------------------------------------------------------------
---  Bitmap Heap Scan on person p  (cost=916.83..1224.56 rows=107 width=40)
---    Recheck Cond: ("values" @> '"a"=>"1"'::hstore)
---    ->  Bitmap Index Scan on ix_gin_values  (cost=0.00..916.80 rows=107 width=0)
---          Index Cond: ("values" @> '"a"=>"1"'::hstore)
--- (4 rows)

DROP SCHEMA stackoverflow20589058 CASCADE;
--- NOTICE:  drop cascades to 2 other objects
--- DETAIL:  drop cascades to extension hstore
--- drop cascades to table person
--- DROP SCHEMA

- solidsnack

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Denis de Bernardy · Accepted Answer

如果你只是在玩弄它，请确保添加足够的数据以使索引扫描变得有意义。如果你只有几行数据，或者许多行包含相似的值（即，你的where条件不够选择性），那么顺序扫描通常比索引扫描更快。

此外，确保在填充测试数据后分析你的表格。

为@maxm提供一些额外阅读：

(自那时起，性能已经大大改善。)

为什么他/她的索引没有被使用？

因为对于Postgres来说，扫描整个表格（只有一行）并从单个磁盘页面中过滤出该行的速度比进行索引查找，然后同样顺序扫描表格以检索行数据更快。

询问者创建索引存在问题吗？

没有，但请参见上面的链接了解何时最好使用规范化数据。

并且更喜欢使用json或jsonb而不是hstore。

查询hstore列？应该如何修复以便SELECT查询使用此类索引？

没有，但请再次参见上面的链接了解何时最好使用规范化数据。