Re: [SQL] Using SKYLINE command on PostgreSQL

[Yeb Havinga]

On Sun, Aug 8, 2010 at 5:35 AM, Craig Ringer wrote:

>
> The masters thesis page does contain a patch against PostgreSQL 8.3:
>  http://skyline.dbai.tuwien.ac.at/
>

That page has a nice web page and examples where queries can be run. 'The
skyline operator' is described in
http://www.dbis.ethz.ch/research/publications/38.pdf. It reminds me a bit of
the staircase join operator, something on my personal wish list for postgres
as well (latest patch seems to have got lost on the internet).

>
> They say they're porting the patch to 8.4devel, but I suspect that since
> 8.4 has been out for ages and 9.0 is almost released, they've probably
> lost interest and dropped the work.
>

I looked a bit in the latest patch. There's a lot of FIXME's and TODO's in
it. It adds at least one plan node, so it's probably hard to rewrite into
contrib form. That'll probably make it harder to get accepted for mainline
postgres, also because the skyline queries can be translated into 'normal'
sql (referenced pdf, section 3.1), though the authors claim the performance
using skyline is better. Since 8.3 CTE's have been introduced and maybe that
makes writing these kinds of queries easier. Also the knngist ordering of
'interesting points' might be a solution for problems in the same ballpark.

regards,
Yeb Havinga

Re: [SQL] tsearch2 query question

[Tom Lane]

=?ISO-8859-1?Q?Anders_=D8stergaard_Jensen?=  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

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql