Обсуждение: Feature request: Optimizer improvement

Поиск
Список
Период
Сортировка

Feature request: Optimizer improvement

От
Joe Love
Дата:
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.


Re: Feature request: Optimizer improvement

От
Kevin Grittner
Дата:
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



Re: Feature request: Optimizer improvement

От
Jim Nasby
Дата:
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…?

Re: Feature request: Optimizer improvement

От
Atri Sharma
Дата:


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

Re: Feature request: Optimizer improvement

От
David Johnston
Дата:
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.