Re: [BUGS] [7.3.3] select with stable function not being optimized to index scan

[Barrie Slaymaker]

The underlying problem turned out to be using an function returning 
int8 on a column of type int. When the function is altered to return 
int, an index scan is used.

On Dec 10 2003, Barrie Slaymaker wrote:

Your name               : Barrie Slaymaker
Your email address      : [EMAIL PROTECTED]
System Configuration
---------------------
  Architecture (example: Intel Pentium)         : Pentium
  Operating System (example: Linux 2.0.26 ELF) 	: WinXP+cygwin

  PostgreSQL version (example: PostgreSQL-7.3.3):   PostgreSQL-7.3.3

  Compiler used (example:  gcc 2.95.2)		:

Please enter a FULL description of your problem:
------------------------------------------------
Stable functions aren't elevated to index scans.



Please describe a way to repeat the problem.   Please try to provide a
concise reproducible example, if at all possible: 
----------------------------------------------------------------------



interchange=# create or replace function int8foo() returns int8 as 
'BEGIN RETURN 1; END' language 'plpgsql' stable; CREATE FUNCTION 
mydb=# explain select * from ann where fax_key = int8foo();
                       QUERY PLAN
---------------------------------------------------------
 Seq Scan on ann  (cost=0.00..2524.55 rows=8 width=1012)
   Filter: (fax_key = int8foo())
(2 rows)

mydb=# explain select * from ann where fax_key = 1;
                                   QUERY PLAN 
--------------------------------------------------------------------------------
 Index Scan using ann_fax_key_index on ann (cost=0.00..3.11 rows=7 
width=1012)
   Index Cond: (fax_key = 1)
(2 rows)

mydb=# create or replace function int8foo() returns int8 as 'select 
1::int8' language 'sql' stable; CREATE FUNCTION mydb=# explain select 
* from ann where fax_key = int8foo();
                       QUERY PLAN
---------------------------------------------------------
 Seq Scan on ann  (cost=0.00..2524.55 rows=8 width=1012)
   Filter: (fax_key = int8foo())
(2 rows)
mydb=#



If you know how this problem might be fixed, list the solution below:
---------------------------------------------------------------------


If you know how this problem might be fixed, list the solution below:
---------------------------------------------------------------------



---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
              http://archives.postgresql.org