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