The following bug has been logged online:
Bug reference: 5816
Logged by: frank
Email address: frank@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
