Re: Join runs for > 10 hours and then fills up >1.3TB of disk space

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(at)postgresql(dot)org
>> )
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-performance
>