pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Steve Atkins <steve@blighty.com>
To: pgsql-performance@postgresql.org
Subject: Re: sequential scan performance
Date: Mon, 30 May 2005 09:53:40 -0700
Message-ID: <20050530165340.GA21210@gp.word-to-the-wise.com> (raw)
In-Reply-To: <D520F8B3-20D6-4272-A6D6-8B690871DE73@mac.com>
References: <D520F8B3-20D6-4272-A6D6-8B690871DE73@mac.com>

On Sun, May 29, 2005 at 08:27:26AM -0500, Michael Engelhart wrote:
> Hi -
> 
> I have a table of about 3 million rows of city "aliases" that I need  
> to query using LIKE - for example:
> 
> select * from city_alias where city_name like '%FRANCISCO'
> 
> 
> When I do an EXPLAIN ANALYZE on the above query, the result is:
> 
>  Seq Scan on city_alias  (cost=0.00..59282.31 rows=2 width=42)  
> (actual time=73.369..3330.281 rows=407 loops=1)
>    Filter: ((name)::text ~~ '%FRANCISCO'::text)
> Total runtime: 3330.524 ms
> (3 rows)
> 
> 
> this is a query that our system needs to do a LOT.   Is there any way  
> to improve the performance on this either with changes to our query  
> or by configuring the database deployment?   We have an index on  
> city_name but when using the % operator on the front of the query  
> string postgresql can't use the index .

If that's really what you're doing (the wildcard is always at the beginning)
then something like this

  create index city_name_idx on foo (reverse(city_name));

  select * from city_alias where reverse(city_name) like reverse('%FRANCISCO');

should do just what you need.

I use this, with a plpgsql implementation of reverse, and it works nicely.

CREATE OR REPLACE FUNCTION reverse(text) RETURNS text AS '
DECLARE
       original alias for $1;
       reverse_str text;
       i int4;
BEGIN
 reverse_str = '''';
 FOR i IN REVERSE LENGTH(original)..1 LOOP
  reverse_str = reverse_str || substr(original,i,1);
 END LOOP;
 return reverse_str;
END;'
LANGUAGE 'plpgsql' IMMUTABLE;


Someone will no doubt suggest using tsearch2, and you might want to
take a look at it if you actually need full-text search, but my
experience has been that it's too slow to be useful in production, and
it's not needed for the simple "leading wildcard" case.

Cheers,
  Steve



view thread (6+ messages)

Message-ID: <20050530165340.GA21210@gp.word-to-the-wise.com>
Permalink:  ../20050530165340.GA21210@gp.word-to-the-wise.com/
Also on:    postgresql.org/message-id/20050530165340.GA21210@gp.word-to-the-wise.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: steve@blighty.com
  Subject: Re: sequential scan performance
  In-Reply-To: <20050530165340.GA21210@gp.word-to-the-wise.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