Thread

1. Planner does not use btree index for LIKE 'prefix%' on text column, but does for equivalent range query (PostgreSQL 17.4)

   Abdullah DURSUN <adursuns@gmail.com> — 2025-05-03T16:50:00Z

   Hello PostgreSQL team,
   
   I have encountered what appears to be a planner regression or bug in
   PostgreSQL 17.4 regarding the use of btree indexes for prefix searches with
   LIKE 'prefix%'.
   
   A btree index on a text column is not used for a query of the form WHERE
   col LIKE 'prefix%', even though the index is healthy, statistics are
   correct, and the query is highly selective.
   
   The same index is used and performs well for an equivalent range query
   (WHERE col >= 'prefix' AND col < 'nextprefix').
   
   - This behavior persists even after VACUUM FULL, ANALYZE, and index
   re-creation.
   - The column and index use the default collation (en_US.UTF-8).
   - The index is valid and ready.
   - The planner does not use the index for LIKE 'prefix%', but does for the
   equivalent range.
   - Rewriting the query as a range (col >= 'prefix' AND col < 'nextprefix')
   uses the index and is fast, but this should not be necessary.
   
   Thank you for your attention!
   

2. Re: Planner does not use btree index for LIKE 'prefix%' on text column, but does for equivalent range query (PostgreSQL 17.4)

   Tom Lane <tgl@sss.pgh.pa.us> — 2025-05-03T20:04:14Z

   Abdullah DURSUN <adursuns@gmail.com> writes:
   > A btree index on a text column is not used for a query of the form WHERE
   > col LIKE 'prefix%', even though the index is healthy, statistics are
   > correct, and the query is highly selective.
   
   LIKE with a prefix condition can only use an index if the index's
   collation is "C", or if it uses the "pattern" opclass.  This isn't
   new in v17, it's been true for a very long time.
   
   d1=# create table foo (t text);
   CREATE TABLE
   d1=# create index on foo (t);
   CREATE INDEX
   d1=# explain select * from foo where t like 'prefix%';
                        QUERY PLAN                      
   -----------------------------------------------------
    Seq Scan on foo  (cost=0.00..27.00 rows=7 width=32)
      Filter: (t ~~ 'prefix%'::text)
   (2 rows)
   
   d1=# create index on foo (t collate "C");
   CREATE INDEX
   d1=# explain select * from foo where t like 'prefix%';
                                  QUERY PLAN                                
   -------------------------------------------------------------------------
    Bitmap Heap Scan on foo  (cost=4.22..14.37 rows=7 width=32)
      Filter: (t ~~ 'prefix%'::text)
      ->  Bitmap Index Scan on foo_t_idx1  (cost=0.00..4.22 rows=7 width=0)
            Index Cond: ((t >= 'prefix'::text) AND (t < 'prefiy'::text))
   (4 rows)
   
   			regards, tom lane