pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedIndex working, but not inside function
2+ messages / 2 participants
[nested] [flat]
* Index working, but not inside function
@ 2007-07-10 06:38 Patrick Clery <Patrick.Clery@gmail.com>
2007-07-10 17:24 ` Re: Index working, but not inside function Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 2+ messages in thread
From: Patrick Clery @ 2007-07-10 06:38 UTC (permalink / raw)
To: 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.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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Index working, but not inside function
2007-07-10 06:38 Index working, but not inside function Patrick Clery <Patrick.Clery@gmail.com>
@ 2007-07-10 17:24 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2007-07-10 17:24 UTC (permalink / raw)
To: Patrick.Clery@gmail.com; +Cc: pgsql-sql
Patrick Clery <Patrick.Clery@gmail.com> writes:
> When I execute the function the query stalls and is apparently not taking
> advantage of the index.
Your example doesn't demonstrate any such thing; what seems more likely
is that there are a whole lot of rows matching 'United States' and so
the query takes longer than you expect. What would be convincing is
if you had actually *executed* the prepared query, on the same data not
different data, and timed that.
regards, tom lane
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2007-07-10 17:24 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-07-10 06:38 Index working, but not inside function Patrick Clery <Patrick.Clery@gmail.com>
2007-07-10 17:24 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox