pg.ddx.io  pgsql-hackers@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Markus Wanner <markus@bluegap.ch>
To: Hans-Juergen Schoenig -- PostgreSQL <postgres@cybertec.at>
Cc: Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>
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: Thu, 03 Sep 2009 07:33:39 +0200
Message-ID: <4A9F5533.2010105@bluegap.ch> (raw)
In-Reply-To: <4A9BED7A.5000202@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>
	<4A9BEBD6.60407@enterprisedb.com>
	<4A9BED7A.5000202@cybertec.at>

Hi,

Hans-Juergen Schoenig -- PostgreSQL wrote:
> 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.

How about an inverted index, either via GIN or with a custom table, such
that you have the cheapest price per existing word. (That's pretty close
to how full text searching itself works). Either reduce the number of
words with tsearch2's stemming algorithms. Or go for trigrams right
away. Split a word or search query in all its trigrams, then look up the
(cheapest) price(s) per trigram and return the n least expensive ones.

I've done somethings pretty similar for a customer, using the custom
table approach, as integration of GIN just started back then. Even now,
you need to consider the downside of that index lacking visibility
information and having to recreate the index from time to time. OTOH a
custom table needs a lot more manual twiddling with triggers and bulk
"index" rebuilding.

I guess I'd still go for a custom table today, as it's simply more
flexible. Something like:

CREATE TABLE cheapest_product_by_word (
  trgm TEXT,
  cheapest_products bigint[]
);

However, all of this is assuming that data (i.e. prices, products)
change very rarely and it's beneficial to calculate such an intermediate
lookup-table in advance. Not sure how much that's the case for you.

Regards

Markus Wanner




view thread (8+ messages)

Message-ID: <4A9F5533.2010105@bluegap.ch>
Permalink:  ../4A9F5533.2010105@bluegap.ch/
Also on:    postgresql.org/message-id/4A9F5533.2010105@bluegap.ch

 · 

reply
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: markus@bluegap.ch, postgres@cybertec.at, heikki.linnakangas@enterprisedb.com, kleptog@svana.org, tgl@sss.pgh.pa.us, zb@cybertec.at
  Subject: Re: combined indexes with Gist - planner issues?
  In-Reply-To: <4A9F5533.2010105@bluegap.ch>

* 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