## 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