Received: from localhost (unknown [200.46.204.191])
	by postgresql.org (Postfix) with ESMTP id 85E349F9E20
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Wed,  5 Sep 2007 05:33:32 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024)
	with ESMTP id 55662-02 for <pgsql-sql-postgresql.org@postgresql.org>;
	Wed,  5 Sep 2007 05:33:28 -0300 (ADT)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5
Received: from bay0-omc2-s35.bay0.hotmail.com (bay0-omc2-s35.bay0.hotmail.com
	[65.54.246.171])
	by postgresql.org (Postfix) with ESMTP id 355219F9DF9
	for <pgsql-sql@postgresql.org>; Wed,  5 Sep 2007 05:33:28 -0300 (ADT)
Received: from BAY138-W14 ([64.4.49.49]) by bay0-omc2-s35.bay0.hotmail.com
	with Microsoft SMTPSVC(6.0.3790.3959);
	Wed, 5 Sep 2007 01:33:27 -0700
Message-ID: <BAY138-W145CBF45333AF9859555ECA1CB0@phx.gbl>
Content-Type: multipart/alternative;
	boundary="_68c8bdb3-3163-4f0b-9e5c-cfb60086c89d_"
X-Originating-IP: [85.97.128.179]
From: roy simkes <roysimkes@hotmail.com>
To: <pgsql-sql@postgresql.org>
Subject: tsearch2 query question
Date: Wed, 5 Sep 2007 08:33:26 +0000
Importance: Normal
MIME-Version: 1.0
X-OriginalArrivalTime: 05 Sep 2007 08:33:27.0283 (UTC)
	FILETIME=[6E4A9430:01C7EF97]
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200709/49
X-Sequence-Number: 29262

--_68c8bdb3-3163-4f0b-9e5c-cfb60086c89d_
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable


Hi,



SELECT contentid, title, (rank(to_tsvector(body),q) +=20

rank(to_tsvector(title),q) + rank(to_tsvector(subtitle),q)) AS Score

FROM content, to_tsquery('parkyeri') AS q

WHERE statusid =3D 1

AND ispublished =3D 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=20

the part of the question. As you see I'm using a lot to_tsvector()=20

function. Which I believe it will not be good for the performance. So I=20

thought changing my query to something like this:



SELECT contentid, title, (rank(fts_body, q) + rank(fts_title,q) +=20

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 =3D 1

AND ispublished =3D 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=20

available for every row? Or will it be just computed once? I mean in the=20

first query where part is executed for every row, so I'm sure that it=20

will be evaluated for all the rows. But when I put that in the from part=20

will it compute the value once and will use the same value for all the=20

where clauses? If that's the case what will be the value of fts_body?=20

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 Space=
s. It's easy!
http://spaces.live.com/spacesapi.aspx?wx_action=3Dcreate&wx_url=3D/friends.=
aspx&mkt=3Den-us=

--_68c8bdb3-3163-4f0b-9e5c-cfb60086c89d_
Content-Type: text/html; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<html>
<head>
<style>
.hmmessage P
{
margin:0px;
padding:0px
}
body.hmmessage
{
FONT-SIZE: 10pt;
FONT-FAMILY:Tahoma
}
</style>
</head>
<body class=3D'hmmessage'>
Hi,
<br>
<br>SELECT contentid, title, (rank(to_tsvector(body),q) +=20
<br>rank(to_tsvector(title),q) + rank(to_tsvector(subtitle),q)) AS Score
<br>FROM content, to_tsquery('parkyeri') AS q
<br>WHERE statusid =3D 1
<br>AND ispublished =3D 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's not=20
<br>the part of the question. As you see I'm using a lot to_tsvector()=20
<br>function. Which I believe it will not be good for the performance. So I=
=20
<br>thought changing my query to something like this:
<br>
<br>SELECT contentid, title, (rank(fts_body, q) + rank(fts_title,q) +=20
<br>rank(fts_subtitle,q) ) AS Score
<br>FROM content,
<br>       to_tsquery('search &amp; 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 =3D 1
<br>AND ispublished =3D 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=20
<br>available for every row? Or will it be just computed once? I mean in th=
e=20
<br>first query where part is executed for every row, so I'm sure that it=20
<br>will be evaluated for all the rows. But when I put that in the from par=
t=20
<br>will it compute the value once and will use the same value for all the=
=20
<br>where clauses? If that's the case what will be the value of fts_body?=20
<br>The tsvector of every row's data or just one row's data?
<br>
<br>thank you for your time and patience
<br>
<br>roy simkes
<br>
<br><br /><hr />Invite your mail contacts to join your friends list with Wi=
ndows Live Spaces. It's easy! <a href=3D'http://spaces.live.com/spacesapi.a=
spx?wx_action=3Dcreate&wx_url=3D/friends.aspx&mkt=3Den-us' target=3D'_new'>=
Try it!</a></body>
</html>=

--_68c8bdb3-3163-4f0b-9e5c-cfb60086c89d_--