pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedtsearch2 query question
3+ messages / 3 participants
[nested] [flat]
* tsearch2 query question
@ 2007-09-05 08:33 roy simkes <roysimkes@hotmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: roy simkes @ 2007-09-05 08:33 UTC (permalink / raw)
To: pgsql-sql
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=
^ permalink raw reply [nested|flat] 3+ messages in thread
* tsearch2 query question
@ 2010-08-08 02:59 Anders Østergaard Jensen <aj@itersys.dk>
2010-08-08 15:51 ` Re: tsearch2 query question Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 3+ messages in thread
From: Anders Østergaard Jensen @ 2010-08-08 02:59 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: tsearch2 query question
2010-08-08 02:59 tsearch2 query question Anders Østergaard Jensen <aj@itersys.dk>
@ 2010-08-08 15:51 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 3+ messages in thread
From: Tom Lane @ 2010-08-08 15:51 UTC (permalink / raw)
To: Anders Østergaard Jensen <aj@itersys.dk>; +Cc: pgsql-sql
=?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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2010-08-08 15:51 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-09-05 08:33 tsearch2 query question roy simkes <roysimkes@hotmail.com>
2010-08-08 02:59 tsearch2 query question Anders Østergaard Jensen <aj@itersys.dk>
2010-08-08 15:51 ` Tom Lane <tgl@sss.pgh.pa.us>
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