Full text search, SQL functions, and the planner

[Marc Dahn <dahn@xxxxxxx>]

Hi,

To maintain an externally defined interface, I'd like to define a
function hasword(haystack, needle) that (essentially) returns 1 when
to_tsvector(haystack) @@ to_tsquery(needle), 0 otherwise.

I've tried

	CREATE OR REPLACE FUNCTION ivo_hasword(haystack TEXT, needle TEXT)
	RETURNS INTEGER AS $func$
		SELECT CASE WHEN to_tsvector('english', $1) @@ plainto_tsquery($2) 
			THEN 1 
			ELSE 0 
		END
	$func$ LANGUAGE SQL STABLE;

It seems the planner inlines the function body, as intended:

# explain select * from rr.resource where 1=ivo_hasword(res_title, 'optical');
...
 Seq Scan on resource  (cost=0.00..2269.72 rows=69 width=924)
   Filter: (1 = CASE WHEN (to_tsvector('english'::regconfig, res_title) @@ plainto_tsquery('optical'::text)) THEN 1 ELSE 0 END)

-- but it doesn't use the index on res_title, as it could, as
exhibited by this equivalent query:

# explain select * from rr.resource where to_tsvector('english'::regconfig, res_title) @@ plainto_tsquery('optical'::text);
...
 Bitmap Heap Scan on resource  (cost=21.96..731.76 rows=252 width=924)
   Recheck Cond: (to_tsvector('english'::regconfig, res_title) @@ plainto_tsquery('optical'::text))
   ->  Bitmap Index Scan on resource_res_title  (cost=0.00..21.89 rows=252 width=0)
         Index Cond: (to_tsvector('english'::regconfig, res_title) @@ plainto_tsquery('optical'::text))

Indeed, the index isn't used either when I'm writing the 1 = CASE WHEN
statement into the query directly (this is on postgres 9.1).

Sure enough, if I define the function like this:

CREATE OR REPLACE FUNCTION bool_hasword(haystack TEXT, needle TEXT)
	RETURNS BOOLEAN AS $func$
		SELECT to_tsvector('english', $1) @@ plainto_tsquery($2) 
	$func$ LANGUAGE SQL STABLE;

(i.e., returning a boolean instead of the lousy integer), the index
is used.

So -- is there a way to let the planner look through the CASE?  Or
some way of turning the boolean to an integer that the planner can
see through?  The CAST(... AS INTEGER) that fortunately would even
produce the right numbers appears to be opaque to the planner.


Anticipating the sane and logical answer: The boolean function
doesn't really help me; this is about handing through that function
directly to ADQL (http://www.ivoa.net/documents/latest/ADQL.html)
as a user defined function, and those user defined functions cannot
be boolean-valued.

Cheers,

           Markus




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