Thread: hash as an search key and hash collision

On Fri, Jun 19, 2020 at 04:24:01PM +0800, Andy Fan wrote:
>I want to maintain an internal table which the primary key is sql_text and
>planstmt::text, it is efficient since it both may be very long.  So a
>general
>idea is to use sql_hash_value and plan_hash_value. Then we have to
>handle the hash collision case.  However I checked the codes both in
>sr_plans[1]
>and pg_stat_statements[2],  both of them didn't handle such cases, IIUC.  so
>how can I understand this situation?
>

IIRC pg_stat_statements simply accepts the hash collision risk. This is
what the docs say:

     In some cases, queries with visibly different texts might get merged
     into a single pg_stat_statements entry. Normally this will happen
     only for semantically equivalent queries, but there is a small
     chance of hash collisions causing unrelated queries to be merged
     into one entry. (This cannot happen for queries belonging to
     different users or databases, however.)

The consequences of a hash collision are relatively harmless, enough to
make it not worth the extra checks (e.g. because the SQL text may not be
available in memory and would need to be read from the file).

I suppose sr_plan does the same thing, but I haven't checked.

regards

-- 
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services