Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 1AF359FB9B6 for ; Wed, 13 Jun 2007 08:50:22 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 27535-05 for ; Wed, 13 Jun 2007 08:50:15 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by postgresql.org (Postfix) with ESMTP id 183559FB92B for ; Wed, 13 Jun 2007 08:50:16 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.3 Received: from achille.dalibo.net (achille.dalibo.net [212.85.153.230]) by svr4.postgresql.org (Postfix) with ESMTP id 254095AF895 for ; Wed, 13 Jun 2007 11:50:15 +0000 (GMT) Received: from localhost (localhost.localdomain [127.0.0.1]) by achille.dalibo.net (Postfix) with ESMTP id 8D02415F77; Wed, 13 Jun 2007 13:50:02 +0200 (CEST) Received: from achille.dalibo.net ([127.0.0.1]) by localhost (achille [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 11499-02; Wed, 13 Jun 2007 13:50:01 +0200 (CEST) Received: from asterix.local (AToulouse-152-1-5-106.w82-125.abo.wanadoo.fr [82.125.3.106]) by achille.dalibo.net (Postfix) with ESMTP id 8B3D615F75; Wed, 13 Jun 2007 13:50:01 +0200 (CEST) From: =?iso-8859-1?q?C=E9dric_Villemain?= Organization: dalibo.com To: pgsql-performance@postgresql.org Subject: Re: Optimize slow query Date: Wed, 13 Jun 2007 13:47:23 +0200 User-Agent: KMail/1.9.7 Cc: "Tyler Durden" References: In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/signed; boundary="nextPart2109308.6SVtqdhlj1"; protocol="application/pgp-signature"; micalg=pgp-sha1 Content-Transfer-Encoding: 7bit Message-Id: <200706131350.01695.cedric.villemain@dalibo.com> X-Virus-Scanned: by amavisd-new-20030616-p10 (Debian) at dalibo.com X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200706/232 X-Sequence-Number: 25265 --nextPart2109308.6SVtqdhlj1 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Le mercredi 13 juin 2007, Tyler Durden a =E9crit=A0: > 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=3D'4250' or r_pc=3D'4250') AND (l_struc=3D'O' or r_struc=3D'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? Could you add 'explain analyze' to your post ?=20 And how much content have the text fields ? (isn't tsearch2 an option for=20 you ?) > > Thanks, > Nuno Mariz > > ---------------------------(end of broadcast)--------------------------- > TIP 7: You can help support the PostgreSQL project by donating at > > http://www.postgresql.org/about/donate =2D-=20 C=E9dric Villemain Administrateur de Base de Donn=E9es Cel: +33 (0)6 74 15 56 53 http://dalibo.com - http://dalibo.org --nextPart2109308.6SVtqdhlj1 Content-Type: application/pgp-signature; name=signature.asc Content-Description: This is a digitally signed message part. -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQBGb9npo/dppWjpEvwRAuHlAJkB41k2fOQVdVQHdUDmft/BQCOFLgCcDWyL ajCR9IR3foyhHfo59I2WH7o= =s9V2 -----END PGP SIGNATURE----- --nextPart2109308.6SVtqdhlj1--