Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 55FDF9FB6FC for ; Wed, 13 Jun 2007 08:49:13 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 31094-05 for ; Wed, 13 Jun 2007 08:49:09 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.168]) by postgresql.org (Postfix) with ESMTP id 83A389FB698 for ; Wed, 13 Jun 2007 08:49:09 -0300 (ADT) Received: by ug-out-1314.google.com with SMTP id u2so420868uge for ; Wed, 13 Jun 2007 04:49:08 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=nWh0kSsSZpz6BFoqrb8pyVY2aF54wUxtLXpIvsiwt/l+oXUn8oluH3DnvyOm2Kv9fVP77KYWkRT8qXoX5qwjlgHXLOe6qmzbeU4I/DyvmTq+aac9uGTmzY3SFx7InCwHk1LHqKWBepXk5N2daD/5z27MfFVoB8Pt520udprE8CU= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=egOqbk3+fYh9Rk5ukDp0bgu6m8l2UsT2SRIIiwkq7uTO+3odfqhI8YmJMZaWIUXkz2t/x9/Wz71GaAsScHSyXnnV3G4NHMJJV92BVXg61RUcP87EqmRVM0nlYCzSYZUWvj29uvpyKjjvsD1M666d25poKqv9wTf2Adn9EQtzP24= Received: by 10.78.183.8 with SMTP id g8mr147320huf.1181735347851; Wed, 13 Jun 2007 04:49:07 -0700 (PDT) Received: by 10.78.172.7 with HTTP; Wed, 13 Jun 2007 04:49:07 -0700 (PDT) Message-ID: Date: Wed, 13 Jun 2007 12:49:07 +0100 From: "Tyler Durden" To: pgsql-performance@postgresql.org Subject: Re: Optimize slow query In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline References: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.118 tagged_above=0 required=5 tests=AWL=0.303, BAYES_40=-0.185 X-Spam-Level: X-Archive-Number: 200706/231 X-Sequence-Number: 25264 On 6/13/07, Tyler Durden wrote: > Hi, > I've a table with 300 000 records and I'm trying to do a search: > > SELECT * FROM addresses WHERE address ILIKE '%Jean Paul%' AND > (l_pc='4250' or r_pc='4250') AND (l_struc='O' or r_struc='O') AND > (prenm ILIKE 'Street') > > It performs in 2 seconds in a dual Xeon 2.4mhz with 2Gb of RAM. > I'm using Postgresql 8.1 on ubuntu. > I've indexes on l_pc, r_pc, l_struc,r_struc and prenm (all btrees) > What I'm doing wrong to have such a slow query? > > Thanks, > Nuno Mariz > My bad! Sorry, I've missed an index on l_struc and r_struc. Thanks, anyway.