postgresqlpostgresql-performancepg-trgm

How to make PostgreSQL use gin_trgm_ops index for equality comparison

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?

Solution

  • 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.