> > 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
