* 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
Tatsuo Ishii is...@postgresql.org 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
Tony Capobianco tcapobia...@prospectiv.com 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
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
* 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
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
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
Hello
what is your settings for
random_page_cost, seq_page_cost and work_mem?
Regards
Pavel Stehule
2011/6/8 Tony Capobianco tcapobia...@prospectiv.com:
Here's the explain analyze:
pg_dw=# explain analyze CREATE TABLE ecr_opens with (FILLFACTOR=100)
as
select o.emailcampaignid,
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_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
Tony Capobianco tcapobia...@prospectiv.com 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
2011/6/8 Tony Capobianco tcapobia...@prospectiv.com:
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
--
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?
Tony Capobianco tcapobia...@prospectiv.com 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
On Wed, Jun 8, 2011 at 12:03 PM, Tony Capobianco tcapobia...@prospectiv.com
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
15 matches
Mail list logo