hello ...
we did some experiments with doing such a table.
the problem is if you want to allow arbitrary combinations of words
which can be modeled perfectly with FTI.
you would instantly end up with a self join with 5 relations or so -
which is again bad.
there are too many common words to consider doing with partly with gist
and partly with a btree.
is there any option to adapt gist in a way that a combined index would
make sense here?
many thanks,
hans
Heikki Linnakangas wrote:
Hans-Juergen Schoenig -- PostgreSQL wrote:
my knowledge of how gist works internally is not too extensive. any
"kickstart" idea would be appreciated.
If there's not too many of those common words, you can create a simple
partial b-tree index for each, and handle the less common words with the
gist index you have (you can drop the display_price column from the index).
Another idea:
Create a table containing one row for each word in each product:
CREATE TABLE t_product_word (id bigint, word text, display_price
numeric(10,4));
with triggers to keep it up-to-date. You can then create a regular two
column b-tree index on that:
CREATE INDEX idx_word_price ON t_product_word (word, display_price);
And query with:
SELECT p.art_number, p.title
FROM t_product p INNER JOIN t_product_word pw ON p.id = pw.id
WHERE pw.word = 'harddisk'
ORDER BY pw.display_price DESC LIMIT 10;
The t_product_word table will be huge, but with a few gigabytes of data
it should still be manageable.
--
Cybertec Schoenig & Schoenig GmbH
Reyergasse 9 / 2
A-2700 Wiener Neustadt
Web: www.postgresql-support.de
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
