Re: One source of constant annoyance identified

On Wed, 3 Jul 2002 10:34:53 +0200, "Markus Wollny"
<Markus(dot)Wollny(at)computec(dot)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