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

Your name        : Barrie Slaymaker
Your email address    : barries@slaysys.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:=20
----------------------------------------------------------------------



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

mydb=3D# explain select * from ann where fax_key =3D 1;
                                   QUERY PLAN=20
---------------------------------------------------------------------------=
-----
 Index Scan using ann_fax_key_index on ann (cost=3D0.00..3.11 rows=3D7=20
width=3D1012)
   Index Cond: (fax_key =3D 1)
(2 rows)

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



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:
---------------------------------------------------------------------

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


On Dec 10 2003, Barrie Slaymaker wrote:

> Your name        : Barrie Slaymaker
> Your email address    : barries@slaysys.com
>=20
>=20
> System Configuration
> ---------------------
>   Architecture (example: Intel Pentium)      : Pentium
>=20
>   Operating System (example: Linux 2.0.26 ELF)     : WinXP+cygwin
>=20
>   PostgreSQL version (example: PostgreSQL-7.3.3):   PostgreSQL-7.3.3
>=20
>   Compiler used (example:  gcc 2.95.2)        :
>=20
>=20
> Please enter a FULL description of your problem:
> ------------------------------------------------
>=20
> Stable functions aren't elevated to index scans.
>=20
>=20
>=20
> Please describe a way to repeat the problem.   Please try to provide a
> concise reproducible example, if at all possible:=20
> ----------------------------------------------------------------------
>=20
>=20
>=20
> interchange=3D# create or replace function int8foo() returns int8 as=20
> 'BEGIN RETURN 1; END' language 'plpgsql' stable; CREATE FUNCTION=20
> mydb=3D# explain select * from ann where fax_key =3D int8foo();
>                        QUERY PLAN
> ---------------------------------------------------------
>  Seq Scan on ann  (cost=3D0.00..2524.55 rows=3D8 width=3D1012)
>    Filter: (fax_key =3D int8foo())
> (2 rows)
>=20
> mydb=3D# explain select * from ann where fax_key =3D 1;
>                                    QUERY PLAN=20
> -------------------------------------------------------------------------=
-------
>  Index Scan using ann_fax_key_index on ann (cost=3D0.00..3.11 rows=3D7=20
> width=3D1012)
>    Index Cond: (fax_key =3D 1)
> (2 rows)
>=20
> mydb=3D# create or replace function int8foo() returns int8 as 'select=20
> 1::int8' language 'sql' stable; CREATE FUNCTION mydb=3D# explain select=
=20
> * from ann where fax_key =3D int8foo();
>                        QUERY PLAN
> ---------------------------------------------------------
>  Seq Scan on ann  (cost=3D0.00..2524.55 rows=3D8 width=3D1012)
>    Filter: (fax_key =3D int8foo())
> (2 rows)
> mydb=3D#
>=20
>=20
>=20
> If you know how this problem might be fixed, list the solution below:
> ---------------------------------------------------------------------
>=20
>=20
>=20
>=20
> If you know how this problem might be fixed, list the solution below:
> ---------------------------------------------------------------------
>=20
>=20
>=20
>