pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Antony Paul <antonypaul24@gmail.com>
To: Russell Smith <mr-russ@pws.com.au>
Cc: pgsql-performance@postgresql.org
Subject: Re: How to boost performance of ilike queries ?
Date: Tue, 25 Jan 2005 13:53:26 +0530
Message-ID: <2989532e05012500235c78fd47@mail.gmail.com> (raw)
In-Reply-To: <200501242058.54670.mr-russ@pws.com.au>
References: <2989532e05012401184b0c3ef9@mail.gmail.com>
	<200501242058.54670.mr-russ@pws.com.au>

Creating an index and using lower(column) does not change the explain
plan estimates.
It seems that it is not using index for like or ilike queries
irrespective of whether it have a pattern matching character in it or
not. (using PostgreSQL 7.3.3)

On googling I found this thread 

http://archives.postgresql.org/pgsql-sql/2004-11/msg00285.php

It says that index is not used if the search string begins with a % symbol.

rgds
Antony Paul

On Mon, 24 Jan 2005 20:58:54 +1100, Russell Smith <mr-russ@pws.com.au> wrote:
> On Mon, 24 Jan 2005 08:18 pm, Antony Paul wrote:
> > Hi,
> >     I have a query which is executed using ilike. The query values are
> > received from user and it is executed using PreparedStatement.
> > Currently all queries are executed as it is using iilike irrespective
> > of whether it have a pattern matching character or not. Can using =
> > instead of ilike boot performance ?.  If creating index can help then
> > how the index should be created on lower case or uppercase ?.
> > 
> It depends on the type of queries you are doing.
> 
> changing it to something like  lower(column) like lower('text%'), and
> creating an index on lower(column) will give you much better performance.
> 
> If you have % in the middle of the query, it will still be slow, but I assume that is not
> the general case.
> 
> I am not sure what the effect of it being prepared will be, however I've had much success
> with the method above without the queries being prepared.  Others may be able to offer advice
> about if prepare will effect it.
> 
> Regards
> 
> Russell Smith
>



view thread (8+ messages)  latest in thread

Message-ID: <2989532e05012500235c78fd47@mail.gmail.com>
Permalink:  ../2989532e05012500235c78fd47@mail.gmail.com/
Also on:    postgresql.org/message-id/2989532e05012500235c78fd47@mail.gmail.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: antonypaul24@gmail.com, mr-russ@pws.com.au
  Subject: Re: How to boost performance of ilike queries ?
  In-Reply-To: <2989532e05012500235c78fd47@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox