Re: IN subquery not using a hash

Tom Lane wrote:

>Paul Tillotson <spam1011(at)adelphia(dot)net> writes:
>
>
>>Tom Lane wrote:
>>
>>
>>>Hardly likely, considering it's estimating only 296 rows in the subquery
>>>output. My bet is that you've chosen a datatype whose comparisons are
>>>not hashable (like char(n)). What is the datatype of parentid in these
>>>tables, anyway?
>>>
>>>
>>>
>>I don't have access to the machine now, but my memory is that
>>parent.parentid is numeric(10,2) and child.parentid is int.
>>
>>
>
>Offhand I don't believe there are any hashable crosstype comparisons.
>In this case the int is probably getting promoted to numeric, but I
>think numeric comparison isn't hashable either (because for example
>'0.0' = '0.000' but the internal representations are different).
>
>
This is apparently the trouble. This query doesn't use a hash:

SELECT * FROM table1
WHERE <condition> OR numeric1 IN (SELECT int1 FROM table2)

But, this query (identical except for the cast) does:

SELECT * FROM table1
WHERE <condition> OR numeric1::int IN (SELECT int1 FROM table2)

Thanks for the help, Tom and others.

Paul Tillotson