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.