Re: is it helpful for the optimiser/planner to add LIMIT 1

"Ivan Sergio Borgonovo" <mail(at)webthatworks(dot)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