Re: non index use on LIKE on a non pattern string

From: Cédric Villemain <cedric(at)2ndquadrant(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Cc: Guillaume Cottenceau <gc(at)mnc(dot)ch>
Subject: Re: non index use on LIKE on a non pattern string
Date: 2012-06-08 11:51:50
Message-ID: 201206081351.54589.cedric@2ndquadrant.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

> I have noticed that with a SELECT query containing the following
> constraint:
>
> column LIKE ?
>
> and an index on that column, PostgreSQL will not use the index
> even if the parameter doesn't contain special pattern characters
> such as %.

you should have a postgresql 8.3,isn't it ?
like is equal to "=" in your case, since 8.4

Also you probably want to have a look at
http://www.postgresql.org/docs/9.1/static/indexes-opclass.html
about your index definition (add the "text_pattern_ops" when required)

--
Cédric Villemain +33 (0)6 20 30 22 52
http://2ndQuadrant.fr/
PostgreSQL: Support 24x7 - Développement, Expertise et Formation

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Rural Hunter 2012-06-08 13:33:08 how to change the index chosen in plan?
Previous Message Guillaume Cottenceau 2012-06-08 11:11:11 non index use on LIKE on a non pattern string