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 ED10E9DC9B9 for ; Sat, 4 Mar 2006 09:49:41 -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 50167-08 for ; Sat, 4 Mar 2006 09:49:45 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from pproxy.gmail.com (pproxy.gmail.com [64.233.166.179]) by postgresql.org (Postfix) with ESMTP id EA0FD9DC9F8 for ; Sat, 4 Mar 2006 09:49:39 -0400 (AST) Received: by pproxy.gmail.com with SMTP id x31so469274pye for ; Sat, 04 Mar 2006 05:49:44 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=lwxWf+yoghhiuOvC+QJx+JLtGRLQCYZ1htVpHsuDI+YD05+o+CMNajTg5vgdDZtTZR9ILUc1Khtb5Dp8vHdMDd6Dv0ZvyoRnMxik/5z9yM3B1Y5sDBQx02+QjXVy4z8vP84gJTD6W7hFzS7WFLHagjh72bI/diyQmczFNumzroE= Received: by 10.65.35.6 with SMTP id n6mr1925728qbj; Sat, 04 Mar 2006 05:49:44 -0800 (PST) Received: by 10.65.61.17 with HTTP; Sat, 4 Mar 2006 05:49:44 -0800 (PST) Message-ID: <9e4684ce0603040549n44e71c29jab72a45f5c440f9@mail.gmail.com> Date: Sat, 4 Mar 2006 14:49:44 +0100 From: "hubert depesz lubaczewski" To: "Joost Kraaijeveld" Subject: Re: How to query and index for customer with lastname and city Cc: Pgsql-Performance In-Reply-To: <1141466283.6729.17.camel@localhost> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline References: <1141466283.6729.17.camel@localhost> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.883 required=5 tests=[AWL=-0.449, RCVD_IN_BL_SPAMCOP_NET=1.332] X-Spam-Score: 0.883 X-Spam-Level: X-Archive-Number: 200603/22 X-Sequence-Number: 17459 On 3/4/06, Joost Kraaijeveld wrote: > Below is what I actually have. Given the fact that it takes forever to g= et > a result (> 6 seconds) , there must be something wrong with my solution o= r > my expectation. Can anyone tell what I should do to make this query go > faster ( or convince me to wait for the result ;-()? > Explain analyze after a full alayse vacuum: > Sort (cost=3D54710.68..54954.39 rows=3D97484 width=3D111) (actual > time=3D7398.971..7680.405 rows=3D96041 loops=3D1) > Sort Key: btrim(upper(customers.lastname)), btrim(upper(addresses.city= )) > -> Hash Join (cost=3D14341.12..46632.73 rows=3D97484 width=3D111) (a= ctual > time=3D1068.862..5472.788 rows=3D96041 loops=3D1) > Hash Cond: ("outer".contactaddress =3D "inner".objectid) > -> Seq Scan on customers (cost=3D0.00..24094.01 rows=3D227197 > width=3D116) (actual time=3D0.018..1902.646 rows=3D223990 loops=3D1) > Filter: (btrim(upper(lastname)) >=3D 'JANSEN'::text) > -> Hash (cost=3D13944.94..13944.94 rows=3D158473 width=3D75) (= actual > time=3D1068.467..1068.467 rows=3D158003 loops=3D1) > -> Bitmap Heap Scan on addresses (cost=3D1189.66..13944.= 94 > rows=3D158473 width=3D75) (actual time=3D71.259..530.986 rows=3D158003 lo= ops=3D1) > Recheck Cond: (btrim(upper(city)) >=3D 'NIJMEGEN'::t= ext) > -> Bitmap Index Scan on > prototype_addresses_trim_upper_city (cost=3D0.00..1189.66 > rows=3D158473 width=3D0) (actual time=3D68.290..68.290 rows=3D158003 loop= s=3D1) > Index Cond: (btrim(upper(city)) >=3D > 'NIJMEGEN'::text) > Total runtime: 7941.095 ms explain clearly shows, that index is used for addresses scan, but it is not so for users. explain estimates that 227197 customers match the lastname criteria - which looks awfuly high. how many record do you have in the customers table? i would try to create index test on customers(contactAddress, trim(uppercase(lastname))); or with other ordring of fields. try this - create the index, make analyze of customers table, and recheck explain. then try the second index in the same manner. maybe this could of some help... depesz