X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 96280D1CA67 for ; Fri, 6 Feb 2004 07:18:53 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 67557-09 for ; Fri, 6 Feb 2004 03:18:46 -0400 (AST) Received: from alvarezp.ods.org (unknown [200.77.201.28]) by svr1.postgresql.org (Postfix) with ESMTP id B9D9AD1D36C for ; Fri, 6 Feb 2004 03:18:44 -0400 (AST) Received: (from apache@localhost) by alvarezp.ods.org (8.11.6/8.11.6) id i167J5B11836; Thu, 5 Feb 2004 23:19:05 -0800 X-Authentication-Warning: alvarezp.ods.org: apache set sender to alvarezp@alvarezp.ods.org using -f Received: from 192.168.0.64 (SquirrelMail authenticated user alvarezp) by alvarezp.ods.org with HTTP; Thu, 5 Feb 2004 23:19:04 -0800 (PST) Message-ID: <4207.192.168.0.64.1076051944.squirrel@alvarezp.ods.org> Date: Thu, 5 Feb 2004 23:19:04 -0800 (PST) Subject: Seq scan on zero-parameters function From: "Octavio Alvarez" To: pgsql-performance@postgresql.org Reply-To: alvarezp@alvarezp.ods.org User-Agent: SquirrelMail/1.4.0-1.7.x MIME-Version: 1.0 Content-Type: text/plain;charset=iso-8859-1 X-Priority: 3 Importance: Normal X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200402/84 X-Sequence-Number: 5608 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.