X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org
Received: from localhost (mx1.hub.org [200.46.208.251])
	by postgresql.org (Postfix) with ESMTP id 2784B9FA2FA;
	Wed, 10 May 2006 10:29:25 -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 00142-01; Wed, 10 May 2006 10:29:06 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from mail.logix-tt.com (unknown [212.211.145.186])
	by postgresql.org (Postfix) with ESMTP id 395829FA1B0;
	Wed, 10 May 2006 10:29:06 -0300 (ADT)
Received: from kingfisher.intern.logix-tt.com (p549D13DA.dip0.t-ipconnect.de
	[84.157.19.218])
	(using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits))
	(No client certificate requested)
	by mail.logix-tt.com (Postfix) with ESMTP id E24B765837;
	Wed, 10 May 2006 15:30:15 +0200 (CEST)
Received: from [127.0.0.1] (localhost [127.0.0.1])
	by kingfisher.intern.logix-tt.com (Postfix) with ESMTP id 98A4E18222EEA;
	Wed, 10 May 2006 15:29:16 +0200 (CEST)
Message-ID: <4461EAAC.7030105@logix-tt.com>
Date: Wed, 10 May 2006 15:29:16 +0200
From: Markus Schaber <schabi@logix-tt.com>
Organization: Logical Tracking and Tracing International AG, Switzerland
User-Agent: Debian Thunderbird 1.0.7 (X11/20051017)
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Big IN() clauses etc : feature proposal
References: <445F7FF8.6070707@comcast.net>
	<1147110142.32369.7.camel@archimedes>
	<20060508175013.GY99570@pervasive.com>
	<28288.1147111196@sss.pgh.pa.us> <op.s89zl30wcigqcu@apollo13>
	<20060509105214.P90693@vesihiisi.cksoft.de>
	<op.s8916gm6cigqcu@apollo13>
	<758d5e7f0605090943p1bbfdae2p4cb28ba128288316@mail.gmail.com>
	<op.s9amckxhcigqcu@apollo13>
In-Reply-To: <op.s9amckxhcigqcu@apollo13>
X-Enigmail-Version: 0.93.0.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200605/242
X-Sequence-Number: 19029

Hi, PFC,

PFC wrote:

>     The problem is that you need a set-returning function to retrieve
> the  values. SRFs don't have rowcount estimates, so the plans suck.

What about adding some way of rowcount estimation to SRFs, in the way of:

CREATE FUNCTION foo (para, meters) RETURNS SETOF bar AS
$$ ... function code ... $$ LANGUAGE plpgsql
ROWCOUNT_ESTIMATOR $$ ... estimation code ... $$ ;

Internally, this could create two functions, foo (para, meters) and
estimate_foo(para, meters) that are the same language and coupled
together (just like a SERIAL column and its sequence). The estimator
functions have an implicit return parameter of int8. Parameters may be
NULL when they are not known at query planning time.

What do you think about this idea?

The same scheme could be used to add a CPUCOST_ESTIMATOR to expensive
functions.

HTH,
Markus
-- 
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf.     | Software Development GIS

Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org