"Tom Lane" <tgl@sss.pgh.pa.us> writes:
> Gregory Stark <stark@enterprisedb.com> writes:
>> Merge Join (cost=100000149.78..100000448.70 rows=13161 width=36)
>> Merge Cond: (a.a = "inner"."?column2?")
>> -> Index Scan using aa on a (cost=0.00..62.45 rows=1230 width=32)
>> -> Sort (cost=100000149.78..100000155.13 rows=2140 width=4)
>> Sort Key: (b.b)::numeric
>> -> Seq Scan on b (cost=100000000.00..100000031.40 rows=2140 width=4)
>
>> That doesn't seem to even fit your description; the join is in fact on the
>> unmodified inner variable here, no? Is this a bug?
>
> No, the join condition is "a.a = b.b::numeric", because the only usable =
> operator is numeric-eq-numeric. If we were to create a numeric-eq-int
> operator then use of an indexscan on b would be possible.
Hm, that's a thought. We could add some choice operators such as = and < even
if we didn't complete the set of operators to make a full transitively
merge-joinable set of operators. Not sure that buys much except some brain
cells dealing with the pg_operator columns though.
It does seem like those merge-joinable operator columns ought to be
automatically filled in based on the btree strategy numbers.
> is an issue in real-world usage is debatable, though. Certainly my
> advice to anyone worried about the performance of such a join would be
> to change the numeric column to an integer type --- without that you're
> going to have performance problems anyway, just because numeric
> arithmetic is slow.
Well I don't think those performance problems are comparable. Numeric
arithmetic is slow, and numeric storage is inefficient but both of those costs
are proportional. A user may well find it entirely tolerable that his database
runs 50% slower than his DBA tells him it can. But if his application runs
1000x slower or worse then effectively Postgres just doesn't work for his
application at all.
To be honest I'm just as happy to be told it's something we're not too
concerned about. pg_operator entries are a pain to maintain. We haven't seen
many of the "why is my query using a sequential scan" type queries since the
cross-data-type operators went in so perhaps my concern is misplaced.
-- Gregory Stark EnterpriseDB http://www.enterprisedb.com