3

There is a table and a gin index, Insert 1,000,000 random numbers. 0 < number < 100,000.
Test two equivalent queries

create table Test
(
    id   serial primary key,
    code varchar(255) not null
);
create index Test_code_gin on Test using gin (code gin_trgm_ops);

-- Test1
explain analyse
select * from Test where code like '1234';

-- Test2
explain analyse 
select * from Test where code = '1234';

Test1 used gin_trgm_ops index, Execution Time: 1.640 ms;
Test2 does not use index, Execution Time: 24.531 ms;

What can I do to make PostgreSQL use the index? Or modify the ORM strategy and my SQL statement? Or simply add a BTree index?

user10339780
  • 953
  • 7
  • 13
  • 1
    From the [manual](https://www.postgresql.org/docs/14/pgtrgm.html#id-1.11.7.42.8), the GIN index should support the condition with equality : "The pg_trgm module provides GiST and GIN index operator classes that allow you to create an index over a text column for the purpose of very fast similarity searches. These index types support trigram-based index searches for LIKE, ILIKE, ~, ~* and **=** queries." Can you show the EXPLAIN ANALYSE results for both queries ? – Edouard Dec 16 '21 at 21:28
  • 2
    Why gin index to begin with? If you only need to support equality, a btree index is a much better choice. –  Dec 16 '21 at 21:58

1 Answers1

5

That capability was added in v14. So you could upgrade PostgreSQL to v14, then upgrade pg_trgm to its newest version (upgrading with pg_upgrade does not do that automatically).

But I would just create the btree index, as that should be faster for equality than using trigrams. Upgrading just to get access to an inferior implementation is not a victory, unless the extra space needed for the index is intolerable.

jjanes
  • 37,812
  • 5
  • 27
  • 34