Re: [HACKERS] Multicolumn hash indexes - Mailing list pgsql-hackers

From Nico Williams
Subject Re: [HACKERS] Multicolumn hash indexes
Date
Msg-id 20170929195036.GE1251@localhost
Whole thread Raw
In response to Re: [HACKERS] Multicolumn hash indexes  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-hackers
On Fri, Sep 29, 2017 at 10:54:55AM -0400, Tom Lane wrote:
> There are few if any indexing techniques where the first column isn't
> significantly more important than the rest --- certainly that's true
> for btree, for example.  I do not think it's a showstopper if that's
> true for hash as well.

You have N>1 columns to index and which you'll be using a conjunction of
all of them together in queries, with equiality predicates.  No one of
those columns is sufficiently selective.  But all the columns together
are plenty selective enough.

Obviously a [multi-column] hash index should do.  The main question is
whether the planner can be made to not consider subsets of the columns
to the exclusion of the hash index -- maybe not, or not easily enough.

This is easy enough to implement with a B-Tree index on an expression
consisting of a decent hash function application to the row_to_json() of
a row composed of the columns in question.  But it requires using the
same expression in queries, naturally, which then acts as a barrier to
the planner's propensity to drop columns from consideration for index
selection.

A multi-column hash index facility shouldn't have to require anything
more than where clauses that are a conjunction of all the columns with
equality predicates.

Nico
-- 


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

pgsql-hackers by date:

Previous
From: Schneider
Date:
Subject: Re: [HACKERS] User-perspective knowledge about wait events
Next
From: Robert Haas
Date:
Subject: Re: [HACKERS] UPDATE of partition key