From: Julius Tuskenis <julius@nsoft.lt>
To: pgsql-performance@postgresql.org
Subject: Re: unexpected stable function behavior
Date: Tue, 15 Mar 2011 10:04:31 +0200
Message-ID: <4D7F1D8F.2080005@nsoft.lt> (raw)
In-Reply-To: <AANLkTimm2ufjWUqWz-XXjsMFhebdHAxTx3ZnMOVsStxU@mail.gmail.com>
References: <4D78FB98.7090609@nsoft.lt>
<AANLkTimTR_f98H4+h57iW6-S=BjZaMTSQ6VznRgXU5Ly@mail.gmail.com>
<4D7DD5CF.4080502@nsoft.lt>
<AANLkTimm2ufjWUqWz-XXjsMFhebdHAxTx3ZnMOVsStxU@mail.gmail.com>
Hello,
2011.03.14 15:41, Merlin Moncure rašė:
> WITH results as
> (
> SELECT distinct price_id as price_id
> FROM ticket_price
> JOIN ticket_has_ticket_price ON (price_id = thtp_price_id)
> WHERE price_event_id = 7820 and (current_timestamp>= price_date AND
> current_timestamp<= price_date_till)
> ) as qq
> )
> SELECT web_select_extra_price(price_id, 7820, 1) from results;
>
Thank you Merlin for your help. I have updated my function to use CTE.
Although there was no performance improvement (I had the select with
function using distinct values joined earlyer) it's good to know the
optimizer will not change the way I want the query to be executed. Thank
you once again.
> CTEs are great btw, I'd start learning them immediately.
I am going to do that.
> IMNSHO, this (uncontrolled number of function executions when run via
> field select list) is a common gotcha w/postgres and a FAQ. Also the
> documentation is not very helpful on this point...
Yes, I totally agree with you. I think sentence like "Although function
is marked as STABLE or IMMUTABLE the optimizer is not obliged to take
advantage of these properties." (sorry for my English).
> do you agree CTE is the right way to advise handling this problem...is it worth further
> notation?
Yes, the CTE worked fine for me. Reading some more on this topic I found
some comments that the optimizer has no possibility to know how many
times the function is to be called in such queries (without actually
executing the query), so there is no way to determine the cost. That
explains why not the optimal plan was chosen to my query.
--
Julius Tuskenis
Programavimo skyriaus vadovas
UAB nSoft
mob. +37068233050
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: julius@nsoft.lt
Subject: Re: unexpected stable function behavior
In-Reply-To: <4D7F1D8F.2080005@nsoft.lt>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox