X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id C7F0C8B9E3F for ; Thu, 3 Feb 2005 23:23:14 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 17554-04 for ; Thu, 3 Feb 2005 23:23:02 +0000 (GMT) Received: from gizmo08ps.bigpond.com (gizmo08ps.bigpond.com [144.140.71.18]) by svr1.postgresql.org (Postfix) with SMTP id 86BA88B9E23 for ; Thu, 3 Feb 2005 23:23:02 +0000 (GMT) Received: (qmail 11604 invoked from network); 3 Feb 2005 23:22:56 -0000 Received: from unknown (HELO psmam11.bigpond.com) (144.135.25.100) by gizmo08ps.bigpond.com with SMTP; 3 Feb 2005 23:22:56 -0000 Received: from cpe-203-45-190-81.qld.bigpond.net.au ([203.45.190.81]) by psmam11.bigpond.com(MAM REL_3_4_2a 225/65786981) with SMTP id 65786981; Fri, 04 Feb 2005 09:22:56 +1000 Message-ID: <4202B23F.5080604@bigpond.net.au> Date: Fri, 04 Feb 2005 09:22:39 +1000 From: David Brown User-Agent: Mozilla Thunderbird 1.0RC1 (Windows/20041201) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Tom Lane Cc: pgsql-performance@postgresql.org Subject: Re: Planner really hates nested loops References: <420206AD.8040206@bigpond.net.au> <16694.1107447931@sss.pgh.pa.us> In-Reply-To: <16694.1107447931@sss.pgh.pa.us> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.906 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_ABUSE, DNS_FROM_RFC_WHOIS X-Spam-Level: X-Archive-Number: 200502/74 X-Sequence-Number: 10403 Tom Lane wrote: >What exactly did you do to force the various plan choices? (I see some >ridiculous choices of indexscans, for instance, suggesting improper use >of enable_seqscan in some cases.) > Except for forcing a hash with indexes (to show that increased use of indexes is not necessarily good), the "ridiculous choices of indexscans" are straight from the planner, i.e. I did not use enable_seqscan. Obviously, the alternative join methods were obtained by disabling hash joins and merge joins. > And what's the "cache rows" and "disk >rows" stuff, and how do you know that what you were measuring is >actually what you think it is? I have zero confidence in >Windows-atop-ATA as a platform for measuring disk-related behaviors, >because I don't think you can control or even know what caching is >going on. > > The terms are just abbreviated headings to make it easier to check what you're looking at. "Cache" refers to repeated runs without disk I/O. "Disk" refers to a completely initialized system with no PostgreSQL data in the OS cache (i.e. after a reboot - this is Benchmarking 101). All results were verified with *at least* two runs at different times. This is not to say the "disk" results are an accurate or absolute benchmark, but they're useful as a reference when looking at the cached results. In any case, I can get the same "cached" results by increasing buffers to take up most of the memory and thereby make them the defacto cache. With respect, could we please focus on the point of this thread? I've spent a great deal of time experimenting with PostgreSQL over the last couple of months, including reading every known web page regarding tuning and following every post in this list in that period. I'm confident that my results here are what most people will experience when trying PostgreSQL, and I'd like to help in a constructive way.