Re: One source of constant annoyance identified

On Wed, 3 Jul 2002 10:34:53 +0200, "Markus Wollny"
<Markus.Wollny@computec.de> wrote:
>This one generates an overview over all the forum-threads in one board
>which has 41,624 messages, 2971 of them are FATHER_ID=0, so
                            ^^^^
>thread-starters: [long SQL statement follows]

Markus, that's strange.  Your explain says:
>Sort  (cost=30695.27..30695.27 rows=7693 width=154) (actual
>time=9745.94..9751.58 rows=4663 loops=1)
                            ^^^^
>  ->  Index Scan using idx_bm_show_topics on ct_com_board_message
>message  (cost=0.00..30198.72 rows=7693 width=154) (actual
>time=111.56..9549.99 rows=4663 loops=1)
>        SubPlan
>          ->  Aggregate  (cost=5.83..5.83 rows=1 width=0) (actual
>time=0.91..0.91 rows=1 loops=4663)
>                ->  Index Scan using idx_user_thread_follow on
>ct_com_user_thread_follow  (cost=0.00..5.83 rows=1 width=0) (actual
>time=0.88..0.88 rows=0 loops=4663)
>Total runtime: 9835.57 msec

Anyway, that's not my point here.  I'd want you to do an EXPLAIN
ANALYZE of another SQL statement which does the same IMHO.

From the schema you posted I see that (user_id, thread_id) is the
primary key, or at least a unique index, in CT_COM_USER_THREAD_FOLLOW,
so the sub-select
>(select count(*)
> from CT_COM_USER_THREAD_FOLLOW
> where USER_ID= '295798' and thread_id=MESSAGE.THREAD_ID) as TFUID
can only give 0 or 1.  So following my first rule of thumb "Avoid
subselects; use joins wherever possible" I'd write:

select  MESSAGE.BOARD_ID
      , MESSAGE.THREAD_ID
      , MESSAGE.FATHER_ID
      , MESSAGE.MESSAGE_ID
      , MESSAGE.USER_ID
      , MESSAGE.USER_LOGIN as LOGIN
      , MESSAGE.USER_STATUS as STATUS
      , MESSAGE.USER_RIGHTS as RIGHTS
      , MESSAGE.TITLE
      , MESSAGE.COUNT_REPLY as COUNT_REPLY
      , to_char(MESSAGE.LAST_REPLY,'DD.MM.YY hh24:mi')    as
LAST_REPLY
      , round((date_part('epoch',CURRENT_TIMESTAMP) -
               date_part('epoch',MESSAGE.LAST_REPLY))/60) as
diff_posting
      , to_char(MESSAGE.CREATED,'DD.MM.YY hh24:mi')       as DATUM
      , count(TH.THREAD_ID) as TFUID
from   CT_COM_BOARD_MESSAGE    MESSAGE
       left join CT_COM_USER_THREAD_FOLLOW TH
                 ON (TH.USER_ID='295798' and
                     TH.thread_id=MESSAGE.THREAD_ID)
where  (0=0)
and    MESSAGE.BOARD_ID    = 10
and    MESSAGE.FATHER_ID    = 0
and    MESSAGE.STATE_ID    = 0
order by  MESSAGE.LAST_REPLY    desc;

Could you try to EXPLAIN ANALYZE this and post the result?
And please cross-check whether it returns the same result set as your
original query.

Servus
 Manfred