pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedSlow Query on Postgres 8.2
7+ messages / 3 participants
[nested] [flat]
* Slow Query on Postgres 8.2
@ 2007-01-04 23:31 Dave Dutcher <dave@tridecap.com>
2007-01-05 01:12 ` Re: Slow Query on Postgres 8.2 Tom Lane <tgl@sss.pgh.pa.us>
2007-01-05 01:19 ` Re: Slow Query on Postgres 8.2 Adam Rich <adam.r@sbcglobal.net>
0 siblings, 2 replies; 7+ messages in thread
From: Dave Dutcher @ 2007-01-04 23:31 UTC (permalink / raw)
To: pgsql-performance
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','BASK
ET8','BASKET9','BASKET10','BASKET11')
and owner_trader_id in ('dave','sam','bob','tad',
'tim','harry','frank','bart','lisa','homer','marge','maggie','apu','milhouse
','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
Attachments:
plans.txt (text/plain, 10.4K) download
explain analyze
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','milhouse','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;
Slow:
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=9287.82..9288.44 rows=49 width=35) (actual time=3411.238..3411.238 rows=0 loops=1)
-> Bitmap Heap Scan on om_transaction (cost=7421.67..9282.94 rows=488 width=35) (actual time=3411.235..3411.235 rows=0 loops=1)
Recheck 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[])))
-> 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[])))
Total runtime: 3411.429 ms
(6 rows)
Fast:
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=431.45..431.87 rows=34 width=35) (actual time=25.442..25.442 rows=0 loops=1)
-> Bitmap Heap Scan on om_transaction (cost=383.09..428.08 rows=337 width=35) (actual time=25.429..25.429 rows=0 loops=1)
Recheck Cond: ((as_of_date > '2006-12-04'::date) AND (as_of_date <= '2006-12-05'::date) AND (((as_of_date > '2006-12-04'::date) AND (as_of_date <= '2006-12-05'::date) AND (cf_account_id = 1)) OR ((as_of_date > '2006-12-04'::date) AND (as_of_date <= '2006-12-05'::date) AND (cf_account_id = 2)) OR (cf_account_id = 3) OR (cf_account_id = 4) OR (cf_account_id = 5) OR (cf_account_id = 6) OR (cf_account_id = 7) OR (cf_account_id = 8) OR (cf_account_id = 9) OR (cf_account_id = 10) OR (cf_account_id = 11) OR (cf_account_id = 12) OR (cf_account_id = 13) OR (cf_account_id = 14) OR (cf_account_id = 15) OR (cf_account_id = 16) OR (cf_account_id = 17) OR (cf_account_id = 18) OR (cf_account_id = 29)))
Filter: ((((strategy_id)::text = 'BASKET1'::text) OR ((strategy_id)::text = 'BASKET2'::text) OR ((strategy_id)::text = 'BASKET3'::text) OR ((strategy_id)::text = 'BASKET4'::text) OR ((strategy_id)::text = 'BASKET5'::text) OR ((strategy_id)::text = 'BASKET6'::text) OR ((strategy_id)::text = 'BASKET7'::text) OR ((strategy_id)::text = 'BASKET8'::text) OR ((strategy_id)::text = 'BASKET9'::text) OR ((strategy_id)::text = 'BASKET10'::text) OR ((strategy_id)::text = 'BASKET11'::text)) AND (((owner_trader_id)::text = 'dave'::text) OR ((owner_trader_id)::text = 'sam'::text) OR ((owner_trader_id)::text = 'bob'::text) OR ((owner_trader_id)::text = 'tad'::text) OR ((owner_trader_id)::text = 'tim'::text) OR ((owner_trader_id)::text = 'harry'::text) OR ((owner_trader_id)::text = 'frank'::text) OR ((owner_trader_id)::text = 'bart'::text) OR ((owner_trader_id)::text = 'lisa'::text) OR ((owner_trader_id)::text = 'homer'::text) OR ((owner_trader_id)::text = 'marge'::text) OR ((owner_trader_id)::text = 'maggie'::text) OR ((owner_trader_id)::text = 'apu'::text) OR ((owner_trader_id)::text = 'milhouse'::text) OR ((owner_trader_id)::text = 'disco stu'::text)))
-> BitmapAnd (cost=383.09..383.09 rows=11 width=0) (actual time=13.497..13.497 rows=0 loops=1)
-> 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))
-> BitmapOr (cost=281.14..281.14 rows=7297 width=0) (actual time=9.968..9.968 rows=0 loops=1)
-> Bitmap Index Scan on om_transaction_om_transaction_index (cost=0.00..116.57 rows=2699 width=0) (actual time=3.627..3.627 rows=2747 loops=1)
Index Cond: ((as_of_date > '2006-12-04'::date) AND (as_of_date <= '2006-12-05'::date) AND (cf_account_id = 1))
-> Bitmap Index Scan on om_transaction_om_transaction_index (cost=0.00..116.57 rows=2039 width=0) (actual time=3.501..3.501 rows=2152 loops=1)
Index Cond: ((as_of_date > '2006-12-04'::date) AND (as_of_date <= '2006-12-05'::date) AND (cf_account_id = 2))
-> Bitmap Index Scan on cf_account_id_om_transaction_index (cost=0.00..2.00 rows=1 width=0) (actual time=0.053..0.053 rows=0 loops=1)
Index Cond: (cf_account_id = 3)
-> Bitmap Index Scan on cf_account_id_om_transaction_index (cost=0.00..2.00 rows=1 width=0) (actual time=0.011..0.011 rows=0 loops=1)
Index Cond: (cf_account_id = 4)
-> Bitmap Index Scan on cf_account_id_om_transaction_index (cost=0.00..2.00 rows=1 width=0) (actual time=0.011..0.011 rows=1 loops=1)
Index Cond: (cf_account_id = 5)
-> Bitmap Index Scan on cf_account_id_om_transaction_index (cost=0.00..2.00 rows=1 width=0) (actual time=0.078..0.078 rows=194 loops=1)
Index Cond: (cf_account_id = 6)
-> Bitmap Index Scan on cf_account_id_om_transaction_index (cost=0.00..2.00 rows=1 width=0) (actual time=0.016..0.016 rows=0 loops=1)
Index Cond: (cf_account_id = 7)
-> Bitmap Index Scan on cf_account_id_om_transaction_index (cost=0.00..2.00 rows=1 width=0) (actual time=0.010..0.010 rows=0 loops=1)
Index Cond: (cf_account_id = 8)
-> Bitmap Index Scan on cf_account_id_om_transaction_index (cost=0.00..2.00 rows=1 width=0) (actual time=0.023..0.023 rows=6 loops=1)
Index Cond: (cf_account_id = 9)
-> Bitmap Index Scan on cf_account_id_om_transaction_index (cost=0.00..2.00 rows=1 width=0) (actual time=0.010..0.010 rows=0 loops=1)
Index Cond: (cf_account_id = 10)
-> Bitmap Index Scan on cf_account_id_om_transaction_index (cost=0.00..2.00 rows=1 width=0) (actual time=0.010..0.010 rows=0 loops=1)
Index Cond: (cf_account_id = 11)
-> Bitmap Index Scan on cf_account_id_om_transaction_index (cost=0.00..2.00 rows=1 width=0) (actual time=0.009..0.009 rows=0 loops=1)
Index Cond: (cf_account_id = 12)
-> Bitmap Index Scan on cf_account_id_om_transaction_index (cost=0.00..2.00 rows=1 width=0) (actual time=0.013..0.013 rows=12 loops=1)
Index Cond: (cf_account_id = 13)
-> Bitmap Index Scan on cf_account_id_om_transaction_index (cost=0.00..2.00 rows=1 width=0) (actual time=0.010..0.010 rows=0 loops=1)
Index Cond: (cf_account_id = 14)
-> Bitmap Index Scan on cf_account_id_om_transaction_index (cost=0.00..2.00 rows=1 width=0) (actual time=0.010..0.010 rows=1 loops=1)
Index Cond: (cf_account_id = 15)
-> Bitmap Index Scan on cf_account_id_om_transaction_index (cost=0.00..2.00 rows=1 width=0) (actual time=0.011..0.011 rows=0 loops=1)
Index Cond: (cf_account_id = 16)
-> Bitmap Index Scan on cf_account_id_om_transaction_index (cost=0.00..2.00 rows=1 width=0) (actual time=0.036..0.036 rows=66 loops=1)
Index Cond: (cf_account_id = 17)
-> Bitmap Index Scan on cf_account_id_om_transaction_index (cost=0.00..15.96 rows=2559 width=0) (actual time=2.403..2.403 rows=8201 loops=1)
Index Cond: (cf_account_id = 18)
-> Bitmap Index Scan on cf_account_id_om_transaction_index (cost=0.00..2.00 rows=1 width=0) (actual time=0.032..0.032 rows=0 loops=1)
Index Cond: (cf_account_id = 29)
Total runtime: 26.109 ms
(47 rows)
* Re: Slow Query on Postgres 8.2
2007-01-04 23:31 Slow Query on Postgres 8.2 Dave Dutcher <dave@tridecap.com>
@ 2007-01-05 01:12 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-01-05 01:51 ` Re: Slow Query on Postgres 8.2 Dave Dutcher <dave@tridecap.com>
1 sibling, 1 reply; 7+ messages in thread
From: Tom Lane @ 2007-01-05 01:12 UTC (permalink / raw)
To: Dave Dutcher <dave@tridecap.com>; +Cc: pgsql-performance
"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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Slow Query on Postgres 8.2
2007-01-04 23:31 Slow Query on Postgres 8.2 Dave Dutcher <dave@tridecap.com>
2007-01-05 01:12 ` Re: Slow Query on Postgres 8.2 Tom Lane <tgl@sss.pgh.pa.us>
@ 2007-01-05 01:51 ` Dave Dutcher <dave@tridecap.com>
2007-01-05 03:04 ` Re: Slow Query on Postgres 8.2 Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: Dave Dutcher @ 2007-01-05 01:51 UTC (permalink / raw)
To: 'Tom Lane' <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
> -----Original Message-----
> From: pgsql-performance-owner@postgresql.org
> [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Tom Lane
>
> Um ... what indexes has this table got exactly? It's very
> unclear what
> alternatives the planner is being faced with.
>
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 = 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)
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Slow Query on Postgres 8.2
2007-01-04 23:31 Slow Query on Postgres 8.2 Dave Dutcher <dave@tridecap.com>
2007-01-05 01:12 ` Re: Slow Query on Postgres 8.2 Tom Lane <tgl@sss.pgh.pa.us>
2007-01-05 01:51 ` Re: Slow Query on Postgres 8.2 Dave Dutcher <dave@tridecap.com>
@ 2007-01-05 03:04 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-01-08 18:59 ` Re: Slow Query on Postgres 8.2 Dave Dutcher <dave@tridecap.com>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2007-01-05 03:04 UTC (permalink / raw)
To: Dave Dutcher <dave@tridecap.com>; +Cc: pgsql-performance
"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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Slow Query on Postgres 8.2
2007-01-04 23:31 Slow Query on Postgres 8.2 Dave Dutcher <dave@tridecap.com>
2007-01-05 01:12 ` Re: Slow Query on Postgres 8.2 Tom Lane <tgl@sss.pgh.pa.us>
2007-01-05 01:51 ` Re: Slow Query on Postgres 8.2 Dave Dutcher <dave@tridecap.com>
2007-01-05 03:04 ` Re: Slow Query on Postgres 8.2 Tom Lane <tgl@sss.pgh.pa.us>
@ 2007-01-08 18:59 ` Dave Dutcher <dave@tridecap.com>
0 siblings, 0 replies; 7+ messages in thread
From: Dave Dutcher @ 2007-01-08 18:59 UTC (permalink / raw)
To: 'Tom Lane' <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
> -----Original Message-----
> From: pgsql-performance-owner@postgresql.org
> [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Tom Lane
>
> [ 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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Slow Query on Postgres 8.2
2007-01-04 23:31 Slow Query on Postgres 8.2 Dave Dutcher <dave@tridecap.com>
@ 2007-01-05 01:19 ` Adam Rich <adam.r@sbcglobal.net>
2007-01-05 02:01 ` Re: Slow Query on Postgres 8.2 Dave Dutcher <dave@tridecap.com>
1 sibling, 1 reply; 7+ messages in thread
From: Adam Rich @ 2007-01-05 01:19 UTC (permalink / raw)
To: 'Dave Dutcher' <dave@tridecap.com>; pgsql-performance
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Slow Query on Postgres 8.2
2007-01-04 23:31 Slow Query on Postgres 8.2 Dave Dutcher <dave@tridecap.com>
2007-01-05 01:19 ` Re: Slow Query on Postgres 8.2 Adam Rich <adam.r@sbcglobal.net>
@ 2007-01-05 02:01 ` Dave Dutcher <dave@tridecap.com>
0 siblings, 0 replies; 7+ messages in thread
From: Dave Dutcher @ 2007-01-05 02:01 UTC (permalink / raw)
To: 'Adam Rich' <adam.r@sbcglobal.net>; pgsql-performance
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.
-----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?
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','BASK
ET8','BASKET9','BASKET10','BASKET11')
and owner_trader_id in ('dave','sam','bob','tad',
'tim','harry','frank','bart','lisa','homer','marge','maggie','apu','milhouse
','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
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2007-01-08 18:59 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-01-04 23:31 Slow Query on Postgres 8.2 Dave Dutcher <dave@tridecap.com>
2007-01-05 01:12 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-01-05 01:51 ` Dave Dutcher <dave@tridecap.com>
2007-01-05 03:04 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-01-08 18:59 ` Dave Dutcher <dave@tridecap.com>
2007-01-05 01:19 ` Adam Rich <adam.r@sbcglobal.net>
2007-01-05 02:01 ` Dave Dutcher <dave@tridecap.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox