Thread: Feature request: Optimizer improvement
In postgres 9.2 I have a function that is relatively expensive. When I write a query such as:
select expensive_function(o.id),o.* from offeirng o where valid='Y' order by name limit 1;
the query runs slow and appears to be running the function on each ID, which in this case should be totally unnecessary as it really only needs to run on 1 row.
When I rewrite the query like so:
select expensive_function(o.id), o.*
from (select *offering where valid='Y' order by name limit 1) o;
the expensive function only runs once and thus, much faster. I would think that the optimizer could handle this situation, especially when limit or offset is used and the expensive function is not used in a group by, order by or where.
Joe Love <joe@primoweb.com> wrote: > In postgres 9.2 I have a function that is relatively expensive. What did you specify in the COST clause on the CREATE FUNCTION statement? -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On Oct 31, 2013, at 2:57 PM, Kevin Grittner <kgrittn@ymail.com> wrote:
Joe Love <joe@primoweb.com> wrote:In postgres 9.2 I have a function that is relatively expensive.
What did you specify in the COST clause on the CREATE FUNCTION
statement?
Should that really matter in this case? ISTM we should always handle LIMIT before moving on to the SELECT clause…?
On Friday, November 1, 2013, Jim Nasby wrote:
On Oct 31, 2013, at 2:57 PM, Kevin Grittner <kgrittn@ymail.com> wrote:Joe Love <joe@primoweb.com> wrote:In postgres 9.2 I have a function that is relatively expensive.
What did you specify in the COST clause on the CREATE FUNCTION
statement?Should that really matter in this case? ISTM we should always handle LIMIT before moving on to the SELECT clause…?
+1
It's sounds straight logical
--
Regards,
Atri
l'apprenant
Jim Nasby-2 wrote > Should that really matter in this case? ISTM we should always handle LIMIT > before moving on to the SELECT clause…? SELECT generate_series(1,10) LIMIT 1 David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Feature-request-Optimizer-improvement-tp5776589p5776707.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.