tsearch2 query question - Mailing list pgsql-sql

From roy simkes
Subject tsearch2 query question
Date
Msg-id BAY138-W145CBF45333AF9859555ECA1CB0@phx.gbl
Whole thread Raw
List pgsql-sql
Hi, <br /><br />SELECT contentid, title, (rank(to_tsvector(body),q) + <br />rank(to_tsvector(title),q) +
rank(to_tsvector(subtitle),q))AS Score <br />FROM content, to_tsquery('parkyeri') AS q <br />WHERE statusid = 1 <br
/>ANDispublished = 1 <br />AND (to_tsvector(body) @@ q <br /> OR to_tsvector(title) @@ q <br /> OR
to_tsvector(subtitle)@@ q ) <br />ORDER BY Score <br /><br />I have such a query. I'm not very sure if it will work but
that'snot <br />the part of the question. As you see I'm using a lot to_tsvector() <br />function. Which I believe it
willnot be good for the performance. So I <br />thought changing my query to something like this: <br /><br />SELECT
contentid,title, (rank(fts_body, q) + rank(fts_title,q) + <br />rank(fts_subtitle,q) ) AS Score <br />FROM content, <br
/>to_tsquery('search & string') AS q, <br /> to_tsvector(body) AS fts_body, <br /> to_tsvector(title) AS fts_title,
<br/> to_tsvector(subtitle) AS fts_subtitle <br />WHERE statusid = 1 <br />AND ispublished = 1 <br />AND ( fts_body @@
q<br /> OR fts_title @@ q <br /> OR fts_subtitle @@ q ) <br />ORDER BY Score <br /><br />So when I have changed to
this,will the to_tsvector part will be <br />available for every row? Or will it be just computed once? I mean in the
<br/>first query where part is executed for every row, so I'm sure that it <br />will be evaluated for all the rows.
Butwhen I put that in the from part <br />will it compute the value once and will use the same value for all the <br
/>whereclauses? If that's the case what will be the value of fts_body? <br />The tsvector of every row's data or just
onerow's data? <br /><br />thank you for your time and patience <br /><br />roy simkes <br /><br /><br /><hr />Invite
yourmail contacts to join your friends list with Windows Live Spaces. It's easy! <a
href="http://spaces.live.com/spacesapi.aspx?wx_action=create&wx_url=/friends.aspx&mkt=en-us"target="_new">Try
it!</a>

pgsql-sql by date:

Previous
From: "Marc Mamin"
Date:
Subject: Re: EXECUTE 'EXECUTE ...' or how to use dynamic prepared statements ?
Next
From: "Sabin Coanda"
Date:
Subject: Re: ISO time zone format