Início / coding / Perguntas / 79590141
Accepted
Andrus
Asked: 2025-04-24 16:36:28 +0800 CST

Como usar o índice para pesquisar se o trim é usado no valor de pesquisa

  • 772

Meu banco de dados Postgres 17 tem uma tabela grande com 245 colunas contendo uma coluna indexada 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);

A consulta com trim():

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

não usa o índice:

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

A mesma consulta sem trim():

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

usa o índice:

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

O Postgres converte a ribakoodcoluna para o tipo texte não consegue encontrar o índice. Como forçar a primeira consulta trim()a usar o índice?

Usando: PostgreSQL 17.0 em x86_64-windows, compilado por msvc-19.41.34120, 64 bits

postgresql

1 respostas

  1. Best Answer

    Chamar trim()o valor alvo é claramente um pouco estranho — no mínimo, ltrim()provavelmente faz mais sentido. Como não há ltrim()definição para o tipo de caractere de largura fixa, obviamente ele terminará como texto, então, para usar um índice, você só precisa converter o resultado de volta.

    Com a tabela de teste que tenho aqui, o planejador decidiu que se trata de uma varredura de índice de bitmap, mas o princípio ainda é bom.

    => 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                                                                                             │
└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

    Para quem se deparar com isso e não for o autor original, simplesmente não use o tipo de caractere de largura fixa. Quase sempre é a escolha errada.

    • 3

relate perguntas