The following bug has been logged online: Bug reference: 5816 Logged by: frank Email address: fr...@ros-i.com PostgreSQL version: 8.3.7 Operating system: linux Description: index not used in function Details:
Linux: Linux <server name> 2.6.24-24-server #1 SMP Tue Jul 7 20:21:17 UTC 2009 i686 GNU/Linux Postgres: "PostgreSQL 8.3.7 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.2.4 (Ubuntu 4.2.4-1ubuntu3)" Table Description: 1. table (say thisTable) with a column (say thisColumn) of varchar in mixed case data 2. w/o primary key on this column 3. an upper case index on this column with text_pattern_ops as the opclass Query Description: 1. select "thisColumn" from "thisTable" where upper("thisColumn") like 'ABC%' 2. select * from get_this_column('ABC') where get_this_column() is defined as: CREATE OR REPLACE FUNCTION get_this_column(c text) RETURNS SETOF text AS $BODY$ SELECT "thisColumn" FROM "thisTable" WHERE upper("thisColumn") like $1 limit 10; $BODY$ LANGUAGE 'sql' VOLATILE COST 100 ROWS 1000; ALTER FUNCTION get_this_column(text) OWNER TO postgres; Issue Description: The plain query (1) uses the upper case index. The function (2), even though having the same underlying query, does not seem to be able to use index. The wild card can either be in the argument or appended at the query inside the function, the result is the same. Further Question: Why can't force the use of index (even if the plan results in worse performance when user desires)? The index HINT (of ORACLE say) is a bad concept and a horrible design idea. The user should be allowed to force the use of any index regardless of performance. Anyway, the user should know what he/she is doing. Besides, an explain could show which, the system plan or the user's, is better. The combination described here is perhaps not normal. I have not tested it with any other version. Likely the same is true for all versions including 9. Regardless of resolution, I would appreciate a brief response. Thanks -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs