主页 / coding / 问题 / 79590141
Accepted
Andrus
Asked: 2025-04-24 16:36:28 +0800 CST

如果搜索值中使用了修剪，如何使用索引进行搜索

  • 772

我的 Postgres 17 数据库有一个大表，其中有 245 列，其中包含一个索引列ribakood：

CREATE TABLE firma2.toode
(
...
    ribakood character(20) COLLATE pg_catalog."default",
...
);

CREATE INDEX toode_ribakood_idx
    ON firma2.toode 
    (ribakood COLLATE pg_catalog."default" ASC NULLS LAST);

查询如下trim()：

explain analyze select toode,ostuhind, nimetus, pangateen
  from firma2.toode where ribakood=TRIM('TESTTOODE/H ')

不使用索引：

Gather  (cost=1000.00..575155.04 rows=4927 width=114) (actual time=101.341..2257.639 rows=1 loops=1)
  Workers Planned: 3
  Workers Launched: 0
  ->  Parallel Seq Scan on toode (cost=0.00..573662.34 rows=1589 width=114) (actual time=101.186..2257.436 rows=1 loops=1)
        Filter: ((ribakood)::text = 'TESTTOODE/H'::text)
        Rows Removed by Filter: 986481
Planning Time: 0.098 ms
Execution Time: 2257.653 ms

相同的查询，但不包含trim()：

explain analyze select toode,ostuhind, nimetus, pangateen
  from firma2.toode where ribakood='TESTTOODE/H'

使用索引：

Index Scan using toode_ribakood_idx on toode  (cost=0.42..12.45 rows=2 width=114) (actual time=0.475..0.477 rows=1 loops=1)
  Index Cond: (ribakood = 'TESTTOODE/H'::bpchar)
Planning Time: 0.147 ms
Execution Time: 0.490 ms

Postgres 将ribakood列强制转换为类型text，但找不到索引。如何强制第一个查询使用trim()索引？

使用：x86_64-windows 上的 PostgreSQL 17.0，由 msvc-19.41.34120 编译，64 位

postgresql

1 个回答

  1. Best Answer

    调用trim()目标值显然有点奇怪——至少ltrim()可能更合理。由于固定宽度字符类型没有ltrim()定义，它最终显然会返回文本，所以要使用索引，你只需要将结果强制转换回去即可。

    对于我在这里得到的测试表，规划器已确定它处于位图索引扫描中，但原理仍然很好。

    => EXPLAIN ANALYSE SELECT * FROM tt WHERE c = 'row 5                   ';
┌──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                                      QUERY PLAN                                                      │
├──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Bitmap Heap Scan on tt  (cost=139.18..7153.38 rows=5000 width=88) (actual time=0.026..0.026 rows=1 loops=1)          │
│   Recheck Cond: (c = 'row 5                   '::bpchar)                                                             │
│   Heap Blocks: exact=1                                                                                               │
│   ->  Bitmap Index Scan on tt_c_idx  (cost=0.00..137.93 rows=5000 width=0) (actual time=0.023..0.023 rows=1 loops=1) │
│         Index Cond: (c = 'row 5                   '::bpchar)                                                         │
│ Planning Time: 0.078 ms                                                                                              │
│ Execution Time: 0.045 ms                                                                                             │
└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

=> EXPLAIN ANALYSE SELECT * FROM tt WHERE c = ltrim('row 5  ')::character(20);
┌──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                                      QUERY PLAN                                                      │
├──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Bitmap Heap Scan on tt  (cost=139.18..7153.38 rows=5000 width=88) (actual time=0.040..0.041 rows=1 loops=1)          │
│   Recheck Cond: (c = 'row 5               '::character(20))                                                          │
│   Heap Blocks: exact=1                                                                                               │
│   ->  Bitmap Index Scan on tt_c_idx  (cost=0.00..137.93 rows=5000 width=0) (actual time=0.031..0.031 rows=1 loops=1) │
│         Index Cond: (c = 'row 5               '::character(20))                                                      │
│ Planning Time: 0.129 ms                                                                                              │
│ Execution Time: 0.051 ms                                                                                             │
└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

    对于那些可能遇到这种情况但并非原帖作者的人，建议永远不要使用等宽字符类型。这几乎总是错误的选择。

    • 3

相关问题