Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 740732E2FB6 for ; Wed, 30 Jan 2008 00:39:29 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 62714-02 for ; Wed, 30 Jan 2008 00:38:17 -0400 (AST) Received: from rv-out-0910.google.com (rv-out-0910.google.com [209.85.198.184]) by postgresql.org (Postfix) with ESMTP id A74292E02E3 for ; Wed, 30 Jan 2008 00:34:04 -0400 (AST) Received: by rv-out-0910.google.com with SMTP id g13so80731rvb.43 for ; Tue, 29 Jan 2008 20:34:02 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=googlemail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; bh=E6u0OnkMewafYO6/3gAEnx06MshE8YEIKKDmGiB+Mho=; b=nnzUCIBpwEcBbJ7FCJdtzB/cm5m0N3dy+xL8j9RlsuXFe/JA5Dl3qimz4Ynby1q0gJR2mex7ozPsm75KABhNgkYWPI91LXtAUXuRVVPtJU9T1NK4mv07nPslbPN3cscoZnW+/CmO+c+qYoQ9z2902LtrGXcLqIbywprDWSx12sc= DomainKey-Signature: a=rsa-sha1; c=nofws; d=googlemail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=PEsfc2xm2OxwRBmCfxiOuJd6ZIYM7B7wDBzTXXQoWP7BJa5h/V7PGMHerUfIbrcS5fDAgs45R+6/PEtdJTJXKldjAiyR5qKoyZH/r2lZLF5QRSb4KnLOUxxgqJZm2ByzyBgLNFewyyDBObK8/Nbg+E0k1wOYkJNOdjTzSaUp+IM= Received: by 10.141.197.8 with SMTP id z8mr203676rvp.157.1201667642509; Tue, 29 Jan 2008 20:34:02 -0800 (PST) Received: by 10.141.128.7 with HTTP; Tue, 29 Jan 2008 20:34:02 -0800 (PST) Message-ID: <684362e10801292034g1a077655i4b67e314eb92f37a@mail.gmail.com> Date: Wed, 30 Jan 2008 10:34:02 +0600 From: "Markus Bertheau" To: A.Burbello Subject: Re: Search string without tsearch Cc: "Postgres Adm List" In-Reply-To: <440310.20885.qm@web36204.mail.mud.yahoo.com> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <440310.20885.qm@web36204.mail.mud.yahoo.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200801/278 X-Sequence-Number: 28052 2008/1/28, A.Burbello : > Hi people, > > I would like to some suggestion to search person name > in a table that has more than 150 milions of rows ... > > e.g ... name like 'JOHN%SMITH'; > > I know there is contrib Tsearch, but I can't for > political reasons. > Is there any way to do with good performance??? For LIKE 'John%Smith' you can create one index on the column and one on reverse(column), and use LIKE 'John%' AND LIKE reverse('%Smith'). The first condition should use the forward index, the second one the reverse index. The reverse function needs to be implemented in C for speed, I'm sure someone else on the list can help out with that. Markus