Postgresql Index scan performed with LIKE query even though no varchar_pattern_ops is defined and not using "C" Locale

Question

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

Laurenz Albe · Accepted Answer · 2021-12-01 12:55:39Z

2

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.

edited Dec 1, 2021 at 12:55

answered Dec 1, 2021 at 10:10

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Nom1fan Over a year ago

Thanks for your answer! So if I understand correctly, in order for both columns to be indexed scanned and not just col1, I do need to use varchar_pattern_ops or text_pattern_ops on the primary key?

Laurenz Albe Over a year ago

You would, but you cannot use a non-standard operator class with a primary key index. Either create a second index (not appealing) or do without a primary key and define a unique index on non-null columns (which is just as good as a primary key and the best solution).

Nom1fan Over a year ago

Let's say I went for this solution CREATE INDEX col2_idx ON table (col2 VARCHAR_PATTERN_OPS); . How should my explain analyze look now? Because I can still see the "filter".

Laurenz Albe Over a year ago

That's because the second condition is not selective enough to warrant a second index scan. The plan in your question has no "rows removed by filter".

Nom1fan Over a year ago

Was it because I didn't have any data in the table? I inserted some now and I see even though I added the index above, the query performs a seq scan:

Limit  (cost=0.00..0.05 rows=2 width=38) (actual time=0.285..0.306 rows=2 loops=1)   ->  Seq Scan on "table"  (cost=0.00..2334.01 rows=99991 width=38) (actual time=0.275..0.284 rows=2 loops=1)         Filter: (((col2)::text ~~ 'job_name_prefix%'::text) AND ((col1)::text = 'SOME_CAT'::text)) Planning time: 0.999 ms Execution time: 0.354 ms

|

Collectives™ on Stack Overflow

Postgresql Index scan performed with LIKE query even though no varchar_pattern_ops is defined and not using "C" Locale

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related