Re: One source of constant annoyance identified - Mailing list pgsql-general

> And the swapping activity is exactly the problem, isn't it?

Yupp, I guess so; once swapping is reduced, there should be not only
much more fast memory available to the queries but also more processing
time.

> In any case, we can't make much progress until we identify the query
> that is making the backend's address space grow.

This here is one of them (see full text in mail from 13:25):

> -----Ursprüngliche Nachricht-----
> Von: Markus Wollny
> Gesendet: Freitag, 28. Juni 2002 13:25
> An: pgsql-general@postgresql.org
> Betreff: Re: [GENERAL] One source of constant annoyance identified
>
>
> Hi!
>
> It seems I found one of the queries which suck up memory as if there
> were terabytes available.
>
> If a user starts a search for e.g. "Ich brauche Mitleid" on one of our
> websites (site-ID is 43 in this example), we construct our select like
> this:
>
> select      MESSAGE.BOARD_ID
>                 , MESSAGE.THREAD_ID
>                 , MESSAGE.MESSAGE_ID
>                 , MESSAGE.TITLE
>                 , MESSAGE.USER_ID
>                 , USERS.LOGIN
>                 , USERS.STATUS
>                 , USERS.RIGHTS
>                 , to_char(MESSAGE.CREATED,'DD.MM.YY
> hh24:mi') as DATUM
>                 , MESSAGE.COUNT_REPLY
>
>                 , (select count(*) from
> CT_COM_USER_THREAD_FOLLOW where USER_ID= '484387' and
> thread_id=MESSAGE.THREAD_ID) as TFUID
>
>         from        CT_COM_BOARD_MESSAGE    MESSAGE
>                 ,    CT_COM_USER
> USERS
>                 ,    CT_COM_BOARD_RULES    READRULE
>                 ,    CT_COM_SITE_BOARDS    SITE
>         where        SITE.SITE_ID    =    '43'
>
>             and
>             (
>                 lower(MESSAGE.TEXT) like '%ich%'
>             or    lower(MESSAGE.TEXT) like 'ich%'
>             or    lower(MESSAGE.TEXT) like '%ich'
>
>             or    lower(MESSAGE.TITLE) like '%ich%'
>             or    lower(MESSAGE.TITLE) like 'ich%'
>             or    lower(MESSAGE.TITLE) like '%ich'
>
>             )
>
>             and
>             (
>                 lower(MESSAGE.TEXT) like '%brauche%'
>             or    lower(MESSAGE.TEXT) like 'brauche%'
>             or    lower(MESSAGE.TEXT) like '%brauche'
>
>             or    lower(MESSAGE.TITLE) like '%brauche%'
>             or    lower(MESSAGE.TITLE) like 'brauche%'
>             or    lower(MESSAGE.TITLE) like '%brauche'
>
>             )
>
>             and
>             (
>                 lower(MESSAGE.TEXT) like '%mitleid%'
>             or    lower(MESSAGE.TEXT) like 'mitleid%'
>             or    lower(MESSAGE.TEXT) like '%mitleid'
>
>             or    lower(MESSAGE.TITLE) like '%mitleid%'
>             or    lower(MESSAGE.TITLE) like 'mitleid%'
>             or    lower(MESSAGE.TITLE) like '%mitleid'
>
>             )
>
>     and            MESSAGE.STATE_ID    =    0
>     and            MESSAGE.USER_ID        =
> USERS.USER_ID
>     and            USERS.STATUS        >    0
>     and            SITE.BOARD_ID        =
> MESSAGE.BOARD_ID
>     and            READRULE.BOARD_ID    =
> MESSAGE.BOARD_ID
>     and            READRULE.RULE_ID    =    1
>     and            READRULE.VALUE        <=    '5'
>     order by    MESSAGE.LAST_REPLY desc
>

[snip]

> (Markus, you don't happen to have SORT_MEM set to a large value,
> do you?)

Not at all - recommendations are 2-4% of available RAM AFAIK. On a 1GB
machine this would be 21000 to 42000KBs. Nevertheless I reduced it from
these values to a meagre sort_mem = 8192, that's 8MB, but to no avail.
Reducing this value any further doesn't really make too much sense, does
it.

Anyway, I think that one of our main problems is the lack of options for
fulltext-indexing. I'm currently trying to find out how to get FTI (from
CVS/Contrib) up and running, because I hope that this will solve 90% of
my problems if not more :)

Regards,

Markus