Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 9BDE09FA48A for ; 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 ; 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 ; 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" To: "'Dave Dutcher'" , 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 Message
Dave,
Is it=20 me or are the two examples you attached returning different row = counts? =20
That=20 means either the source data is different, or your queries=20 are.
 
 
-----Original Message-----
From:=20 pgsql-performance-owner@postgresql.org=20 [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of = Dave=20 Dutcher
Sent: Thursday, January 04, 2007 5:32 = PM
To:=20 pgsql-performance@postgresql.org
Subject: [PERFORM] Slow = Query on=20 Postgres 8.2

Hello,
 
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.  Here is the query:
 
select type,=20 currency_id, instrument_id, sum(amount) as total_amount from = om_transaction=20
where
strategy_id in=20 = ('BASKET1','BASKET2','BASKET3','BASKET4','BASKET5','BASKET6','BASKET7','B= ASKET8','BASKET9','BASKET10','BASKET11')
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')
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)
and as_of_date = >=20 '2006-12-04' and as_of_date <=3D '2006-12-05'
group by type, = currency_id,=20 instrument_id;
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.  When I = increase=20 the number of valules in the IN clauses, the query rapidly gets = worse.  I=20 tried increasing my stats target to 1000 and analyzing, but that = didn't help=20 so I put that back to 10.  While the query is running the CPU is = at=20 100%.  Is there a more efficient way to write a query like = this? =20 I've attached the output from EXPLAIN ANALYZE in a file because it is = somewhat=20 large.
 
Thanks,
 

Dave = Dutcher
Telluride Asset=20 Management
952.653.6411

 

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