From: Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
To: Pgsql-Performance <pgsql-performance@postgresql.org>
Subject: How to query and index for customer with lastname and city
Date: Sat, 04 Mar 2006 10:58:03 +0100
Message-ID: <1141466283.6729.17.camel@localhost> (raw)
Hi,
I have two tables:
Customer: objectid, lastname, fk_address
Address: objectid, city
I want to select all customers with a name >= some_name and living in a
city >= some_city, all comparisons case insensitive
Below is what I actually have. Given the fact that it takes forever to
get a result (> 6 seconds) , there must be something wrong with my
solution or my expectation. Can anyone tell what I should do to make
this query go faster ( or convince me to wait for the result ;-()?
SELECT customers.objectid FROM prototype.customers,prototype.addresses
WHERE
customers.contactAddress = addresses.objectId
AND
(
TRIM(UPPER(lastName)) >= TRIM(UPPER('some_name'))
AND
TRIM(UPPER(city)) >= TRIM(UPPER('some_city'))
)
order by TRIM(UPPER(lastname)), TRIM(UPPER(city))
Explain analyze after a full alayse vacuum:
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
I have indices on :
fki_customers_addresses
customer.lastname (both lastname and trim(uppercase(lastname))
addresses.city (both city and trim(uppercase(city))
I
--
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
Subject: Re: How to query and index for customer with lastname and city
In-Reply-To: <1141466283.6729.17.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