Re: Q: text query search and

On Thu, Jul 23, 2015 at 2:55 AM, amihay gonen <agonenil(at)gmail(dot)com> wrote:

> Hi I'm trying to implement a text search in PG .
>
> My goal to enable the user search on several columns also on partial words.
>
> here is sample code :
> create table test_test( text_data tsvector, text_a varchar,text_b varchar);
>
> insert into test_test(text_a,text_b) select 'name 10.10.2.3 ','name3 name'
> from generate_series(1,500);
> update test_test set text_data=to_tsvector(text_a||' '||text_b);
> CREATE INDEX test_test_idx ON test_test USING gin(text_data);
>
> explain ANALYZE select * from test_test where text_data@
> @plainto_tsquery('name');
>
> my questions are :
> 1. why the index is not used (I guess it is related to the way the data is
> generated)
>

It returns the entire table, so there is no point in using an index. Yes,
it is the way it is generated, the same data repeated over and over is not
very realistic. If you just want random text, I use md5(random()::text).
But if you want text that looks vaguely like English, I don't have a nice
trick for that. Maybe load the sgml files into a table.

> 2, how can I use pg_trgm with ts_vector to enable to answer query like
> 10.10 or nam ?
> the idea is to use the gin index , maybe there are other option without
> using pg_trgm?
>

Do you mean:

WHERE text_a LIKE '%10.10%' or text_a LIKE '%nam%' ?

With the or, that going to be hard to optimize.

Anyway, pg_tgrm requires its own special GIN index, it can't piggy back on
the tsvector GIN index.

CREATE INDEX whatever ON test_test USING gin(text_a gin_trgm_ops);

or

CREATE INDEX whatever ON test_test USING gin((text_a||' '||text_b)
gin_trgm_ops);

But, LIKE '%10.10%' is going to be tough for a pg_trgm index to help with,
unless you compile your own code after removing "#define KEEPONLYALNUM"

Cheers,

Jeff