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

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 : barries(at)slaysys(dot)com
>
>
> 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:
> ---------------------------------------------------------------------
>
>
>
>