From: | Yan Cheng Cheok <yccheok(at)yahoo(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Optimization on JOIN |
Date: | 2010-01-22 06:59:42 |
Message-ID: | 616471.52100.qm@web65716.mail.ac4.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | DM | 2010-01-22 08:07:05 | When is the release date for Postgres 8.5? |
Previous Message | Greg Smith | 2010-01-22 06:21:59 | Re: Slow Query / Check Point Segments |