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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
