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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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<javascript:_e({}, 'cvml', 'kgrittn@ymail.com');>>
wrote:Joe Love <joe@primoweb.com <javascript:_e({}, 'cvml',
'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.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers