Index working, but not inside function - Mailing list pgsql-sql

## THE PROBLEM

I'm trying to write a function that will select the ID of a row from a very 
large table (2M rows) using an index (places_autocomplete_idx).
When I execute the function the query stalls and is apparently not taking 
advantage of the index. However, executing the same query outside
of the function uses the index.

When using a prepared statement, the planner uses the index if I use the "~=~" 
operator instead of the "LIKE" operator. Using a different
operator within the function makes no difference whatsoever.

## THE FUNCTION

CREATE OR REPLACE FUNCTION pop_country_place_id(varchar) RETURNS INTEGER AS $$   SELECT id FROM places WHERE
LOWER(shortname)LIKE LOWER($1);
 
$$ LANGUAGE sql;

## PLANNER USES INDEX WHEN "~=~" OPERATOR IS USED INSTEAD OF "LIKE" WITH 
PREPARED STATEMENT

pop=> PREPARE pop_plan(varchar) AS SELECT id FROM places WHERE 
LOWER(shortname) LIKE LOWER($1::varchar);
PREPARE
Time: 0.295 ms
pop=> 
pop=> EXPLAIN EXECUTE pop_plan('Canada');                                                      QUERY PLAN
           
 
---------------------------------------------------------------Seq Scan on places  (cost=0.00..214301.44 rows=12194
width=4) Filter: (lower((shortname)::text) ~~ lower(($1)::text))
 
(2 rows)

Time: 0.310 ms
pop=> DEALLOCATE pop_plan;
DEALLOCATE
Time: 0.131 ms
pop=> PREPARE pop_plan(varchar) AS SELECT id FROM places WHERE 
LOWER(shortname) ~=~ LOWER($1::varchar);
PREPARE
Time: 0.330 ms
pop=> EXPLAIN EXECUTE pop_plan('Canada');                                        QUERY PLAN
           
 
--------------------------------------------------------------------------------------------Bitmap Heap Scan on places
(cost=375.31..38438.72rows=12194 width=4)  Recheck Cond: (lower((shortname)::text) ~=~ lower(($1)::text))  ->  Bitmap
IndexScan on places_autocomplete_idx  (cost=0.00..372.26 
 
rows=12194 width=0)        Index Cond: (lower((shortname)::text) ~=~ lower(($1)::text))
(4 rows)

Time: 0.318 ms

## PLANNER NOT USING INDEX (places_autocomplete_idx) INSIDE OF FUNCTION 
(TESTED WITH BOTH "~=~" AND "LIKE" OPERATORS) [sql]

pop=> CREATE OR REPLACE FUNCTION pop_country_place_id(varchar) RETURNS INTEGER 
AS $$
pop$>     SELECT id FROM places WHERE LOWER(shortname) ~=~ LOWER($1::varchar);
pop$> $$ LANGUAGE sql;
CREATE FUNCTION
Time: 29.310 ms
pop=> SELECT pop_country_place_id('United States');
* * * STALLS * * *
Cancel request sent
ERROR:  canceling statement due to user request
CONTEXT:  SQL function "pop_country_place_id" statement 1

## THE TABLE

pop=> \d places                                      Table "public.places"      Column        |          Type
|                     Modifiers                      
 
---------------------+------------------------+-----------------------------------------------------id
|integer                | not null default 
 
nextval('places_id_seq'::regclass)name                | character varying(255) | permalink           | character
varying(255)| parent_id           | integer                | abreviation         | character varying(3)   | type
       | character varying(255) | is_approved         | boolean                | not null default falsepermalinks
  | character varying(255) | pictures_permalinks | character varying(255) | pictures_count      | integer
| not null default 0region_code         | character varying(2)   | country_code        | character varying(2)   |
is_active          | boolean                | not null default trueshortname           | character varying(255) | 
 
Indexes:   "places_pkey" PRIMARY KEY, btree (id)   "places_autocomplete_idx" btree (lower(shortname::text) 
varchar_pattern_ops)   "places_idx_abreviation" btree (abreviation) WHERE "type"::text 
= 'Region'::text   "places_parent_idx" btree (parent_id)   "places_permalinks_idx" btree (lower(permalinks::text))
"places_pictures_permalinks_idx"btree (lower(pictures_permalinks::text))   "places_region_idx" btree (country_code,
region_code)WHERE "type"::text 
 
= 'Region'::text   "regions_idx" btree (country_code, region_code) WHERE "type"::text 
= 'Region'::text
Foreign-key constraints:   "places_regions_fkey" FOREIGN KEY (country_code, region_code) REFERENCES 
regions(country_code, region_code) ON UPDATE CASCADE