Re: [GENERAL] Fast join

Bruce Momjian wrote:

> > adb=> EXPLAIN SELECT COUNT(*) FROM atable WHERE atable.cfield = btable.cfield
> > AND atable.afield<10;
> > NOTICE: QUERY PLAN:
>
> But your only restriction is < 10. That is not enough. Make it = 10,
> and I think it will use the index.

Ok. We did it! :)
-------------
adb=> EXPLAIN SELECT COUNT(*) FROM atable WHERE atable.cfield = btable.cfield
AND atable.afield=10;
NOTICE: QUERY PLAN:

Aggregate (cost=4.10 rows=1 width=12)
-> Nested Loop (cost=4.10 rows=1 width=12)
-> Index Scan using aindex on atable (cost=2.05 rows=1 width=8)
-> Index Scan using hindex on btable (cost=2.05 rows=10000 width=4)
-------------

But look here:
-------------
adb=> EXPLAIN SELECT * FROM atable WHERE atable.cfield = btable.cfield AND
atable.afield IN (SELECT btable.bfield WHERE btable.bfield=10);
NOTICE: QUERY PLAN:

Hash Join (cost=1483.00 rows=10000 width=24)
-> Seq Scan on atable (cost=399.00 rows=10000 width=20)
SubPlan
-> Index Scan using gindex on btable (cost=2.05 rows=1 width=4)
-> Hash (cost=399.00 rows=10000 width=4)
-> Seq Scan on btable (cost=399.00 rows=10000 width=4)
-------------

This is the same dumbness again. Will you fix the optimizer?

And more: would you make a cool data type, a reference, which
is a physical record number of a foreign record? This could make certain
type of joins VERY fast, too good to be true. Such thing is really
an incorporation of elements of networking (networked? :) data
model into relational model.

--
Leon.