X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 06E939DCA62 for ; Mon, 13 Feb 2006 22:32:27 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 86923-07 for ; Mon, 13 Feb 2006 22:32:31 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from floppy.pyrenet.fr (news.pyrenet.fr [194.116.145.2]) by postgresql.org (Postfix) with ESMTP id 82C119DC86C for ; Mon, 13 Feb 2006 22:32:24 -0400 (AST) Received: by floppy.pyrenet.fr (Postfix, from userid 106) id 8F30A3093C; Tue, 14 Feb 2006 03:32:27 +0100 (MET) From: Chris X-Newsgroups: pgsql.performance Subject: Re: Optimizing performance of a like '%...%' condition Date: Tue, 14 Feb 2006 13:32:32 +1100 Organization: Hub.Org Networking Services Lines: 19 Message-ID: <43F14140.8050005@gmail.com> References: <1139586432.014319.114830@g47g2000cwa.googlegroups.com> Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Complaints-To: usenet@news.hub.org To: Nico User-Agent: Mozilla Thunderbird 1.0.7 (Windows/20050923) X-Accept-Language: en-us, en In-Reply-To: <1139586432.014319.114830@g47g2000cwa.googlegroups.com> X-Antivirus: avast! (VPS 0607-0, 13/02/2006), Outbound message X-Antivirus-Status: Clean To: pgsql-performance@postgresql.org X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0 required=5 tests=[none] X-Spam-Score: 0 X-Spam-Level: X-Archive-Number: 200602/294 X-Sequence-Number: 17280 > Indexing the t_name.name field, I can increase speed, but only if I > restrict my search to something like : > > select * > from t_name > where t_name.name like 'my_search%' > > (In this case it takes generally less than 1 second) > > > My question : Are there algorithms or tools that can speed up such a > type of queries ("like" condition begining with a "%" symbol) ? Apart from indexing the field you could use full text indexing. See http://techdocs.postgresql.org/techdocs/fulltextindexing.php What other types of queries are you running that you want to speed up ?