Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 64EAD633A5C for ; Tue, 10 Mar 2009 11:51:12 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 66307-02 for ; Tue, 10 Mar 2009 11:50:57 -0300 (ADT) X-Greylist: delayed 00:10:04.44175 by SQLgrey-1.7.6 Received: from mx2.redhat.com (mx2.redhat.com [66.187.237.31]) by mail.postgresql.org (Postfix) with ESMTP id E9E95632A91 for ; Tue, 10 Mar 2009 11:51:06 -0300 (ADT) Received: from int-mx2.corp.redhat.com (int-mx2.corp.redhat.com [172.16.27.26]) by mx2.redhat.com (8.13.8/8.13.8) with ESMTP id n2AEetpO021484; Tue, 10 Mar 2009 10:40:55 -0400 Received: from ns3.rdu.redhat.com (ns3.rdu.redhat.com [10.11.255.199]) by int-mx2.corp.redhat.com (8.13.1/8.13.1) with ESMTP id n2AEetnq005763; Tue, 10 Mar 2009 10:40:55 -0400 Received: from ton.toronto.redhat.com (ton.yyz.redhat.com [10.15.16.15]) by ns3.rdu.redhat.com (8.13.8/8.13.8) with ESMTP id n2AEesG2025309; Tue, 10 Mar 2009 10:40:54 -0400 Received: from ton.toronto.redhat.com (localhost.localdomain [127.0.0.1]) by ton.toronto.redhat.com (8.13.1/8.13.1) with ESMTP id n2AEeqbV025300; Tue, 10 Mar 2009 10:40:52 -0400 Received: (from fche@localhost) by ton.toronto.redhat.com (8.13.1/8.13.1/Submit) id n2AEeq0h025299; Tue, 10 Mar 2009 10:40:52 -0400 X-Authentication-Warning: ton.toronto.redhat.com: fche set sender to fche@redhat.com using -f To: Tom Lane Cc: Mario Splivalo , pgsql-performance@postgresql.org Subject: Re: Query much slower when run from postgres function References: <49B51B60.6030600@megafon.hr> <27649.1236616294@sss.pgh.pa.us> From: fche@redhat.com (Frank Ch. Eigler) Date: Tue, 10 Mar 2009 10:40:51 -0400 In-Reply-To: <27649.1236616294@sss.pgh.pa.us> (Tom Lane's message of "Mon, 09 Mar 2009 12:31:34 -0400") Message-ID: User-Agent: Gnus/5.1008 (Gnus v5.10.8) Emacs/21.3 (gnu/linux) MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Scanned-By: MIMEDefang 2.58 on 172.16.27.26 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200903/79 X-Sequence-Number: 33001 Tom Lane writes: > Mario Splivalo writes: >> Now I'm confused, why is 'sql' function much slower than 'direct' SELECT? > > Usually the reason for this is that the planner chooses a different plan > when it has knowledge of the particular value you are searching for than > when it does not. I suppose 'service_id' has a very skewed distribution > and you are looking for an uncommon value? For a prepared statement, could the planner produce *several* plans, if it guesses great sensitivity to the parameter values? Then it could choose amongst them at run time. - FChE