Message-ID: <4A9BEBD6.60407@enterprisedb.com>
Date: Mon, 31 Aug 2009 18:27:18 +0300
From: Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>
Organization: EnterpriseDB
User-Agent: Mozilla-Thunderbird 2.0.0.19 (X11/20090103)
MIME-Version: 1.0
To: Hans-Juergen Schoenig -- PostgreSQL <postgres@cybertec.at>
CC: Martijn van Oosterhout <kleptog@svana.org>, Tom Lane <tgl@sss.pgh.pa.us>,
	pgsql-hackers <pgsql-hackers@postgresql.org>,
	Zoltan Boszormenyi <zb@cybertec.at>
Subject: Re: combined indexes with Gist - planner issues?
References: <4A9BBD39.2010500@cybertec.at> <543.1251726841@sss.pgh.pa.us>
	<4A9BD8DE.3060005@cybertec.at> <20090831142441.GA30008@svana.org>
	<4A9BE0D5.7060106@cybertec.at>
In-Reply-To: <4A9BE0D5.7060106@cybertec.at>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

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