Thread: Seq scan on zero-parameters function
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.
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.
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
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