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

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

Now I think it's the bit with the LIKEs that kills us, especially as the
database refuses to create an index on MESSAGE.TEXT for it being to big
or whatever - search me, but it just wouldn't do it (the field is of
type varchar with a maximum length of 10,000 characters). This query is
a true killer, taking over 2 minutes to complete while eating up more
than a quarter of a gig of memory. Oracle wasn't too bothered about this
one, but now PostgreSQL seems to act very differently...

Now as far as I searched through the docs and the archives, there's this
fulltext-search method provided in CONTRIB
(http://developer.postgresql.org/cvsweb.cgi/pgsql/contrib/fulltextindex/
?only_with_tag=REL7_2_STABLE); is this an equivalent of the
conText-cartridge provided by Oracle? This lack for a full-text-search
might be the main issue in our attempts to migrate from Oracle to
PostgreSQL, so to me it looks like it might just be the saving straw.
Please feel free to correct me if I'm wrong...

I never had much experience with this CVS-system and as yet left it to
those ambitous enough to tinker with the innards of their projects, but
right now it seems like I am forced to risk a try... Unfortunately from
my point of view this thing lacks a bit in terms of documentation - do I
need to recompile the whole of PostgreSQL or just this bit? How would I
go about installing it up to the point of actually running and making
use of it on two columns (TITLE and TEXT in the MESSAGE-table)?

Or am I completely misled concerning this fulltext-search-option - and
there's some cheaper way out to speed things up without reducing
functionality?

Regards,

Markus