X-Original-To: pgsql-performance@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 7AF36476478 for ; Wed, 23 Apr 2003 13:55:45 -0400 (EDT) Received: from tux.tuxee.net (tuxee.net [62.212.110.244]) by postgresql.org (Postfix) with ESMTP id 54D7B476407 for ; Wed, 23 Apr 2003 13:53:57 -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 h3NHrti3019870 for ; Wed, 23 Apr 2003 19:53:55 +0200 Received: from mau.localdomain (localhost [127.0.0.1]) by mau.localdomain (8.12.8/8.12.6) with ESMTP id h3NHrtVo025025; Wed, 23 Apr 2003 19:53:55 +0200 Received: (from fred@localhost) by mau.localdomain (8.12.8/8.12.8/Submit) id h3NHrt50025024; Wed, 23 Apr 2003 19:53:55 +0200 X-Authentication-Warning: mau.localdomain: fred set sender to fred@jolliton.com using -f To: pgsql-performance@postgresql.org From: Frederic Jolliton Subject: Important speed difference between a query and a function with the same query Date: Wed, 23 Apr 2003 19:53:55 +0200 Message-ID: <8665p56qx8.fsf@mau.localdomain> User-Agent: Gnus/5.090015 (Oort Gnus v0.15) Emacs/21.3 MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable X-Spam-Status: No, hits=-14.3 required=5.0 tests=BAYES_01,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/195 X-Sequence-Number: 1701 Hi, (PostgreSQL 7.3.2 on i386-portbld-freebsd4.7, compiled by GCC 2.95.4) I've a curious performance problem with a function returning set of rows. The query alone is very fast, but not when called from the function. To "emulate" a parametred view=B9, 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; The table table1 have 330K rows, and table2 have 3K rows. When I run the following query (prefixed with SELECT * to try to get the same behavior that the second query), I obtain very good time. database=3D# SELECT * FROM ( (SELECT a.field1,a.field2,a.field3,b.field3,b.field4,a.field5 FROM table1 AS a, table1 AS b WHERE a.field6=3Db.field4 ORDER BY a.field6 DESC LIMIT 10) UNION (SELECT a.field1,a.field2,b.field3,a.field3,a.field4,b.field5 FROM table2 AS a, table1 AS b WHERE a.field4=3Db.field6 ORDER BY a.field4 DESC LIMIT 10) ORDER BY field4 DESC LIMIT 10 ) AS type_get_info; [...] (10 rows) Time: 185.86 ms But, when I run the function (with 10 as parameter, but even 1 is slow) I get poor time: database=3D# SELECT * FROM get_info(10); [...] (10 rows) Time: 32782.26 ms database=3D#=20 (even after a VACUUM FULL ANALYZE, and REINDEX of indexes used in the queries) What is curious is that I remember that the function was fast at a time.. What is the difference between the two case ? [1] Is there another solution to this 'hack' ? I can't simply create a view and use 'LIMIT 10' because intermediate SELECT have be limited too (to avoid UNION with 300K rows where only the first 10 are of interest to me.) --=20 Fr=E9d=E9ric Jolliton