Skip site navigation (1) Skip section navigation (2)

SELECT ... WHERE fti_query ORDER BY numeric_col LIMIT x - problem

From: PostgreSQL - Hans-Jürgen Schönig <postgres(at)cybertec(dot)at>
To: pgsql-hackers Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: SELECT ... WHERE fti_query ORDER BY numeric_col LIMIT x - problem
Date: 2011-02-04 17:48:23
Message-ID: 0CAF69D8-7BF0-4024-BEAC-975732F71B22@cybertec.at (view raw or flat)
Thread:
Lists: pgsql-hackers
I have toyed around with KNN a little and I am pretty impressed when it comes to the results we have seen in the GIS world.
Given the infrastructure we have at the moment I wonder if KNN can help to speedup queries like that:

SELECT ... WHERE fti_query ORDER BY numeric_col LIMIT x

The use case is fairly simple: Give me all products matching a certain tsquery and order those products by price or so to show the top 10.
KNN is supposed to gives sorted output which works perfectly for points and so on but can there theoretically be a sensible / reliable distance function for a (tsvector / numeric) combination? Some first testing gave me some "interesting" output .
If there is no way of defining a reasonable distance function performance is screwed up if fti_query returns a large list (it requires a complete sort of all prices then).

does anybody have some useful input here?

	many thanks,

		hans


--
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt, Austria
Web: http://www.postgresql-support.de


pgsql-hackers by date

Next:From: Itagaki TakahiroDate: 2011-02-04 18:02:51
Subject: Re: multiset patch review
Previous:From: Andrew DunstanDate: 2011-02-04 17:29:59
Subject: Re: arrays as pl/perl input arguments [PATCH]

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group