postgresql B树索引似乎没有用？

neskvpey 于 2023-08-04 发布在 PostgreSQL

关注(0)|答案(2)|浏览(151)

我是Postgres的新手，我正试图更多地了解索引。我使用的是12.5版本，下面是我的代码：

CREATE TABLE textfun(content TEXT);
CREATE UNIQUE INDEX text_b ON textfun(content);

INSERT INTO textfun (content)
SELECT (CASE WHEN (random()<=0.3) THEN 'https://mywebsite/nanana/'
WHEN (random()<=0.6) THEN 'https://mywebsite/friendy/'
ELSE 'https://mywebsite/mina/' END) || generate_series(1000000,2000000);

字符串
在这里，我创建了一百万条记录，希望看到索引的效果。
当我尝试获取查询计划时：

explain analyze
SELECT content FROM textfun WHERE content LIKE 'mina%';

型
我得到这个回复：

Gather  (cost=1000.00..14300.34 rows=100 width=32) (actual time=77.574..80.054 rows=0 loops=1)
  Workers Planned: 2    
  Workers Launched: 2
  Parallel Seq Scan on textfun  (cost=0.00..13290.34 rows=42 width=32) (actual time=69.022..69.022 rows=0 loops=3)
     Filter: (content ~~ 'mina%'::text)
     Rows Removed by Filter: 333334  
Planning Time: 0.254 ms  
Execution Time: 80.071 ms 
(8 rows)

型
我以为是并行索引扫描。
我试过：

explain analyze
SELECT content FROM textfun WHERE content LIKE '1500000%';

型
以及：

explain analyze
SELECT content FROM textfun WHERE content LIKE '%mina';

型
但都给予了我一个顺序扫描计划
这里是否有我遗漏的细节，为什么我没有得到索引扫描？

postgresql

来源：https://stackoverflow.com/questions/68384666/b-tree-index-does-not-seem-to-be-used

2条答案

按热度按时间

c9x0cxw01#

要支持LIKE条件，您需要使用text_pattern_ops创建索引

CREATE UNIQUE INDEX text_b ON textfun(content text_pattern_ops);

字符串
这样，结果就是以下执行计划：

Bitmap Heap Scan on textfun  (cost=191.68..7654.53 rows=5000 width=32) (actual time=2.553..2.554 rows=0 loops=1)
  Filter: (content ~~ '1500000%'::text)
  ->  Bitmap Index Scan on text_b  (cost=0.00..190.43 rows=5000 width=0) (actual time=2.550..2.550 rows=0 loops=1)
        Index Cond: ((content ~>=~ '1500000'::text) AND (content ~<~ '1500001'::text))
Planning Time: 6.247 ms
Execution Time: 6.809 ms

型
Online example的

赞(0）回复(0）举报 2023-08-04

gwbalxhn2#

正确性第一。您的 predicate 将找不到'https：//mywebsite/mina/'：

content LIKE 'mina%'

字符串
其中一个会起作用：

content LIKE '%mina%'
content ~ 'mina'

型
但是btree索引都不支持。一个trigram index可以做到这一点：

CREATE INDEX ON textfun USING gin (content gin_trgm_ops);

型
请参阅：

PostgreSQL LIKE查询性能变化

或者是text search index（在URI中分隔单词之后...）
但是GIN索引不能强制唯一性。您可能需要第二个B树索引。（或暗示这样的索引的约束）。
为了真正支持你原来的左锚 predicate ，我将使用一个B树索引**COLLATE "C"**：

CREATE UNIQUE INDEX text_b ON textfun(content COLLATE "C");

型
Per-column collation support added with Postgres 9.1基本上淘汰了旧的xxx_pattern_ops operator classes。请参阅：

Is there a difference between text_pattern_ops and COLLATE "C"?的
db<>fiddle here * -使用COLLATE "C"，减少行数（足以证明这一点）。

以下是Postgres中模式匹配选项的全面概述：

Pattern matching with LIKE, SIMILAR TO or regular expressions的

除此之外，只有三个不同值的测试用例不是很有用。当Postgres期望获取所有行的百分之几以上时，它通常根本不使用任何索引，因为顺序扫描通常更快。而且它会知道一些“最常见的值”，因为它通过ANALYZE（或默认情况下的autovacuum）更新统计信息。
搜索content LIKE '1500000%'在这方面是不同的，因为Postgres会知道它不常见，并使用适用的索引。
索引优化取决于整体情况：环境和要求…

赞(0）回复(0）举报 2023-08-04

我来回答

postgresql B树索引似乎没有用？

2条答案

相关问题

热门标签

最新问答