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

Re: Operator class group proposal

From: Gregory Stark <stark(at)enterprisedb(dot)com>
To: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: "Gregory Stark" <gsstark(at)mit(dot)edu>, <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Operator class group proposal
Date: 2007-01-03 19:17:22
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-hackers
"Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> 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.  

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

In response to

pgsql-hackers by date

Next:From: Pavel StehuleDate: 2007-01-03 19:39:33
Subject: Re: proposal - new SPI cursor function
Previous:From: Simon RiggsDate: 2007-01-03 19:01:41
Subject: Mark/Restore and avoiding RandomAccess sorts

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