pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Daniel Cristian Cruz <danielcristian@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Strange left outer join performance issue
Date: Fri, 23 Mar 2007 17:04:51 -0300
Message-ID: <48d0cacb0703231304o75b8b493k85ef16c3b3bb495b@mail.gmail.com> (raw)
In-Reply-To: <5335C295-4D3E-4A1B-A270-6B822377DD25@mac.com>
References: <5335C295-4D3E-4A1B-A270-6B822377DD25@mac.com>

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@mac.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

view thread (6+ messages)  latest in thread

Message-ID: <48d0cacb0703231304o75b8b493k85ef16c3b3bb495b@mail.gmail.com>
Permalink:  ../48d0cacb0703231304o75b8b493k85ef16c3b3bb495b@mail.gmail.com/
Also on:    postgresql.org/message-id/48d0cacb0703231304o75b8b493k85ef16c3b3bb495b@mail.gmail.com

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: danielcristian@gmail.com
  Subject: Re: Strange left outer join performance issue
  In-Reply-To: <48d0cacb0703231304o75b8b493k85ef16c3b3bb495b@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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