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

#1

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:

Quote:

Your name : Barrie Slaymaker
Your email address : barries (AT) slaysys (DOT) 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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

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

mailing.database.pgsql-bugs mailing.database.pgsql-bugs

Re: [BUGS] [7.3.3] select with stable function not being optimized to index scan - 12-11-2003 , 08:56 AM