From: | Andy Colson <andy(at)squeakycode(dot)net> |
---|---|
To: | pgsql <pgsql-general(at)postgresql(dot)org> |
Subject: | cannot get stable function to use index |
Date: | 2015-12-29 22:52:21 |
Message-ID: | 56830EA5.7080907@squeakycode.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi all,
I seem to be missing something.
I'm using PG 9.3.9 on Slackware64.
My table:
create table search (
gid integer,
descr text,
search_vec tsvector
);
create index search_key on search using gin(search_vec);
I've put a bunch of data in it, and using to_tsquery uses the index fine:
explain analyze
select *
from search
where search_vec @@ to_tsquery('213 & E & 13 & ST & N');
QUERY PLAN
-----------------------------------------------------------------------------------
Bitmap Heap Scan on search (cost=16.00..17.02 rows=1 width=69)
(actual time=87.493..87.494 rows=1 loops=1)
Recheck Cond: (search_vec @@ to_tsquery('213 & E & 13 & ST & N'::text))
-> Bitmap Index Scan on search_key (cost=0.00..16.00 rows=1
width=0) (actual time=87.478..87.478 rows=1 loops=1)
Index Cond: (search_vec @@ to_tsquery('213 & E & 13 & ST &
N'::text))
Total runtime: 87.554 ms
(5 rows)
Here is the problem, I'd like to use this function from
http://workshops.boundlessgeo.com/tutorial-autocomplete/
CREATE OR REPLACE FUNCTION public.to_tsquery_partial(text)
RETURNS tsquery AS $$
SELECT to_tsquery(
array_to_string(
regexp_split_to_array(
trim($1),E'\\s+'),' & ') ||
CASE WHEN $1 ~ ' $' THEN '' ELSE ':*' END)
$$ LANGUAGE 'sql' STABLE STRICT;
Originally it didn't have "STABLE STRICT", but I added it. Doesn't seem
to matter though. I cannot get this sql to use the index:
explain analyze
select *
from search
where search_vec @@ to_tsquery_partial('213 E 13 ST N')
--------------------------------------------------------------------------
Seq Scan on search (cost=0.00..2526.56 rows=1 width=69) (actual
time=68.033..677.490 rows=1 loops=1)
Filter: (search_vec @@
to_tsquery((array_to_string('{213,E,13,ST,N}'::text[], ' & '::text) ||
':*'::text)))
Rows Removed by Filter: 76427
Total runtime: 677.548 ms
(4 rows)
to_tsquery_partial() calls to_tsquery() and array_to_string(), both of
which I checked, and all of them are marked as stable.
Any hints why this is happening?
Thanks,
-Andy
From | Date | Subject | |
---|---|---|---|
Next Message | David G. Johnston | 2015-12-29 23:13:09 | Re: cannot get stable function to use index |
Previous Message | David G. Johnston | 2015-12-29 22:16:48 | Re: Regex help again (sorry, I am bad at these) |