Optimization on JOIN - Mailing list pgsql-general

I create 1 lot.
every lot is having 10000 unit
every unit is having 100 measurement.

hence :

lot - 1 row entry
unit - 10000 row entries
measurement - 1000000 row entries

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;

I thought, I may optimized it using : (2nd case, Take note on the WHERE statement)


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) WHERE lot_id = 7)
            ON (fk_unit_id = unit_id))
        ON (fk_measurement_unit_id = measurement_unit_id))
    ON (fk_measurement_type_id = measurement_type_id);


My thought is as follow :

For 1st case, my visualization is :

(lot join unit)

lot_id  unit_id  -> 6 rows
===============
1        1
1        2
1        3
2     4
2     5
2     6


measurement join (lot join unit)

lot_id  unit_id     measurement_id   -> 18 rows
========================
1        1     1
1        1     2
1        1     3
1        2       4
1        2     5
1        2     6
1        3       7
1        3       8
1        3       9
2     4       10
2     4       11
2     4       12
2     5       13
2     5       14
2     5       15
2     6     16
2     6       17
2     6       18


measurement join (lot join unit) where lot_id = 1

lot_id  unit_id     measurement_id   -> 9 rows
========================
1        1     1
1        1     2
1        1     3
1        2       4
1        2     5
1        2     6
1        3       7
1        3       8
1        3       9



For 2nd case, my visualization is :

(lot join unit where lot_id = 1)

lot_id  unit_id  -> 3 rows
===============
1        1
1        2
1        3


measurement join (lot join unit where lot_id = 1)

lot_id  unit_id     measurement_id   -> 9 rows
========================
1        1     1
1        1     2
1        1     3
1        2       4
1        2     5
1        2     6
1        3       7
1        3       8
1        3       9


During the process, 2nd case only need maximum 9 rows, compare to 1st case 18 rows.

However, the 2nd case syntax is incorrect :(

ERROR:  syntax error at or near "WHERE"
LINE 6: ...     (lot INNER JOIN unit ON (lot_id = fk_lot_id) WHERE lot_...
                                                             ^

Is there any way I may first perform filter on the small table, then only I use the filtered result for sub-sequence
join?

Instead of I first join into a very large table, only I perform filtering (which I assume will be slower)

Thanks

Thanks and Regards
Yan Cheng CHEOK