X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 99BC45292D for ; Mon, 30 May 2005 13:53:00 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 90449-03 for ; Mon, 30 May 2005 16:52:59 +0000 (GMT) Received: from gp.word-to-the-wise.com (gp.word-to-the-wise.com [64.71.176.18]) by svr1.postgresql.org (Postfix) with ESMTP id 562525292B for ; Mon, 30 May 2005 13:52:58 -0300 (ADT) Received: by gp.word-to-the-wise.com (Postfix, from userid 500) id DBACA900010; Mon, 30 May 2005 09:53:40 -0700 (PDT) Date: Mon, 30 May 2005 09:53:40 -0700 From: Steve Atkins To: pgsql-performance@postgresql.org Subject: Re: sequential scan performance Message-ID: <20050530165340.GA21210@gp.word-to-the-wise.com> References: Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: User-Agent: Mutt/1.4.1i X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.781 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_POST X-Spam-Level: X-Archive-Number: 200505/437 X-Sequence-Number: 12601 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