From dave@tridecap.com Fri Jan  5 00:31:57 2007
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--




From tgl@sss.pgh.pa.us Fri Jan  5 01:12:56 2007
Received: from localhost (maia-3.hub.org [200.46.204.184])
	by postgresql.org (Postfix) with ESMTP id 038D49FA31E
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Thu,  4 Jan 2007 21:12:56 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.184]) (amavisd-new, port 10024)
	with ESMTP id 58461-06 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Thu,  4 Jan 2007 21:12:46 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.4
Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130])
	by postgresql.org (Postfix) with ESMTP id 4FF8F9FA0B7
	for <pgsql-performance@postgresql.org>;
	Thu,  4 Jan 2007 21:12:51 -0400 (AST)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.13.6/8.13.6) with ESMTP id l051CoJT007599;
	Thu, 4 Jan 2007 20:12:50 -0500 (EST)
To: "Dave Dutcher" <dave@tridecap.com>
cc: pgsql-performance@postgresql.org
Subject: Re: Slow Query on Postgres 8.2 
In-reply-to: <006801c73058$7eb7d2a0$8300a8c0@tridecap.com> 
References: <006801c73058$7eb7d2a0$8300a8c0@tridecap.com>
Comments: In-reply-to "Dave Dutcher" <dave@tridecap.com>
	message dated "Thu, 04 Jan 2007 17:31:43 -0600"
Date: Thu, 04 Jan 2007 20:12:49 -0500
Message-ID: <7598.1167959569@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200701/62
X-Sequence-Number: 22663

"Dave Dutcher" <dave@tridecap.com> writes:
> I am looking at upgrading from 8.1.2 to 8.2.0, and I've found a query which
> runs a lot slower.

Um ... what indexes has this table got exactly?  It's very unclear what
alternatives the planner is being faced with.

			regards, tom lane



From adam.r@sbcglobal.net Fri Jan  5 01:18:34 2007
Received: from localhost (maia-3.hub.org [200.46.204.184])
	by postgresql.org (Postfix) with ESMTP id 9BDE09FA48A
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Thu,  4 Jan 2007 21:18:34 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.184]) (amavisd-new, port 10024)
	with ESMTP id 59517-04 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Thu,  4 Jan 2007 21:18:24 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.4
Received: from smtp114.sbc.mail.re2.yahoo.com (smtp114.sbc.mail.re2.yahoo.com
	[68.142.229.91]) by postgresql.org (Postfix) with SMTP id C51349FA31E
	for <pgsql-performance@postgresql.org>;
	Thu,  4 Jan 2007 21:18:28 -0400 (AST)
Received: (qmail 29634 invoked from network); 5 Jan 2007 01:18:27 -0000
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=sbcglobal.net;
	h=Received:X-YMail-OSG:From:To:Subject:Date:Message-ID:MIME-Version:Content-Type:X-Priority:X-MSMail-Priority:X-Mailer:X-MimeOLE:In-Reply-To:Importance;
	b=OLCAM9yKf0Hk84CjDobCbXNuoSrwDhwd+8AqcqzeFeWcfWjcKMl4a+HvYGPeEfBcJPseuXSKW65rHnXTwMzyKXcARUSFzQpzTdF9LEz9OTd2BdWtKfyULYpJF5Ayx26Y7bK+Ez9w4T1kE6AXywEjTU9YDlFk7YvwSO/VvAyoFjk=
	;
Received: from unknown (HELO dualcore) (adam.r@sbcglobal.net@75.2.73.81 with
	login)
	by smtp114.sbc.mail.re2.yahoo.com with SMTP; 5 Jan 2007 01:18:27 -0000
X-YMail-OSG: 
 L7hmNxoVM1m2Z5zJeB7oIIlp3lMS9OVM9upR2ccGEH6.ERLpIRPvDdquX3hf.Rz1aD3Yv9vexJX1sDGZpxGmh2EBlORa5oWHAjysp1..aks18VbpXIQvekto47lxj45dB2JLOEtcpAV11FClp61WpergwSaV9h7IRgkuS84dMSm8UFTnJWwp.ncu5oun
From: "Adam Rich" <adam.r@sbcglobal.net>
To: "'Dave Dutcher'" <dave@tridecap.com>,
	<pgsql-performance@postgresql.org>
Subject: Re: Slow Query on Postgres 8.2
Date: Thu, 4 Jan 2007 19:19:25 -0600
Message-ID: <00f501c73067$89f2bd60$6400a8c0@dualcore>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_00F6_01C73035.3F584D60"
X-Priority: 3 (Normal)
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook, Build 10.0.2627
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.2826
In-Reply-To: <006801c73058$7eb7d2a0$8300a8c0@tridecap.com>
Importance: Normal
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.493 tagged_above=0 required=5 tests=AWL, BAYES_00,
	DNS_FROM_RFC_ABUSE, DNS_FROM_RFC_POST, HTML_50_60, HTML_MESSAGE
X-Spam-Level: 
X-Archive-Number: 200701/63
X-Sequence-Number: 22664

This is a multi-part message in MIME format.

------=_NextPart_000_00F6_01C73035.3F584D60
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit

Dave,
Is it me or are the two examples you attached returning different row
counts?  
That means either the source data is different, or your queries are.
 
 

-----Original Message-----
From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Dave
Dutcher
Sent: Thursday, January 04, 2007 5:32 PM
To: pgsql-performance@postgresql.org
Subject: [PERFORM] Slow Query on Postgres 8.2



Hello,
 
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:
 
select type, currency_id, instrument_id, sum(amount) as total_amount
from om_transaction 
where 
strategy_id in
('BASKET1','BASKET2','BASKET3','BASKET4','BASKET5','BASKET6','BASKET7','
BASKET8','BASKET9','BASKET10','BASKET11')
and owner_trader_id in ('dave','sam','bob','tad',
'tim','harry','frank','bart','lisa','homer','marge','maggie','apu','milh
ouse','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 <= '2006-12-05' 
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.
 
Thanks,
 

Dave Dutcher
Telluride Asset Management
952.653.6411

 

 


------=_NextPart_000_00F6_01C73035.3F584D60
Content-Type: text/html;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML xmlns:o =3D=20
"urn:schemas-microsoft-com:office:office"><HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Dus-ascii">
<TITLE>Message</TITLE>

<META content=3D"MSHTML 6.00.3790.2817" name=3DGENERATOR></HEAD>
<BODY>
<DIV><SPAN class=3D015201701-05012007><FONT face=3DArial color=3D#0000ff =

size=3D2>Dave,</FONT></SPAN></DIV>
<DIV><SPAN class=3D015201701-05012007><FONT face=3DArial color=3D#0000ff =
size=3D2>Is it=20
me or are the two examples you attached returning different row =
counts?&nbsp;=20
</FONT></SPAN></DIV>
<DIV><SPAN class=3D015201701-05012007><FONT face=3DArial color=3D#0000ff =
size=3D2>That=20
means either </FONT></SPAN><SPAN class=3D015201701-05012007><FONT =
face=3DArial=20
color=3D#0000ff size=3D2>the source data is different, or your queries=20
are.</FONT></SPAN></DIV>
<DIV><SPAN class=3D015201701-05012007><FONT face=3DArial color=3D#0000ff =

size=3D2></FONT></SPAN>&nbsp;</DIV>
<DIV><SPAN class=3D015201701-05012007><FONT face=3DArial color=3D#0000ff =

size=3D2></FONT></SPAN>&nbsp;</DIV>
<BLOCKQUOTE dir=3Dltr style=3D"MARGIN-RIGHT: 0px">
  <DIV></DIV>
  <DIV class=3DOutlookMessageHeader lang=3Den-us dir=3Dltr =
align=3Dleft><FONT=20
  face=3DTahoma size=3D2>-----Original Message-----<BR><B>From:</B>=20
  pgsql-performance-owner@postgresql.org=20
  [mailto:pgsql-performance-owner@postgresql.org] <B>On Behalf Of =
</B>Dave=20
  Dutcher<BR><B>Sent:</B> Thursday, January 04, 2007 5:32 =
PM<BR><B>To:</B>=20
  pgsql-performance@postgresql.org<BR><B>Subject:</B> [PERFORM] Slow =
Query on=20
  Postgres 8.2<BR><BR></FONT></DIV>
  <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 =

  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;=20
  '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=20
  values in the in statements to fake ones, but it still takes over =
three=20
  seconds on 8.2, where 8.1 only takes 26 milliseconds.&nbsp; When I =
increase=20
  the number of valules in the IN clauses, the query rapidly gets =
worse.&nbsp; I=20
  tried increasing my stats target to 1000 and analyzing, but that =
didn't help=20
  so I put that back to 10.&nbsp; While the query is running the CPU is =
at=20
  100%.&nbsp; Is there a more efficient way to write a query like =
this?&nbsp;=20
  I've attached the 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><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></BLOCKQUOTE></BODY></HTML>

------=_NextPart_000_00F6_01C73035.3F584D60--




From dave@tridecap.com Fri Jan  5 01:51:24 2007
Received: from localhost (maia-5.hub.org [200.46.204.182])
	by postgresql.org (Postfix) with ESMTP id 8E5D59FA433
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Thu,  4 Jan 2007 21:51:24 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.182]) (amavisd-new, port 10024)
	with ESMTP id 44266-06 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Thu,  4 Jan 2007 21:51:13 -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 B8B2B9FA0B7
	for <pgsql-performance@postgresql.org>;
	Thu,  4 Jan 2007 21:51:18 -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 121078790; Thu, 04 Jan 2007 17:51:46 -0800
From: "Dave Dutcher" <dave@tridecap.com>
To: "'Tom Lane'" <tgl@sss.pgh.pa.us>
Cc: <pgsql-performance@postgresql.org>
Subject: Re: Slow Query on Postgres 8.2 
Date: Thu, 4 Jan 2007 19:51:16 -0600
Message-ID: <007901c7306b$fd1d2c90$8300a8c0@tridecap.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="US-ASCII"
Content-Transfer-Encoding: quoted-printable
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
In-Reply-To: <7598.1167959569@sss.pgh.pa.us>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200701/64
X-Sequence-Number: 22665

> -----Original Message-----
> From: pgsql-performance-owner@postgresql.org=20
> [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Tom Lane
>=20
> Um ... what indexes has this table got exactly?  It's very=20
> unclear what
> alternatives the planner is being faced with.
>=20

Here is the table definition.  Thanks.

                           Table "public.om_transaction"
     Column      |          Type          |                Modifiers
-----------------+------------------------+------------------------------=
---
--------
 transaction_id  | character varying(20)  | not null default =
'0'::character
varying
 type            | character varying(20)  | not null default =
''::character
varying
 fund_id         | character varying(10)  | not null default =
''::character
varying
 owner_trader_id | character varying(10)  | not null default =
''::character
varying
 strategy_id     | character varying(30)  | not null default =
''::character
varying
 instrument_id   | integer                | default 0
 cf_account_id   | integer                | not null default 0
 as_of_date      | date                   | not null default
'0001-01-01'::date
 insert_date     | date                   | not null default
'0001-01-01'::date
 amount          | numeric(22,9)          | not null default 0.000000000
 currency_id     | integer                | not null default 0
 process_state   | integer                | not null
 comment         | character varying(256) | default ''::character =
varying
Indexes:
    "om_transaction_pkey" PRIMARY KEY, btree (transaction_id)
    "cf_account_id_om_transaction_index" btree (cf_account_id)
    "currency_id_om_transaction_index" btree (currency_id)
    "fund_id_om_transaction_index" btree (fund_id)
    "instrument_id_om_transaction_index" btree (instrument_id)
    "om_transaction_om_transaction_index" btree (as_of_date, fund_id,
strategy_id, owner_trader_id, cf_account_id, instrument_id, "type")
    "om_transaction_partial_process_state_index" btree (process_state) =
WHERE
process_state =3D 0
    "owner_trader_id_om_transaction_index" btree (owner_trader_id)
    "strategy_id_om_transaction_index" btree (strategy_id)
Foreign-key constraints:
    "$1" FOREIGN KEY (owner_trader_id) REFERENCES om_trader(trader_id)
    "$2" FOREIGN KEY (fund_id) REFERENCES om_fund(fund_id)
    "$3" FOREIGN KEY (strategy_id) REFERENCES om_strategy(strategy_id)
    "$4" FOREIGN KEY (cf_account_id) REFERENCES om_cf_account(id)
    "$5" FOREIGN KEY (instrument_id) REFERENCES om_instrument(id)
    "$6" FOREIGN KEY (currency_id) REFERENCES om_instrument(id)




From dave@tridecap.com Fri Jan  5 02:01:28 2007
Received: from localhost (maia-5.hub.org [200.46.204.182])
	by postgresql.org (Postfix) with ESMTP id C1F809FA4D6
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Thu,  4 Jan 2007 22:01:28 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.182]) (amavisd-new, port 10024)
	with ESMTP id 44782-10 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Thu,  4 Jan 2007 22:01:11 -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 899B79FA509
	for <pgsql-performance@postgresql.org>;
	Thu,  4 Jan 2007 22:01:15 -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 121079599; Thu, 04 Jan 2007 18:01:43 -0800
From: "Dave Dutcher" <dave@tridecap.com>
To: "'Adam Rich'" <adam.r@sbcglobal.net>,
	<pgsql-performance@postgresql.org>
Subject: Re: Slow Query on Postgres 8.2
Date: Thu, 4 Jan 2007 20:01:12 -0600
Message-ID: <007e01c7306d$60ccc6f0$8300a8c0@tridecap.com>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_007F_01C7303B.163256F0"
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
In-Reply-To: <00f501c73067$89f2bd60$6400a8c0@dualcore>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200701/65
X-Sequence-Number: 22666

This is a multi-part message in MIME format.

------=_NextPart_000_007F_01C7303B.163256F0
Content-Type: text/plain;
	charset="US-ASCII"
Content-Transfer-Encoding: quoted-printable

The source data is a little different.  The fast query was on our =
production
8.1 server, and the other was a test 8.2 server with day old data.  The
production server has like 3.84 million rows vs 3.83 million rows in =
test,
so the statistics might be a little different, but I would figure the
compairison is still valid.
=20

-----Original Message-----
From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Adam Rich
Sent: Thursday, January 04, 2007 7:19 PM
To: 'Dave Dutcher'; pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Slow Query on Postgres 8.2


Dave,
Is it me or are the two examples you attached returning different row
counts? =20
That means either the source data is different, or your queries are.
=20
=20

-----Original Message-----
From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Dave =
Dutcher
Sent: Thursday, January 04, 2007 5:32 PM
To: pgsql-performance@postgresql.org
Subject: [PERFORM] Slow Query on Postgres 8.2



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_000_007F_01C7303B.163256F0
Content-Type: text/html;
	charset="US-ASCII"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML xmlns:o =3D=20
"urn:schemas-microsoft-com:office:office"><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><SPAN class=3D891205201-05012007><FONT face=3DArial color=3D#0000ff =
size=3D2>The=20
source data is a little different.&nbsp;&nbsp;The fast&nbsp;query was on =
our=20
production 8.1 server, and the other was a test 8.2 server with day old=20
data.&nbsp; The production server has like 3.84 million rows vs 3.83 =
million=20
rows in test, so the statistics might be a little different, but I would =
figure=20
the compairison is still valid.</FONT></SPAN></DIV>
<DIV><SPAN class=3D891205201-05012007><FONT face=3DArial color=3D#0000ff =

size=3D2></FONT></SPAN>&nbsp;</DIV>
<BLOCKQUOTE dir=3Dltr=20
style=3D"PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #0000ff 2px =
solid; MARGIN-RIGHT: 0px">
  <DIV></DIV>
  <DIV class=3DOutlookMessageHeader lang=3Den-us dir=3Dltr =
align=3Dleft><FONT=20
  face=3DTahoma size=3D2>-----Original Message-----<BR><B>From:</B>=20
  pgsql-performance-owner@postgresql.org=20
  [mailto:pgsql-performance-owner@postgresql.org] <B>On Behalf Of =
</B>Adam=20
  Rich<BR><B>Sent:</B> Thursday, January 04, 2007 7:19 PM<BR><B>To:</B> =
'Dave=20
  Dutcher'; pgsql-performance@postgresql.org<BR><B>Subject:</B> Re: =
[PERFORM]=20
  Slow Query on Postgres 8.2<BR><BR></FONT></DIV>
  <DIV><SPAN class=3D015201701-05012007><FONT face=3DArial =
color=3D#0000ff=20
  size=3D2>Dave,</FONT></SPAN></DIV>
  <DIV><SPAN class=3D015201701-05012007><FONT face=3DArial =
color=3D#0000ff size=3D2>Is=20
  it me or are the two examples you attached returning different row=20
  counts?&nbsp; </FONT></SPAN></DIV>
  <DIV><SPAN class=3D015201701-05012007><FONT face=3DArial =
color=3D#0000ff size=3D2>That=20
  means either </FONT></SPAN><SPAN class=3D015201701-05012007><FONT =
face=3DArial=20
  color=3D#0000ff size=3D2>the source data is different, or your queries =

  are.</FONT></SPAN></DIV>
  <DIV><SPAN class=3D015201701-05012007><FONT face=3DArial =
color=3D#0000ff=20
  size=3D2></FONT></SPAN>&nbsp;</DIV>
  <DIV><SPAN class=3D015201701-05012007><FONT face=3DArial =
color=3D#0000ff=20
  size=3D2></FONT></SPAN>&nbsp;</DIV>
  <BLOCKQUOTE dir=3Dltr style=3D"MARGIN-RIGHT: 0px">
    <DIV></DIV>
    <DIV class=3DOutlookMessageHeader lang=3Den-us dir=3Dltr =
align=3Dleft><FONT=20
    face=3DTahoma size=3D2>-----Original Message-----<BR><B>From:</B>=20
    pgsql-performance-owner@postgresql.org=20
    [mailto:pgsql-performance-owner@postgresql.org] <B>On Behalf Of =
</B>Dave=20
    Dutcher<BR><B>Sent:</B> Thursday, January 04, 2007 5:32 =
PM<BR><B>To:</B>=20
    pgsql-performance@postgresql.org<BR><B>Subject:</B> [PERFORM] Slow =
Query on=20
    Postgres 8.2<BR><BR></FONT></DIV>
    <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;=20
    '2006-12-04' and as_of_date &lt;=3D '2006-12-05' <BR>group by type,=20
    currency_id, instrument_id;<BR></SPAN></FONT></DIV>
    <DIV><FONT face=3DArial size=3D2><SPAN class=3D041131722-04012007>I =
changed the=20
    values in the in statements to fake ones, but it still takes over =
three=20
    seconds on 8.2, where 8.1 only takes 26 milliseconds.&nbsp; When I =
increase=20
    the number of valules in the IN clauses, the query rapidly gets =
worse.&nbsp;=20
    I tried increasing my stats target to 1000 and analyzing, but that =
didn't=20
    help so I put that back to 10.&nbsp; While the query is running the =
CPU is=20
    at 100%.&nbsp; Is there a more efficient way to write a query like=20
    this?&nbsp; I've attached the output from EXPLAIN ANALYZE in a file =
because=20
    it is somewhat 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><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></BLOCKQUOTE></BLOCKQUOTE></BODY></HTML>

------=_NextPart_000_007F_01C7303B.163256F0--




From tgl@sss.pgh.pa.us Fri Jan  5 03:04:13 2007
Received: from localhost (maia-1.hub.org [200.46.204.191])
	by postgresql.org (Postfix) with ESMTP id 489479FA0B7
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Thu,  4 Jan 2007 23:04:13 -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 27355-02 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Thu,  4 Jan 2007 23:04:07 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.4
Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130])
	by postgresql.org (Postfix) with ESMTP id F29A19FA131
	for <pgsql-performance@postgresql.org>;
	Thu,  4 Jan 2007 23:04:06 -0400 (AST)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.13.6/8.13.6) with ESMTP id l05345wR008871;
	Thu, 4 Jan 2007 22:04:05 -0500 (EST)
To: "Dave Dutcher" <dave@tridecap.com>
cc: pgsql-performance@postgresql.org
Subject: Re: Slow Query on Postgres 8.2 
In-reply-to: <007901c7306b$fd1d2c90$8300a8c0@tridecap.com> 
References: <007901c7306b$fd1d2c90$8300a8c0@tridecap.com>
Comments: In-reply-to "Dave Dutcher" <dave@tridecap.com>
	message dated "Thu, 04 Jan 2007 19:51:16 -0600"
Date: Thu, 04 Jan 2007 22:04:05 -0500
Message-ID: <8870.1167966245@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200701/66
X-Sequence-Number: 22667

"Dave Dutcher" <dave@tridecap.com> writes:
> Here is the table definition.  Thanks.

[ fools around with it for awhile... ]  I think this is already fixed
for 8.2.1.  Note the costs of the two related index scans:

8.2.0:
         ->  Bitmap Index Scan on om_transaction_om_transaction_index  (cost=0.00..7421.67 rows=488 width=0) (actual time=3411.227..3411.227 rows=0 loops=1)
               Index Cond: ((as_of_date > '2006-12-04'::date) AND (as_of_date <= '2006-12-05'::date) AND ((strategy_id)::text = ANY (('{BASKET1,BASKET2,BASKET3,BASKET4,BASKET5,BASKET6,BASKET7,BASKET8,BASKET9,BASKET10,BASKET11}'::character varying[])::text[])) AND ((owner_trader_id)::text = ANY (('{dave,sam,bob,tad,tim,harry,frank,bart,lisa,homer,marge,maggie,apu,milhouse,"disco stu"}'::character varying[])::text[])) AND (cf_account_id = ANY ('{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,29}'::integer[])))

8.1.2:
               ->  Bitmap Index Scan on om_transaction_om_transaction_index  (cost=0.00..101.69 rows=5949 width=0) (actual time=3.419..3.419 rows=7967 loops=1)
                     Index Cond: ((as_of_date > '2006-12-04'::date) AND (as_of_date <= '2006-12-05'::date))

8.1.2 returns a lot more rows but spends a lot less time doing it.
The reason is that using all those =ANY clauses as index quals is
*expensive* --- they actually trigger multiple scans of the index.
8.2.0 is underestimating their cost.  We fixed that a couple weeks ago
(after some reports from Arjen van der Meijden) and I can't actually get
8.2 branch tip to produce a plan like what you show.

Please try it again when 8.2.1 comes out (Monday) and we'll see if
there's any more tweaking needed.

BTW, it's interesting to note that the plan 8.1.2 produces is pretty
obviously bogus in itself ... why do only the first two arms of the
BitmapOr use as_of_date conditions?  We fixed some sillinesses in the
bitmap scan planning later in the 8.1 series, so I think you'd find
that 8.1.latest does this differently.

			regards, tom lane



From dave@tridecap.com Mon Jan  8 18:59:24 2007
Received: from localhost (maia-4.hub.org [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id 348149FB204
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Mon,  8 Jan 2007 14:59:24 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-new, port 10024)
	with ESMTP id 63589-06 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Mon,  8 Jan 2007 14:59:17 -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 CBCD79FB1DE
	for <pgsql-performance@postgresql.org>;
	Mon,  8 Jan 2007 14:59:16 -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 121546768; Mon, 08 Jan 2007 10:59:45 -0800
From: "Dave Dutcher" <dave@tridecap.com>
To: "'Tom Lane'" <tgl@sss.pgh.pa.us>
Cc: <pgsql-performance@postgresql.org>
Subject: Re: Slow Query on Postgres 8.2 
Date: Mon, 8 Jan 2007 12:59:14 -0600
Message-ID: <012601c73357$1736ff00$8300a8c0@tridecap.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="US-ASCII"
Content-Transfer-Encoding: quoted-printable
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
In-Reply-To: <8870.1167966245@sss.pgh.pa.us>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200701/99
X-Sequence-Number: 22700

> -----Original Message-----
> From: pgsql-performance-owner@postgresql.org=20
> [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Tom Lane
>=20
> [ fools around with it for awhile... ]  I think this is already fixed
> for 8.2.1.  Note the costs of the two related index scans:

I installed 8.2.1 this morning and it works much better.  The query that =
was
taking 3411.429ms on 8.2.0 now takes 9.3ms.  Thanks for your help.