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.
-- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com
