From: roy simkes <roysimkes@hotmail.com>
To: pgsql-sql@postgresql.org
Subject: tsearch2 query question
Date: Wed, 5 Sep 2007 08:33:26 +0000
Message-ID: <BAY138-W145CBF45333AF9859555ECA1CB0@phx.gbl> (raw)
Hi,
SELECT contentid, title, (rank(to_tsvector(body),q) +
rank(to_tsvector(title),q) + rank(to_tsvector(subtitle),q)) AS Score
FROM content, to_tsquery('parkyeri') AS q
WHERE statusid = 1
AND ispublished = 1
AND (to_tsvector(body) @@ q
OR to_tsvector(title) @@ q
OR to_tsvector(subtitle) @@ q )
ORDER BY Score
I have such a query. I'm not very sure if it will work but that's not
the part of the question. As you see I'm using a lot to_tsvector()
function. Which I believe it will not be good for the performance. So I
thought changing my query to something like this:
SELECT contentid, title, (rank(fts_body, q) + rank(fts_title,q) +
rank(fts_subtitle,q) ) AS Score
FROM content,
to_tsquery('search & string') AS q,
to_tsvector(body) AS fts_body,
to_tsvector(title) AS fts_title,
to_tsvector(subtitle) AS fts_subtitle
WHERE statusid = 1
AND ispublished = 1
AND ( fts_body @@ q
OR fts_title @@ q
OR fts_subtitle @@ q )
ORDER BY Score
So when I have changed to this, will the to_tsvector part will be
available for every row? Or will it be just computed once? I mean in the
first query where part is executed for every row, so I'm sure that it
will be evaluated for all the rows. But when I put that in the from part
will it compute the value once and will use the same value for all the
where clauses? If that's the case what will be the value of fts_body?
The tsvector of every row's data or just one row's data?
thank you for your time and patience
roy simkes
_________________________________________________________________
Invite your mail contacts to join your friends list with Windows Live Spaces. It's easy!
http://spaces.live.com/spacesapi.aspx?wx_action=create&wx_url=/friends.aspx&mkt=en-us=
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: roysimkes@hotmail.com
Subject: Re: tsearch2 query question
In-Reply-To: <BAY138-W145CBF45333AF9859555ECA1CB0@phx.gbl>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox