SQL performance issue : forum_get_topics executes in 720 ms

Dear Damien, please reopen.
This grave issue needs review from the community.

This is such an important issue at Drupal that it could double the speed of the whole framework.
So let us leave this issue open until core developers fix this either locally or in the database abstraction layer.

This is line 574 from forum.module

$sql = db_rewrite_sql("SELECT n.nid, r.tid, n.title, n.type, n.sticky, u.name, u.uid, n.created AS timestamp, n.comment AS comment_mode, l.last_comment_timestamp, IF(l.last_comment_uid != 0, cu.name, l.last_comment_name) AS last_comment_name, l.last_comment_uid, l.comment_count AS num_comments, f.tid AS forum_tid FROM {node_comment_statistics} l INNER JOIN {node} n ON n.nid = l.nid INNER JOIN {users} cu ON l.last_comment_uid = cu.uid INNER JOIN {term_node} r ON n.vid = r.vid INNER JOIN {users} u ON n.uid = u.uid INNER JOIN {forum} f ON n.vid = f.vid WHERE n.status = 1 AND r.tid = %d");

I don't understand where to look further.
Thanks for your help, I wait further.

Damien, I upgrade to Drupal-dev meanwhile to make sure we use the same code base.

Okay Damien, I install dev module or display these values by hand.

Stay tuned, I am learning the devel module.

Yes, I read _db_rewrite_sql() and I think it is a flow in design to let a computer run DISTINCT automaticaly when most of the time the query produces distinct results. I installed dev module, I output the information you nee. This is a cool module.

Look for example, this is my forum:

2380.57
1
forum_get_forums
SELECT ncs.last_comment_timestamp, IF (ncs.last_comment_uid != 0, u2.name, ncs.last_comment_name) AS last_comment_name, ncs.last_comment_uid FROM (SELECT DISTINCT ON (nid) * FROM node) n INNER JOIN users u1 ON n.uid = u1.uid INNER JOIN term_node tn ON n.vid = tn.vid INNER JOIN node_comment_statistics ncs ON n.nid = ncs.nid INNER JOIN users u2 ON ncs.last_comment_uid=u2.uid WHERE n.status = 1 AND tn.tid = 24 ORDER BY ncs.last_comment_timestamp DESC LIMIT 1 OFFSET 0
1100.26
1
forum_get_forums
SELECT ncs.last_comment_timestamp, IF (ncs.last_comment_uid != 0, u2.name, ncs.last_comment_name) AS last_comment_name, ncs.last_comment_uid FROM (SELECT DISTINCT ON (nid) * FROM node) n INNER JOIN users u1 ON n.uid = u1.uid INNER JOIN term_node tn ON n.vid = tn.vid INNER JOIN node_comment_statistics ncs ON n.nid = ncs.nid INNER JOIN users u2 ON ncs.last_comment_uid=u2.uid WHERE n.status = 1 AND tn.tid = 27 ORDER BY ncs.last_comment_timestamp DESC LIMIT 1 OFFSET 0
639.03
1
forum_get_forums
SELECT ncs.last_comment_timestamp, IF (ncs.last_comment_uid != 0, u2.name, ncs.last_comment_name) AS last_comment_name, ncs.last_comment_uid FROM (SELECT DISTINCT ON (nid) * FROM node) n INNER JOIN users u1 ON n.uid = u1.uid INNER JOIN term_node tn ON n.vid = tn.vid INNER JOIN node_comment_statistics ncs ON n.nid = ncs.nid INNER JOIN users u2 ON ncs.last_comment_uid=u2.uid WHERE n.status = 1 AND tn.tid = 50 ORDER BY ncs.last_comment_timestamp DESC LIMIT 1 OFFSET 0

These queries are natural distinct queries, no need to force them distinct:

SELECT ncs.last_comment_timestamp, IF (ncs.last_comment_uid != 0, u2.name, ncs.last_comment_name) AS last_comment_name, ncs.last_comment_uid FROM node n INNER JOIN users u1 ON n.uid = u1.uid INNER JOIN term_node tn ON n.vid = tn.vid INNER JOIN node_comment_statistics ncs ON n.nid = ncs.nid INNER JOIN users u2 ON ncs.last_comment_uid=u2.uid WHERE n.status = 1 AND tn.tid = 24 ORDER BY ncs.last_comment_timestamp DESC LIMIT 1 OFFSET 0
executes in 62 ms not 2380.57

Wait, I give you the needed information.

Okay. Wait.

Disconnecting and browsing under Anonymous account gives instant results.

On the converse using a simple user returns very long queries
I need to wait 10 seconds to display the forum list.

Executed 361 queries in 11516.66 milliseconds. Queries taking longer than 30 ms and queries executed more than once, are highlighted. Page execution time was 11681.42 ms.
ms
#
where
query
2339.48
1
forum_get_forums
SELECT ncs.last_comment_timestamp, IF (ncs.last_comment_uid != 0, u2.name, ncs.last_comment_name) AS last_comment_name, ncs.last_comment_uid FROM (SELECT DISTINCT ON (nid) * FROM node) n INNER JOIN users u1 ON n.uid = u1.uid INNER JOIN term_node tn ON n.vid = tn.vid INNER JOIN node_comment_statistics ncs ON n.nid = ncs.nid INNER JOIN users u2 ON ncs.last_comment_uid=u2.uid INNER JOIN node_access na ON na.nid = n.nid WHERE (na.grant_view >= 1 AND ((na.gid = 0 AND na.realm = 'all'))) AND ( n.status = 1 AND tn.tid = 24 )ORDER BY ncs.last_comment_timestamp DESC LIMIT 1 OFFSET 0
1075.42
1
forum_get_forums
SELECT ncs.last_comment_timestamp, IF (ncs.last_comment_uid != 0, u2.name, ncs.last_comment_name) AS last_comment_name, ncs.last_comment_uid FROM (SELECT DISTINCT ON (nid) * FROM node) n INNER JOIN users u1 ON n.uid = u1.uid INNER JOIN term_node tn ON n.vid = tn.vid INNER JOIN node_comment_statistics ncs ON n.nid = ncs.nid INNER JOIN users u2 ON ncs.last_comment_uid=u2.uid INNER JOIN node_access na ON na.nid = n.nid WHERE (na.grant_view >= 1 AND ((na.gid = 0 AND na.realm = 'all'))) AND ( n.status = 1 AND tn.tid = 27 )ORDER BY ncs.last_comment_timestamp DESC LIMIT 1 OFFSET 0
712.09
1
forum_get_forums
SELECT r.tid, COUNT(n.nid) AS topic_count, SUM(l.comment_count) AS comment_count FROM (SELECT DISTINCT ON (nid) * FROM node) n INNER JOIN node_comment_statistics l ON n.nid = l.nid INNER JOIN term_node r ON n.vid = r.vid INNER JOIN node_access na ON na.nid = n.nid WHERE (na.grant_view >= 1 AND ((na.gid = 0 AND na.realm = 'all'))) AND ( n.status = 1 )GROUP BY r.tid
625.64
1
forum_get_forums
SELECT ncs.last_comment_timestamp, IF (ncs.last_comment_uid != 0, u2.name, ncs.last_comment_name) AS last_comment_name, ncs.last_comment_uid FROM (SELECT DISTINCT ON (nid) * FROM node) n INNER JOIN users u1 ON n.uid = u1.uid INNER JOIN term_node tn ON n.vid = tn.vid INNER JOIN node_comment_statistics ncs ON n.nid = ncs.nid INNER JOIN users u2 ON ncs.last_comment_uid=u2.uid INNER JOIN node_access na ON na.nid = n.nid WHERE (na.grant_view >= 1 AND ((na.gid = 0 AND na.realm = 'all'))) AND ( n.status = 1 AND tn.tid = 50 )ORDER BY ncs.last_comment_timestamp DESC LIMIT 1 OFFSET 0
585.69
1
forum_get_forums
SELECT ncs.last_comment_timestamp, IF (ncs.last_comment_uid != 0, u2.name, ncs.last_comment_name) AS last_comment_name, ncs.last_comment_uid FROM (SELECT DISTINCT ON (nid) * FROM node) n INNER JOIN users u1 ON n.uid = u1.uid INNER JOIN term_node tn ON n.vid = tn.vid INNER JOIN node_comment_statistics ncs ON n.nid = ncs.nid INNER JOIN users u2 ON ncs.last_comment_uid=u2.uid INNER JOIN node_access na ON na.nid = n.nid WHERE (na.grant_view >= 1 AND ((na.gid = 0 AND na.realm = 'all'))) AND ( n.status = 1 AND tn.tid = 17 )ORDER BY ncs.last_comment_timestamp DESC LIMIT 1 OFFSET 0
203.41
1
forum_get_forums
SELECT ncs.last_comment_timestamp, IF (ncs.last_comment_uid != 0, u2.name, ncs.last_comment_name) AS last_comment_name, ncs.last_comment_uid FROM (SELECT DISTINCT ON (nid) * FROM node) n INNER JOIN users u1 ON n.uid = u1.uid INNER JOIN term_node tn ON n.vid = tn.vid INNER JOIN node_comment_statistics ncs ON n.nid = ncs.nid INNER JOIN users u2 ON ncs.last_comment_uid=u2.uid INNER JOIN node_access na ON na.nid = n.nid WHERE (na.grant_view >= 1 AND ((na.gid = 0 AND na.realm = 'all'))) AND ( n.status = 1 AND tn.tid = 22 )ORDER BY ncs.last_comment_timestamp DESC LIMIT 1 OFFSET 0
[...]

0.24
113
acl_node_grants
SELECT acl_id FROM acl_user WHERE uid = 4284
0.24
113
acl_node_grants
SELECT acl_id FROM acl_user WHERE uid = 4284
0.24
113
acl_node_grants
SELECT acl_id FROM acl_user WHERE uid = 4284
0.24
113
acl_node_grants
SELECT acl_id FROM acl_user WHERE uid = 4284
0.24
113
acl_node_grants
SELECT acl_id FROM acl_user WHERE uid = 4284
0.24
113
acl_node_grants
SELECT acl_id FROM acl_user WHERE uid = 4284
0.23
113
acl_node_grants
SELECT acl_id FROM acl_user WHERE uid = 4284
0.23
113
acl_node_grants
SELECT acl_id FROM acl_user WHERE uid = 4284
0.2
113
acl_node_grants
SELECT acl_id FROM acl_user WHERE uid = 4284
Memory usage:
Memory used at: devel_init()=0.67 MB, devel_shutdown()=8.28 MB.

Dear Damien, I am upgrading to dev to make sure we are using the same code base.

After upgrading to Drupal-dev, browsing under anonymous returns long queries too:

Executed 300 queries in 10848.98 milliseconds. Queries taking longer than 30 ms and queries executed more than once, are highlighted. Page execution time was 11063.85 ms.
ms
#
where
query
2470.37
1
forum_get_forums
SELECT ncs.last_comment_timestamp, IF (ncs.last_comment_uid != 0, u2.name, ncs.last_comment_name) AS last_comment_name, ncs.last_comment_uid FROM (SELECT DISTINCT ON (nid) * FROM node) n INNER JOIN users u1 ON n.uid = u1.uid INNER JOIN term_node tn ON n.vid = tn.vid INNER JOIN node_comment_statistics ncs ON n.nid = ncs.nid INNER JOIN users u2 ON ncs.last_comment_uid=u2.uid INNER JOIN node_access na ON na.nid = n.nid WHERE (na.grant_view >= 1 AND ((na.gid = 0 AND na.realm = 'all'))) AND ((n.type NOT IN ('project_project', 'project_issue'))) AND ( n.status = 1 AND tn.tid = 24 )ORDER BY ncs.last_comment_timestamp DESC LIMIT 1 OFFSET 0

Okay, I am lost. Could you tell me exactly how to output the resquested debug information.

Dear Damien, I remove the contributed access module and it works again:

Executed 331 queries in 2289.42 milliseconds. Queries taking longer than 30 ms and queries executed more than once, are highlighted. Page execution time was 2773.9 ms.
ms
#
where
query
401.46
1
forum_get_forums
SELECT ncs.last_comment_timestamp, IF (ncs.last_comment_uid != 0, u2.name, ncs.last_comment_name) AS last_comment_name, ncs.last_comment_uid FROM node n INNER JOIN users u1 ON n.uid = u1.uid INNER JOIN term_node tn ON n.vid = tn.vid INNER JOIN node_comment_statistics ncs ON n.nid = ncs.nid INNER JOIN users u2 ON ncs.last_comment_uid=u2.uid WHERE n.status = 1 AND tn.tid = 3 ORDER BY ncs.last_comment_timestamp DESC LIMIT 1 OFFSET 0
389.69
1
forum_get_forums
SELECT ncs.last_comment_timestamp, IF (ncs.last_comment_uid != 0, u2.name, ncs.last_comment_name) AS last_comment_name, ncs.last_comment_uid FROM node n INNER JOIN users u1 ON n.uid = u1.uid INNER JOIN term_node tn ON n.vid = tn.vid INNER JOIN node_comment_statistics ncs ON n.nid = ncs.nid INNER JOIN users u2 ON ncs.last_comment_uid=u2.uid WHERE n.status = 1 AND tn.tid = 2 ORDER BY ncs.last_comment_timestamp DESC LIMIT 1 OFFSET 0
372.71
1
forum_get_forums
SELECT ncs.last_comment_timestamp, IF (ncs.last_comment_uid != 0, u2.name, ncs.last_comment_name) AS last_comment_name, ncs.last_comment_uid FROM node n INNER JOIN users u1 ON n.uid = u1.uid INNER JOIN term_node tn ON n.vid = tn.vid INNER JOIN node_comment_statistics ncs ON n.nid = ncs.nid INNER JOIN users u2 ON ncs.last_comment_uid=u2.uid WHERE n.status = 1 AND tn.tid = 4 ORDER BY ncs.last_comment_timestamp DESC LIMIT 1 OFFSET 0

Three seconds instead of 12.

But I still do not understand why results are not cached. I would save a tremendous time on normal middle-size forums. My cache is set to 3 minutes. It is enough. Large forums should use a kind of cache to display.

Well, the DISTINCT is legit here, but it is not at the correct place. This query should be:

SELECT DISTINCT ncs.last_comment_timestamp, IF (ncs.last_comment_uid != 0, u2.name, ncs.last_comment_name) AS last_comment_name, ncs.last_comment_uid FROM node n INNER JOIN users u1 ON n.uid = u1.uid INNER JOIN term_node tn ON n.vid = tn.vid INNER JOIN node_comment_statistics ncs ON n.nid = ncs.nid INNER JOIN users u2 ON ncs.last_comment_uid=u2.uid INNER JOIN node_access na ON na.nid = n.nid WHERE (na.grant_view >= 1 AND ((na.gid = 0 AND na.realm = 'all'))) AND ((n.type NOT IN ('project_project', 'project_issue'))) AND ( n.status = 1 AND tn.tid = 24 )ORDER BY ncs.last_comment_timestamp DESC LIMIT 1

{node] carries distinct values.
The following query starts processing on {node} and does only INNER JOINS.
Therefore there is no need to force DISTINCT values.

SELECT ncs.last_comment_timestamp, IF (ncs.last_comment_uid != 0, u2.name, ncs.last_comment_name) AS last_comment_name, ncs.last_comment_uid 
FROM node n 
INNER JOIN users u1 ON n.uid = u1.uid 
INNER JOIN term_node tn ON n.vid = tn.vid 
INNER JOIN node_comment_statistics ncs ON n.nid = ncs.nid 
INNER JOIN users u2 ON ncs.last_comment_uid=u2.uid 
INNER JOIN node_access na ON na.nid = n.nid 
WHERE (na.grant_view >= 1 AND ((na.gid = 0 AND na.realm = 'all'))) AND ((n.type NOT IN ('project_project', 'project_issue'))) AND ( n.status = 1 AND tn.tid = 24 )ORDER BY ncs.last_comment_timestamp DESC LIMIT 1

So Drupal code is exact.

Again, when developers understand the logic of DISTINCT, there is no need to add it to SQL.
Adding a DISTINCT on large tables can destroy a query when not needed.

Okay, I understand:

SELECT ncs.last_comment_timestamp, IF (ncs.last_comment_uid != 0, u2.name, ncs.last_comment_name) AS last_comment_name, ncs.last_comment_uid FROM node n INNER JOIN users u1 ON n.uid = u1.uid INNER JOIN term_node tn ON n.vid = tn.vid INNER JOIN node_comment_statistics ncs ON n.nid = ncs.nid INNER JOIN users u2 ON ncs.last_comment_uid=u2.uid WHERE n.status = 1 AND tn.tid = 2 ORDER BY ncs.last_comment_timestamp DESC LIMIT 1 OFFSET 0

Is a complex query returning no result. It is trying to fetch the latest comment on the database container. Therefore it can only result in nothing.

In this case, the cache does not seem to record the result.
Thefore Drupal runs the query, which gets very long answers because of the huge database.

Right?

@jmpoure: you don't understand how DISTINCT and JOINS work.
SELECT t1.id FROM table1 t1 INNER JOIN table2 t2 ON t2.id = t1.id
^ This query *will* return duplicate entries, even if id is a primary key for table1.
That's exactly what happens here, and why the DISTINCT is needed.

Dear Damien.
I am afraid no.

In your case, t1.id is a primary key, thus unique.
In SQL92, primary key implies unique Index.

SELECT t1.id FROM table1 t1
produces a unique set of t1.id because it is unique by defaut in the database.

When people wrote SQL, they set primary keys and constraint to make sure that records were unique from the entry of the database.
The database make sure t1.id is unique on entry.

Then you agree that this is exactly the same as:
SELECT DISTINCT t1.id FROM table1 t1

Then the INNER JOINS produces unique output, by defaut, because it is only inclusive.

Thefore:

Is exactly the same as:
<code>SELECT DISTINCT t1.id 
FROM table1 t1 
INNER JOIN table2 t2 ON t2.id = t1.id

Do we agree?

Now in the upper query, last_comment_timestamp and last_comment_name are unique.
A single user cannot enter two comments on the same exact Unix time.

Thefore, the query is unique.
Note that you can force uniqueness setting a double unique index.

Setting unique indexes is the way to make sure tables produces distinct results.

Dear Damien, I tested setting a constraint on a 700.000 entries comment table and some uid,timestamp are not unique. So you are right for the query, but wrong in general about INNER JOINs. But I guess we agree. So you are right to say Drupal should run the DISTINCT clause. But why do we have duplicates?

SELECT COUNT (DISTINCT (uid, timestamp)) FROM comments
647021

SELECT COUNT (*) FROM comments
647224

SELECT t1.id 
FROM table1 t1 
INNER JOIN table2 t2 ON t2.id = t1.id

is the same as

SELECT DISTINCT t1.id 
FROM table1 t1 
INNER JOIN table2 t2 ON t2.id = t1.id

when t1.Id is unique or primary key

If the join is performed on a non unique index, then it may produce duplicates.
So I agree with you.

Okay. Do you know why some of my empty SQL results are not cached?
It seems like queries returning empty results are not cached. Right?

For example:

ms
#
where
query
401.46
1
forum_get_forums
SELECT ncs.last_comment_timestamp, IF (ncs.last_comment_uid != 0, u2.name, ncs.last_comment_name) AS last_comment_name, ncs.last_comment_uid FROM node n INNER JOIN users u1 ON n.uid = u1.uid INNER JOIN term_node tn ON n.vid = tn.vid INNER JOIN node_comment_statistics ncs ON n.nid = ncs.nid INNER JOIN users u2 ON ncs.last_comment_uid=u2.uid WHERE n.status = 1 AND tn.tid = 3 ORDER BY ncs.last_comment_timestamp DESC LIMIT 1 OFFSET 0

This is quite bad, because without this Drupal would be lightning speed.
I will enquire more ... and get back.

Many thanks Damien for help and YES you were right.

The ACL module needs some fixing, urgently.

Dear Damien, I wrote this :

Devel should warn that SQL execution times are from cache
http://drupal.org/node/559158

This is especially with queries returning natural DISTINCT VALUES and that have a DISTINCT in the query.
It make very long queries and people don't notice it Devel module.

If you would like to comment, you are welcome.

Okay, I think I found the issue debugging the SQL query server side.

The query:

SELECT ncs.last_comment_timestamp, IF (ncs.last_comment_uid != 0, u2.name, ncs.last_comment_name) AS last_comment_name, ncs.last_comment_uid
FROM node n
INNER JOIN users u1 ON n.uid = u1.uid
INNER JOIN term_node tn ON n.vid = tn.vid
INNER JOIN node_comment_statistics ncs ON n.nid = ncs.nid
INNER JOIN users u2 ON ncs.last_comment_uid=u2.uid
WHERE n.status = 1 AND tn.tid = 3
ORDER BY ncs.last_comment_timestamp DESC LIMIT 1 OFFSET 0

400 ms
should be replaced with:

SELECT ncs.last_comment_timestamp, IF (ncs.last_comment_uid != 0, u2.name, ncs.last_comment_name) AS last_comment_name, ncs.last_comment_uid
FROM node n
LEFT JOIN users u1 ON n.uid = u1.uid
LEFT JOIN term_node tn ON n.vid = tn.vid
LEFT JOIN node_comment_statistics ncs ON n.nid = ncs.nid
LEFT JOIN users u2 ON ncs.last_comment_uid=u2.uid
WHERE n.status = 1 AND tn.tid = 3
ORDER BY ncs.last_comment_timestamp DESC
LIMIT 1 OFFSET 0

63 ms.

When a query needs to kwno whether there is a record or not (LIMIT 1 OFFSET 0),
INNER JOINS should be replaced with LEFT JOINS.

Internaly, databases treat INNER JOINS like LEFT JOINS with a DISTINCT CLAUSE.
On large databases like mine this ends-up with a nested loop and DISTINCT sort.

This can be nightmare for a database system.

Bingo:

Executed 299 queries in 1144.19 milliseconds. Queries taking longer than 30 ms and queries executed more than once, are highlighted. Page execution time was 1284.1 ms.
ms
#
where
query
215.43
1
forum_get_forums
SELECT r.tid, COUNT(n.nid) AS topic_count, SUM(l.comment_count) AS comment_count FROM node n INNER JOIN node_comment_statistics l ON n.nid = l.nid INNER JOIN term_node r ON n.vid = r.vid WHERE n.status = 1 GROUP BY r.tid
141.1
1
forum_get_forums
SELECT ncs.last_comment_timestamp, IF (ncs.last_comment_uid != 0, u2.name, ncs.last_comment_name) AS last_comment_name, ncs.last_comment_uid FROM node n LEFT JOIN users u1 ON n.uid = u1.uid LEFT JOIN term_node tn ON n.vid = tn.vid LEFT JOIN node_comment_statistics ncs ON n.nid = ncs.nid LEFT JOIN users u2 ON ncs.last_comment_uid=u2.uid WHERE n.status = 1 AND tn.tid = 8 ORDER BY ncs.last_comment_timestamp DESC LIMIT 1 OFFSET 0
140.13
1
forum_get_forums
SELECT ncs.last_comment_timestamp, IF (ncs.last_comment_uid != 0, u2.name, ncs.last_comment_name) AS last_comment_name, ncs.last_comment_uid FROM node n LEFT JOIN users u1 ON n.uid = u1.uid LEFT JOIN term_node tn ON n.vid = tn.vid LEFT JOIN node_comment_statistics ncs ON n.nid = ncs.nid LEFT JOIN users u2 ON ncs.last_comment_uid=u2.uid WHERE n.status = 1 AND tn.tid = 9 ORDER BY ncs.last_comment_timestamp DESC LIMIT 1 OFFSET 0

Excution time divided by two for the forum code !!!
So funny.

Patch attached, please verify under MySQL using Devel module.
Under PostgreSQL, the result is quite impressive.
MySQL internals also treat INNER JOINS like LEFT JOINS with a distinct clause, so this should result in the same gain.

I am going to open a dedicated issue thread for database and start hunting for LIMIT 1 OFFSET 0 queries.
Your guys should leave MySQL, which is not a very serious database and start using PostgreSQL exclusively for devel.

Patch needs review on a large forum under MySQL.
Otherwize if you know database programming apply safely.

I fixed the explanation about LEFT JOINs vs INNER JOINS here:
Replace INNER JOINS with LEFT JOINS whenever possible

INNER JOINs are processed as LEFT JOINs and then the parser looks for NULL values to remove them.

On large tables, an INNER JOIN has more chances to result in a sequential scan, either in memory or on disc. When SORT BY is used, it may even be worse.

On the converse, a LEFT JOIN has more chances to be optimized by the parser, as the database will not bother for removing NULLs thus will be able to access data directly. This leads to performance result ranging from 1 to 5 or more.

This is true for PostgreSQL and MySQL and many other databases.
LEFT JOINs are known to be faster than INNER JOINs in all databases systems.

Sorry, there is no link with DISTINCT.
I wrote that texte during the night and was quite tired.

I hope that you have enough information now.

Please apply the patch safely.

Thanks I am in contact with PostgreSQL hackers and we enquire on that query.

It seems that LEFT JOINs in MySQL do not execute as fast they could.
According to PostgreSQL developers, this is a know issue.

In PostgreSQL, I can execute this query in 63ms. Maybe drive it further down.
v.s. 200ms under MySQL (see previous post)

So the best is to let us enquire and I will report back later on.

My plan:

EXPLAIN ANALYSE
SELECT ncs.last_comment_timestamp, IF (ncs.last_comment_uid != 0, u2.name, ncs.last_comment_name) AS last_comment_name, ncs.last_comment_uid
FROM node n
LEFT JOIN users u1 ON n.uid = u1.uid
LEFT JOIN term_node tn ON n.vid = tn.vid
LEFT JOIN node_comment_statistics ncs ON n.nid = ncs.nid
LEFT JOIN users u2 ON ncs.last_comment_uid=u2.uid
WHERE n.status = 1 AND tn.tid = 3
ORDER BY ncs.last_comment_timestamp DESC
LIMIT 1 OFFSET 0 

"Limit  (cost=1156.57..1156.57 rows=1 width=17) (actual time=0.020..0.020 rows=0 loops=1)"
"  ->  Sort  (cost=1156.57..1156.85 rows=112 width=17) (actual time=0.019..0.019 rows=0 loops=1)"
"        Sort Key: ncs.last_comment_timestamp"
"        Sort Method:  quicksort  Memory: 25kB"
"        ->  Nested Loop Left Join  (cost=5.13..1156.01 rows=112 width=17) (actual time=0.014..0.014 rows=0 loops=1)"
"              ->  Nested Loop Left Join  (cost=5.13..1123.24 rows=112 width=21) (actual time=0.014..0.014 rows=0 loops=1)"
"                    ->  Nested Loop Left Join  (cost=5.13..1090.76 rows=112 width=13) (actual time=0.014..0.014 rows=0 loops=1)"
"                          ->  Nested Loop  (cost=5.13..1054.66 rows=112 width=8) (actual time=0.014..0.014 rows=0 loops=1)"
"                                ->  Bitmap Heap Scan on term_node tn  (cost=5.13..246.19 rows=112 width=4) (actual time=0.013..0.013 rows=0 loops=1)"
"                                      Recheck Cond: ((tid)::integer = 3)"
"                                      ->  Bitmap Index Scan on term_node_tid_idx  (cost=0.00..5.10 rows=112 width=0) (actual time=0.012..0.012 rows=0 loops=1)"
"                                            Index Cond: ((tid)::integer = 3)"
"                                ->  Index Scan using node_vid_idx on node n  (cost=0.00..7.21 rows=1 width=12) (never executed)"
"                                      Index Cond: ((n.vid)::integer = (tn.vid)::integer)"
"                                      Filter: (n.status = 1)"
"                          ->  Index Scan using node_comment_statistics_pkey on node_comment_statistics ncs  (cost=0.00..0.31 rows=1 width=13) (never executed)"
"                                Index Cond: (n.nid = (ncs.nid)::integer)"
"                    ->  Index Scan using users_pkey on users u2  (cost=0.00..0.28 rows=1 width=12) (never executed)"
"                          Index Cond: (ncs.last_comment_uid = u2.uid)"
"              ->  Index Scan using users_pkey on users u1  (cost=0.00..0.28 rows=1 width=4) (never executed)"
"                    Index Cond: (n.uid = u1.uid)"
"Total runtime: 0.089 ms"

As you can notice, a cast happens which forces nested loop.
If we can avoid this cast, the query may execute in 20 ms or less.
We are enquiring on that issue.

I will get back to you.
This is extremely interesting to be able to execute queries on large installs to analyse plans and results.

Kind regards,
Jean-Michel

I closed this issue.

The planner does not have enough information to find the right query plan.
Eventually, the query planner may even rewrite the query itself.

My database is running on a 650.000 nodes install.
But there are no real connections from the internet.

I will only be able to review this issue when going live.

Reference: http://drupal.org/node/562180