Tatsuo Ishii writes:
> Just out of curiosity, is there any chance that this kind of query is
> speeding up in 9.1 because of following changes?
> * Allow FULL OUTER JOIN to be implemented as a hash join, and allow
>either side of a LEFT OUTER JOIN or RIGHT OUTER JOIN to be hashed
>
> * Tony Capobianco (tcapobia...@prospectiv.com) wrote:
>> HashAggregate (cost=4391163.81..4391288.05 rows=9939 width=12)
>>-> Hash Join (cost=14.78..4344767.23 rows=9279316 width=12)
>> Hash Cond: (o.emailcampaignid = s.emailcampaignid)
>> -> Seq Scan on openactivity o
Oooo...some bad math there. Thanks.
On Wed, 2011-06-08 at 12:38 -0700, Samuel Gendler wrote:
>
>
> On Wed, Jun 8, 2011 at 12:03 PM, Tony Capobianco
> wrote:
> My current setting is 22G. According to some documentation, I
> want to
> set effective_cache_size to my OS di
On Wed, Jun 8, 2011 at 12:03 PM, Tony Capobianco wrote:
> My current setting is 22G. According to some documentation, I want to
> set effective_cache_size to my OS disk cache + shared_buffers. In this
> case, I have 4 quad-core processors with 512K cache (8G) and my
> shared_buffers is 7680M.
Tony Capobianco wrote:
> According to some documentation, I want to set
> effective_cache_size to my OS disk cache + shared_buffers.
That seems reasonable, and is what has worked well for me.
> In this case, I have 4 quad-core processors with 512K cache (8G)
> and my shared_buffers is 7680M.
My current setting is 22G. According to some documentation, I want to
set effective_cache_size to my OS disk cache + shared_buffers. In this
case, I have 4 quad-core processors with 512K cache (8G) and my
shared_buffers is 7680M. Therefore my effective_cache_size should be
approximately 16G? Mo
2011/6/8 Tony Capobianco :
> pg_dw=# show random_page_cost ;
> random_page_cost
> --
> 4
> (1 row)
>
> Time: 0.299 ms
> pg_dw=# show seq_page_cost ;
> seq_page_cost
> ---
> 1
> (1 row)
>
> Time: 0.250 ms
> pg_dw=# show work_mem ;
> work_mem
> --
> 768MB
> (
Tony Capobianco writes:
> Well, this ran much better. However, I'm not sure if it's because of
> set enable_nestloop = 0, or because I'm executing the query twice in a
> row, where previous results may be cached. I will try this setting in
> my code for when this process runs later today and see
pg_dw=# show random_page_cost ;
random_page_cost
--
4
(1 row)
Time: 0.299 ms
pg_dw=# show seq_page_cost ;
seq_page_cost
---
1
(1 row)
Time: 0.250 ms
pg_dw=# show work_mem ;
work_mem
--
768MB
(1 row)
On Wed, 2011-06-08 at 18:27 +0200, Pavel Stehule
Well, this ran much better. However, I'm not sure if it's because of
set enable_nestloop = 0, or because I'm executing the query twice in a
row, where previous results may be cached. I will try this setting in
my code for when this process runs later today and see what the result
is.
Thanks!
pg
Hello
what is your settings for
random_page_cost, seq_page_cost and work_mem?
Regards
Pavel Stehule
2011/6/8 Tony Capobianco :
> Here's the explain analyze:
>
> pg_dw=# explain analyze CREATE TABLE ecr_opens with (FILLFACTOR=100)
> as
> select o.emailcampaignid, count(memberid) opencnt
> from
Here's the explain analyze:
pg_dw=# explain analyze CREATE TABLE ecr_opens with (FILLFACTOR=100)
as
select o.emailcampaignid, count(memberid) opencnt
from openactivity o,ecr_sents s
where s.emailcampaignid = o.emailcampaignid
group by o.emailcampaignid;
QUERY
PLAN
08.06.11 18:40, Tony Capobianco написав(ла):
pg_dw=# set enable_nestloop =0;
SET
Time: 0.165 ms
pg_dw=# explain CREATE TABLE ecr_opens with (FILLFACTOR=100)
pg_dw-# as
pg_dw-# select o.emailcampaignid, count(memberid) opencnt
pg_dw-# from openactivity o,ecr_sents s
pg_dw-# where s.emailcampaig
* Tony Capobianco (tcapobia...@prospectiv.com) wrote:
> HashAggregate (cost=4391163.81..4391288.05 rows=9939 width=12)
>-> Hash Join (cost=14.78..4344767.23 rows=9279316 width=12)
> Hash Cond: (o.emailcampaignid = s.emailcampaignid)
> -> Seq Scan on openactivity o (cost=
pg_dw=# set enable_nestloop =0;
SET
Time: 0.165 ms
pg_dw=# explain CREATE TABLE ecr_opens with (FILLFACTOR=100)
pg_dw-# as
pg_dw-# select o.emailcampaignid, count(memberid) opencnt
pg_dw-# from openactivity o,ecr_sents s
pg_dw-# where s.emailcampaignid = o.emailcampaignid
pg_dw-# group by o.ema
Tony Capobianco writes:
> pg_dw=# explain CREATE TABLE ecr_opens with (FILLFACTOR=100)
> pg_dw-# as
> pg_dw-# select o.emailcampaignid, count(memberid) opencnt
> pg_dw-# from openactivity o,ecr_sents s
> pg_dw-# where s.emailcampaignid = o.emailcampaignid
> pg_dw-# group by o.emailcampaignid;
> On Postgres, this same query takes about 58 minutes (could not run
> explain analyze because it is in progress):
>
> pg_dw=# explain CREATE TABLE ecr_opens with (FILLFACTOR=100)
> pg_dw-# as
> pg_dw-# select o.emailcampaignid, count(memberid) opencnt
> pg_dw-# from openactivity o,ecr_sents s
>
17 matches
Mail list logo
