X-Original-To: pgsql-performance-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 1E2449FA197 for ; Wed, 13 Sep 2006 16:27:12 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 77687-01 for ; Wed, 13 Sep 2006 16:27:08 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from nf-out-0910.google.com (nf-out-0910.google.com [64.233.182.186]) by postgresql.org (Postfix) with ESMTP id F21B89FA089 for ; Wed, 13 Sep 2006 16:27:07 -0300 (ADT) Received: by nf-out-0910.google.com with SMTP id p77so1889509nfc for ; Wed, 13 Sep 2006 12:27:06 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=DcfEhUale2dpSS3kqrcWGZVYsoEEVRSQ+BXDGg1BL+OKznt6ZmdBG1ZJgkPVCpoYg5q8sb34+DGj75HrHUoVzLiTxaYWqg6Cj+MwqEQdl9yEmKs4bJ4qy9X6IJ1CD4q1poW7PGxq5oiGWv+fKTjOIaA6mcNloAUsaWnU7TMNzF8= Received: by 10.49.8.15 with SMTP id l15mr11286678nfi; Wed, 13 Sep 2006 12:27:06 -0700 (PDT) Received: by 10.49.42.19 with HTTP; Wed, 13 Sep 2006 12:27:06 -0700 (PDT) Message-ID: <38242de90609131227q4837d1cekc50bc1a450175bec@mail.gmail.com> Date: Wed, 13 Sep 2006 13:27:06 -0600 From: "Joshua Marsh" To: "Jeff Davis" , pgsql-performance@postgresql.org, jim@nasby.net Subject: Re: Performance With Joins on Large Tables In-Reply-To: <1158167054.29889.72.camel@dogma.v10.wvs> 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> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.166 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_ABUSE, SPF_PASS X-Spam-Level: X-Archive-Number: 200609/128 X-Sequence-Number: 20814 > Hmm... that sounds bad. I'm sure your system will always choose indexes > with that value. > > 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? > > Regards, > Jeff Davis 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) This is what I wanted, two index scans. Just to give you an idea of the difference in time, this plan would allow me to process 100,000 records ever few seconds, while the sequential scan would only produces 100,000 every 10 minutes.