From: Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>
To: Hans-Juergen Schoenig -- PostgreSQL <postgres@cybertec.at>
Cc: Martijn van Oosterhout <kleptog@svana.org>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-hackers <pgsql-hackers@postgresql.org>
Cc: Zoltan Boszormenyi <zb@cybertec.at>
Subject: Re: combined indexes with Gist - planner issues?
Date: Mon, 31 Aug 2009 18:27:18 +0300
Message-ID: <4A9BEBD6.60407@enterprisedb.com> (raw)
In-Reply-To: <4A9BE0D5.7060106@cybertec.at>
References: <4A9BBD39.2010500@cybertec.at>
<543.1251726841@sss.pgh.pa.us>
<4A9BD8DE.3060005@cybertec.at>
<20090831142441.GA30008@svana.org>
<4A9BE0D5.7060106@cybertec.at>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: heikki.linnakangas@enterprisedb.com, postgres@cybertec.at, kleptog@svana.org, tgl@sss.pgh.pa.us, zb@cybertec.at
Subject: Re: combined indexes with Gist - planner issues?
In-Reply-To: <4A9BEBD6.60407@enterprisedb.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox