Tom Lane wrote:
Paul Tillotson <spam1011@xxxxxxxxxxxx> 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
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
