PosgreasSQL index is not applied when select?

Question

In my database, I created a index

CREATE INDEX meeting_name_idx ON swoosh.meeting (meeting_name varchar_pattern_ops);

So based on the index that I have for table "meeting", if I do:

Explain SELECT meeting_name FROM meeting WHERE meeting_name like 'Pro%';

I would get

index only scan using meeting_name_idx on meeting

But if I changed the % in this case, and I do

Explain SELECT meeting_name FROM meeting WHERE meeting_name like '%Pro';

The result would be a sequential scan, why is that?

When we use `LIKE` operator and the first character in the search string is a wildcard character (`%` or `_`), the sql optimizer will be forced to do a table/index scan. In Postgresql, you may consider to enable `pg_trgm`. See https://scoutapm.com/blog/how-to-make-text-searches-in-postgresql-faster-with-trigram-similarity — yoonghm, Nov 21 '21 at 05:41

score 0 · Accepted Answer · answered Nov 21 '21 at 05:39

When you create a default index on the meeting_name text column, it will create a B-tree data structure. This B-tree inherently only allows for lookups starting at the very beginning of the meeting name, not from some arbitrary location in the string. So the following WHERE clauses can leverage the index:

WHERE meeting_name = 'Professional SQL Users'
WHERE meeting_name = 'Pro%'

The following cannot use the index:

WHERE meeting_name LIKE '%Pro%';

Note that Postgres actually could still use the index even for the above, but it would have to do a full index scan, along with doing many lookups in the clustered index. Most likely, Postgres would prefer to just scan the original table in this case and avoid the index.

PosgreasSQL index is not applied when select?

1 Answers1