The 2026 Drupal Business Survey is open. Your response will help shape the future of the Drupal business ecosystem.2009-03-13 17:23:50 CET LOG: durée : 1922.511 ms, instruction : SELECT COUNT(cid) FROM comments c INNER JOIN forum f ON (f.nid = c.nid)
PostgreSQL shows that this query runs in 2 seconds, which is a security issue and can lead to overflow.
Let's look at the query planner:
EXPLAIN ANALYSE ...
"Aggregate (cost=103733.96..103733.97 rows=1 width=4) (actual time=2012.138..2012.138 rows=1 loops=1)"
" -> Hash Join (cost=2151.87..101885.45 rows=739403 width=4) (actual time=30.144..1923.167 rows=646158 loops=1)"
" Hash Cond: (c.nid = (f.nid)::integer)"
" -> Seq Scan on comments c (cost=0.00..80752.03 rows=739403 width=8) (actual time=0.042..424.133 rows=647224 loops=1)"
" -> Hash (cost=1045.72..1045.72 rows=67372 width=4) (actual time=30.045..30.045 rows=67479 loops=1)"
" -> Seq Scan on forum f (cost=0.00..1045.72 rows=67372 width=4) (actual time=0.006..12.665 rows=67479 loops=1)"
"Total runtime: 2012.362 ms"This should be hard-coded and calculated in a cron job.
Comments
Comment #1
grub3 commentedUpcoming PostgreSQL 8.4 offers
sum() OVER
which hard codes the result and answers in 3 ms.
Comment #2
michelleI've told you before, I have optomized the queries within AF to the best of my ability and made optional those with known issues. Most of the bad queries are in core forum, not AF. Posting issue after issue about this is not helpful. If you have patches for either core or AF, feel free to submit those. Otherwise, there is nothing I can do here. I'm not going to hard code the comment count as that is something that changes frequently.
Michelle
Comment #3
grub3 commentedThanks. I will be be looking at the core forum code.
Comment #4
michelleThere are already issues on the core forum performance problems so make sure you search before posting.
Michelle
Comment #5
grub3 commentedI found the resolution. Okay I will be looking at issues. Thanks.