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 17:47:30
Message-ID: 87d55wvw7h.fsf@enterprisedb.com (view raw or flat)
Thread:
Lists: pgsql-hackers
Hope you had a nice holiday.


"Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> Gregory Stark <gsstark(at)mit(dot)edu> writes:
>> So the only reason we needed the cross-data-type operators was to get better
>> estimates? I thought without them you couldn't get an index-based plan at all.
>
> Oh, hm, there is that --- you won't get a nestloop with inner indexscan
> unless the join expression uses the unmodified inner variable (unless
> you do something weird like provide an index on the casted value...)

Hm, so you're saying this case:

postgres=# create table a (a numeric);
CREATE TABLE
postgres=# create table b (b integer);
CREATE TABLE
postgres=# create index aa on a(a);
CREATE INDEX
postgres=# create index bb on b(b);
CREATE INDEX
postgres=# set enable_seqscan = off;
SET


So this case works (though I find the actual plan chosen to be a bit
surprising here):

postgres=# explain select * from a where a = 1;
                           QUERY PLAN                            
-----------------------------------------------------------------
 Bitmap Heap Scan on a  (cost=4.05..13.51 rows=6 width=32)
   Recheck Cond: (a = 1::numeric)
   ->  Bitmap Index Scan on aa  (cost=0.00..4.05 rows=6 width=0)
         Index Cond: (a = 1::numeric)
(4 rows)



But this case doesn't work:

postgres=# explain select * from a join b on (a=b);
                                   QUERY PLAN                                   
--------------------------------------------------------------------------------
 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)
(6 rows)

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?


> However, we need to be pretty wary about widening the families unless
> we're sure that the semantics are right.  In particular, I think that
> numeric-vs-float crosstype operators would violate the transitive law:
> you could have values for which A=B and B=C but A!=C.  This is because
> we smash numerics to float for comparison, and so there are distinct
> numeric values that can compare equal to the same float.  bigint against
> float same problem.  It'd be OK to integrate integers and numeric into
> one class, but how much real value is there in that?

I'm not sure. In all of my schemas I've rarely used NUMERIC and never for an
indexed column. But I'm sure there are users out there with databases
(probably existing databases from other database products) which use NUMERIC
extensively.

-- 
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com

In response to

Responses

pgsql-hackers by date

Next:From: Simon RiggsDate: 2007-01-03 18:12:59
Subject: Re: proposal - new SPI cursor function
Previous:From: Pavel StehuleDate: 2007-01-03 17:30:48
Subject: proposal - new SPI cursor function

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