The following bug has been logged online:
Bug reference: 5816
Logged by: frank
Email address: frank(at)ros-i(dot)com
PostgreSQL version: 8.3.7
Operating system: linux
Description: index not used in function
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)"
1. table (say thisTable) with a column (say thisColumn) of varchar in mixed
2. w/o primary key on this column
3. an upper case index on this column with text_pattern_ops as the opclass
1. select "thisColumn" from "thisTable" where upper("thisColumn") like
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
SELECT "thisColumn" FROM "thisTable" WHERE upper("thisColumn") like $1
LANGUAGE 'sql' VOLATILE
ALTER FUNCTION get_this_column(text) OWNER TO postgres;
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.
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
Regardless of resolution, I would appreciate a brief response.
pgsql-bugs by date
|Next:||From: jengeno||Date: 2011-01-05 22:02:53|
|Subject: BUG #5817: Language problem...|
|Previous:||From: kapil||Date: 2011-01-05 14:14:29|
|Subject: BUG #5815: connecting to Atllassian products|