pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedJoin runs for > 10 hours and then fills up >1.3TB of disk space
11+ messages / 5 participants
[nested] [flat]
* Join runs for > 10 hours and then fills up >1.3TB of disk space
@ 2008-05-16 06:31 kevin kempter <kevin@kevinkempterllc.com>
0 siblings, 5 replies; 11+ messages in thread
From: kevin kempter @ 2008-05-16 06:31 UTC (permalink / raw)
To: pgsql-performance
Hi List;
I have a table with 9,961,914 rows in it (see the describe of
bigtab_stats_fact_tmp14 below)
I also have a table with 7,785 rows in it (see the describe of
xsegment_dim below)
I'm running the join shown below and it takes > 10 hours and
eventually runs out of disk space on a 1.4TB file system
I've included below a describe of both tables, the join and an explain
plan, any help / suggestions would be much appreciated !
I need to get this beast to run as quickly as possible (without
filling up my file system)
Thanks in advance...
select
f14.xpublisher_dim_id,
f14.xtime_dim_id,
f14.xlocation_dim_id,
f14.xreferrer_dim_id,
f14.xsite_dim_id,
f14.xsystem_cfg_dim_id,
f14.xaffiliate_dim_id,
f14.customer_id,
pf_dts_id,
episode_id,
sessionid,
bytes_received,
bytes_transmitted,
total_played_time_sec,
segdim.xsegment_dim_id as episode_level_segid
from
bigtab_stats_fact_tmp14 f14,
xsegment_dim segdim
where
f14.customer_id = segdim.customer_srcid
and f14.show_id = segdim.show_srcid
and f14.season_id = segdim.season_srcid
and f14.episode_id = segdim.episode_srcid
and segdim.segment_srcid is NULL;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Merge Join (cost=1757001.74..73569676.49 rows=3191677219 width=118)
Merge Cond: ((segdim.episode_srcid = f14.episode_id) AND
(segdim.customer_srcid = f14.customer_id) AND (segdim.show_srcid =
f14.show_id) AND (segdim.season_srcid = f14.season_id))
-> Sort (cost=1570.35..1579.46 rows=3643 width=40)
Sort Key: segdim.episode_srcid, segdim.customer_srcid,
segdim.show_srcid, segdim.season_srcid
-> Seq Scan on xsegment_dim segdim (cost=0.00..1354.85 rows=3643
width=40)
Filter: (segment_srcid IS NULL)
-> Sort (cost=1755323.26..1780227.95 rows=9961874 width=126)
Sort Key: f14.episode_id, f14.customer_id, f14.show_id, f14.season_id
-> Seq Scan on bigtab_stats_fact_tmp14 f14 (cost=0.00..597355.74
rows=9961874 width=126)
(9 rows)
# \d bigtab_stats_fact_tmp14
Table "public.bigtab_stats_fact_tmp14"
Column | Type | Modifiers
--------------------------+-----------------------------+-----------
pf_dts_id | bigint |
pf_device_id | bigint |
segment_id | bigint |
cdn_id | bigint |
collector_id | bigint |
digital_envoy_id | bigint |
maxmind_id | bigint |
quova_id | bigint |
website_id | bigint |
referrer_id | bigint |
affiliate_id | bigint |
custom_info_id | bigint |
start_dt | timestamp without time zone |
total_played_time_sec | numeric(18,5) |
bytes_received | bigint |
bytes_transmitted | bigint |
stall_count | integer |
stall_duration_sec | numeric(18,5) |
hiccup_count | integer |
hiccup_duration_sec | numeric(18,5) |
watched_duration_sec | numeric(18,5) |
rewatched_duration_sec | numeric(18,5) |
requested_start_position | numeric(18,5) |
requested_stop_position | numeric(18,5) |
post_position | numeric(18,5) |
is_vod | numeric(1,0) |
sessionid | bigint |
create_dt | timestamp without time zone |
segment_type_id | bigint |
customer_id | bigint |
content_publisher_id | bigint |
content_owner_id | bigint |
episode_id | bigint |
duration_sec | numeric(18,5) |
device_id | bigint |
os_id | bigint |
browser_id | bigint |
cpu_id | bigint |
xsystem_cfg_dim_id | bigint |
xreferrer_dim_id | bigint |
xaffiliate_dim_id | bigint |
xsite_dim_id | bigint |
xpublisher_dim_id | bigint |
season_id | bigint |
show_id | bigint |
xsegment_dim_id | bigint |
location_id | bigint |
zipcode | character varying(20) |
xlocation_dim_id | bigint |
location_srcid | bigint |
timezone | real |
xtime_dim_id | bigint |
Indexes:
"bigtab_stats_fact_tmp14_idx1" btree (customer_id)
"bigtab_stats_fact_tmp14_idx2" btree (show_id)
"bigtab_stats_fact_tmp14_idx3" btree (season_id)
"bigtab_stats_fact_tmp14_idx4" btree (episode_id)
# \d xsegment_dim
Table "public.xsegment_dim"
Column | Type |
Modifiers
----------------------+-----------------------------
+-------------------------------------------------------------
xsegment_dim_id | bigint | not null default
nextval('xsegment_dim_seq'::regclass)
customer_srcid | bigint | not null
show_srcid | bigint | not null
show_name | character varying(500) | not null
season_srcid | bigint | not null
season_name | character varying(500) | not null
episode_srcid | bigint | not null
episode_name | character varying(500) | not null
segment_type_id | integer |
segment_type | character varying(500) |
segment_srcid | bigint |
segment_name | character varying(500) |
effective_dt | timestamp without time zone | not null default
now()
inactive_dt | timestamp without time zone |
last_update_dt | timestamp without time zone | not null default
now()
Indexes:
"xsegment_dim_pk" PRIMARY KEY, btree (xsegment_dim_id)
"seg1" btree (customer_srcid)
"seg2" btree (show_srcid)
"seg3" btree (season_srcid)
"seg4" btree (episode_srcid)
"seg5" btree (segment_srcid)
"xsegment_dim_ix1" btree (customer_srcid)
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Join runs for > 10 hours and then fills up >1.3TB of disk space
@ 2008-05-16 06:58 kevin kempter <kevin@kevinkempterllc.com>
parent: kevin kempter <kevin@kevinkempterllc.com>
4 siblings, 1 reply; 11+ messages in thread
From: kevin kempter @ 2008-05-16 06:58 UTC (permalink / raw)
To: kevin kempter <kevin@kevinkempterllc.com>; +Cc: pgsql-performance
Sorry I goofed on the query text Here's the correct query:
select
f14.xpublisher_dim_id,
f14.xtime_dim_id,
f14.xlocation_dim_id,
f14.xreferrer_dim_id,
f14.xsite_dim_id,
f14.xsystem_cfg_dim_id,
f14.xaffiliate_dim_id,
f14.customer_id,
f14.pf_dts_id,
f14.episode_id,
f14.sessionid,
f14.bytes_received,
f14.bytes_transmitted,
f14.total_played_time_sec,
segdim.xsegment_dim_id as episode_level_segid
from
bigtab_stats_fact_tmp14 f14,
xsegment_dim segdim
where
f14.customer_id = segdim.customer_srcid
and f14.show_id = segdim.show_srcid
and f14.season_id = segdim.season_srcid
and f14.episode_id = segdim.episode_srcid
and segdim.segment_srcid is NULL;
On May 16, 2008, at 12:31 AM, kevin kempter wrote:
> Hi List;
>
> I have a table with 9,961,914 rows in it (see the describe of
> bigtab_stats_fact_tmp14 below)
>
> I also have a table with 7,785 rows in it (see the describe of
> xsegment_dim below)
>
> I'm running the join shown below and it takes > 10 hours and
> eventually runs out of disk space on a 1.4TB file system
>
> I've included below a describe of both tables, the join and an
> explain plan, any help / suggestions would be much appreciated !
>
> I need to get this beast to run as quickly as possible (without
> filling up my file system)
>
>
> Thanks in advance...
>
>
>
>
>
>
>
>
>
>
>
> select
> f14.xpublisher_dim_id,
> f14.xtime_dim_id,
> f14.xlocation_dim_id,
> f14.xreferrer_dim_id,
> f14.xsite_dim_id,
> f14.xsystem_cfg_dim_id,
> f14.xaffiliate_dim_id,
> f14.customer_id,
> pf_dts_id,
> episode_id,
> sessionid,
> bytes_received,
> bytes_transmitted,
> total_played_time_sec,
> segdim.xsegment_dim_id as episode_level_segid
> from
> bigtab_stats_fact_tmp14 f14,
> xsegment_dim segdim
> where
> f14.customer_id = segdim.customer_srcid
> and f14.show_id = segdim.show_srcid
> and f14.season_id = segdim.season_srcid
> and f14.episode_id = segdim.episode_srcid
> and segdim.segment_srcid is NULL;
>
>
>
>
>
>
> QUERY PLAN
> ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> Merge Join (cost=1757001.74..73569676.49 rows=3191677219 width=118)
> Merge Cond: ((segdim.episode_srcid = f14.episode_id) AND
> (segdim.customer_srcid = f14.customer_id) AND (segdim.show_srcid =
> f14.show_id) AND (segdim.season_srcid = f14.season_id))
> -> Sort (cost=1570.35..1579.46 rows=3643 width=40)
> Sort Key: segdim.episode_srcid, segdim.customer_srcid,
> segdim.show_srcid, segdim.season_srcid
> -> Seq Scan on xsegment_dim segdim (cost=0.00..1354.85 rows=3643
> width=40)
> Filter: (segment_srcid IS NULL)
> -> Sort (cost=1755323.26..1780227.95 rows=9961874 width=126)
> Sort Key: f14.episode_id, f14.customer_id, f14.show_id, f14.season_id
> -> Seq Scan on bigtab_stats_fact_tmp14 f14 (cost=0.00..597355.74
> rows=9961874 width=126)
> (9 rows)
>
>
>
>
>
>
>
>
>
> # \d bigtab_stats_fact_tmp14
> Table "public.bigtab_stats_fact_tmp14"
> Column | Type | Modifiers
> --------------------------+-----------------------------+-----------
> pf_dts_id | bigint |
> pf_device_id | bigint |
> segment_id | bigint |
> cdn_id | bigint |
> collector_id | bigint |
> digital_envoy_id | bigint |
> maxmind_id | bigint |
> quova_id | bigint |
> website_id | bigint |
> referrer_id | bigint |
> affiliate_id | bigint |
> custom_info_id | bigint |
> start_dt | timestamp without time zone |
> total_played_time_sec | numeric(18,5) |
> bytes_received | bigint |
> bytes_transmitted | bigint |
> stall_count | integer |
> stall_duration_sec | numeric(18,5) |
> hiccup_count | integer |
> hiccup_duration_sec | numeric(18,5) |
> watched_duration_sec | numeric(18,5) |
> rewatched_duration_sec | numeric(18,5) |
> requested_start_position | numeric(18,5) |
> requested_stop_position | numeric(18,5) |
> post_position | numeric(18,5) |
> is_vod | numeric(1,0) |
> sessionid | bigint |
> create_dt | timestamp without time zone |
> segment_type_id | bigint |
> customer_id | bigint |
> content_publisher_id | bigint |
> content_owner_id | bigint |
> episode_id | bigint |
> duration_sec | numeric(18,5) |
> device_id | bigint |
> os_id | bigint |
> browser_id | bigint |
> cpu_id | bigint |
> xsystem_cfg_dim_id | bigint |
> xreferrer_dim_id | bigint |
> xaffiliate_dim_id | bigint |
> xsite_dim_id | bigint |
> xpublisher_dim_id | bigint |
> season_id | bigint |
> show_id | bigint |
> xsegment_dim_id | bigint |
> location_id | bigint |
> zipcode | character varying(20) |
> xlocation_dim_id | bigint |
> location_srcid | bigint |
> timezone | real |
> xtime_dim_id | bigint |
> Indexes:
> "bigtab_stats_fact_tmp14_idx1" btree (customer_id)
> "bigtab_stats_fact_tmp14_idx2" btree (show_id)
> "bigtab_stats_fact_tmp14_idx3" btree (season_id)
> "bigtab_stats_fact_tmp14_idx4" btree (episode_id)
>
>
>
>
>
>
> # \d xsegment_dim
> Table "public.xsegment_dim"
> Column | Type
> | Modifiers
> ----------------------+-----------------------------
> +-------------------------------------------------------------
> xsegment_dim_id | bigint | not null default
> nextval('xsegment_dim_seq'::regclass)
> customer_srcid | bigint | not null
> show_srcid | bigint | not null
> show_name | character varying(500) | not null
> season_srcid | bigint | not null
> season_name | character varying(500) | not null
> episode_srcid | bigint | not null
> episode_name | character varying(500) | not null
> segment_type_id | integer |
> segment_type | character varying(500) |
> segment_srcid | bigint |
> segment_name | character varying(500) |
> effective_dt | timestamp without time zone | not null
> default now()
> inactive_dt | timestamp without time zone |
> last_update_dt | timestamp without time zone | not null
> default now()
> Indexes:
> "xsegment_dim_pk" PRIMARY KEY, btree (xsegment_dim_id)
> "seg1" btree (customer_srcid)
> "seg2" btree (show_srcid)
> "seg3" btree (season_srcid)
> "seg4" btree (episode_srcid)
> "seg5" btree (segment_srcid)
> "xsegment_dim_ix1" btree (customer_srcid)
>
>
>
>
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org
> )
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Join runs for > 10 hours and then fills up >1.3TB of disk space
@ 2008-05-16 07:08 kevin kempter <kevin@kevinkempterllc.com>
parent: kevin kempter <kevin@kevinkempterllc.com>
0 siblings, 0 replies; 11+ messages in thread
From: kevin kempter @ 2008-05-16 07:08 UTC (permalink / raw)
To: kevin kempter <kevin@kevinkempterllc.com>; +Cc: pgsql-performance
Also, I'm running version 8.3 on a centOS box with 2 dual core CPU's
and 32Gig of ram
On May 16, 2008, at 12:58 AM, kevin kempter wrote:
> Sorry I goofed on the query text Here's the correct query:
>
> select
> f14.xpublisher_dim_id,
> f14.xtime_dim_id,
> f14.xlocation_dim_id,
> f14.xreferrer_dim_id,
> f14.xsite_dim_id,
> f14.xsystem_cfg_dim_id,
> f14.xaffiliate_dim_id,
> f14.customer_id,
> f14.pf_dts_id,
> f14.episode_id,
> f14.sessionid,
> f14.bytes_received,
> f14.bytes_transmitted,
> f14.total_played_time_sec,
> segdim.xsegment_dim_id as episode_level_segid
> from
> bigtab_stats_fact_tmp14 f14,
> xsegment_dim segdim
> where
> f14.customer_id = segdim.customer_srcid
> and f14.show_id = segdim.show_srcid
> and f14.season_id = segdim.season_srcid
> and f14.episode_id = segdim.episode_srcid
> and segdim.segment_srcid is NULL;
>
>
>
>
>
>
> On May 16, 2008, at 12:31 AM, kevin kempter wrote:
>
>> Hi List;
>>
>> I have a table with 9,961,914 rows in it (see the describe of
>> bigtab_stats_fact_tmp14 below)
>>
>> I also have a table with 7,785 rows in it (see the describe of
>> xsegment_dim below)
>>
>> I'm running the join shown below and it takes > 10 hours and
>> eventually runs out of disk space on a 1.4TB file system
>>
>> I've included below a describe of both tables, the join and an
>> explain plan, any help / suggestions would be much appreciated !
>>
>> I need to get this beast to run as quickly as possible (without
>> filling up my file system)
>>
>>
>> Thanks in advance...
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>> select
>> f14.xpublisher_dim_id,
>> f14.xtime_dim_id,
>> f14.xlocation_dim_id,
>> f14.xreferrer_dim_id,
>> f14.xsite_dim_id,
>> f14.xsystem_cfg_dim_id,
>> f14.xaffiliate_dim_id,
>> f14.customer_id,
>> pf_dts_id,
>> episode_id,
>> sessionid,
>> bytes_received,
>> bytes_transmitted,
>> total_played_time_sec,
>> segdim.xsegment_dim_id as episode_level_segid
>> from
>> bigtab_stats_fact_tmp14 f14,
>> xsegment_dim segdim
>> where
>> f14.customer_id = segdim.customer_srcid
>> and f14.show_id = segdim.show_srcid
>> and f14.season_id = segdim.season_srcid
>> and f14.episode_id = segdim.episode_srcid
>> and segdim.segment_srcid is NULL;
>>
>>
>>
>>
>>
>>
>> QUERY PLAN
>> ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>> Merge Join (cost=1757001.74..73569676.49 rows=3191677219 width=118)
>> Merge Cond: ((segdim.episode_srcid = f14.episode_id) AND
>> (segdim.customer_srcid = f14.customer_id) AND (segdim.show_srcid =
>> f14.show_id) AND (segdim.season_srcid = f14.season_id))
>> -> Sort (cost=1570.35..1579.46 rows=3643 width=40)
>> Sort Key: segdim.episode_srcid, segdim.customer_srcid,
>> segdim.show_srcid, segdim.season_srcid
>> -> Seq Scan on xsegment_dim segdim (cost=0.00..1354.85 rows=3643
>> width=40)
>> Filter: (segment_srcid IS NULL)
>> -> Sort (cost=1755323.26..1780227.95 rows=9961874 width=126)
>> Sort Key: f14.episode_id, f14.customer_id, f14.show_id, f14.season_id
>> -> Seq Scan on bigtab_stats_fact_tmp14 f14 (cost=0.00..597355.74
>> rows=9961874 width=126)
>> (9 rows)
>>
>>
>>
>>
>>
>>
>>
>>
>>
>> # \d bigtab_stats_fact_tmp14
>> Table "public.bigtab_stats_fact_tmp14"
>> Column | Type | Modifiers
>> --------------------------+-----------------------------+-----------
>> pf_dts_id | bigint |
>> pf_device_id | bigint |
>> segment_id | bigint |
>> cdn_id | bigint |
>> collector_id | bigint |
>> digital_envoy_id | bigint |
>> maxmind_id | bigint |
>> quova_id | bigint |
>> website_id | bigint |
>> referrer_id | bigint |
>> affiliate_id | bigint |
>> custom_info_id | bigint |
>> start_dt | timestamp without time zone |
>> total_played_time_sec | numeric(18,5) |
>> bytes_received | bigint |
>> bytes_transmitted | bigint |
>> stall_count | integer |
>> stall_duration_sec | numeric(18,5) |
>> hiccup_count | integer |
>> hiccup_duration_sec | numeric(18,5) |
>> watched_duration_sec | numeric(18,5) |
>> rewatched_duration_sec | numeric(18,5) |
>> requested_start_position | numeric(18,5) |
>> requested_stop_position | numeric(18,5) |
>> post_position | numeric(18,5) |
>> is_vod | numeric(1,0) |
>> sessionid | bigint |
>> create_dt | timestamp without time zone |
>> segment_type_id | bigint |
>> customer_id | bigint |
>> content_publisher_id | bigint |
>> content_owner_id | bigint |
>> episode_id | bigint |
>> duration_sec | numeric(18,5) |
>> device_id | bigint |
>> os_id | bigint |
>> browser_id | bigint |
>> cpu_id | bigint |
>> xsystem_cfg_dim_id | bigint |
>> xreferrer_dim_id | bigint |
>> xaffiliate_dim_id | bigint |
>> xsite_dim_id | bigint |
>> xpublisher_dim_id | bigint |
>> season_id | bigint |
>> show_id | bigint |
>> xsegment_dim_id | bigint |
>> location_id | bigint |
>> zipcode | character varying(20) |
>> xlocation_dim_id | bigint |
>> location_srcid | bigint |
>> timezone | real |
>> xtime_dim_id | bigint |
>> Indexes:
>> "bigtab_stats_fact_tmp14_idx1" btree (customer_id)
>> "bigtab_stats_fact_tmp14_idx2" btree (show_id)
>> "bigtab_stats_fact_tmp14_idx3" btree (season_id)
>> "bigtab_stats_fact_tmp14_idx4" btree (episode_id)
>>
>>
>>
>>
>>
>>
>> # \d xsegment_dim
>> Table "public.xsegment_dim"
>> Column | Type
>> | Modifiers
>> ----------------------+-----------------------------
>> +-------------------------------------------------------------
>> xsegment_dim_id | bigint | not null default
>> nextval('xsegment_dim_seq'::regclass)
>> customer_srcid | bigint | not null
>> show_srcid | bigint | not null
>> show_name | character varying(500) | not null
>> season_srcid | bigint | not null
>> season_name | character varying(500) | not null
>> episode_srcid | bigint | not null
>> episode_name | character varying(500) | not null
>> segment_type_id | integer |
>> segment_type | character varying(500) |
>> segment_srcid | bigint |
>> segment_name | character varying(500) |
>> effective_dt | timestamp without time zone | not null
>> default now()
>> inactive_dt | timestamp without time zone |
>> last_update_dt | timestamp without time zone | not null
>> default now()
>> Indexes:
>> "xsegment_dim_pk" PRIMARY KEY, btree (xsegment_dim_id)
>> "seg1" btree (customer_srcid)
>> "seg2" btree (show_srcid)
>> "seg3" btree (season_srcid)
>> "seg4" btree (episode_srcid)
>> "seg5" btree (segment_srcid)
>> "xsegment_dim_ix1" btree (customer_srcid)
>>
>>
>>
>>
>>
>>
>> --
>> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org
>> )
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-performance
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Join runs for > 10 hours and then fills up >1.3TB of disk space
@ 2008-05-16 07:15 Claus Guttesen <kometen@gmail.com>
parent: kevin kempter <kevin@kevinkempterllc.com>
4 siblings, 0 replies; 11+ messages in thread
From: Claus Guttesen @ 2008-05-16 07:15 UTC (permalink / raw)
To: kevin kempter <kevin@kevinkempterllc.com>; +Cc: pgsql-performance
> I have a table with 9,961,914 rows in it (see the describe of
> bigtab_stats_fact_tmp14 below)
>
> I also have a table with 7,785 rows in it (see the describe of xsegment_dim
> below)
>
> I'm running the join shown below and it takes > 10 hours and eventually runs
> out of disk space on a 1.4TB file system
>
> I've included below a describe of both tables, the join and an explain plan,
> any help / suggestions would be much appreciated !
>
> I need to get this beast to run as quickly as possible (without filling up
> my file system)
>
>
> Thanks in advance...
What version of postgresql are you using? According to
http://www.postgresql.org/docs/8.2/static/runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-MEMOR...
you may benefit from adjusting work_mem.
You also index segment_srcid (in table xsegment_dim) but if you search
for NULL and you have enough of those it defaults to a seq. scan:
Seq Scan on xsegment_dim segdim (cost=0.00..1354.85 rows=3643 width=40)
> Filter: (segment_srcid IS NULL)
Maby you could insert some default value into segment_srcid (some
arbitrary large numbers) instead of NULL and then search for values
greater than??
You could also try to lower random_page_cost from default to 2.
> select
> f14.xpublisher_dim_id,
> f14.xtime_dim_id,
> f14.xlocation_dim_id,
> f14.xreferrer_dim_id,
> f14.xsite_dim_id,
> f14.xsystem_cfg_dim_id,
> f14.xaffiliate_dim_id,
> f14.customer_id,
> pf_dts_id,
> episode_id,
> sessionid,
> bytes_received,
> bytes_transmitted,
> total_played_time_sec,
> segdim.xsegment_dim_id as episode_level_segid
> from
> bigtab_stats_fact_tmp14 f14,
> xsegment_dim segdim
> where
> f14.customer_id = segdim.customer_srcid
> and f14.show_id = segdim.show_srcid
> and f14.season_id = segdim.season_srcid
> and f14.episode_id = segdim.episode_srcid
> and segdim.segment_srcid is NULL;
>
>
>
>
>
>
> QUERY PLAN
> ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> Merge Join (cost=1757001.74..73569676.49 rows=3191677219 width=118)
> Merge Cond: ((segdim.episode_srcid = f14.episode_id) AND
> (segdim.customer_srcid = f14.customer_id) AND (segdim.show_srcid =
> f14.show_id) AND (segdim.season_srcid = f14.season_id))
> -> Sort (cost=1570.35..1579.46 rows=3643 width=40)
> Sort Key: segdim.episode_srcid, segdim.customer_srcid, segdim.show_srcid,
> segdim.season_srcid
> -> Seq Scan on xsegment_dim segdim (cost=0.00..1354.85 rows=3643 width=40)
> Filter: (segment_srcid IS NULL)
> -> Sort (cost=1755323.26..1780227.95 rows=9961874 width=126)
> Sort Key: f14.episode_id, f14.customer_id, f14.show_id, f14.season_id
> -> Seq Scan on bigtab_stats_fact_tmp14 f14 (cost=0.00..597355.74
> rows=9961874 width=126)
> (9 rows)
>
>
>
>
>
>
>
>
>
> # \d bigtab_stats_fact_tmp14
> Table "public.bigtab_stats_fact_tmp14"
> Column | Type | Modifiers
> --------------------------+-----------------------------+-----------
> pf_dts_id | bigint |
> pf_device_id | bigint |
> segment_id | bigint |
> cdn_id | bigint |
> collector_id | bigint |
> digital_envoy_id | bigint |
> maxmind_id | bigint |
> quova_id | bigint |
> website_id | bigint |
> referrer_id | bigint |
> affiliate_id | bigint |
> custom_info_id | bigint |
> start_dt | timestamp without time zone |
> total_played_time_sec | numeric(18,5) |
> bytes_received | bigint |
> bytes_transmitted | bigint |
> stall_count | integer |
> stall_duration_sec | numeric(18,5) |
> hiccup_count | integer |
> hiccup_duration_sec | numeric(18,5) |
> watched_duration_sec | numeric(18,5) |
> rewatched_duration_sec | numeric(18,5) |
> requested_start_position | numeric(18,5) |
> requested_stop_position | numeric(18,5) |
> post_position | numeric(18,5) |
> is_vod | numeric(1,0) |
> sessionid | bigint |
> create_dt | timestamp without time zone |
> segment_type_id | bigint |
> customer_id | bigint |
> content_publisher_id | bigint |
> content_owner_id | bigint |
> episode_id | bigint |
> duration_sec | numeric(18,5) |
> device_id | bigint |
> os_id | bigint |
> browser_id | bigint |
> cpu_id | bigint |
> xsystem_cfg_dim_id | bigint |
> xreferrer_dim_id | bigint |
> xaffiliate_dim_id | bigint |
> xsite_dim_id | bigint |
> xpublisher_dim_id | bigint |
> season_id | bigint |
> show_id | bigint |
> xsegment_dim_id | bigint |
> location_id | bigint |
> zipcode | character varying(20) |
> xlocation_dim_id | bigint |
> location_srcid | bigint |
> timezone | real |
> xtime_dim_id | bigint |
> Indexes:
> "bigtab_stats_fact_tmp14_idx1" btree (customer_id)
> "bigtab_stats_fact_tmp14_idx2" btree (show_id)
> "bigtab_stats_fact_tmp14_idx3" btree (season_id)
> "bigtab_stats_fact_tmp14_idx4" btree (episode_id)
>
>
>
>
>
>
> # \d xsegment_dim
> Table "public.xsegment_dim"
> Column | Type |
> Modifiers
> ----------------------+-----------------------------+-------------------------------------------------------------
> xsegment_dim_id | bigint | not null default
> nextval('xsegment_dim_seq'::regclass)
> customer_srcid | bigint | not null
> show_srcid | bigint | not null
> show_name | character varying(500) | not null
> season_srcid | bigint | not null
> season_name | character varying(500) | not null
> episode_srcid | bigint | not null
> episode_name | character varying(500) | not null
> segment_type_id | integer |
> segment_type | character varying(500) |
> segment_srcid | bigint |
> segment_name | character varying(500) |
> effective_dt | timestamp without time zone | not null default now()
> inactive_dt | timestamp without time zone |
> last_update_dt | timestamp without time zone | not null default now()
> Indexes:
> "xsegment_dim_pk" PRIMARY KEY, btree (xsegment_dim_id)
> "seg1" btree (customer_srcid)
> "seg2" btree (show_srcid)
> "seg3" btree (season_srcid)
> "seg4" btree (episode_srcid)
> "seg5" btree (segment_srcid)
> "xsegment_dim_ix1" btree (customer_srcid)
>
>
>
>
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
--
regards
Claus
When lenity and cruelty play for a kingdom,
the gentlest gamester is the soonest winner.
Shakespeare
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Join runs for > 10 hours and then fills up >1.3TB of disk space
@ 2008-05-16 07:38 Simon Riggs <simon@2ndquadrant.com>
parent: kevin kempter <kevin@kevinkempterllc.com>
4 siblings, 1 reply; 11+ messages in thread
From: Simon Riggs @ 2008-05-16 07:38 UTC (permalink / raw)
To: kevin kempter <kevin@kevinkempterllc.com>; +Cc: pgsql-performance
On Fri, 2008-05-16 at 00:31 -0600, kevin kempter wrote:
> I'm running the join shown below and it takes > 10 hours and
> eventually runs out of disk space on a 1.4TB file system
Well, running in 10 hours doesn't mean there's a software problem, nor
does running out of disk space.
Please crunch some numbers before you ask, such as how much disk space
was used by the query, how big you'd expect it to be etc, plus provide
information such as what the primary key of the large table is and what
is your release level is etc..
Are you sure you want to retrieve an estimated 3 billion rows? Can you
cope if that estimate is wrong and the true figure is much higher? Do
you think the estimate is realistic?
--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Join runs for > 10 hours and then fills up >1.3TB of disk space
@ 2008-05-16 07:40 Richard Huxton <dev@archonet.com>
parent: kevin kempter <kevin@kevinkempterllc.com>
4 siblings, 1 reply; 11+ messages in thread
From: Richard Huxton @ 2008-05-16 07:40 UTC (permalink / raw)
To: kevin kempter <kevin@kevinkempterllc.com>; +Cc: pgsql-performance
kevin kempter wrote:
> Hi List;
>
> I have a table with 9,961,914 rows in it (see the describe of
> bigtab_stats_fact_tmp14 below)
>
> I also have a table with 7,785 rows in it (see the describe of
> xsegment_dim below)
>
> I'm running the join shown below and it takes > 10 hours and eventually
> runs out of disk space on a 1.4TB file system
> QUERY PLAN
> ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
> Merge Join (cost=1757001.74..73569676.49 rows=3191677219 width=118)
Dumb question Kevin, but are you really expecting 3.2 billion rows in
the result-set? Because that's approaching 400GB of result-set without
any overheads.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Join runs for > 10 hours and then fills up >1.3TB of disk space
@ 2008-05-16 08:00 kevin kempter <kevin@kevinkempterllc.com>
parent: Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 11+ messages in thread
From: kevin kempter @ 2008-05-16 08:00 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: pgsql-performance
I'm expecting 9,961,914 rows returned. Each row in the big table
should have a corresponding key in the smaller tale, I want to
basically "expand" the big table column list by one, via adding the
appropriate key from the smaller table for each row in the big table.
It's not a cartesion product join.
On May 16, 2008, at 1:40 AM, Richard Huxton wrote:
> kevin kempter wrote:
>> Hi List;
>> I have a table with 9,961,914 rows in it (see the describe of
>> bigtab_stats_fact_tmp14 below)
>> I also have a table with 7,785 rows in it (see the describe of
>> xsegment_dim below)
>> I'm running the join shown below and it takes > 10 hours and
>> eventually runs out of disk space on a 1.4TB file system
>
>> QUERY PLAN
>> --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Merge
>> Join (cost=1757001.74..73569676.49 rows=3191677219 width=118)
>
> Dumb question Kevin, but are you really expecting 3.2 billion rows
> in the result-set? Because that's approaching 400GB of result-set
> without any overheads.
>
> --
> Richard Huxton
> Archonet Ltd
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Join runs for > 10 hours and then fills up >1.3TB of disk space
@ 2008-05-16 08:16 Richard Huxton <dev@archonet.com>
parent: kevin kempter <kevin@kevinkempterllc.com>
0 siblings, 0 replies; 11+ messages in thread
From: Richard Huxton @ 2008-05-16 08:16 UTC (permalink / raw)
To: kevin kempter <kevin@kevinkempterllc.com>; +Cc: pgsql-performance
kevin kempter wrote:
> I'm expecting 9,961,914 rows returned. Each row in the big table should
> have a corresponding key in the smaller tale, I want to basically
> "expand" the big table column list by one, via adding the appropriate
> key from the smaller table for each row in the big table. It's not a
> cartesion product join.
Didn't seem likely, to be honest.
What happens if you try the query as a cursor, perhaps with an order-by
on customer_id or something to encourage index use? Do you ever get a
first row back?
In fact, what happens if you slap an index over all your join columns on
xsegment_dim? With 7,000 rows that should make it a cheap test.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Join runs for > 10 hours and then fills up >1.3TB of disk space
@ 2008-05-16 08:18 Richard Huxton <dev@archonet.com>
parent: kevin kempter <kevin@kevinkempterllc.com>
4 siblings, 0 replies; 11+ messages in thread
From: Richard Huxton @ 2008-05-16 08:18 UTC (permalink / raw)
To: kevin kempter <kevin@kevinkempterllc.com>; +Cc: pgsql-performance
kevin kempter wrote:
> Hi List;
>
> I have a table with 9,961,914 rows in it (see the describe of
> bigtab_stats_fact_tmp14 below)
>
> I also have a table with 7,785 rows in it (see the describe of
> xsegment_dim below)
Something else is puzzling me with this - you're joining over four fields.
> from
> bigtab_stats_fact_tmp14 f14,
> xsegment_dim segdim
> where
> f14.customer_id = segdim.customer_srcid
> and f14.show_id = segdim.show_srcid
> and f14.season_id = segdim.season_srcid
> and f14.episode_id = segdim.episode_srcid
> and segdim.segment_srcid is NULL;
> ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
> Merge Join (cost=1757001.74..73569676.49 rows=3191677219 width=118)
> -> Sort (cost=1570.35..1579.46 rows=3643 width=40)
> -> Sort (cost=1755323.26..1780227.95 rows=9961874 width=126)
Here it's still expecting 320 matches against each row from the large
table. That's ~ 10% of the small table (or that fraction of it that PG
expects) which seems very high for four clauses ANDed together.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Join runs for > 10 hours and then fills up >1.3TB of disk space
@ 2008-05-16 08:36 Luke Lonergan <LLonergan@greenplum.com>
0 siblings, 0 replies; 11+ messages in thread
From: Luke Lonergan @ 2008-05-16 08:36 UTC (permalink / raw)
To: kevin@kevinkempterllc.com; dev@archonet.com; +Cc: pgsql-performance
Try 'set enable-mergejoin=false' and see if you get a hashjoin.
- Luke
----- Original Message -----
From: pgsql-performance-owner@postgresql.org <pgsql-performance-owner@postgresql.org>
To: Richard Huxton <dev@archonet.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Sent: Fri May 16 04:00:41 2008
Subject: Re: [PERFORM] Join runs for > 10 hours and then fills up >1.3TB of disk space
I'm expecting 9,961,914 rows returned. Each row in the big table
should have a corresponding key in the smaller tale, I want to
basically "expand" the big table column list by one, via adding the
appropriate key from the smaller table for each row in the big table.
It's not a cartesion product join.
On May 16, 2008, at 1:40 AM, Richard Huxton wrote:
> kevin kempter wrote:
>> Hi List;
>> I have a table with 9,961,914 rows in it (see the describe of
>> bigtab_stats_fact_tmp14 below)
>> I also have a table with 7,785 rows in it (see the describe of
>> xsegment_dim below)
>> I'm running the join shown below and it takes > 10 hours and
>> eventually runs out of disk space on a 1.4TB file system
>
>> QUERY PLAN
>> --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Merge
>> Join (cost=1757001.74..73569676.49 rows=3191677219 width=118)
>
> Dumb question Kevin, but are you really expecting 3.2 billion rows
> in the result-set? Because that's approaching 400GB of result-set
> without any overheads.
>
> --
> Richard Huxton
> Archonet Ltd
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Join runs for > 10 hours and then fills up >1.3TB of disk space
@ 2008-05-16 08:38 kevin kempter <kevin@kevinkempterllc.com>
parent: Simon Riggs <simon@2ndquadrant.com>
0 siblings, 0 replies; 11+ messages in thread
From: kevin kempter @ 2008-05-16 08:38 UTC (permalink / raw)
To: Simon Riggs <simon@2ndquadrant.com>; +Cc: pgsql-performance
On further investigation it turns out that I/we have a serious data
issue in that my small table is full of 'UNKNOWN' tags so my query
cannot associate the data correctly - thus I will end up with 2+
billion rows.
Thanks everyone for your help
On May 16, 2008, at 1:38 AM, Simon Riggs wrote:
>
> On Fri, 2008-05-16 at 00:31 -0600, kevin kempter wrote:
>
>> I'm running the join shown below and it takes > 10 hours and
>> eventually runs out of disk space on a 1.4TB file system
>
> Well, running in 10 hours doesn't mean there's a software problem, nor
> does running out of disk space.
>
> Please crunch some numbers before you ask, such as how much disk space
> was used by the query, how big you'd expect it to be etc, plus provide
> information such as what the primary key of the large table is and
> what
> is your release level is etc..
>
> Are you sure you want to retrieve an estimated 3 billion rows? Can you
> cope if that estimate is wrong and the true figure is much higher? Do
> you think the estimate is realistic?
>
> --
> Simon Riggs www.2ndQuadrant.com
> PostgreSQL Training, Services and Support
>
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org
> )
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2008-05-16 08:38 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-05-16 06:31 Join runs for > 10 hours and then fills up >1.3TB of disk space kevin kempter <kevin@kevinkempterllc.com>
2008-05-16 06:58 ` kevin kempter <kevin@kevinkempterllc.com>
2008-05-16 07:08 ` kevin kempter <kevin@kevinkempterllc.com>
2008-05-16 07:15 ` Claus Guttesen <kometen@gmail.com>
2008-05-16 07:38 ` Simon Riggs <simon@2ndquadrant.com>
2008-05-16 08:38 ` kevin kempter <kevin@kevinkempterllc.com>
2008-05-16 07:40 ` Richard Huxton <dev@archonet.com>
2008-05-16 08:00 ` kevin kempter <kevin@kevinkempterllc.com>
2008-05-16 08:16 ` Richard Huxton <dev@archonet.com>
2008-05-16 08:18 ` Richard Huxton <dev@archonet.com>
2008-05-16 08:36 Re: Join runs for > 10 hours and then fills up >1.3TB of disk space Luke Lonergan <LLonergan@greenplum.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