pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSeq scan on zero-parameters function
7+ messages / 4 participants
[nested] [flat]
* Seq scan on zero-parameters function
@ 2004-02-06 07:19 Octavio Alvarez <alvarezp@alvarezp.ods.org>
2004-02-06 08:43 ` Re: Seq scan on zero-parameters function Octavio Alvarez <alvarezp@alvarezp.ods.org>
2004-02-06 08:50 ` Re: Seq scan on zero-parameters function Richard Huxton <dev@archonet.com>
0 siblings, 2 replies; 7+ messages in thread
From: Octavio Alvarez @ 2004-02-06 07:19 UTC (permalink / raw)
To: pgsql-performance@postgresql.org
Hi!
I'd like to know if this is expected behavior. These are two couples of
queries. 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.
I'm concerned about this, because the second form looks like a workaround.
*** TESTED IN: PostgreSQL 7.4.1 on i686-pc-cygwin ***
pgdb=# explain analyze select count(*) from t_students where period =
(select current_period_id());
QUERY
PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=127.84..127.84 rows=1 width=0) (actual time=1.000..1.000
rows=1 loops=1)
InitPlan
-> Result (cost=0.00..0.01 rows=1 width=0) (actual
time=1.000..1.000 rows=1 loops=1)
-> Index Scan using i_t_students__period on t_students
(cost=0.00..127.71 rows=44 width=0) (actual time=1.000..1.000 rows=21
loop=1)
Index Cond: (period = $0)
Total runtime: 1.000 ms
(6 rows)
pgdb=# explain analyze select count(*) from t_students where period =
(select current_period_id());
QUERY
PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=127.84..127.84 rows=1 width=0) (actual time=1.000..1.000
rows=1 loops=1)
InitPlan
-> Result (cost=0.00..0.01 rows=1 width=0) (actual
time=1.000..1.000 rows=1 loops=1)
-> Index Scan using i_t_students__period on t_students
(cost=0.00..127.71 rows=44 width=0) (actual time=1.000..1.000 rows=21
loop=1)
Index Cond: (period = $0)
Total runtime: 1.000 ms
(6 rows)
pgdb=# select version();
version
---------------------------------------------------------------------------------------
PostgreSQL 7.4.1 on i686-pc-cygwin, compiled by GCC gcc (GCC) 3.3.1
(cygming special)
(1 row)
pgdb=#
*** TESTED IN: PostgreSQL 7.3.4 on i386-redhat-linux-gnu ***
pgdb=# explain analyze select count(*) from t_students where period =
current_period_id();
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=182.32..182.32 rows=1 width=0) (actual
time=49077.38..49077.38 rows=1 loops=1)
-> Seq Scan on t_students (cost=0.00..182.22 rows=43 width=0) (actual
time=17993.89..49077.13 rows=21 loops=1)
Filter: (period = current_period_id())
Total runtime: 49077.61 msec
(4 rows)
pgdb=# explain analyze select count(*) from t_students where period =
(select current_period_id());
QUERY
PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=125.19..125.19 rows=1 width=0) (actual
time=131.59..131.60 rows=1 loops=1)
InitPlan
-> Result (cost=0.00..0.01 rows=1 width=0) (actual
time=41.05..41.06 rows=1 loops=1)
-> Index Scan using i_t_students__period on t_students
(cost=0.00..125.08 rows=43 width=0) (actual time=131.28..131.48 rows=21
loops=1)
Index Cond: (period = $0)
Total runtime: 131.95 msec
(6 rows)
pgdb=# select version();
version
-----------------------------------------------------------------
PostgreSQL 7.3.4 on i386-redhat-linux-gnu, compiled by GCC 2.96
(1 row)
--
Octavio Alvarez.
E-mail: alvarezp@alvarezp.ods.org.
Agradezco que sus correos sean enviados siempre a esta dirección.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Seq scan on zero-parameters function
2004-02-06 07:19 Seq scan on zero-parameters function Octavio Alvarez <alvarezp@alvarezp.ods.org>
@ 2004-02-06 08:43 ` Octavio Alvarez <alvarezp@alvarezp.ods.org>
2004-02-06 08:55 ` Re: [PERFORM] Seq scan on zero-parameters function Tomasz Myrta <jasiek@klaster.net>
1 sibling, 1 reply; 7+ messages in thread
From: Octavio Alvarez @ 2004-02-06 08:43 UTC (permalink / raw)
To: Tomasz Myrta <jasiek@klaster.net>; +Cc: pgsql-performance@postgresql.org
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [PERFORM] Seq scan on zero-parameters function
2004-02-06 07:19 Seq scan on zero-parameters function Octavio Alvarez <alvarezp@alvarezp.ods.org>
2004-02-06 08:43 ` Re: Seq scan on zero-parameters function Octavio Alvarez <alvarezp@alvarezp.ods.org>
@ 2004-02-06 08:55 ` Tomasz Myrta <jasiek@klaster.net>
2004-02-06 17:24 ` Re: [PERFORM] Seq scan on zero-parameters function Octavio Alvarez <alvarezp@alvarezp.ods.org>
0 siblings, 1 reply; 7+ messages in thread
From: Tomasz Myrta @ 2004-02-06 08:55 UTC (permalink / raw)
To: pgsql-sql
Dnia 2004-02-06 09:43, Użytkownik Octavio Alvarez napisał:
> 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.
Well - you didn't read the chapter I noticed you, did you?
Look at function now(). It returns always the same value inside
transaction. If your current_period_id() works the same way as now()
then declare it as STABLE.
Regards,
Tomasz Myrta
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [PERFORM] Seq scan on zero-parameters function
2004-02-06 07:19 Seq scan on zero-parameters function Octavio Alvarez <alvarezp@alvarezp.ods.org>
2004-02-06 08:43 ` Re: Seq scan on zero-parameters function Octavio Alvarez <alvarezp@alvarezp.ods.org>
2004-02-06 08:55 ` Re: [PERFORM] Seq scan on zero-parameters function Tomasz Myrta <jasiek@klaster.net>
@ 2004-02-06 17:24 ` Octavio Alvarez <alvarezp@alvarezp.ods.org>
0 siblings, 0 replies; 7+ messages in thread
From: Octavio Alvarez @ 2004-02-06 17:24 UTC (permalink / raw)
To: Tomasz Myrta <jasiek@klaster.net>; +Cc: pgsql-sql
Tomasz Myrta said:
> Dnia 2004-02-06 09:43, U¿ytkownik Octavio Alvarez napisa³:
>> 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.
>
> Well - you didn't read the chapter I noticed you, did you?
Huummm.. No... :-$
But now I did. Although the chapter makes it look as "how will the
optimizer think the function behaves", not "how the function actually
behaves".
But thanks. It's a lot clearer now. I assume that if I want to make
"timeofday" have a stable-behavior, I must enclose it in a sub-query. Am I
right?
--
Octavio Alvarez.
E-mail: alvarezp@alvarezp.ods.org.
Agradezco que sus correos sean enviados siempre a esta dirección.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Seq scan on zero-parameters function
2004-02-06 07:19 Seq scan on zero-parameters function Octavio Alvarez <alvarezp@alvarezp.ods.org>
@ 2004-02-06 08:50 ` Richard Huxton <dev@archonet.com>
2004-02-06 14:55 ` Re: Seq scan on zero-parameters function Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 7+ messages in thread
From: Richard Huxton @ 2004-02-06 08:50 UTC (permalink / raw)
To: alvarezp@alvarezp.ods.org; pgsql-performance@postgresql.org
On Friday 06 February 2004 07:19, Octavio Alvarez wrote:
> Hi!
>
> I'd like to know if this is expected behavior. These are two couples of
> queries. 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.
Not necessarily constant - think about random() or timeofday().
Have you set the attributes on your function?
http://www.postgresql.org/docs/7.4/static/sql-createfunction.html
> pgdb=# explain analyze select count(*) from t_students where period =
> (select current_period_id());
It's not entirely clear to me why this form is different from the other form
though.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Seq scan on zero-parameters function
2004-02-06 07:19 Seq scan on zero-parameters function Octavio Alvarez <alvarezp@alvarezp.ods.org>
2004-02-06 08:50 ` Re: Seq scan on zero-parameters function Richard Huxton <dev@archonet.com>
@ 2004-02-06 14:55 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 7+ messages in thread
From: Tom Lane @ 2004-02-06 14:55 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: alvarezp@alvarezp.ods.org; pgsql-performance@postgresql.org
Richard Huxton <dev@archonet.com> writes:
> It's not entirely clear to me why this form is different from the other form
> though.
The code that checks for expressions containing unstable functions
doesn't look inside sub-selects. Arguably this is a bug, but people
were relying on that behavior way back before we had these nice
STABLE/IMMUTABLE tags for functions. I'm hesitant to change it for
fear of breaking people's apps.
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* [Fwd: Re: [PERFORM] Seq scan on zero-parameters function]
@ 2004-02-06 07:28 Tomasz Myrta <jasiek@klaster.net>
0 siblings, 0 replies; 7+ messages in thread
From: Tomasz Myrta @ 2004-02-06 07:28 UTC (permalink / raw)
To: alvarezp@alvarezp.ods.org; +Cc: pgsql-sql
Dnia 2004-02-06 08:19, Użytkownik Octavio Alvarez napisał:
> Hi!
>
> I'd like to know if this is expected behavior. These are two couples of
> queries. 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?
If you are sure, that your function returns constant value - declare it
as IMMUTABLE. (look at CREATE FUNCTION documentation)
Regards,
Tomasz Myrta
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2004-02-06 17:24 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-02-06 07:19 Seq scan on zero-parameters function Octavio Alvarez <alvarezp@alvarezp.ods.org>
2004-02-06 08:43 ` Octavio Alvarez <alvarezp@alvarezp.ods.org>
2004-02-06 08:55 ` Tomasz Myrta <jasiek@klaster.net>
2004-02-06 17:24 ` Octavio Alvarez <alvarezp@alvarezp.ods.org>
2004-02-06 08:50 ` Richard Huxton <dev@archonet.com>
2004-02-06 14:55 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-02-06 07:28 [Fwd: Re: [PERFORM] Seq scan on zero-parameters function] Tomasz Myrta <jasiek@klaster.net>
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