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 845088BA15B for ; Tue, 15 Feb 2005 15:12:04 +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 61927-04 for ; Tue, 15 Feb 2005 15:12:00 +0000 (GMT) Received: from presinet-main.presinet.com (mail.presinet.com [209.53.156.1]) by svr1.postgresql.org (Postfix) with ESMTP id A31868BA124 for ; Tue, 15 Feb 2005 15:11:58 +0000 (GMT) Received: from [10.10.1.151] (BRICK [10.10.1.151]) by presinet-main.presinet.com with SMTP (Microsoft Exchange Internet Mail Service Version 5.5.2658.3) id 1VVBZZVX; Tue, 15 Feb 2005 07:10:18 -0800 Message-ID: <4212113D.40808@PresiNET.com> Date: Tue, 15 Feb 2005 07:11:57 -0800 From: Bricklen Anderson User-Agent: Debian Thunderbird 1.0 (X11/20050116) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Greg Stark Cc: "Pgsql-Performance (E-mail)" Subject: Re: How to interpret this explain analyse? References: <13008.1108150825@sss.pgh.pa.us> <20050215001011.GA31014@filer> <877jla47he.fsf@stark.xeocode.com> In-Reply-To: <877jla47he.fsf@stark.xeocode.com> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200502/238 X-Sequence-Number: 10567 Greg Stark wrote: > 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% */" > > Not to mention that hints would be helpful if you want to specify a particular index for a specific query (case in point, testing plans and response of various indices without having to drop and create other ones). This is a bit of functionality that I'd like to see.