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 6CE139DC9F8 for ; Sat, 4 Mar 2006 10:18:23 -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 53383-09 for ; Sat, 4 Mar 2006 10:18:27 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from obelix.askesis.nl (laudanum.demon.nl [82.161.125.16]) by postgresql.org (Postfix) with ESMTP id C81969DC9E7 for ; Sat, 4 Mar 2006 10:18:20 -0400 (AST) Received: obelix 172.31.0.1 from 172.31.1.4 172.31.1.4 via HTTP with MS-WebStorage 6.0.6249 Received: from panoramix by obelix; 04 Mar 2006 15:18:23 +0100 Subject: Re: How to query and index for customer with lastname From: Joost Kraaijeveld To: hubert depesz lubaczewski Cc: Pgsql-Performance In-Reply-To: <9e4684ce0603040549n44e71c29jab72a45f5c440f9@mail.gmail.com> References: <1141466283.6729.17.camel@localhost> <9e4684ce0603040549n44e71c29jab72a45f5c440f9@mail.gmail.com> Content-Type: text/plain Content-Transfer-Encoding: 7bit Date: Sat, 04 Mar 2006 15:18:23 +0100 Message-Id: <1141481903.10078.14.camel@localhost> Mime-Version: 1.0 X-Mailer: Evolution 2.4.2.1 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.086 required=5 tests=[AWL=0.085, UNPARSEABLE_RELAY=0.001] X-Spam-Score: 0.086 X-Spam-Level: X-Archive-Number: 200603/23 X-Sequence-Number: 17460 Hi Hubert, On Sat, 2006-03-04 at 14:49 +0100, hubert depesz lubaczewski wrote: > > Sort (cost=54710.68..54954.39 rows=97484 width=111) (actual > > time=7398.971..7680.405 rows=96041 loops=1) > > Sort Key: btrim(upper(customers.lastname)), btrim(upper(addresses.city)) > > -> Hash Join (cost=14341.12..46632.73 rows=97484 width=111) (actual time=1068.862..5472.788 rows=96041 loops=1) > > Hash Cond: ("outer".contactaddress = "inner".objectid) > > -> Seq Scan on customers (cost=0.00..24094.01 rows=227197 width=116) (actual time=0.018..1902.646 rows=223990 loops=1) > > Filter: (btrim(upper(lastname)) >= 'JANSEN'::text) > > -> Hash (cost=13944.94..13944.94 rows=158473 width=75) (actual time=1068.467..1068.467 rows=158003 loops=1) > > -> Bitmap Heap Scan on addresses (cost=1189.66..13944.94 rows=158473 width=75) (actual time=71.259..530.986 rows=158003 loops=1) > > Recheck Cond: (btrim(upper(city)) >= 'NIJMEGEN'::text) > > -> Bitmap Index Scan on prototype_addresses_trim_upper_city (cost=0.00..1189.66 rows=158473 width=0) (actual time=68.290..68.290 rows=158003 loops=1) > > Index Cond: (btrim(upper(city)) >=> 'NIJMEGEN'::text) > > Total runtime: 7941.095 ms > > explain clearly shows, that index is used for addresses scan, but it Yes, but I do not understand why I have both a "Bitmap Index Scan" and a "Bitmap Heap Scan" on (btrim(upper(city)) >=> 'NIJMEGEN'::text)? > 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? 368915 of which 222465 actually meet the condition. From what I understand from the mailing list, PostgreSQL prefers a table scan whenever it expects that the number of records in the resultset will be ~ > 10 % of the total number of records in the table. Which explains the table scan for customers, but than again, it does not explain why it uses the index on addresses: it has 369337 addresses of which 158003 meet the condition > 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. Makes no difference. -- Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 e-mail: J.Kraaijeveld@Askesis.nl web: www.askesis.nl