HASH JOIN inner did not fit in memory

[Deleted User]

While attempting to execute a query, user receives the following error message: Error Msg: ROLLBACK: HASH JOIN inner did not fit in memory

[Deleted User]

Symptoms / Errors: The error message, "HASH JOIN inner did not fit in memory", can possibly be caused by the way that Vertica is determining which table is used as the inner and outer tables while doing a Hash join between tables. When Vertica performs a hash join, the optimizer chooses the smaller of the two projections and keeps that data in memory. For example, when a fact table is joined to a dimension table, the projection for the dimension table will be smaller, so the dimension data will be kept in memory. Vertica will determine that a table is a dimension table based on Foreign Key and Primary Key constraints. If no constraints have been defined, then Vertica will choose the smaller projection to keep in memory. Also if the larger 'fact' table is being used as the inner table, there may be too many rows flowing into the same hash bucket, which will cause the above error. In order to accurately select the smaller projection, statistics for the projection must be available. Resolution / Workaround: Analyze database statistics by running the following command select analyze_statistics(''); By running analyze statistics, the statistics of all tables and projections are updated in catalog and then it becomes easy for Vertica to decide which table is large and which is small. Based upon this, Vertica will put the smaller table into the memory. We recommend Vertica DBA?s to analyze statistics at least once in a week. Preferably when system is idle because analyze statistics requires some system resources to compute all statistics. As of version 2.5, it is not necessary to stop the Tuple Mover process when analyze statistics is running. On successful completion of analyze statistics this will return/print 0. If the SQL query still results in the same error, please contact Technical Support along with the query plan and diagnostics.