Using a GIN index in PostgreSQL with a LIKE clause

Asked Oct 14 '22 at 06:52

Active Oct 14 '22 at 11:54

Viewed 67 times

I'm wondering (I know it's stupid, but...) why the first query works in postgreSQL 11 by index scan (using a GIN index), and the second and the third do not.

select * FROM test_tbl WHERE "TEST_COL" like '%stinge'::text
select * FROM test_tbl WHERE "TEST_COL" like 'string%'::text
select * FROM test_tbl WHERE "TEST_COL" like '%string%'::text

edited Oct 14 '22 at 11:54

asked Oct 14 '22 at 06:52

Nicola Urbinati

Did you install the extension pg_trgm? And how did you create the index? https://www.postgresql.org/docs/11/pgtrgm.html#id-1.11.7.40.7 – Frank Heikens Oct 14 '22 at 07:09
This question exhibits a clear lack of information. – Laurenz Albe Oct 14 '22 at 07:20
Look at the query plans, using `EXPLAIN (ANALYZE, BUFFERS)`. Turn track_io_timing on first if possible. – jjanes Oct 14 '22 at 13:57

Using a GIN index in PostgreSQL with a LIKE clause

0 Answers0

Linked