TSearch2: find a QUERY that does match a single document

Поиск
Список
Период
Сортировка
От Dmitry Koterov
Тема TSearch2: find a QUERY that does match a single document
Дата
Msg-id d7df81620809120835y3bb7f138u811f119382be2aca@mail.gmail.com
обсуждение исходный текст
Ответы Re: TSearch2: find a QUERY that does match a single document  (Oleg Bartunov <oleg@sai.msu.su>)
Список pgsql-general
Hello.

TSearch2 allows to search a table of tsvectors by a single tsquery.
I need to solve the reverse problem.

I have a large table of tsquery. I need to find all tsqueries in that table that match a single document tsvector:

CREATE TABLE "test"."test_tsq" (
  "id" SERIAL,
  "q" TSQUERY NOT NULL,
  CONSTRAINT "test_tsq_pkey" PRIMARY KEY("id")
);

insert into test.test_tsq(q)
select to_tsquery(g || 'x' || g) from generate_series(100000, 900000) as g;

explain analyze
select * from test.test_tsq
where to_tsvector('400000x400000') @@ q

This gets a strange explain analyze:

QUERY PLAN
Seq Scan on test_tsq  (cost=0.00..17477.01 rows=800 width=36) (actual time=68.698..181.458 rows=1 loops=1)
  Filter: ('''400000x400000'':1'::tsvector @@ q)
Total runtime: 181.484 ms

No matter if I use GIST index on test_tsq.q or not: the explain analyze result is the same.
So, why "rows=800"? The table contains much more rows...

В списке pgsql-general по дате отправления:

Предыдущее
От: Richard Huxton
Дата:
Сообщение: Re: Converting string to IN query
Следующее
От: Sam Mason
Дата:
Сообщение: Re: Converting string to IN query