X-Original-To: pgsql-performance@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 86EF3476387 for ; Thu, 24 Apr 2003 12:41:45 -0400 (EDT) Received: from tux.tuxee.net (tuxee.net [62.212.110.244]) by postgresql.org (Postfix) with ESMTP id 39B4447637F for ; Thu, 24 Apr 2003 12:41:44 -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 h3OGfni3023327 for ; Thu, 24 Apr 2003 18:41:49 +0200 Received: from mau.localdomain (localhost [127.0.0.1]) by mau.localdomain (8.12.8/8.12.6) with ESMTP id h3OGfnw7007302 for ; Thu, 24 Apr 2003 18:41:49 +0200 Received: (from fred@localhost) by mau.localdomain (8.12.8/8.12.8/Submit) id h3OGfnvr007301; Thu, 24 Apr 2003 18:41:49 +0200 X-Authentication-Warning: mau.localdomain: fred set sender to fred@jolliton.com using -f From: Frederic Jolliton To: Subject: Re: Important speed difference between a query and a Date: Thu, 24 Apr 2003 18:41:49 +0200 In-Reply-To: <20030424085451.P1362-100000@megazone23.bigpanda.com> (Stephan Szabo's message of "Thu, 24 Apr 2003 08:57:13 -0700 (PDT)") Message-ID: <868ytzsv8y.fsf@mau.localdomain> User-Agent: Gnus/5.090015 (Oort Gnus v0.15) Emacs/21.3 References: <20030424085451.P1362-100000@megazone23.bigpanda.com> MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable 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/200 X-Sequence-Number: 1706 > On Thu, 24 Apr 2003, Frederic Jolliton wrote: >> > CREATE FUNCTION get_info (integer) RETURNS SETOF type_get_info >> > AS '...' <- here the query show below, where 'LIMIT $1' is used in= stead of 'LIMIT 10' >> > LANGUAGE sql; >> >> Setting enable_seqscan to off give same result speed between the query >> and the function ! >> >> So, the query in the function is not using index but the exact same >> query alone does ! >> >> Is there an explanation ? Stephan Szabo writes: > My guess is that limit $1 is assuming a larger number of rows when > planning the queries, large enough that it expects seqscan to be > better (assuming the limit is what it expects). It's probably not > going to plan that query each time the function is called so it's > not going to know whether you're calling with a small number (index > scan may be better) or a large number (seq scan may be better). For > example, if you sent 100000, the index scan might be a loser. > > Perhaps plpgsql with EXECUTE would work better for that, although > it's likely to have some general overhead. The server is rather fast, and the query return 10 to 50 rows in most case. So, this is probably a solution, even if it's not very clean. (Well, I have to find an example to RETURN the result of EXECUTE..) But, what I don't understand is why enable_seqscan change something if the query is already planed. --=20 Fr=E9d=E9ric Jolliton