Skip site navigation (1) Skip section navigation (2)

Important speed difference between a query and a function with the same query

From: Frederic Jolliton <fred-pg(at)jolliton(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Important speed difference between a query and a function with the same query
Date: 2003-04-23 17:53:55
Message-ID: 8665p56qx8.fsf@mau.localdomain (view raw or whole thread)
Lists: pgsql-performance

(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

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;

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=# 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=b.field4
		ORDER BY a.field6 DESC
		LIMIT 10)
	(SELECT a.field1,a.field2,b.field3,a.field3,a.field4,b.field5
		FROM table2 AS a, table1 AS b
		WHERE a.field4=b.field6
		ORDER BY a.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=# SELECT * FROM get_info(10);
(10 rows)

Time: 32782.26 ms

(even after a VACUUM FULL ANALYZE, and REINDEX of indexes used in the

What is curious is that I remember that the function was fast at a

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.)

Frédéric Jolliton


pgsql-performance by date

Next:From: Frederic JollitonDate: 2003-04-24 15:47:53
Subject: Re: Important speed difference between a query and a
Previous:From: Greg StarkDate: 2003-04-23 16:32:09
Subject: Re: [SQL] Yet Another (Simple) Case of Index not used

Privacy Policy | About PostgreSQL
Copyright © 1996-2015 The PostgreSQL Global Development Group