Tom Lane wrote:
Paul Tillotson <spam1011@xxxxxxxxxxxx> 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
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
