Re: [HACKERS] Multicolumn hash indexes

2017-09-29 Thread Nico Williams
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.

Re: [HACKERS] Multicolumn hash indexes

2017-09-29 Thread Alexander Korotkov
On Fri, Sep 29, 2017 at 6:33 PM, Robert Haas wrote: > Now you could also imagine something where we keep a separate set of > hash buckets for each column and multi-column searches are handled by > searching each hash table separately and taking the intersection of > the

Re: [HACKERS] Multicolumn hash indexes

2017-09-29 Thread Robert Haas
On Fri, Sep 29, 2017 at 10:54 AM, 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. Well, BRIN doesn't care. > I do not think it's a

Re: [HACKERS] Multicolumn hash indexes

2017-09-29 Thread Tom Lane
Robert Haas writes: > Maybe you're worrying about something like a billion-row table where > there are 3 columns that form a composite key: (1,1,1), (1,1,2), ..., > (1,1000),(1,2,1),...,(1,1000,1000),(2,1,1),...,(1000,1000,1000). In > that case, treating the leading column

Re: [HACKERS] Multicolumn hash indexes

2017-09-29 Thread Robert Haas
On Wed, Sep 27, 2017 at 5:52 PM, Tomasz Ostrowski wrote: > I feel that this would eliminate a large amount of potential gains from such > an index. This would be usable only when a sufficiently variable column > exists, in which case a simple hash index on the column

Re: [HACKERS] Multicolumn hash indexes

2017-09-27 Thread Tomasz Ostrowski
On 09/27/2017 05:57 PM, Tom Lane wrote: If we follow GIST's lead that the leading column is "most important", the idea could be to require a search constraint on the first column, which produces the hash that determines the bucket assignment. Hashes for additional columns would just be payload

Re: [HACKERS] Multicolumn hash indexes

2017-09-27 Thread Nico Williams
On Wed, Sep 27, 2017 at 11:57:23AM -0400, Tom Lane wrote: > Robert Haas writes: > > On Wed, Sep 27, 2017 at 9:56 AM, Jesper Pedersen > > wrote: > >> Maybe an initial proof-of-concept could store the hash of the first column > >> (col1) plus the

Re: [HACKERS] Multicolumn hash indexes

2017-09-27 Thread Robert Haas
On Wed, Sep 27, 2017 at 11:57 AM, Tom Lane wrote: > Robert Haas writes: >> On Wed, Sep 27, 2017 at 9:56 AM, Jesper Pedersen >> wrote: >>> Maybe an initial proof-of-concept could store the hash of the first column >>> (col1)

Re: [HACKERS] Multicolumn hash indexes

2017-09-27 Thread Tom Lane
Robert Haas writes: > On Wed, Sep 27, 2017 at 9:56 AM, Jesper Pedersen > wrote: >> Maybe an initial proof-of-concept could store the hash of the first column >> (col1) plus the hash of all columns (col1, col2, col3) in the index, and see >> what

Re: [HACKERS] Multicolumn hash indexes

2017-09-27 Thread Robert Haas
On Wed, Sep 27, 2017 at 9:56 AM, Jesper Pedersen wrote: > Maybe an initial proof-of-concept could store the hash of the first column > (col1) plus the hash of all columns (col1, col2, col3) in the index, and see > what requirements / design decisions would appear from

Re: [HACKERS] Multicolumn hash indexes

2017-09-27 Thread Nico Williams
On Wed, Sep 27, 2017 at 09:56:26AM -0400, Jesper Pedersen wrote: > On 09/26/2017 08:11 PM, Robert Haas wrote: > >On Tue, Sep 26, 2017 at 7:18 PM, Tom Lane wrote: > >>Tomasz Ostrowski writes: > >>>I've noticed that hash indexes can't currently (in PG10)

Re: [HACKERS] Multicolumn hash indexes

2017-09-27 Thread Robert Haas
On Tue, Sep 26, 2017 at 11:32 PM, Tom Lane wrote: > There is a facility in the planner to require a condition for the first > column of an index before considering an indexscan plan. We could perhaps > extend that to require a condition for each column of the index, though >

Re: [HACKERS] Multicolumn hash indexes

2017-09-27 Thread Jesper Pedersen
On 09/26/2017 08:11 PM, Robert Haas wrote: On Tue, Sep 26, 2017 at 7:18 PM, Tom Lane wrote: Tomasz Ostrowski writes: I've noticed that hash indexes can't currently (in PG10) be multicolumn. Are they technically hard to implement or just nobody took

Re: [HACKERS] Multicolumn hash indexes

2017-09-27 Thread David Fetter
On Tue, Sep 26, 2017 at 11:32:52PM -0400, Tom Lane wrote: > Robert Haas writes: > > On Tue, Sep 26, 2017 at 7:18 PM, Tom Lane wrote: > >> It's not simple, particularly not if you wish that the index would support > >> queries specifying conditions for

Re: [HACKERS] Multicolumn hash indexes

2017-09-26 Thread Tom Lane
Robert Haas writes: > On Tue, Sep 26, 2017 at 7:18 PM, Tom Lane wrote: >> It's not simple, particularly not if you wish that the index would support >> queries specifying conditions for just a subset of the indexed columns >> (an assumption that's

Re: [HACKERS] Multicolumn hash indexes

2017-09-26 Thread Robert Haas
On Tue, Sep 26, 2017 at 7:18 PM, Tom Lane wrote: > Tomasz Ostrowski writes: >> I've noticed that hash indexes can't currently (in PG10) be multicolumn. >> Are they technically hard to implement or just nobody took such a feature? > > It's not simple,

Re: [HACKERS] Multicolumn hash indexes

2017-09-26 Thread Robert Haas
On Tue, Sep 26, 2017 at 5:41 PM, Tomasz Ostrowski wrote: > I've noticed that hash indexes can't currently (in PG10) be multicolumn. Are > they technically hard to implement or just nobody took such a feature? > > I think multicolumn hash indexes should help pretty

Re: [HACKERS] Multicolumn hash indexes

2017-09-26 Thread Tom Lane
Tomasz Ostrowski writes: > I've noticed that hash indexes can't currently (in PG10) be multicolumn. > Are they technically hard to implement or just nobody took such a feature? It's not simple, particularly not if you wish that the index would support queries specifying

[HACKERS] Multicolumn hash indexes

2017-09-26 Thread Tomasz Ostrowski
Hi. I've noticed that hash indexes can't currently (in PG10) be multicolumn. Are they technically hard to implement or just nobody took such a feature? I think multicolumn hash indexes should help pretty significantly with queries like: - where username=? and user_post_id=? - where