Re: [GENERAL] Fast join

Herouth Maoz wrote:

> >
> > adb=> EXPLAIN SELECT COUNT(*) FROM atable WHERE atable.cfield =
> >btable.cfield
> > AND atable.afield>100;
>
> Hey, shouldn't these be:
>
> SELECT COUNT(*)
> FROM atable, btable <----- Note this!
> WHERE atable.cfield = btable.cfield
> AND ....
>
> I'm not sure that when the other table is implicit, the optimizer checks
> the statistics of the btable on time.
>

adb=> EXPLAIN SELECT * FROM atable,btable WHERE atable.cfield = btable.cfield
AND atable.afield<10;
NOTICE: QUERY PLAN:

Hash Join (cost=1052.69 rows=3334 width=40)
-> Seq Scan on btable (cost=399.00 rows=10000 width=20)
-> Hash (cost=198.67 rows=3334 width=20)
-> Index Scan using aindex on atable (cost=198.67 rows=3334 width=20)

--
Leon.