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

Re: Slow set-returning functions

From: "Heikki Linnakangas" <heikki(at)enterprisedb(dot)com>
To: "Dean Rasheed" <dean_rasheed(at)hotmail(dot)com>
Cc: <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Slow set-returning functions
Date: 2008-01-20 14:34:52
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-performance
Dean Rasheed wrote:
> I have been having difficulty with some functions which return sets of
> rows. The functions seem to run very slowly, even though the queries
> they run execute very quicky if I run them directly from psgl.
> Typically these queries are only returning a few hundred rows with my
> real data.
> I have had difficulty coming up with a simple test case, but the code
> below usually shows the same problem. Sometimes I have to run the
> setup code a few times before it happens - not sure why (I would
> expect this to be deterministic), but perhaps there is some randomness
> introduced by the sampling done by the analyse.
> The function foo() which has a hard-coded LIMIT always executes
> quickly (comparable to running the query directly).
> However, the function foo(int) which is passed the same LIMIT as a
> parameter executes around 30 times slower. The only difference is that
> the LIMIT is a parameter to the function, although the LIMIT isn't
> reached anyway in this case. Sometimes running this same script
> generates data for which this function executes as fast as the other
> one (which is always fast).

This is clearly because the planner doesn't know what the value for the 
parameter will be at run time, so it chooses a plan that's not optimal 
for LIMIT 100.

> Is there any way that I can see what execution plan is being used
> internally by the functions?

Not directly, but you can do this:

postgres=# PREPARE p (int4) AS SELECT id FROM foo WHERE lower(name) LIKE 
'foo' ORDER BY id OFFSET 0 LIMIT $1;
postgres=# EXPLAIN EXECUTE p(100);                                 QUERY 
  Limit  (cost=0.00..49.18 rows=2 width=4)
    ->  Index Scan using foo_pkey on foo  (cost=0.00..614.77 rows=25 
          Filter: (lower(name) ~~ 'foo'::text)
(3 rows)

You could work around that by using EXECUTE in the plpgsql function, to 
force the query to be planned on every execution with the actual value 
of the LIMIT.

   Heikki Linnakangas

In response to


pgsql-performance by date

Next:From: Merlin MoncureDate: 2008-01-20 15:25:48
Subject: Re: Slow set-returning functions
Previous:From: Dean RasheedDate: 2008-01-20 11:40:19
Subject: Slow set-returning functions

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