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 BA71B9DC99E
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Sat,  4 Mar 2006 05:58:08 -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 01239-02
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Sat,  4 Mar 2006 05:58:09 -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 BDC629DC942
	for <pgsql-performance@postgresql.org>;
	Sat,  4 Mar 2006 05:58:04 -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 10:58:03 +0100
Subject: How to query and index for customer with lastname and city
From: Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
To: Pgsql-Performance <pgsql-performance@postgresql.org>
Content-Type: multipart/alternative; boundary="=-tJT4+j40YrxwA9R+Imeb"
Date: Sat, 04 Mar 2006 10:58:03 +0100
Message-Id: <1141466283.6729.17.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.084,
	HTML_MESSAGE=0.001, UNPARSEABLE_RELAY=0.001]
X-Spam-Score: 0.086
X-Spam-Level: 
X-Archive-Number: 200603/21
X-Sequence-Number: 17458


--=-tJT4+j40YrxwA9R+Imeb
Content-Type: text/plain
Content-Transfer-Encoding: 7bit

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


--=-tJT4+j40YrxwA9R+Imeb
Content-Type: text/html; charset=utf-8

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 TRANSITIONAL//EN">
<HTML>
<HEAD>
  <META HTTP-EQUIV="Content-Type" CONTENT="text/html; CHARSET=UTF-8">
  <META NAME="GENERATOR" CONTENT="GtkHTML/3.8.1">
</HEAD>
<BODY>
Hi,<BR>
<BR>
I have two tables:<BR>
<BR>
Customer: objectid, lastname, fk_address<BR>
Address: objectid, city<BR>
<BR>
I want to select all customers with a name &gt;= some_name and living in a city &gt;= some_city, all comparisons case insensitive<BR>
<BR>
Below is what I actually have. Given the fact that it takes forever to get a result (&gt; 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 ;-()?<BR>
<BR>
<BR>
SELECT customers.objectid FROM prototype.customers,prototype.addresses <BR>
WHERE<BR>
customers.contactAddress = addresses.objectId <BR>
AND <BR>
( <BR>
 TRIM(UPPER(lastName)) &gt;= TRIM(UPPER('some_name'))<BR>
 AND <BR>
 TRIM(UPPER(city)) &gt;= TRIM(UPPER('some_city'))<BR>
)&nbsp; <BR>
order by TRIM(UPPER(lastname)), TRIM(UPPER(city)) <BR>
<BR>
Explain analyze after a full alayse vacuum:<BR>
<BR>
Sort&nbsp; (cost=54710.68..54954.39 rows=97484 width=111) (actual time=7398.971..7680.405 rows=96041 loops=1)<BR>
&nbsp; Sort Key: btrim(upper(customers.lastname)), btrim(upper(addresses.city))<BR>
&nbsp; -&gt;&nbsp; Hash Join&nbsp; (cost=14341.12..46632.73 rows=97484 width=111) (actual time=1068.862..5472.788 rows=96041 loops=1)<BR>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Hash Cond: (&quot;outer&quot;.contactaddress = &quot;inner&quot;.objectid)<BR>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&gt;&nbsp; Seq Scan on customers&nbsp; (cost=0.00..24094.01 rows=227197 width=116) (actual time=0.018..1902.646 rows=223990 loops=1)<BR>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Filter: (btrim(upper(lastname)) &gt;= 'JANSEN'::text)<BR>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&gt;&nbsp; Hash&nbsp; (cost=13944.94..13944.94 rows=158473 width=75) (actual time=1068.467..1068.467 rows=158003 loops=1)<BR>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&gt;&nbsp; Bitmap Heap Scan on addresses&nbsp; (cost=1189.66..13944.94 rows=158473 width=75) (actual time=71.259..530.986 rows=158003 loops=1)<BR>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Recheck Cond: (btrim(upper(city)) &gt;= 'NIJMEGEN'::text)<BR>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&gt;&nbsp; Bitmap Index Scan on prototype_addresses_trim_upper_city&nbsp; (cost=0.00..1189.66 rows=158473 width=0) (actual time=68.290..68.290 rows=158003 loops=1)<BR>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Index Cond: (btrim(upper(city)) &gt;= 'NIJMEGEN'::text)<BR>
Total runtime: 7941.095 ms<BR>
<BR>
<BR>
I have indices on :<BR>
fki_customers_addresses<BR>
customer.lastname (both lastname and trim(uppercase(lastname)) <BR>
addresses.city (both city and trim(uppercase(city)) <BR>
<BR>
I <BR>
<BR>
<BR>
<TABLE CELLSPACING="0" CELLPADDING="0" WIDTH="100%">
<TR>
<TD>
<PRE>
-- 
Groeten,

Joost Kraaijeveld
Askesis B.V.
Molukkenstraat 14
6524NB Nijmegen
tel: 024-3888063 / 06-51855277
fax: 024-3608416
e-mail: <A HREF="mailto:J.Kraaijeveld@Askesis.nl">J.Kraaijeveld@Askesis.nl</A>
web: <A HREF="http://www.askesis.nl">www.askesis.nl</A>

</PRE>
</TD>
</TR>
</TABLE>
</BODY>
</HTML>

--=-tJT4+j40YrxwA9R+Imeb--