Skip site navigation (1) Skip section navigation (2)

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

From: Richard Huxton <dev(at)archonet(dot)com>
To: kevin kempter <kevin(at)kevinkempterllc(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Join runs for > 10 hours and then fills up >1.3TB of disk space
Date: 2008-05-16 08:18:12
Message-ID: 482D4344.2060607@archonet.com (view raw or flat)
Thread:
Lists: 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

In response to

pgsql-performance by date

Next:From: Luke LonerganDate: 2008-05-16 08:36:11
Subject: Re: Join runs for > 10 hours and then fills up >1.3TB of disk space
Previous:From: Richard HuxtonDate: 2008-05-16 08:16:19
Subject: Re: Join runs for > 10 hours and then fills up >1.3TB of disk space

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group