BUG #5816: index not used in function

From: "frank" <frank(at)ros-i(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #5816: index not used in function
Date: 2011-01-05 17:51:47
Message-ID: 201101051751.p05HplaM055549@wwwmaster.postgresql.org
Views: Raw Message | Whole Thread | Download mbox
Lists: pgsql-bugs

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)"

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
2. select * from get_this_column('ABC')
where get_this_column() is defined as:

CREATE OR REPLACE FUNCTION get_this_column(c text)
SELECT "thisColumn" FROM "thisTable" WHERE upper("thisColumn") like $1
limit 10;
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

Regardless of resolution, I would appreciate a brief response.



Browse pgsql-bugs by date

  From Date Subject
Next Message jengeno 2011-01-05 22:02:53 BUG #5817: Language problem...
Previous Message kapil 2011-01-05 14:14:29 BUG #5815: connecting to Atllassian products