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 A7CDC8BA156 for ; Tue, 15 Feb 2005 07:40:20 +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 93659-10 for ; Tue, 15 Feb 2005 07:40:18 +0000 (GMT) Received: from stark.xeocode.com (gsstark.mtl.istop.com [66.11.160.162]) by svr1.postgresql.org (Postfix) with ESMTP id 209ED8BA151 for ; Tue, 15 Feb 2005 07:40:17 +0000 (GMT) Received: from localhost ([127.0.0.1] helo=stark.xeocode.com) by stark.xeocode.com with smtp (Exim 3.36 #1 (Debian)) id 1D0wxx-0000aU-00; Tue, 15 Feb 2005 02:17:50 -0500 To: Kevin Brown Cc: "Pgsql-Performance (E-mail)" Subject: Re: How to interpret this explain analyse? References: <13008.1108150825@sss.pgh.pa.us> <20050215001011.GA31014@filer> In-Reply-To: <20050215001011.GA31014@filer> From: Greg Stark Organization: The Emacs Conspiracy; member since 1992 Date: 15 Feb 2005 02:17:49 -0500 Message-ID: <877jla47he.fsf@stark.xeocode.com> Lines: 25 User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.3 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.056 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200502/234 X-Sequence-Number: 10563 Kevin Brown writes: > Ouch. Is this really a reasonable assumption? I figured the primary > use of a cursor was to fetch small amounts of data at a time from a > large table, so 10% seems extremely high as an average fetch size. Or > is the optimization based on the number of rows that will be fetched > by the cursor during the cursor's lifetime (as opposed to in a single > fetch)? > > Also, one has to ask what the consequences are of assuming a value too > low versus too high. Which ends up being worse? This is one of the things the planner really cannot know. Ultimately it's the kind of thing for which hints really are necessary. Oracle distinguishes between the "minimize total time" versus "minimize startup time" with /*+ ALL_ROWS */ and /*+ FIRST_ROWS */ hints, for example. I would also find it reasonable to have hints to specify a selectivity for expressions the optimizer has no hope of possibly being able to estimate. Things like "WHERE myfunction(col1,col2,?) /*+ 10% */" -- greg