X-Original-To: pgsql-performance-postgresql.org@postgresql.org Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id E49649F9F7E for ; Wed, 13 Sep 2006 17:28:01 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 97943-05 for ; Wed, 13 Sep 2006 20:27:50 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from nf-out-0910.google.com (nf-out-0910.google.com [64.233.182.191]) by postgresql.org (Postfix) with ESMTP id DC7819F9F7D for ; Wed, 13 Sep 2006 17:27:49 -0300 (ADT) Received: by nf-out-0910.google.com with SMTP id k26so2123924nfc for ; Wed, 13 Sep 2006 13:27:48 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=Zuj0K+NrLHGLYXQDETA1TTJ9dtTi6e72V6DYAacoF3Eby3mrInFtV7pRzjEfWAt5Pf+iwTT/h1bVxljuLU2UHi11l+4ee+LFKBbOouJZiqATcT1OK/MToDMS8WUI8q9BIdL6ZFFQyGm4vhAU33IpfckKpTZJuGt1beEH3N2xrnU= Received: by 10.49.8.10 with SMTP id l10mr11313629nfi; Wed, 13 Sep 2006 13:27:48 -0700 (PDT) Received: by 10.49.42.19 with HTTP; Wed, 13 Sep 2006 13:27:47 -0700 (PDT) Message-ID: <38242de90609131327o68d2866fh92c12ddcf364aba7@mail.gmail.com> Date: Wed, 13 Sep 2006 14:27:47 -0600 From: "Joshua Marsh" To: "Terje Elde" Subject: Re: Performance With Joins on Large Tables Cc: "Jeff Davis" , pgsql-performance@postgresql.org, jim@nasby.net In-Reply-To: <45085F3A.1040108@elde.net> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <38242de90609121517y62517040v8622b9ae93cba23d@mail.gmail.com> <20060913064152.GC23173@nasby.net> <38242de90609130749u976295fwb10904ff4797b920@mail.gmail.com> <1158163655.29889.57.camel@dogma.v10.wvs> <38242de90609130919k33208204ka1006ff0ca25f75b@mail.gmail.com> <1158167054.29889.72.camel@dogma.v10.wvs> <45085F3A.1040108@elde.net> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200609/131 X-Sequence-Number: 20817 On 9/13/06, Terje Elde wrote: > Jeff Davis wrote: > > Is it overestimating the cost of using indexes or underestimating the > > cost of a seq scan, or both? Maybe explain with the 0.1 setting will > > help? > > > > If enable_seqscan is off, and cost is still set to 100000000, it could > be that it's quite simply forcibly underestimating the cost of a seqscan > in this case. > > If enable_secscan was off for the mentioned plan, it'd be interesting to > see if things would be saner with seqscans enabled, and a more > reasonable random page cost. If more 'sane' values still produce the > desired plan, it might be better for other plans etc. > > Terje > > I turned enable_seqscan to off and got similar results. random_age_cost at 4.0: data=# explain SELECT v.phonedirect, v.editdrop, EXTRACT (EPOCH FROM v.custdate), EXTRACT (EPOCH FROM s.custdate) FROM view_505 v INNER JOIN r3s169 s on v.dsiacctno = s.dsiacctno; QUERY PLAN ----------------------------------------------------------------------------------------------- Merge Join (cost=293737539.01..301430139.34 rows=285230272 width=11) Merge Cond: ("outer".dsiacctno = "inner".dsiacctno) -> Sort (cost=127311593.00..127592577.62 rows=112393848 width=20) Sort Key: v.dsiacctno -> Seq Scan on view_505 v (cost=100000000.00..104602114.48 rows=112393848 width=20) -> Sort (cost=166425946.01..167139021.69 rows=285230272 width=17) Sort Key: s.dsiacctno -> Seq Scan on r3s169 s (cost=100000000.00..106873675.72 rows=285230272 width=17) (8 rows) random_page_cost at 3.0: data=# explain SELECT v.phonedirect, v.editdrop, EXTRACT (EPOCH FROM v.custdate), EXTRACT (EPOCH FROM s.custdate) FROM view_505 v INNER JOIN r3s169 s on v.dsiacctno = s.dsiacctno; QUERY PLAN ----------------------------------------------------------------------------------------------- Merge Join (cost=288303269.01..295995869.34 rows=285230272 width=11) Merge Cond: ("outer".dsiacctno = "inner".dsiacctno) -> Sort (cost=125775957.00..126056941.62 rows=112393848 width=20) Sort Key: v.dsiacctno -> Seq Scan on view_505 v (cost=100000000.00..104602114.48 rows=112393848 width=20) -> Sort (cost=162527312.01..163240387.69 rows=285230272 width=17) Sort Key: s.dsiacctno -> Seq Scan on r3s169 s (cost=100000000.00..106873675.72 rows=285230272 width=17) (8 rows) random_age_cost ad 2,0: data=# explain SELECT v.phonedirect, v.editdrop, EXTRACT (EPOCH FROM v.custdate), EXTRACT (EPOCH FROM s.custdate) FROM view_505 v INNER JOIN r3s169 s on v.dsiacctno = s.dsiacctno; QUERY PLAN ----------------------------------------------------------------------------------------------- Merge Join (cost=282868999.01..290561599.34 rows=285230272 width=11) Merge Cond: ("outer".dsiacctno = "inner".dsiacctno) -> Sort (cost=124240321.00..124521305.62 rows=112393848 width=20) Sort Key: v.dsiacctno -> Seq Scan on view_505 v (cost=100000000.00..104602114.48 rows=112393848 width=20) -> Sort (cost=158628678.01..159341753.69 rows=285230272 width=17) Sort Key: s.dsiacctno -> Seq Scan on r3s169 s (cost=100000000.00..106873675.72 rows=285230272 width=17) (8 rows) random_page_cost at 1.0: data=# explain SELECT v.phonedirect, v.editdrop, EXTRACT (EPOCH FROM v.custdate), EXTRACT (EPOCH FROM s.custdate) FROM view_505 v INNER JOIN r3s169 s on v.dsiacctno = s.dsiacctno; QUERY PLAN ------------------------------------------------------------------------------------------------------------ Merge Join (cost=154730044.01..274040257.41 rows=285230272 width=11) Merge Cond: ("outer".dsiacctno = "inner".dsiacctno) -> Index Scan using view_505_dsiacctno on view_505 v (cost=0.00..111923570.63 rows=112393848 width=20) -> Sort (cost=154730044.01..155443119.69 rows=285230272 width=17) Sort Key: s.dsiacctno -> Seq Scan on r3s169 s (cost=100000000.00..106873675.72 rows=285230272 width=17) (6 rows) random_page_cost ad 0.1: data=# explain SELECT v.phonedirect, v.editdrop, EXTRACT (EPOCH FROM v.custdate), EXTRACT (EPOCH FROM s.custdate) FROM view_505 v INNER JOIN r3s169 s on v.dsiacctno = s.dsiacctno; QUERY PLAN ----------------------------------------------------------------------------------------------------------- Merge Join (cost=0.00..51808909.26 rows=285230272 width=11) Merge Cond: ("outer".dsiacctno = "inner".dsiacctno) -> Index Scan using view_505_dsiacctno on view_505 v (cost=0.00..12755411.69 rows=112393848 width=20) -> Index Scan using r3s169_dsiacctno on r3s169 s (cost=0.00..32357747.90 rows=285230272 width=17) (4 rows) I have a suspision that pgsql isn't tuned to properly deal with tables of this size. Are there other things I should look at when dealing with a database of this size.