is it helpful for the optimiser/planner to add LIMIT 1
I'm reviewing some function I wrote to add stable, immutable where
needed and I'd like to take the chance to add further "cheap"
optimisation if it helps.
There are many places where I know a function or a statement will
return just one row?
Is it helpful to add LIMIT 1?
eg.
select a, b from myfunction(3,5) limit 1;
select into a,b x,y from tablename where z=5 and u=7 limit 1;
select a,b from from tablename where z=5 and u=7 limit 1;
thx
--
Ivan Sergio Borgonovo
http://www.webthatworks.it
"Ivan Sergio Borgonovo" <mail@webthatworks.it> writes:
I'm reviewing some function I wrote to add stable, immutable where
needed and I'd like to take the chance to add further "cheap"
optimisation if it helps.There are many places where I know a function or a statement will
return just one row?Is it helpful to add LIMIT 1?
eg.
select a, b from myfunction(3,5) limit 1;
select into a,b x,y from tablename where z=5 and u=7 limit 1;
select a,b from from tablename where z=5 and u=7 limit 1;
In such simple queries the limit 1 won't do anything. In more complex queries
it could help correct any problems higher up in the query caused by bad
planner estimations. For example
select * from a join (select x from myfunction(3,5) limit 1) as b(i) using (i)
would work better than without the limit because without it the planner would
have no idea that myfunction is only going to return 1 record.
You could fix that more cleanly with "ALTER FUNCTION myfunction ROWS 1" but
only if that's always true, not just for myfunction(3,5).
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's On-Demand Production Tuning
Gregory Stark <stark@enterprisedb.com> writes:
You could fix that more cleanly with "ALTER FUNCTION myfunction ROWS 1" but
only if that's always true, not just for myfunction(3,5).
Perhaps the function shouldn't be declared SETOF in the first place?
regards, tom lane
On Thu, 03 Apr 2008 10:33:56 -0400
Tom Lane <tgl@sss.pgh.pa.us> wrote:
Gregory Stark <stark@enterprisedb.com> writes:
You could fix that more cleanly with "ALTER FUNCTION myfunction
ROWS 1" but only if that's always true, not just for
myfunction(3,5).Perhaps the function shouldn't be declared SETOF in the first place?
Does I have to take it as:
if it is not declared as SETOF all optimisation and planning marvels
will happen without I add LIMIT 1?
What about queries?
Is it an information that is used for anything else other than
stopping earlier?
Summarising it up: is it worth to add it here and there as an
optimisation flag?
thanks
--
Ivan Sergio Borgonovo
http://www.webthatworks.it
"Ivan Sergio Borgonovo" <mail@webthatworks.it> writes:
Summarising it up: is it worth to add it here and there as an
optimisation flag?
Probably not. Unless you're not planning on reading all the resulting records
anyways and want the planner to optimize with that assumption.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's PostGIS support!