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 3D5A59DCC70 for ; Sat, 4 Mar 2006 10:23:06 -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 56167-03 for ; Sat, 4 Mar 2006 10:23:10 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.197]) by postgresql.org (Postfix) with ESMTP id 22D359DCC07 for ; Sat, 4 Mar 2006 10:23:03 -0400 (AST) Received: by wproxy.gmail.com with SMTP id i11so1096947wra for ; Sat, 04 Mar 2006 06:23:08 -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=qFsAtkVwrX+3jwNqkPsI1kWxFoAJghZK8Vxo4FXzLK7bS5MvahCsaDZO5FsPOSG8nAlQ2a6G2QpHcTpAxjmDgHtywT8d1mXA6TkNV2MHb6cKrxR7LRoGOIZZZFy7Y/8qV4C/cpZS45lidxUH1/OnwqS1wZey8aNUD28rB0CR1uI= Received: by 10.65.212.3 with SMTP id o3mr1946493qbq; Sat, 04 Mar 2006 06:23:08 -0800 (PST) Received: by 10.65.61.17 with HTTP; Sat, 4 Mar 2006 06:23:08 -0800 (PST) Message-ID: <9e4684ce0603040623p24f4672bk55a0a303152138aa@mail.gmail.com> Date: Sat, 4 Mar 2006 15:23:08 +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: <1141481903.10078.14.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> <9e4684ce0603040549n44e71c29jab72a45f5c440f9@mail.gmail.com> <1141481903.10078.14.camel@localhost> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.241 required=5 tests=[AWL=0.241] X-Spam-Score: 0.241 X-Spam-Level: X-Archive-Number: 200603/24 X-Sequence-Number: 17461 On 3/4/06, Joost Kraaijeveld wrote: > > 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 bitmap index scan is faster than sequential table scan. that's all. it was introduced in 8.1 as far as i remember. basically - i doubt if you can get better performace from query when the result row-count is that high. out of curiosity though - why do you need so many rows? it's not possible to view them, nor do anything meaningful with 200 thousand rows! depesz