## 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
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
