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;
PREPARE
postgres=# EXPLAIN EXECUTE p(100); QUERY
PLAN
-----------------------------------------------------------------------------
Limit (cost=0.00..49.18 rows=2 width=4)
-> Index Scan using foo_pkey on foo (cost=0.00..614.77 rows=25
width=4)
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
EnterpriseDB http://www.enterprisedb.com
---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
