X-Original-To: pgsql-performance@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 9F26D4761C0 for ; Fri, 25 Apr 2003 03:01:43 -0400 (EDT) Received: from fuji.krosing.net (unknown [194.204.44.118]) by postgresql.org (Postfix) with ESMTP id 4DAD0476182 for ; Fri, 25 Apr 2003 03:01:42 -0400 (EDT) Received: from fuji.krosing.net (lo [127.0.0.1]) by fuji.krosing.net (8.12.8/8.12.8) with ESMTP id h3P71WmG003399; Fri, 25 Apr 2003 10:01:32 +0300 Received: (from hannu@localhost) by fuji.krosing.net (8.12.8/8.12.8/Submit) id h3P71V5T003397; Fri, 25 Apr 2003 10:01:31 +0300 X-Authentication-Warning: fuji.krosing.net: hannu set sender to hannu@tm.ee using -f Subject: Re: Important speed difference between a query and a From: Hannu Krosing To: Frederic Jolliton Cc: pgsql-performance@postgresql.org In-Reply-To: <86bryvsvmo.fsf@mau.localdomain> References: <8665p56qx8.fsf@mau.localdomain> <86el3rsxqu.fsf@mau.localdomain> <14131.1051199816@sss.pgh.pa.us> <86bryvsvmo.fsf@mau.localdomain> Content-Type: text/plain Content-Transfer-Encoding: 7bit Organization: Message-Id: <1051254090.2997.3.camel@fuji.krosing.net> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.2.4 Date: 25 Apr 2003 10:01:31 +0300 X-Spam-Status: No, hits=-33.7 required=5.0 tests=BAYES_01,IN_REP_TO,QUOTED_EMAIL_TEXT,REFERENCES, REPLY_WITH_QUOTES,USER_AGENT_XIMIAN,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/207 X-Sequence-Number: 1713 Frederic Jolliton kirjutas N, 24.04.2003 kell 19:33: > > 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 ? You could define two functions - one for small sets with constant LIMITs (maybe 50) in UNION parts, and another with $1. Then use accordingly. > 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 ? ------------- Hannu