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 43590538EF for ; Sun, 10 Apr 2005 06:44:11 +0100 (BST) 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 37769-06 for ; Sun, 10 Apr 2005 05:44:10 +0000 (GMT) Received: from mailbox.samurai.com (mailbox.samurai.com [205.207.28.82]) by svr1.postgresql.org (Postfix) with ESMTP id D802E538C9 for ; Sun, 10 Apr 2005 06:44:09 +0100 (BST) Received: from localhost (mailbox.samurai.com [205.207.28.82]) by mailbox.samurai.com (Postfix) with ESMTP id 1D86318CC05; Sun, 10 Apr 2005 01:44:10 -0400 (EDT) Received: from mailbox.samurai.com ([205.207.28.82]) by localhost (mailbox.samurai.com [205.207.28.82]) (amavisd-new, port 10024) with LMTP id 81227-02-8; Sun, 10 Apr 2005 01:44:08 -0400 (EDT) Received: from [220.101.4.182] (r220-101-4-182.cpe.unwired.net.au [220.101.4.182]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (No client certificate requested) by mailbox.samurai.com (Postfix) with ESMTP id 5656418CC2C; Sun, 10 Apr 2005 01:44:05 -0400 (EDT) Message-ID: <4258BD20.7090907@samurai.com> Date: Sun, 10 Apr 2005 15:44:00 +1000 From: Neil Conway User-Agent: Mozilla Thunderbird 1.0.2 (Macintosh/20050317) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Tom Lane Cc: "Jim C. Nasby" , Alvaro Herrera , Josh Berkus , Michael Fuhr , pgsql-performance@postgresql.org Subject: Re: Functionscan estimates References: <200504081515.50340.josh@agliodbs.com> <20050408223820.GA17450@winnie.fuhr.org> <20050408224556.GC19458@dcc.uchile.cl> <200504081604.27845.josh@agliodbs.com> <20050408235731.GA6279@dcc.uchile.cl> <19038.1113019256@sss.pgh.pa.us> <20050409152257.GG93835@decibel.org> <22291.1113061523@sss.pgh.pa.us> In-Reply-To: <22291.1113061523@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 mailbox.samurai.com X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.011 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200504/192 X-Sequence-Number: 11589 Tom Lane wrote: > The larger point is that writing an estimator for an SRF is frequently a > task about as difficult as writing the SRF itself True, although I think this doesn't necessarily kill the idea. If writing an estimator for a given SRF is too difficult, the user is no worse off than they are today. Hopefully there would be a fairly large class of SRFs for which writing an estimator would be relatively simple, and result in improved planner behavior. > I don't foresee a whole lot of use of an estimator hook designed as > proposed here. In particular, if the API is such that we can only > use the estimator when all the function arguments are plan-time > constants, it's not going to be very helpful. Yes :( One approach might be to break the function's domain into pieces and have the estimator function calculate the estimated result set size for each piece. So, given a trivial function like: foo(int): if $1 < 10 then produce 100 rows else produce 10000 rows If the planner has encoded the distribution of input tuples to the function as a histogram, it could invoke the SRF's estimator function for the boundary values of each histogram bucket, and use that to get an idea of the function's likely result set size at runtime. And yes, the idea as sketched is totally unworkable :) For one thing, the difficulty of doing this grows rapidly as the number of arguments to the function increases. But perhaps there is some variant of this idea that might work... Another thought is that the estimator could provide information on the cost of evaluating the function, the number of tuples produced by the function, and even the distribution of those tuples. BTW, why is this on -performance? It should be on -hackers. -Neil