From: | "Dmitry Koterov" <dmitry(at)koterov(dot)ru> |
---|---|
To: | "Postgres General" <pgsql-general(at)postgresql(dot)org> |
Subject: | TSearch2: find a QUERY that does match a single document |
Date: | 2008-09-12 15:35:18 |
Message-ID: | d7df81620809120835y3bb7f138u811f119382be2aca@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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...
From | Date | Subject | |
---|---|---|---|
Next Message | Sam Mason | 2008-09-12 15:57:45 | Re: Converting string to IN query |
Previous Message | Richard Huxton | 2008-09-12 15:04:18 | Re: Converting string to IN query |