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

Re: Strange left outer join performance issue

From: "Noah M(dot) Daniels" <ndaniels(at)mac(dot)com>
To: Daniel Cristian Cruz <danielcristian(at)gmail(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Strange left outer join performance issue
Date: 2007-03-23 21:16:48
Message-ID: 84DE2EAB-8235-402A-9097-1F3D8219D3FD@mac.com (view raw or flat)
Thread:
Lists: pgsql-performance
Not much of a difference, unfortunately... I still wonder why it's  
doing the 'supplier' (slow) query using the merge right join.

the 'fast' query:

Nested Loop Left Join  (cost=0.00..423342.71 rows=2481 width=410)  
(actual time=100.076..6380.865 rows=1355 loops=1)
    ->  Index Scan using index_aliases_company_type_company_id on  
aliases a  (cost=0.00..462.33 rows=118 width=46) (actual  
time=24.811..143.690 rows=388 loops=1)
          Index Cond: ((company_type)::text = 'Buyer'::text)
          Filter: (company_id IS NULL)
    ->  Index Scan using index_customs_records_on_buyer_alias_id on  
customs_records cr  (cost=0.00..3572.61 rows=890 width=368) (actual  
time=5.526..16.042 rows=3 loops=388)
          Index Cond: (cr.buyer_alias_id = "outer".id)
Total runtime: 6382.940 ms
(7 rows)

the 'slow' one:

Merge Right Join  (cost=842.53..479378.17 rows=2281 width=410)  
(actual time=554713.506..555584.825 rows=39 loops=1)
    Merge Cond: ("outer".supplier_alias_id = "inner".id)
    ->  Index Scan using index_customs_records_on_supplier_alias_id  
on customs_records cr  (cost=0.00..6673133.76 rows=1704859 width=368)  
(actual time=42.327..555225.588 rows=117424 loops=1)
    ->  Sort  (cost=842.53..843.07 rows=218 width=46) (actual  
time=0.109..0.164 rows=39 loops=1)
          Sort Key: a.id
          ->  Index Scan using index_aliases_company_type_company_id  
on aliases a  (cost=0.00..834.06 rows=218 width=46) (actual  
time=0.033..0.074 rows=10 loops=1)
                Index Cond: ((company_type)::text = 'Supplier'::text)
                Filter: (company_id IS NULL)
Total runtime: 555584.978 ms
(9 rows)


On Mar 23, 2007, at 4:04 PM, Daniel Cristian Cruz wrote:

> Run VACUUM ANALYZE and see if the cost estimates became close to  
> the effective rows. This could make it faster.
>
> 2007/3/23, Noah M. Daniels <ndaniels(at)mac(dot)com>:
> > SLOW:
> > Merge Right Join  (cost=1138.78..460482.84 rows=2993 width=405)
> > (actual time=1244745.427..1245714.571 rows=39 loops=1)
> >    Merge Cond: ("outer".supplier_alias_id = "inner".id)
> >    ->  Index Scan using  
> index_customs_records_on_supplier_alias_id on
> > customs_records cr  (cost=0.00..6717806.37 rows=1704859 width=363)
> > (actual time=54.567..1245210.707 rows=117424 loops=1)
> >    ->  Sort  (cost=1138.78..1139.53 rows=300 width=46) (actual
> > time=24.093..24.161 rows=39 loops=1)
> >          Sort Key: a.id
> >          ->  Index Scan using index_aliases_company_type_company_id
> > on aliases a  (cost=0.00..1126.44 rows=300 width=46) (actual
> > time=22.400..23.959 rows=10 loops=1)
> >                Index Cond: ((company_type)::text = 'Supplier'::text)
> >                Filter: (company_id IS NULL)
> > Total runtime: 1245714.752 ms
> >
> > FAST:
> >
> > Nested Loop Left Join  (cost=0.00..603052.46 rows=3244 width=405)
> > (actual time=68.526..3115.407 rows=1355 loops=1)
> >     ->  Index Scan using index_aliases_company_type_company_id on
> > aliases a  (cost= 0.00..639.56 rows=165 width=46) (actual
> > time=32.419..132.286 rows=388 loops=1)
> >           Index Cond: ((company_type)::text = 'Buyer'::text)
> >           Filter: (company_id IS NULL)
> >     ->  Index Scan using index_customs_records_on_buyer_alias_id on
> > customs_records cr  (cost=0.00..3639.55 rows=915 width=363) (actual
> > time=2.133..7.649 rows=3 loops=388)
> >           Index Cond: (cr.buyer_alias_id = "outer".id)
> > Total runtime: 3117.713 ms
> > (7 rows)
>
>
> -- 
> Daniel Cristian Cruz
> Analista de Sistemas


In response to

pgsql-performance by date

Next:From: Tom LaneDate: 2007-03-23 21:49:42
Subject: Re: EXISTS optimization
Previous:From: amrit angsusinghDate: 2007-03-23 20:50:16
Subject: Optimization pg 8.14 and postgresql.conf

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