Optimization on JOIN

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

Responses

Browse pgsql-general by date

  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