Skip site navigation (1) Skip section navigation (2)

Re: Operator class group proposal

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Gregory Stark <stark(at)enterprisedb(dot)com>
Cc: "Gregory Stark" <gsstark(at)mit(dot)edu>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: Operator class group proposal
Date: 2007-01-03 18:17:32
Message-ID: 16067.1167848252@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackers
Gregory Stark <stark(at)enterprisedb(dot)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

In response to

Responses

pgsql-hackers by date

Next:From: Simon RiggsDate: 2007-01-03 19:01:41
Subject: Mark/Restore and avoiding RandomAccess sorts
Previous:From: Simon RiggsDate: 2007-01-03 18:12:59
Subject: Re: proposal - new SPI cursor function

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group