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);
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
---------------------------(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
match
