Received: from localhost (maia-1.hub.org [200.46.204.191])
	by postgresql.org (Postfix) with ESMTP id BC50C9FA47D
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Thu,  4 Jan 2007 20:31:57 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.191]) (amavisd-new, port 10024)
	with ESMTP id 96088-09 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Thu,  4 Jan 2007 20:31:51 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.4
Received: from globalrelay.com (mail1.globalrelay.com [204.92.49.200])
	by postgresql.org (Postfix) with ESMTP id EFF0D9FA2EC
	for <pgsql-performance@postgresql.org>;
	Thu,  4 Jan 2007 20:31:50 -0400 (AST)
X-Virus-Scanned: Scanned by GRC-AntiVirus Gateway
X-GR-Acctd: YES
Received: from [71.215.208.64] (HELO DaveEMachine)
	by globalrelay.com (CommuniGate Pro SMTP 4.2.3)
	with ESMTP id 121061423 for pgsql-performance@postgresql.org;
	Thu, 04 Jan 2007 15:32:13 -0800
From: "Dave Dutcher" <dave@tridecap.com>
To: <pgsql-performance@postgresql.org>
Subject: Slow Query on Postgres 8.2
Date: Thu, 4 Jan 2007 17:31:43 -0600
Message-ID: <006801c73058$7eb7d2a0$8300a8c0@tridecap.com>
MIME-Version: 1.0
Content-Type: multipart/mixed;
	boundary="----=_NextPart_000_0069_01C73026.341D62A0"
X-Priority: 3 (Normal)
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook, Build 10.0.6626
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3028
Importance: Normal
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.136 tagged_above=0 required=5 tests=BAYES_50,
	HTML_50_60, HTML_MESSAGE
X-Spam-Level: 
X-Archive-Number: 200701/61
X-Sequence-Number: 22662

This is a multi-part message in MIME format.

------=_NextPart_000_0069_01C73026.341D62A0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_001_006A_01C73026.341D62A0"


------=_NextPart_001_006A_01C73026.341D62A0
Content-Type: text/plain;
	charset="US-ASCII"
Content-Transfer-Encoding: quoted-printable

Hello,
=20
I am looking at upgrading from 8.1.2 to 8.2.0, and I've found a query =
which
runs a lot slower.  Here is the query:
=20
select type, currency_id, instrument_id, sum(amount) as total_amount =
from
om_transaction=20
where=20
strategy_id in
('BASKET1','BASKET2','BASKET3','BASKET4','BASKET5','BASKET6','BASKET7','B=
ASK
ET8','BASKET9','BASKET10','BASKET11')
and owner_trader_id in ('dave','sam','bob','tad',
'tim','harry','frank','bart','lisa','homer','marge','maggie','apu','milho=
use
','disco stu')
and cf_account_id in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,29)
and as_of_date > '2006-12-04' and as_of_date <=3D '2006-12-05'=20
group by type, currency_id, instrument_id;

I changed the values in the in statements to fake ones, but it still =
takes
over three seconds on 8.2, where 8.1 only takes 26 milliseconds.  When I
increase the number of valules in the IN clauses, the query rapidly gets
worse.  I tried increasing my stats target to 1000 and analyzing, but =
that
didn't help so I put that back to 10.  While the query is running the =
CPU is
at 100%.  Is there a more efficient way to write a query like this?  =
I've
attached the output from EXPLAIN ANALYZE in a file because it is =
somewhat
large.
=20
Thanks,
=20

Dave Dutcher
Telluride Asset Management
952.653.6411

=20

=20

------=_NextPart_001_006A_01C73026.341D62A0
Content-Type: text/html;
	charset="US-ASCII"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Dus-ascii">
<TITLE>Message</TITLE>

<META content=3D"MSHTML 6.00.2900.3020" name=3DGENERATOR></HEAD>
<BODY>
<DIV><FONT face=3DArial size=3D2>
<DIV><FONT face=3DArial size=3D2><SPAN=20
class=3D041131722-04012007>Hello,</SPAN></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><SPAN=20
class=3D041131722-04012007></SPAN></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2><SPAN class=3D041131722-04012007>I am =
looking at=20
upgrading from 8.1.2 to 8.2.0, and I've found a query which runs a lot=20
slower.&nbsp; Here is the query:</SPAN></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><SPAN=20
class=3D041131722-04012007></SPAN></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2><SPAN class=3D041131722-04012007>select =
type,=20
currency_id, instrument_id, sum(amount) as total_amount from =
om_transaction=20
<BR>where <BR>strategy_id in=20
('BASKET1','BASKET2','BASKET3','BASKET4','BASKET5','BASKET6','BASKET7','B=
ASKET8','BASKET9','BASKET10','BASKET11')<BR>and=20
owner_trader_id in ('dave','sam','bob','tad',=20
'tim','harry','frank','bart','lisa','homer','marge','maggie','apu','milho=
use','disco=20
stu')<BR>and cf_account_id in=20
(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,29)<BR>and as_of_date &gt; =

'2006-12-04' and as_of_date &lt;=3D '2006-12-05' <BR>group by type, =
currency_id,=20
instrument_id;<BR></SPAN></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><SPAN class=3D041131722-04012007>I =
changed the values=20
in the in statements to fake ones, but it still takes over three seconds =
on 8.2,=20
where 8.1 only takes 26 milliseconds.&nbsp; When I increase the number =
of=20
valules in the IN clauses, the query rapidly gets worse.&nbsp; I tried=20
increasing my stats target to 1000 and analyzing, but that didn't help =
so I put=20
that back to 10.&nbsp; While the query is running the CPU is at =
100%.&nbsp; Is=20
there a more efficient way to write a query like this?&nbsp; I've =
attached the=20
output from EXPLAIN ANALYZE in a file because it is somewhat=20
large.</SPAN></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><SPAN=20
class=3D041131722-04012007></SPAN></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2><SPAN=20
class=3D041131722-04012007>Thanks,</SPAN></FONT></DIV></FONT></DIV>
<DIV>&nbsp;</DIV>
<DIV align=3Dleft>
<P class=3DMsoNormal style=3D"MARGIN: 0in 0in 0pt"><SPAN=20
style=3D"FONT-SIZE: 10pt; FONT-FAMILY: Arial">Dave Dutcher<BR>Telluride =
Asset=20
Management<BR>952.653.6411</SPAN><?xml:namespace prefix =3D o ns =3D=20
"urn:schemas-microsoft-com:office:office" /><o:p></o:p></P>
<P class=3DMsoNormal style=3D"MARGIN: 0in 0in 0pt"><o:p><FONT =
face=3DArial=20
size=3D2></FONT></o:p>&nbsp;</P></DIV>
<DIV>&nbsp;</DIV></BODY></HTML>

------=_NextPart_001_006A_01C73026.341D62A0--

------=_NextPart_000_0069_01C73026.341D62A0
Content-Type: text/plain;
	name="plans.txt"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: attachment;
	filename="plans.txt"


explain analyze=20
select type, currency_id, instrument_id, sum(amount) as total_amount =
from om_transaction=20
where=20
strategy_id in =
('BASKET1','BASKET2','BASKET3','BASKET4','BASKET5','BASKET6','BASKET7','B=
ASKET8','BASKET9','BASKET10','BASKET11')
and owner_trader_id in ('dave','sam','bob','tad', =
'tim','harry','frank','bart','lisa','homer','marge','maggie','apu','milho=
use','disco stu')
and cf_account_id in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,29)
and as_of_date > '2006-12-04' and as_of_date <=3D '2006-12-05'=20
group by type, currency_id, instrument_id;


Slow:

-------------------------------------------------------------------------=
-------------------------------------------------------------------------=
-------------------------------------------------------------------------=
-------------------------------------------------------------------------=
-------------------------------------------------------------------------=
-------------------------------------------------------------------------=
-------------------------------------------------------------------------=
-
 HashAggregate  (cost=3D9287.82..9288.44 rows=3D49 width=3D35) (actual =
time=3D3411.238..3411.238 rows=3D0 loops=3D1)
   ->  Bitmap Heap Scan on om_transaction  (cost=3D7421.67..9282.94 =
rows=3D488 width=3D35) (actual time=3D3411.235..3411.235 rows=3D0 =
loops=3D1)
         Recheck Cond: ((as_of_date > '2006-12-04'::date) AND =
(as_of_date <=3D '2006-12-05'::date) AND ((strategy_id)::text =3D ANY =
(('{BASKET1,BASKET2,BASKET3,BASKET4,BASKET5,BASKET6,BASKET7,BASKET8,BASKE=
T9,BASKET10,BASKET11}'::character varying[])::text[])) AND =
((owner_trader_id)::text =3D ANY =
(('{dave,sam,bob,tad,tim,harry,frank,bart,lisa,homer,marge,maggie,apu,mil=
house,"disco stu"}'::character varying[])::text[])) AND (cf_account_id =
=3D ANY =
('{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,29}'::integer[])))
         ->  Bitmap Index Scan on om_transaction_om_transaction_index  =
(cost=3D0.00..7421.67 rows=3D488 width=3D0) (actual =
time=3D3411.227..3411.227 rows=3D0 loops=3D1)
               Index Cond: ((as_of_date > '2006-12-04'::date) AND =
(as_of_date <=3D '2006-12-05'::date) AND ((strategy_id)::text =3D ANY =
(('{BASKET1,BASKET2,BASKET3,BASKET4,BASKET5,BASKET6,BASKET7,BASKET8,BASKE=
T9,BASKET10,BASKET11}'::character varying[])::text[])) AND =
((owner_trader_id)::text =3D ANY =
(('{dave,sam,bob,tad,tim,harry,frank,bart,lisa,homer,marge,maggie,apu,mil=
house,"disco stu"}'::character varying[])::text[])) AND (cf_account_id =
=3D ANY =
('{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,29}'::integer[])))
 Total runtime: 3411.429 ms
(6 rows)

Fast:

-------------------------------------------------------------------------=
-------------------------------------------------------------------------=
-------------------------------------------------------------------------=
-------------------------------------------------------------------------=
-------------------------------------------------------------------------=
-------------------------------------------------------------------------=
-------------------------------------------------------------------------=
-------------------------------------------------------------------------=
-------------------------------------------------------------------------=
-------------------------------------------------------------------------=
-------------------------------------------------------------------------=
-------------------------------------------------------------------------=
-------------------------------------------------------------------------=
-------------------------------------------------------------------------=
-------------------------------------------------------------------------=
-----------------------------------------------------------------------
 HashAggregate  (cost=3D431.45..431.87 rows=3D34 width=3D35) (actual =
time=3D25.442..25.442 rows=3D0 loops=3D1)
   ->  Bitmap Heap Scan on om_transaction  (cost=3D383.09..428.08 =
rows=3D337 width=3D35) (actual time=3D25.429..25.429 rows=3D0 loops=3D1)
         Recheck Cond: ((as_of_date > '2006-12-04'::date) AND =
(as_of_date <=3D '2006-12-05'::date) AND (((as_of_date > =
'2006-12-04'::date) AND (as_of_date <=3D '2006-12-05'::date) AND =
(cf_account_id =3D 1)) OR ((as_of_date > '2006-12-04'::date) AND =
(as_of_date <=3D '2006-12-05'::date) AND (cf_account_id =3D 2)) OR =
(cf_account_id =3D 3) OR (cf_account_id =3D 4) OR (cf_account_id =3D 5) =
OR (cf_account_id =3D 6) OR (cf_account_id =3D 7) OR (cf_account_id =3D =
8) OR (cf_account_id =3D 9) OR (cf_account_id =3D 10) OR (cf_account_id =
=3D 11) OR (cf_account_id =3D 12) OR (cf_account_id =3D 13) OR =
(cf_account_id =3D 14) OR (cf_account_id =3D 15) OR (cf_account_id =3D =
16) OR (cf_account_id =3D 17) OR (cf_account_id =3D 18) OR =
(cf_account_id =3D 29)))
         Filter: ((((strategy_id)::text =3D 'BASKET1'::text) OR =
((strategy_id)::text =3D 'BASKET2'::text) OR ((strategy_id)::text =3D =
'BASKET3'::text) OR ((strategy_id)::text =3D 'BASKET4'::text) OR =
((strategy_id)::text =3D 'BASKET5'::text) OR ((strategy_id)::text =3D =
'BASKET6'::text) OR ((strategy_id)::text =3D 'BASKET7'::text) OR =
((strategy_id)::text =3D 'BASKET8'::text) OR ((strategy_id)::text =3D =
'BASKET9'::text) OR ((strategy_id)::text =3D 'BASKET10'::text) OR =
((strategy_id)::text =3D 'BASKET11'::text)) AND =
(((owner_trader_id)::text =3D 'dave'::text) OR ((owner_trader_id)::text =
=3D 'sam'::text) OR ((owner_trader_id)::text =3D 'bob'::text) OR =
((owner_trader_id)::text =3D 'tad'::text) OR ((owner_trader_id)::text =
=3D 'tim'::text) OR ((owner_trader_id)::text =3D 'harry'::text) OR =
((owner_trader_id)::text =3D 'frank'::text) OR ((owner_trader_id)::text =
=3D 'bart'::text) OR ((owner_trader_id)::text =3D 'lisa'::text) OR =
((owner_trader_id)::text =3D 'homer'::text) OR ((owner_trader_id)::text =
=3D 'marge'::text) OR ((owner_trader_id)::text =3D 'maggie'::text) OR =
((owner_trader_id)::text =3D 'apu'::text) OR ((owner_trader_id)::text =
=3D 'milhouse'::text) OR ((owner_trader_id)::text =3D 'disco =
stu'::text)))
         ->  BitmapAnd  (cost=3D383.09..383.09 rows=3D11 width=3D0) =
(actual time=3D13.497..13.497 rows=3D0 loops=3D1)
               ->  Bitmap Index Scan on =
om_transaction_om_transaction_index  (cost=3D0.00..101.69 rows=3D5949 =
width=3D0) (actual time=3D3.419..3.419 rows=3D7967 loops=3D1)
                     Index Cond: ((as_of_date > '2006-12-04'::date) AND =
(as_of_date <=3D '2006-12-05'::date))
               ->  BitmapOr  (cost=3D281.14..281.14 rows=3D7297 =
width=3D0) (actual time=3D9.968..9.968 rows=3D0 loops=3D1)
                     ->  Bitmap Index Scan on =
om_transaction_om_transaction_index  (cost=3D0.00..116.57 rows=3D2699 =
width=3D0) (actual time=3D3.627..3.627 rows=3D2747 loops=3D1)
                           Index Cond: ((as_of_date > =
'2006-12-04'::date) AND (as_of_date <=3D '2006-12-05'::date) AND =
(cf_account_id =3D 1))
                     ->  Bitmap Index Scan on =
om_transaction_om_transaction_index  (cost=3D0.00..116.57 rows=3D2039 =
width=3D0) (actual time=3D3.501..3.501 rows=3D2152 loops=3D1)
                           Index Cond: ((as_of_date > =
'2006-12-04'::date) AND (as_of_date <=3D '2006-12-05'::date) AND =
(cf_account_id =3D 2))
                     ->  Bitmap Index Scan on =
cf_account_id_om_transaction_index  (cost=3D0.00..2.00 rows=3D1 =
width=3D0) (actual time=3D0.053..0.053 rows=3D0 loops=3D1)
                           Index Cond: (cf_account_id =3D 3)
                     ->  Bitmap Index Scan on =
cf_account_id_om_transaction_index  (cost=3D0.00..2.00 rows=3D1 =
width=3D0) (actual time=3D0.011..0.011 rows=3D0 loops=3D1)
                           Index Cond: (cf_account_id =3D 4)
                     ->  Bitmap Index Scan on =
cf_account_id_om_transaction_index  (cost=3D0.00..2.00 rows=3D1 =
width=3D0) (actual time=3D0.011..0.011 rows=3D1 loops=3D1)
                           Index Cond: (cf_account_id =3D 5)
                     ->  Bitmap Index Scan on =
cf_account_id_om_transaction_index  (cost=3D0.00..2.00 rows=3D1 =
width=3D0) (actual time=3D0.078..0.078 rows=3D194 loops=3D1)
                           Index Cond: (cf_account_id =3D 6)
                     ->  Bitmap Index Scan on =
cf_account_id_om_transaction_index  (cost=3D0.00..2.00 rows=3D1 =
width=3D0) (actual time=3D0.016..0.016 rows=3D0 loops=3D1)
                           Index Cond: (cf_account_id =3D 7)
                     ->  Bitmap Index Scan on =
cf_account_id_om_transaction_index  (cost=3D0.00..2.00 rows=3D1 =
width=3D0) (actual time=3D0.010..0.010 rows=3D0 loops=3D1)
                           Index Cond: (cf_account_id =3D 8)
                     ->  Bitmap Index Scan on =
cf_account_id_om_transaction_index  (cost=3D0.00..2.00 rows=3D1 =
width=3D0) (actual time=3D0.023..0.023 rows=3D6 loops=3D1)
                           Index Cond: (cf_account_id =3D 9)
                     ->  Bitmap Index Scan on =
cf_account_id_om_transaction_index  (cost=3D0.00..2.00 rows=3D1 =
width=3D0) (actual time=3D0.010..0.010 rows=3D0 loops=3D1)
                           Index Cond: (cf_account_id =3D 10)
                     ->  Bitmap Index Scan on =
cf_account_id_om_transaction_index  (cost=3D0.00..2.00 rows=3D1 =
width=3D0) (actual time=3D0.010..0.010 rows=3D0 loops=3D1)
                           Index Cond: (cf_account_id =3D 11)
                     ->  Bitmap Index Scan on =
cf_account_id_om_transaction_index  (cost=3D0.00..2.00 rows=3D1 =
width=3D0) (actual time=3D0.009..0.009 rows=3D0 loops=3D1)
                           Index Cond: (cf_account_id =3D 12)
                     ->  Bitmap Index Scan on =
cf_account_id_om_transaction_index  (cost=3D0.00..2.00 rows=3D1 =
width=3D0) (actual time=3D0.013..0.013 rows=3D12 loops=3D1)
                           Index Cond: (cf_account_id =3D 13)
                     ->  Bitmap Index Scan on =
cf_account_id_om_transaction_index  (cost=3D0.00..2.00 rows=3D1 =
width=3D0) (actual time=3D0.010..0.010 rows=3D0 loops=3D1)
                           Index Cond: (cf_account_id =3D 14)
                     ->  Bitmap Index Scan on =
cf_account_id_om_transaction_index  (cost=3D0.00..2.00 rows=3D1 =
width=3D0) (actual time=3D0.010..0.010 rows=3D1 loops=3D1)
                           Index Cond: (cf_account_id =3D 15)
                     ->  Bitmap Index Scan on =
cf_account_id_om_transaction_index  (cost=3D0.00..2.00 rows=3D1 =
width=3D0) (actual time=3D0.011..0.011 rows=3D0 loops=3D1)
                           Index Cond: (cf_account_id =3D 16)
                     ->  Bitmap Index Scan on =
cf_account_id_om_transaction_index  (cost=3D0.00..2.00 rows=3D1 =
width=3D0) (actual time=3D0.036..0.036 rows=3D66 loops=3D1)
                           Index Cond: (cf_account_id =3D 17)
                     ->  Bitmap Index Scan on =
cf_account_id_om_transaction_index  (cost=3D0.00..15.96 rows=3D2559 =
width=3D0) (actual time=3D2.403..2.403 rows=3D8201 loops=3D1)
                           Index Cond: (cf_account_id =3D 18)
                     ->  Bitmap Index Scan on =
cf_account_id_om_transaction_index  (cost=3D0.00..2.00 rows=3D1 =
width=3D0) (actual time=3D0.032..0.032 rows=3D0 loops=3D1)
                           Index Cond: (cf_account_id =3D 29)
 Total runtime: 26.109 ms
(47 rows)


------=_NextPart_000_0069_01C73026.341D62A0--