Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id C1F809FA4D6 for ; 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 ; 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 ; 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" To: "'Adam Rich'" , 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 Message
The=20 source data is a little different.  The fast query was on = our=20 production 8.1 server, and the other was a test 8.2 server with day old=20 data.  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.
 
-----Original Message-----
From:=20 pgsql-performance-owner@postgresql.org=20 [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of = Adam=20 Rich
Sent: Thursday, January 04, 2007 7:19 PM
To: = 'Dave=20 Dutcher'; pgsql-performance@postgresql.org
Subject: Re: = [PERFORM]=20 Slow Query on Postgres 8.2

Dave,
Is=20 it me or are the two examples you attached returning different row=20 counts? 
That=20 means either the source data is different, or your queries = 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=20 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,=20 currency_id, 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. =20 I tried increasing my stats target to 1000 and analyzing, but that = didn't=20 help so I put that back to 10.  While the query is running the = CPU is=20 at 100%.  Is there a more efficient way to write a query like=20 this?  I've attached the output from EXPLAIN ANALYZE in a file = because=20 it is somewhat large.
 
Thanks,
 

Dave = Dutcher
Telluride Asset=20 Management
952.653.6411

 

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