Thread: 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!