Re: IN subquery not using a hash

Tom Lane wrote:

>Paul Tillotson <spam1011(at)adelphia(dot)net> writes:
>
>
>>For the following query, postgres is running the IN subquery over and
>>over again (once for each row scanned in the parent table.)
>>I would have expected it to run the whole query once and create a hash
>>which would then be probed once for every row scanned in the parent
>>table. I assumed that it was not doing so because it thought that the
>>resulting hash table would exceed sort_mem,
>>
>>
>
>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?
>
> regards, tom lane
>
>
>
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. If
child.parentid is int and parent.parentid is numeric, would that cause
this? (Not good database design, I know.)

I am 100% certain that neither of these are char(n), and 99% certain
that they are either numeric or int.

Paul Tillotson