From: Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
To: hubert depesz lubaczewski <depesz@gmail.com>
Cc: Pgsql-Performance <pgsql-performance@postgresql.org>
Subject: Re: How to query and index for customer with lastname
Date: Sat, 04 Mar 2006 15:18:23 +0100
Message-ID: <1141481903.10078.14.camel@localhost> (raw)
In-Reply-To: <9e4684ce0603040549n44e71c29jab72a45f5c440f9@mail.gmail.com>
References: <1141466283.6729.17.camel@localhost>
<9e4684ce0603040549n44e71c29jab72a45f5c440f9@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: J.Kraaijeveld@Askesis.nl, depesz@gmail.com
Subject: Re: How to query and index for customer with lastname
In-Reply-To: <1141481903.10078.14.camel@localhost>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox