Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id EFEED9FB875 for ; Fri, 23 Mar 2007 17:04:57 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-new, port 10024) with ESMTP id 57490-01-8 for ; Fri, 23 Mar 2007 17:04:53 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from wr-out-0506.google.com (wr-out-0506.google.com [64.233.184.237]) by postgresql.org (Postfix) with ESMTP id 762499FB576 for ; Fri, 23 Mar 2007 17:04:53 -0300 (ADT) Received: by wr-out-0506.google.com with SMTP id 76so1052306wra for ; Fri, 23 Mar 2007 13:04:52 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:subject:cc:in-reply-to:mime-version:content-type:references; b=cL+GPFVBfK3kePZVqCQZ2nbQUMzJ4E3MakXFyC5FkiDxUhKYw0lr+6MivRRDOEMPNr7woyZU3MSe58n6k9sdeWMzXKKLMyAsHxzQW775Y4UD2KiA1HhBAchdkVRdon4vtMIEu3TCIFtLJyS0k0snM4FMx0IOrUGUGhugwYvnvDg= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:subject:cc:in-reply-to:mime-version:content-type:references; b=tLWSw4S3LeMg1HFsjHYmQT5u2TUeLLeAqAh4rnk0yabtFYqAs+iL5D9Lv8B764HQSmOFfGfPacf1X+UOeR4fGQcM12WdijQvt4lIIjKenPnrFjfmlERc+/IzdTNSA2FpRoEMqmWwh/7JrJCKfjAzOU8ZPMWJWnM/XKTTTQFDNMQ= Received: by 10.115.88.1 with SMTP id q1mr1377114wal.1174680291468; Fri, 23 Mar 2007 13:04:51 -0700 (PDT) Received: by 10.115.72.5 with HTTP; Fri, 23 Mar 2007 13:04:51 -0700 (PDT) Message-ID: <48d0cacb0703231304o75b8b493k85ef16c3b3bb495b@mail.gmail.com> Date: Fri, 23 Mar 2007 17:04:51 -0300 From: "Daniel Cristian Cruz" Subject: Re: Strange left outer join performance issue Cc: pgsql-performance@postgresql.org In-Reply-To: <5335C295-4D3E-4A1B-A270-6B822377DD25@mac.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_251115_11102037.1174680291421" References: <5335C295-4D3E-4A1B-A270-6B822377DD25@mac.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200703/374 X-Sequence-Number: 23779 ------=_Part_251115_11102037.1174680291421 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline 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 : > 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 ------=_Part_251115_11102037.1174680291421 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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
------=_Part_251115_11102037.1174680291421--