From roysimkes@hotmail.com Wed Sep  5 08:33:32 2007
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_--



From aj@itersys.dk Sun Aug  8 03:07:30 2010
Received: from maia.hub.org (maia-2.hub.org [200.46.204.251])
	by mail.postgresql.org (Postfix) with ESMTP id 6DF58632845
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Sun,  8 Aug 2010 00:07:30 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia,
	port 10024) with ESMTP id 72651-10 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Sun,  8 Aug 2010 03:07:22 +0000 (UTC)
X-Greylist: delayed 00:07:20.62659 by SQLgrey-1.7.6
Received: from mail-qy0-f174.google.com (mail-qy0-f174.google.com
	[209.85.216.174])
	by mail.postgresql.org (Postfix) with ESMTP id 4477D6324FB
	for <pgsql-sql@postgresql.org>; Sun,  8 Aug 2010 00:07:22 -0300 (ADT)
Received: by qyk7 with SMTP id 7so1017483qyk.19
	for <pgsql-sql@postgresql.org>; Sat, 07 Aug 2010 20:07:21 -0700 (PDT)
MIME-Version: 1.0
Received: by 10.220.62.5 with SMTP id v5mr9025060vch.242.1281236399827; Sat,
	07 Aug 2010 19:59:59 -0700 (PDT)
Received: by 10.220.169.85 with HTTP; Sat, 7 Aug 2010 19:59:59 -0700 (PDT)
Date: Sun, 8 Aug 2010 12:59:59 +1000
Message-ID: <AANLkTimaBAG3M+sbfjBVKJ=ZSQi-Tyf1gw-ZhtvjkG-x@mail.gmail.com>
Subject: tsearch2 query question
From: =?ISO-8859-1?Q?Anders_=D8stergaard_Jensen?= <aj@itersys.dk>
To: pgsql-sql@postgresql.org
Content-Type: multipart/alternative; boundary=e0cb4e887ee97c2d21048d4718ca
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.899 tagged_above=-5 required=5 tests=BAYES_00=-1.9,
	HTML_MESSAGE=0.001, RCVD_IN_DNSWL_NONE=-0.0001
X-Spam-Level: 
X-Archive-Number: 201008/38
X-Sequence-Number: 34478

--e0cb4e887ee97c2d21048d4718ca
Content-Type: text/plain; charset=ISO-8859-1

Hello postgresql users,

I have a question regarding building a tsearch2 query that does wildcard
searching by default. Currently, I am using plainto_tsquery(<expr>), but it
does not allow me to build more advanced search expressions. I know
to_tsquery is the way to go with this, but I need some qualified assistance
for building the correct query.

Consider the following table:

metabase=# \d customers;
                                           Table "public.customers"
     Column      |           Type           |
 Modifiers
-----------------+--------------------------+-----------------------------------------------------------------
 id              | integer                  | not null default
nextval(('"customer_id_seq"'::text)::regclass)
 name            | character varying(255)   | not null
 description     | text                     |
[...]
search_idx      | tsvector                 |

search_idx is a tsvector with an ON INSERT OR UPDATE trigger, which
automatically updates the search vector as a combination of name and
description. No black magic there.

Now, let's start out with a classic, non-tsearch2 ILIKE query with two
wildcards around the search token:
metabase=# select id, name from customers where name ilike '%holstebr%';

CASE #1:
  id  |       name
------+-------------------
 3646 | Holstebro Kommune
(1 row)

I am trying to do the same exactly with tsearch2 but with no luck:

CASE #2:
metabase=# select id, name from customers where search_idx @@
to_tsquery('*Holstebr*');
 id | name
----+------
(0 rows)

How do I formulate the input for to_tsquery so that it returns the same as
(1)? And subsequently: how do I handle spaces in between, fx. so that
'holstebro komm*' yields 'holstebro kommune'?

If people have any example of expression builders they put on top of their
SQL queries, I would love to see it. I know that this has been done before.

Thank you for your time and help,

Anders

--e0cb4e887ee97c2d21048d4718ca
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Hello postgresql users,=A0<div><br></div><div>I have a question regarding b=
uilding a tsearch2 query that does wildcard searching by default. Currently=
, I am using plainto_tsquery(&lt;expr&gt;), but it does not allow me to bui=
ld more advanced search expressions. I know to_tsquery is the way to go wit=
h this, but I need some qualified assistance for building the correct query=
.=A0</div>
<div><br></div><div>Consider the following table:=A0</div><div><br></div><d=
iv><div>metabase=3D# \d customers;</div><div>=A0=A0 =A0 =A0 =A0 =A0 =A0 =A0=
 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 Table &quot;public=
.customers&quot;</div><div>=A0=A0 =A0 Column =A0 =A0 =A0| =A0 =A0 =A0 =A0 =
=A0 Type =A0 =A0 =A0 =A0 =A0 | =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0Modifiers =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0</div>
<div>-----------------+--------------------------+-------------------------=
----------------------------------------</div><div>=A0id =A0 =A0 =A0 =A0 =
=A0 =A0 =A0| integer =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0| not null default =
nextval((&#39;&quot;customer_id_seq&quot;&#39;::text)::regclass)</div>
<div>=A0name =A0 =A0 =A0 =A0 =A0 =A0| character varying(255) =A0 | not null=
</div><div>=A0description =A0 =A0 | text =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 |=A0</div><div>[...]=A0</div><div>search_idx =A0 =A0 =A0| tsvector =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 |=A0</div><div><br></div><div>search_idx is=
 a tsvector with an ON INSERT OR UPDATE trigger, which automatically update=
s the search vector as a combination of name and description. No black magi=
c there.=A0</div>
<div><br></div><div>Now, let&#39;s start out with a classic, non-tsearch2 I=
LIKE query with two wildcards around the search token:</div><div><div>metab=
ase=3D# select id, name from customers where name ilike &#39;%holstebr%&#39=
;;=A0</div>
<div><br></div><div>CASE #1:</div><div>=A0=A0id =A0| =A0 =A0 =A0 name =A0 =
=A0 =A0 =A0</div><div>------+-------------------</div><div>=A03646 | Holste=
bro Kommune</div><div>(1 row)</div></div><div><br></div><div>I am trying to=
 do the same exactly with tsearch2 but with no luck:=A0</div>
<div><br></div><div>CASE #2:</div><div><div><div>metabase=3D# select id, na=
me from customers where search_idx @@ to_tsquery(&#39;*Holstebr*&#39;);</di=
v><div>=A0id | name=A0</div><div>----+------</div><div>(0 rows)</div></div>=
</div>
<div><br></div><div>How do I formulate the input for to_tsquery so that it =
returns the same as (1)? And subsequently: how do I handle spaces in betwee=
n, fx. so that &#39;holstebro komm*&#39; yields &#39;holstebro kommune&#39;=
?</div>
<div><br></div><div>If people have any example of expression builders they =
put on top of their SQL queries, I would love to see it. I know that this h=
as been done before.=A0</div><div><br></div><div>Thank you for your time an=
d help,=A0</div>
<div><br></div><div>Anders</div>
</div>

--e0cb4e887ee97c2d21048d4718ca--



From tgl@sss.pgh.pa.us Sun Aug  8 15:51:21 2010
Received: from maia.hub.org (maia-3.hub.org [200.46.204.243])
	by mail.postgresql.org (Postfix) with ESMTP id 32956632287
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Sun,  8 Aug 2010 12:51:21 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia,
	port 10024) with ESMTP id 87951-05 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Sun,  8 Aug 2010 15:51:14 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130])
	by mail.postgresql.org (Postfix) with ESMTP id 31690632CFA
	for <pgsql-sql@postgresql.org>; Sun,  8 Aug 2010 12:51:14 -0300 (ADT)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id o78FpCfS003729;
	Sun, 8 Aug 2010 11:51:12 -0400 (EDT)
To: =?ISO-8859-1?Q?Anders_=D8stergaard_Jensen?= <aj@itersys.dk>
cc: pgsql-sql@postgresql.org
Subject: Re: tsearch2 query question 
In-reply-to: <AANLkTimaBAG3M+sbfjBVKJ=ZSQi-Tyf1gw-ZhtvjkG-x@mail.gmail.com> 
References: <AANLkTimaBAG3M+sbfjBVKJ=ZSQi-Tyf1gw-ZhtvjkG-x@mail.gmail.com>
Comments: In-reply-to =?ISO-8859-1?Q?Anders_=D8stergaard_Jensen?=
	<aj@itersys.dk> message dated "Sun, 08 Aug 2010 12:59:59 +1000"
Date: Sun, 08 Aug 2010 11:51:12 -0400
Message-ID: <3728.1281282672@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.91 tagged_above=-5 required=5 tests=BAYES_00=-1.9,
	T_RP_MATCHES_RCVD=-0.01
X-Spam-Level: 
X-Archive-Number: 201008/41
X-Sequence-Number: 34481

=?ISO-8859-1?Q?Anders_=D8stergaard_Jensen?= <aj@itersys.dk> writes:
> Now, let's start out with a classic, non-tsearch2 ILIKE query with two
> wildcards around the search token:
> metabase=# select id, name from customers where name ilike '%holstebr%';
>   id  |       name
> ------+-------------------
>  3646 | Holstebro Kommune
> (1 row)

> I am trying to do the same exactly with tsearch2 but with no luck:

> metabase=# select id, name from customers where search_idx @@
> to_tsquery('*Holstebr*');

There isn't any exact equivalent for that in tsquery, because it's about
searching for words not arbitrary substrings.  You could do
to_tsquery('Holstebro').  Since 8.4 you could also do a prefix match,
say to_tsquery('Holstebr:*'), but there's no way to omit letters from
the start of the word.  Also, you have to be wary of prefix matches
if you use a stemming dictionary, because dropping characters from the
end of the word might make it stem differently.

> And subsequently: how do I handle spaces in between, fx. so that
> 'holstebro komm*' yields 'holstebro kommune'?

You need to think of that as being two independent word searches,
like to_tsquery('holstebro & komm:*').  AFAIR there isn't any phrase
matching per se in tsquery.  Of course, you could AND this with an ILIKE
clause to filter out matches where the words weren't adjacent.

If you're really dependent on the exact behavior of LIKE-style
searching then tsquery is not going to provide you with an exact
replacement.  You might consider looking at contrib/pg_trgm/ to
find an indexable operation that can speed up LIKE searches.

			regards, tom lane