Re: Optimization on JOIN

On Thu, Jan 21, 2010 at 10:59:42PM -0800, Yan Cheng Cheok wrote:
> Currently, I am having JOIN statement as follow (1st case)
>
> SELECT measurement_type.value, measurement.value, measurement_unit.value
> FROM
> measurement_type INNER JOIN
> (measurement_unit INNER JOIN
> (measurement INNER JOIN
> (lot INNER JOIN unit ON (lot_id = fk_lot_id))
> ON (fk_unit_id = unit_id))
> ON (fk_measurement_unit_id = measurement_unit_id))
> ON (fk_measurement_type_id = measurement_type_id) WHERE lot_id = 7;

As you're only using INNER JOINs and equality conditions it's not going
to matter where you put the WHERE clause. PG can rewrite this any way
it wants and will put the constraints where ever the stats say it's best
placed. Just write the query however it is easiest to read and trust PG
to do the rest.

The syntax you want is to put more in the ON cause though, i.e:

SELECT *
FROM foo f
INNER JOIN bar b ON f.id = b.id AND b.other = 7

is the same as:

SELECT *
FROM foo f
INNER JOIN bar b ON f.id = b.id
WHERE b.other = 7;

is the same as:

SELECT *
FROM foo f, bar b
WHERE f.id = b.id
AND b.other = 7;

there are a few other ways of writing this as well. All are the same
and PG is able to rewrite them all to each other depending on which ever
it thinks will be the most efficient.

Try EXPLAINing the queries to see how PG is interpreting your queries.

--
Sam http://samason.me.uk/