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 E03719FA1B0 for ; Wed, 10 May 2006 12:53:08 -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 18369-10 for ; Wed, 10 May 2006 12:52:58 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from fw.greenpeace.org (fw.greenpeace.org [212.19.215.77]) by postgresql.org (Postfix) with ESMTP id 87D179FA2FA for ; Wed, 10 May 2006 12:52:57 -0300 (ADT) Received: (from root@localhost) by fw.greenpeace.org (8.9.3c/8.6.12) id RAA23766 for ; Wed, 10 May 2006 17:52:56 +0200 (CEST) Received: by fw.greenpeace.org (TUNIX/Firewall SMTP Server) for id sma084377; Wed, 10 May 06 17:29:44 +0200 Received: from bb.nli.gl3 (root@localhost) by bb.nli.gl3 (8.12.8/8.12.5) with SMTP id k4AFTilw027343 for ; Wed, 10 May 2006 17:29:44 +0200 Received: from maila.greenpeace.org (fw.nli.gl3 [192.168.31.7]) by bb.nli.gl3 (8.12.8/8.13.3) with ESMTP id k4AFTa7j027322; Wed, 10 May 2006 17:29:36 +0200 Received: from [192.168.200.143] (vpni.greenpeace.org [212.19.215.76]) (authenticated (128 bits)) by maila.greenpeace.org (8.13.4/8.11.6) with ESMTP id k4AFTZco027041 (using TLSv1/SSLv3 with cipher RC4-MD5 (128 bits) verified NO); Wed, 10 May 2006 17:29:36 +0200 Message-ID: <446206FF.7050603@superlativ.dk> Date: Wed, 10 May 2006 17:30:07 +0200 From: Nis Jorgensen User-Agent: Opera/7.5 (Windows; U; Longhorn; en-US; rv:1.0.0) MIME-Version: 1.0 To: Martijn van Oosterhout CC: PFC , Markus Schaber , pgsql-performance@postgresql.org, pgsql-hackers@postgresql.org Subject: Re: [HACKERS] Big IN() clauses etc : feature proposal References: <1147110142.32369.7.camel@archimedes> <20060508175013.GY99570@pervasive.com> <28288.1147111196@sss.pgh.pa.us> <20060509105214.P90693@vesihiisi.cksoft.de> <758d5e7f0605090943p1bbfdae2p4cb28ba128288316@mail.gmail.com> <4461EAAC.7030105@logix-tt.com> <20060510145551.GB14476@svana.org> In-Reply-To: <20060510145551.GB14476@svana.org> Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit X-Anti-Virus: Kaspersky Anti-Virus for MailServers 5.5.2/RELEASE, bases: 25072005 #131892, status: clean X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200605/263 X-Sequence-Number: 19050 Martijn van Oosterhout wrote: > On Wed, May 10, 2006 at 04:38:31PM +0200, PFC wrote: >> You need to do some processing to know how many rows the function >> would return. >> Often, this processing will be repeated in the function itself. >> Sometimes it's very simple (ie. the function will RETURN NEXT each >> element in an array, you know the array length...) >> Sometimes, for functions returning few rows, it might be faster to >> compute the entire result set in the cost estimator. > > I think the best would probably be to assign a constant. An SRF will > generally return between one of 1-10, 10-100, 100-1000, etc. You don't > need exact number, you just need to get within an order of magnitude > and a constant will work fine for that. > > How many functions sometimes return one and sometimes a million rows? It will probably be quite common for the number to depend on the number of rows in other tables. Even if this is fairly constant within one db (some assumption), it is likely to be different in others using the same function definition. Perhaps a better solution would be to cache the result of the estimator function. /Nis