Postgresql Index scan performed with LIKE query even though no varchar_pattern

Given the following table:

CREATE UNLOGGED TABLE table (
  col1       VARCHAR(64)   NOT NULL,
  col2       VARCHAR(255)      NOT NULL,
  CONSTRAINT table_pk PRIMARY KEY (col1,col2)
);

And following SHOW LC_COLLATE; result:

lc_collate
en_US.utf8

The following query performs an indexed scan:

SELECT * FROM table WHERE col1 ='val1' AND col2 LIKE ('prefix%') LIMIT 2;

As can be seen by its EXPLAIN ANALYZE output:

Limit  (cost=0.14..8.16 rows=1 width=662) (actual time=0.018..0.022 rows=0 loops=1)
  ->  Index Only Scan using table_pk on "table"  (cost=0.14..8.16 rows=1 width=662) (actual time=0.008..0.012 rows=0 loops=1)
        Index Cond: (col1 = 'val1'::text)
        Filter: ((col2)::text ~~ 'prefix%'::text)
        Heap Fetches: 0
Planning time: 4.562 ms
Execution time: 0.068 ms

The question is how does this query which contains LIKE operator performs an indexed scan?

By this article as well as this blog the case when using LIKE operator without "C" locale and without varchar_pattern_ops set on the index, should not be able to perform an indexed scan.

I am running this on a docker image of postgres:9.6

CodePudding user response：

If you look closely, you will see that only col1 is used as an “index condition”, that is, the index is not scanned for the condition on col2. The condition on col2 is in the “filter”.

So everything is just as you expect it to be. The sequence of events during this index scan is like this:

fetch the next index entry that has col1 = 'val1'
fetch the table row for that entry
check if the table row is visible and satisfies col2 LIKE 'prefix%'; if yes, return that row
continue scanning the index in step 1. until we are done

To help you with the underlying problem: It would be nice if you coud define the primary key using a non-standard operator class, so that the primary key index supports your query perfectly, but that is not possible. These are your options:

Drop the primary key and define a unique index with text_pattern_ops instead. If both columns are NOT NULL, that is just as good.
Create a text_pattern_ops index on col2 alone. That can be used by itself or in combination with the primary key index.
Create a second index on both columns.