Search Postgresql Archives

cannot get stable function to use index

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux