This is a test post from vimpress.

[DB] a good tutorial on how to optimize postgresql

http://www.postgresql.org/docs/8.1/interactive/performance-tips.html

Yeah, the documentation of Postgresql is a very good source to learn optimization of postgresql. Read it carefully and periodically.

[DB]The difference between ON and WHERE

“Note: The join condition of an inner join can be written either in the WHERE clause or in the JOIN clause. For example, these table expressions are equivalent:

FROM a, b WHERE a.id = b.id AND b.val > 5

and

FROM a INNER JOIN b ON (a.id = b.id) WHERE b.val > 5

or perhaps even

FROM a NATURAL JOIN b WHERE b.val > 5

Which one of these you use is mainly a matter of style. The JOIN syntax in the FROM clause is probably not as portable to other SQL database management systems. For outer joins there is no choice in any case: they must be done in the FROM clause. An ON/USING clause of an outer join is not equivalent to a WHERE condition, because it determines the addition of rows (for unmatched input rows) as well as the removal of rows from the final result. “

The above document comes from http://www.postgresql.org/docs/8.1/interactive/queries-table-expressions.html, the documentation of postgres.
In short, that means for INNER JOIN, the conditions under ON or WHERE have the same effect;but for OUTER JOIN, the conditions under ON and WHERE are different. After some thought, I think conditions under ON will be applied during or before table-joining, but conditions under WHERE will be applied after the table-joining. The time when conditions are applied is the key point.

For example:

FROM a, b WHERE a.id = b.id AND b.val > 5 [1]

and

FROM a INNER JOIN b ON (a.id = b.id) WHERE b.val > 5 [2]

are equivalent, but

FROM a LEFT JOIN b ON a.id = b.id AND b.val > 5 [3]

and

FROM a LEFT JOIN b ON a.id = b.id WHERE b.val > 5 [4]

are different.

For query [3], condition b.val > 5 will be applied to table b before table a joins table b;
For query [4], condition b.val > 5 will be applied to the joined table after table a joins table b;

PS: After passing the WHERE filter, the derived input table may be subject to grouping, using the GROUP BY clause, and elimination of group rows using the HAVING clause.