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

Re: Strange left outer join performance issue

From: "Daniel Cristian Cruz" <danielcristian(at)gmail(dot)com>
To:
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Strange left outer join performance issue
Date: 2007-03-23 20:04:51
Message-ID: 48d0cacb0703231304o75b8b493k85ef16c3b3bb495b@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
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

Responses

pgsql-performance by date

Next:From: amrit angsusinghDate: 2007-03-23 20:50:16
Subject: Optimization pg 8.14 and postgresql.conf
Previous:From: Noah M. DanielsDate: 2007-03-23 19:44:44
Subject: Strange left outer join performance issue

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