From: Octavio Alvarez <alvarezp@alvarezp.ods.org>
To: Tomasz Myrta <jasiek@klaster.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: Seq scan on zero-parameters function
Date: Fri, 6 Feb 2004 00:43:12 -0800 (PST)
Message-ID: <4795.192.168.0.64.1076056992.squirrel@alvarezp.ods.org> (raw)
In-Reply-To: <40234207.6040207@klaster.net>
References: <4207.192.168.0.64.1076051944.squirrel@alvarezp.ods.org>
<40234207.6040207@klaster.net>
Tomasz Myrta said:
> Dnia 2004-02-06 08:19, U¿ytkownik Octavio Alvarez napisa³:
>> In each couple, the first one has a WHERE field = function()
>> condition, just like the second one, but in the form WHERE field =
>> (SELECT function()). In my opinion, both should have the same execution
>> plan, as the function has no parameters and, therefore, is constant.
>
> Nope.
>
> What would you say about function without params returning timeofday()?
> Is it constant?
No... :-P ;-)
> If you are sure, that your function returns constant value - declare it
> as IMMUTABLE. (look at CREATE FUNCTION documentation)
Thanks for the hint.
In fact, my current_period_id() is based on time, but it should be
constant along the query execution. I mean, I don't want some records
filtered with some values and other with other values... I'll have an
uncongruent recordset.
Say SELECT [field-list] FROM [complex-join] WHERE sec = datepart('second',
now()); Now suppose the query takes always more than 1 second because of
the complex-join or whatever reason: I will naver have a congruent
recordset.
IMMUTABLE wouldn't help here, only wrapping the function in a subquery. Is
this expected behavior? Is this standards compliant (if it can be
qualified as such)?
Octavio.
--
Octavio Alvarez.
E-mail: alvarezp@alvarezp.ods.org.
Agradezco que sus correos sean enviados siempre a esta dirección.
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-sql@postgresql.org
Cc: alvarezp@alvarezp.ods.org, jasiek@klaster.net, pgsql-performance@postgresql.org
Subject: Re: Seq scan on zero-parameters function
In-Reply-To: <4795.192.168.0.64.1076056992.squirrel@alvarezp.ods.org>
* 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