Forum .LRN Q&A: Message View in Forums is very slow on Postgresql

Collapse
1: Message View in Forums is very slow on Postgresql
Posted by Dave Bauer on 08/10/02 01:28 PM
Message-view is very slow. For a thread with one message it takes
approximately 8 secs to return the page.

Compare this to the performace of the current 3.x bboard.

I looked but I could not find any queries that were running slowly.
Could it possibly be the permission checking code?

Speeding this up is a big priority for the new openacs.org site.

Collapse
2: Response to Message View in Forums is very slow on Postgresql (response to 1)
Posted by Dave Bauer on 08/10/02 01:43 PM
I suspect the function calls in this query are the culprit: 
 select forums_messages.*,
                   person__name(forums_messages.user_id) as user_name,
                   party__email(forums_messages.user_id) as user_email,
                   forums_forum__name(forums_messages.forum_id) as forum_name,
                   forums_message__root_message_id(forums_messages.message_id) 
as root_message_id,
                   (select fm2.subject
                    from forums_messages fm2
                    where fm2.message_id = forums_message__root_message_id(foru
ms_messages.message_id)) as root_subject,
                   to_char(forums_messages.posting_date, 'Mon DD YYYY HH24:MI:S
S') as posting_date
            from forums_messages
            where forums_messages.message_id= :message_id
This is in the proc forum::message::get, the select_message query.
Collapse
3: Response to Message View in Forums is very slow on Postgresql (response to 1)
Posted by Dan Wickstrom on 08/10/02 03:02 PM
The main query should be fast assuming there is an index forums_messages.message_id.  Go into psql and try running each of the functions individually.  It should be obvious which function call is slow.

It seems likely that forums_message__root_message_id could be the culprit.  I'm assuming that it probably does some kind of recursion or tree query internally.

Collapse
4: Response to Message View in Forums is very slow on Postgresql (response to 1)
Posted by Dan Wickstrom on 08/10/02 03:17 PM
... or possibly the subquery for root_subject is triggering a sequential scan ...
Collapse
5: Response to Message View in Forums is very slow on Postgresql (response to 1)
Posted by Don Baccus on 08/10/02 07:11 PM
Post the "explain" for the query plan, Dave, it will be a lot easier to answer (remember the "pre" tag!).  That will tell us right off whether or not the subselect triggers a sequential scan.

If the forums data model carries its own tree sortkey in addition to the one for objects, make sure there's an index on it.  It would be easy enough to forget this when the port was made.

But posting the query plan is the first step.

Collapse
6: Response to Message View in Forums is very slow on Postgresql (response to 1)
Posted by Dave Bauer on 08/11/02 04:54 AM
Here is the query plan: 
NOTICE:  QUERY PLAN:

Index Scan using forums_messages_pk on forums_messages  (cost=0.00..3.92 rows=1 width=108)
  SubPlan
    ->  Seq Scan on forums_messages fm2  (cost=0.00..2141.01 rows=1 width=12)

EXPLAIN
I also tried explain verbose, but it have way too much information I think. Here is the table description. It looks like the tree_sortkey has an index. 
openacs.org-dev=# d forums_messages
                        Table "forums_messages"
     Attribute     |           Type           |        Modifier        
-------------------+--------------------------+------------------------
 message_id        | integer                  | not null
 forum_id          | integer                  | 
 subject           | character varying(200)   | 
 content           | text                     | 
 html_p            | character(1)             | not null default 'f'
 user_id           | integer                  | not null
 posting_date      | timestamptz | not null default now()
 state             | character varying(100)   | 
 parent_id         | integer                  | 
 open_p            | character(1)             | not null default 't'
 tree_sortkey      | bit varying              | 
 max_child_sortkey | bit varying              | 
Indices: forums_mess_sk_forum_un,
         forums_messages_pk
Constraints: ((open_p = 't'::bpchar) OR (open_p = 'f'::bpchar))
             (((state = 'pending'::"varchar") OR (state = 'approved'::"varchar")) OR (state = 'rejected'::"varchar"))
             ((html_p = 't'::bpchar) OR (html_p = 'f'::bpchar))
Collapse
7: Response to Message View in Forums is very slow on Postgresql (response to 1)
Posted by Dave Bauer on 08/11/02 04:58 AM
I ran the subselect seperately and it takes a few seconds to run. So I suspect this is the culprit. Also the query plan shows a sequential scan on forums_messages with the query.
Collapse
8: Response to Message View in Forums is very slow on Postgresql (response to 1)
Posted by Dan Wickstrom on 08/11/02 05:41 AM
Try replacing the forums_message__root_message_id(forums_messages.message_id) function call with a subquery that returns the equivalent result, or try putting a functional index on forums_message__root_message_id(forums_messages.message_id).  Either method should eliminate the sequential scan.
Collapse
9: Response to Message View in Forums is very slow on Postgresql (response to 1)
Posted by Dave Bauer on 08/11/02 08:18 PM
I have replaced the subselect 
(select fm2.subject
                    from forums_messages fm2
                    where fm2.message_id = forums_message__root_message_id(foru
ms_messages.message_id)) as root_subject
with 
(select forums_message__get_subject(forums_message__root_message_id(forums_messages.message_id)) as root_subject
and created this function 

create function forums_message__get_subject(integer) returns varchar
as '
declare
  p_message_id  alias for $1;
  v_subject     varchar;
begin

        select subject into v_subject from
        forums_messages where message_id=p_message_id;
return v_subject;
end;' language 'plpgsql';
which causes postgresql to use an index scan to get the subject. Response time is greatly improved.