Re: [GENERAL] Fast join

Bruce Momjian wrote:
>
> > > Also, if a join does most of the table, it is faster do not use indexes,
> > > and just sort on the backend.
> > >
> >
> > The problem is - when you want just a small part of the table(s) and
> > you have indices to facilitate qualifications, Postgres doesn't
> > use 'em !  This is a question of Life and Death - i.e. to use or
> > not to use Postgres.
>
> As I remember, your qualification was x > 10.  That may not be
> restrictive enough to make an index faster.

Oh, I'm sorry, it was a typo. But believe me, such behaviour is
persistent notwithstanding any type of qualification. It is, so
to say, tested and approved. Look at the explanations of Postgres
of his plan of query on database whose creation I showed you
earlier (it has two tables of 10000 rows, properly vacuumed):

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

Aggregate  (cost=1047.69 rows=3334 width=12)
  ->  Hash Join  (cost=1047.69 rows=3334 width=12)
        ->  Seq Scan on btable  (cost=399.00 rows=10000 width=4)
        ->  Hash  (cost=198.67 rows=3334 width=8)
              ->  Index Scan using aindex on atable  (cost=198.67 rows=3334
width=8)

adb=>  EXPLAIN  SELECT COUNT(*) FROM atable WHERE atable.cfield = btable.cfield
AND atable.afield>100;
NOTICE:  QUERY PLAN:

Aggregate  (cost=1047.69 rows=3334 width=12)
  ->  Hash Join  (cost=1047.69 rows=3334 width=12)
        ->  Seq Scan on btable  (cost=399.00 rows=10000 width=4)
        ->  Hash  (cost=198.67 rows=3334 width=8)
              ->  Index Scan using aindex on atable  (cost=198.67 rows=3334
width=8)
---------------------

It is clear that Postgres does hash join of the whole tables ALWAYS.

--
Leon.