Subject: Re: how is a three-way join implemented?

From: [email-masked] (Paul M. Aoki)
Date: Sat, 06 Aug 94 15:56:26 -0700

Pei Cao <[email-masked]> writes:
> Does Postgres join small and large1 first, then join the result relation with 
> large2?  Or does it join all three of them together at the same time, if so,
> how exactly does it do that?

join strategies are determined by a conventional dynamic programming
query optimizer patterned after the system r optimizer.

%A P. G. Selinger
%A M. M. Astrahan
%A D. D. Chamberlin
%A R. A. Lorie
%A T. G. Price
%T Access Path Selection in a Relational Database Management System
%J PROC 1979 SIGMOD
%C Boston, MA
%D JUN 1979
%K system r optimizer
%P 23-34

query plans are (generally speaking) left-deep binary trees; no more
than two tables are joined in a single join operation, though of course
more than one join can be going on at once.

you can dump query plans using the -d flag to "postmaster" and 
"postgres".  the format isn't documented but you can probably figure 
out what's going on.

note that catalog statistics (on which the optimizer must rely for
its cost formulae) are only updated if you run vacuum (all tables)
or build an index (just that one table).
--
  Paul M. Aoki          |  University of California at Berkeley
  [email-masked]  |  Dept. of EECS, Computer Science Division (#1776) 
                        |  Berkeley, CA 94720-1776

==============================================================================
   To add/remove yourself to/from the POSTGRES mailing list: send mail with 
   the subject line ADD or DEL to "[email-masked]"

   If this fails, send mail to "[email-masked]" and
   a human will deal with it.  DO NOT post to the "postgres" mailing list.
==============================================================================

---

From: Pei Cao <[email-masked]>
Date: Sat, 6 Aug 1994 18:42:42 -0400

Hi, I am new to Postgres, and would appreciate if anyone can give me some help
on this.

Suppose I have a three way join:

retrive into temp (small.all, large1.all, large2.all) 
where (small.key1 == large1.key1) and (large2.key1 == large1.key1)

where the size of "small" is 1/10 of that of "large1" and "large2".  

Thanks a lot!

Pei Cao
CS Dept.
Princeton Universtiy