Re: Operator class group proposal

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.  Whether this
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.
        regards, tom lane