Re: combined indexes with Gist - planner issues?

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