X-Original-To: pgsql-performance@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id C63C7475E9D for ; Thu, 24 Apr 2003 12:33:31 -0400 (EDT) Received: from tux.tuxee.net (tuxee.net [62.212.110.244]) by postgresql.org (Postfix) with ESMTP id 93E13474E42 for ; Thu, 24 Apr 2003 12:33:30 -0400 (EDT) Received: from mau.localdomain (mau.localdomain [192.168.1.10]) by tux.tuxee.net (8.12.9/8.12.9) with ESMTP id h3OGXZi3023100 for ; Thu, 24 Apr 2003 18:33:35 +0200 Received: from mau.localdomain (localhost [127.0.0.1]) by mau.localdomain (8.12.8/8.12.6) with ESMTP id h3OGXZw7007268 for ; Thu, 24 Apr 2003 18:33:35 +0200 Received: (from fred@localhost) by mau.localdomain (8.12.8/8.12.8/Submit) id h3OGXZGP007267; Thu, 24 Apr 2003 18:33:35 +0200 X-Authentication-Warning: mau.localdomain: fred set sender to fred@jolliton.com using -f To: pgsql-performance@postgresql.org From: Frederic Jolliton Subject: Re: Important speed difference between a query and a Date: Thu, 24 Apr 2003 18:33:35 +0200 In-Reply-To: <14131.1051199816@sss.pgh.pa.us> (Tom Lane's message of "Thu, 24 Apr 2003 11:56:56 -0400") Message-ID: <86bryvsvmo.fsf@mau.localdomain> User-Agent: Gnus/5.090015 (Oort Gnus v0.15) Emacs/21.3 References: <8665p56qx8.fsf@mau.localdomain> <86el3rsxqu.fsf@mau.localdomain> <14131.1051199816@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Spam-Status: No, hits=-40.2 required=5.0 tests=BAYES_01,EMAIL_ATTRIBUTION,IN_REP_TO,QUOTED_EMAIL_TEXT, REFERENCES,REPLY_WITH_QUOTES,USER_AGENT_GNUS_UA, X_AUTH_WARNING autolearn=ham version=2.50 X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp) X-Archive-Number: 200304/199 X-Sequence-Number: 1705 > Frederic Jolliton writes: >>> To "emulate" a parametred view, I created a function as follow: >>> >>> CREATE FUNCTION get_info (integer) RETURNS SETOF type_get_info >>> AS '...' <- here the query show below, where 'LIMIT $1' is used instead of 'LIMIT 10' >>> LANGUAGE sql; > >> So, the query in the function is not using index but the exact same >> query alone does ! Tom Lane writes: > But it's not the same query, is it? With "LIMIT $1" the planner can't > know what the limit value is exactly, so it has to generate a plan that > won't be too unreasonable for either a small or a large limit. Ok. So the query is optimized once and not each time.. I understand now. But, since I "know" better that PostgreSQL that query must use index in most of case, can I force in some manner the function when declaring it to take this in account ? I suppose (not tested) that setting enable_seqscan just before will probably do it, but what about dump/restore of the database when recreating the function and keep this "fix" automatically ?