Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 05C759FB6B1 for ; Fri, 2 Mar 2007 01:48:15 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-new, port 10024) with ESMTP id 32778-10 for ; Fri, 2 Mar 2007 01:48:11 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id B596F9FB66E for ; Fri, 2 Mar 2007 01:48:11 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.6/8.13.6) with ESMTP id l225m8Fe001764; Fri, 2 Mar 2007 00:48:08 -0500 (EST) To: postgres@leangen.net cc: pgsql-performance@postgresql.org Subject: Re: Improving query performance In-reply-to: <1172808030.27446.12.camel@sonoda.bioscene.co.jp> References: <1172808030.27446.12.camel@sonoda.bioscene.co.jp> Comments: In-reply-to David Leangen message dated "Fri, 02 Mar 2007 13:00:30 +0900" Date: Fri, 02 Mar 2007 00:48:08 -0500 Message-ID: <1763.1172814488@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.06 tagged_above=0 required=5 tests=AWL, BAYES_50, SPF_HELO_PASS, SPF_PASS, UPPERCASE_25_50 X-Spam-Level: X-Archive-Number: 200703/46 X-Sequence-Number: 23451 David Leangen writes: > And this is the actual query: > SELECT COUNT(t0.ID) FROM public.MY_TABLE t0 > WHERE ((POSITION('adenosine cyclic 35-monophosphate' IN LOWER(t0.TITLE)) > - 1) >=0 OR > (POSITION('adenosine cyclic 55-monophosphate' IN LOWER(t0.TEXT)) - 1) >= > 0 OR > (POSITION('cyclic amp, disodium salt' IN LOWER(t0.TITLE)) - 1) >= 0 OR > (POSITION('cyclic amp, disodium salt' IN LOWER(t0.TEXT)) - 1) >= 0 OR > (POSITION('cyclic amp, sodium salt' IN LOWER(t0.TEXT)) - 1) >= 0 OR > ...etc... I think you need to look into full-text indexing (see tsearch2). regards, tom lane