Re: [HACKERS] Multicolumn hash indexes

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 data in the index entry.
> If you have search constraint(s) on low-order column(s), you can check
> for hash matches before visiting the heap, but they don't reduce how
> much of the index you have to search.  Even btree works that way for
> many combinations of incomplete index constraints.

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 wouldn't 
be much worse.

But I have an idea. What if there was a requirement for the search 
criteria to use tuple equality comparison:where (a,b,c)=(?,?,?)
orwhere (a,b,c) in ((?,?,?),(?,?,?),(?,?,?),(?,?,?))

Wouldn't it eliminate problems with disappearing conditions?

Actually maybe this could be implemented just like a functional index. 
So it would implement reasonably something that otherwise would be a 
terribly hackish and slow solution like:
create or replace function hashhack(a bytea, b bytea)returns bigintlanguage sqlimmutableas $$    -- uses 'x1e' (record
separator)   -- to ensure hashhack('a','')!=hashhack('','a')    select (      'x'      ||      substr(
md5($1||'\x1e'::bytea||$2),      1,       16      )    )::bit(64)::bigint;$$;
 
create index t_hashhack_a_b_idx  on t( hashhack(a::bytea,b::bytea) );
        select * from t  where a='a' and b='b'      and    hashhack(a::bytea, b::bytea)    =
hashhack('a'::bytea,'b'::bytea);

If if was automatic man could avoid the overhead of converting data to 
bytea/string, concatenating, truncating, converting back to bigint, 
rechecking condition etc. that make this kind of hack not very sane.

Even providing a specially crafted function or operator for queries 
specifically targeted for the index would be quite sufficient:where pg_equal( (a,b,c), (?,?,?) );

-- 
Tomasz "Tometzky" Ostrowski


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