X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 1CA3BD1B53D for ; Tue, 21 Oct 2003 00:57:54 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 44291-07 for ; Mon, 20 Oct 2003 21:57:26 -0300 (ADT) Received: from davinci.ethosmedia.com (unknown [209.128.84.228]) by svr1.postgresql.org (Postfix) with ESMTP id DAD3ED1B557 for ; Mon, 20 Oct 2003 21:57:22 -0300 (ADT) Received: from [66.219.92.2] (HELO temoku) by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.0.2) with ESMTP id 3798724 for pgsql-performance@postgresql.org; Mon, 20 Oct 2003 17:58:06 -0700 Content-Type: text/plain; charset="us-ascii" From: Josh Berkus Reply-To: josh@agliodbs.com Organization: Aglio Database Solutions To: pgsql-performance@postgresql.org Subject: SRFs ... no performance penalty? Date: Mon, 20 Oct 2003 17:55:12 -0700 User-Agent: KMail/1.4.3 MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Message-Id: <200310201755.12713.josh@agliodbs.com> X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/518 X-Sequence-Number: 4266 Folks, I'm working on the demo session for our upcoming presentation at PHPCon.=20= =20 As a side issue, we ended up comparing 3 versions of the same search screen: 1) All in PHP with views; 2) Using a function to build a query and count results but executing that= =20 query directly and sorting, paging in PHP; 3) Using a Set Returning function to handle row-returning, sorting, and=20 paging. All three methods were executing a series moderately complex query against = a=20 medium-sized data set (only about 20,000 rows but it's on a laptop). The= =20 postgresql.conf was tuned like a webserver; e.g. low sort_mem, high=20 max_connections. So far, on the average of several searches, we have: 1) 0.19687 seconds 2) 0.20667 seconds 3) 0.20594 seconds In our tests, using any kind of PL/pgSQL function seems to carry a 0.01 sec= ond=20 penalty over using PHP to build the search query. I'm not sure if this is= =20 comparitive time for string-parsing or something else; the 0.01 seems to be= =20 consistent regardless of scale. The difference between using a PL/pgSQL function as a query-builder only (t= he=20 7.2.x method) and using SRFs was small enough not to be significant. --=20 -Josh Berkus Aglio Database Solutions San Francisco