Seq scan on zero-parameters function - Mailing list pgsql-performance

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.