It would seem that the planner does not take into account whether the
datatypes involved have the capability to use hash aggregates or not.
sdb=# explain select query_start, current_query from pg_locks join stat_activity on pid = procpid where granted =
trueand transaction in (select transaction from pg_locks where granted = false);
ERROR: could not find hash function for hash operator 716373
ssdb=# set enable_hashagg = off;
SET
ssdb=# explain select query_start, current_query from pg_locks join stat_activity on pid = procpid where granted = true
andtransaction in (select transaction from pg_locks where granted = false);
QUERY PLAN
------------------------------------------------------------------------------------------------------------Hash Join
(cost=60.79..159.12rows=1244 width=40) Hash Cond: ("outer".procpid = "inner".pid) -> Function Scan on stat_activity
(cost=0.00..15.00rows=995 width=44) Filter: (current_query <> '<IDLE>'::text) -> Hash (cost=60.16..60.16
rows=250width=4) -> Hash Join (cost=40.16..60.16 rows=250 width=4) Hash Cond:
("outer"."transaction"= "inner"."transaction") -> Function Scan on pg_lock_status l (cost=0.00..15.00
rows=500width=8) Filter: (granted = true) -> Hash (cost=39.91..39.91 rows=100
width=4) -> Unique (cost=37.41..39.91 rows=100 width=4) -> Sort
(cost=37.41..38.66rows=500 width=4) Sort Key: l."transaction"
-> Function Scan on pg_lock_status l (cost=0.00..15.00 rows=500 width=4)
Filter:(granted = false)
(15 rows)
--