> > One more thing to consider is that it seems that the planner requires
> > a condition for the first column of an index before considering an
> > indexscan plan. See Tom's email [1] in this regard. I think it would
> > be better to see what kind of work is involved there if you want to
> > explore a single hash value for all columns idea.
> >
> > [1] - https://www.postgresql.org/message-id/29263.1506483172%40sss.pgh.pa.us
>
> About this point, I will analyze further and update.
>
I have checked the planner code, there does not seem to be any
complicated changes needed to cover if we take up a single hash value
for all columns... Below are the major part of changes needed:
In build_index_paths(), there is a check like, "if (index_clauses ==
NIL && !index->amoptionalkey)", which helps to figure out if the
leading column has any clause or not. This needs to be moved out of
the loop and check for clauses on all key columns.
With this we need to add a "amallcolumncluse" field to Index
structure, which will be set to TRUE for HASH index and FALSE in other
cases.
And to get the multi-column hash index selected, we may set
enable_hashjoin =off, to avoid any condition become join condition,
saw similar behaviors in other DBs as well...
Thanks & Regards
SadhuPrasad
EnterpriseDB: http://www.enterprisedb.com