From: Octavio Alvarez <alvarezp@alvarezp.ods.org>
To: pgsql-performance@postgresql.org
Subject: Seq scan on zero-parameters function
Date: Thu, 5 Feb 2004 23:19:04 -0800 (PST)
Message-ID: <4207.192.168.0.64.1076051944.squirrel@alvarezp.ods.org> (raw)
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.
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, pgsql-performance@postgresql.org
Subject: Re: Seq scan on zero-parameters function
In-Reply-To: <4207.192.168.0.64.1076051944.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