SQL performance issue: SQL forum bloc executes in 450ms

Dear all,

Looking at my PostgreSQL logs on a testing server with 500.000 messages in a forum (migrated from Drupal), I noticed this long running query:

2009-08-25 13:38:47 CEST LOG: durée : 454.078 ms, instruction : SELECT n.nid, n.title, l.comment_count, l.last_comment_timestamp FROM (SELECT DISTINCT ON (nid) * FROM node) n INNER JOIN term_node tn ON tn.vid = n.vid INNER JOIN term_data td ON td.tid = tn.tid INNER JOIN node_comment_statistics l ON n.nid = l.nid WHERE n.status = 1 AND td.vid = 1 ORDER BY l.last_comment_timestamp DESC LIMIT 5 OFFSET 0

This means that if 1000 users were connected as usual, the quad processor server running Debian SID would probably explode. On a production server, each SQL query should rarely exceed 40 ms. Here it is 450 ms.

Looking at the query, the problem comes from (SELECT DISTINCT ON (nid) * FROM node).
This creates a huge index scan on 500. 000 messages and create a distinct aggregate.
Then the database has to scan the index and build INNER JOINS.

This kind of query is very surprising.

So I looked at the code and discovered theses queries in forum.module:

$sql = db_rewrite_sql("SELECT n.nid, n.title, l.comment_count, l.last_comment_timestamp FROM {node} n INNER JOIN {term_node} tn ON tn.vid = n.vid INNER JOIN {term_data} td ON td.tid = tn.tid INNER JOIN {node_comment_statistics} l ON n.nid = l.nid WHERE n.status = 1 AND td.vid = %d ORDER BY l.last_comment_timestamp DESC");

and

$sql = db_rewrite_sql("SELECT n.nid, n.title, l.comment_count FROM {node} n INNER JOIN {term_node} tn ON tn.vid = n.vid INNER JOIN {term_data} td ON td.tid = tn.tid INNER JOIN {node_comment_statistics} l ON n.nid = l.nid WHERE n.status = 1 AND td.vid = %d ORDER BY n.nid DESC");

The SQL code seems to be ANSI compliant, so there is no need to rewrite queries.

I removed db_rewrite_sql and it solved the performance issue.
Please find attached is a patch.

Please review and comment.
I would like this applied as soon as possible.

Kind regards,
Jean-Michel Pouré