Date: Tue, 10 Jul 2007 00:38:12 -0600
From: Patrick Clery <Patrick.Clery@gmail.com>
Subject: Index working, but not inside function
To: pgsql-sql@postgresql.org
Reply-to: Patrick.Clery@gmail.com
Message-id: <200707100038.15237.Patrick.Clery@gmail.com>
MIME-version: 1.0
Content-type: text/plain; charset=us-ascii
Content-transfer-encoding: 7bit
Content-disposition: inline
User-Agent: KMail/1.9.7

## 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.72 rows=12194 width=4)
   Recheck Cond: (lower((shortname)::text) ~=~ lower(($1)::text))
   ->  Bitmap Index Scan 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 false
 permalinks          | character varying(255) | 
 pictures_permalinks | character varying(255) | 
 pictures_count      | integer                | not null default 0
 region_code         | character varying(2)   | 
 country_code        | character varying(2)   | 
 is_active           | boolean                | not null default true
 shortname           | 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