gpt4 book ai didi

sql - B树索引好像没有用?

转载 作者:行者123 更新时间:2023-12-05 09:32:10 26 4
gpt4 key购买 nike

我是 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';

但两者都给我一个顺序扫描计划。

我是否缺少此处的详细信息,为什么我没有进行索引扫描?

最佳答案

要支持 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

关于sql - B树索引好像没有用?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/68384666/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com