Hope you had a nice holiday.

"Tom Lane" <[EMAIL PROTECTED]> writes:

> Gregory Stark <[EMAIL PROTECTED]> 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);
postgres=# create table b (b integer);
postgres=# create index aa on a(a);
postgres=# create index bb on b(b);
postgres=# set enable_seqscan = off;

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

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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not

Reply via email to