SELECT too complex? - Mailing list pgsql-novice

This is a rather ill-defined enquiry. The main reason behind it is to
find out if I'm going down the right path as a Postgres newbie.

I have a set of complex selects that I'm worried about from the point of
view of 1) can't these be made simpler 2) performance 3) describing the
selects to other developers in my documentation. One of the complex
selects is shown below.

The database schema consists of objects, which are shared on boards by
being recorded in board_objects. Individuals can view things on a board
they themselves are joined to (people are like objects). People are
joined to specific board_objects as board_object_members, or may comment
on specific board_objects. It is relatively straightforward doing a
query on a board_object and discerning what object it refers to and what
comments it has aggregated against it.

The complex query below provides a list of objects which a person has
commented on or joined, garnering summaries of the comments on those
objects across boards the user is joined to, and sorting the objects on
the basis of the last updated board_object instance of that object
which, again, the user has access to.

I realise life would be easier if there was more duplicated data.

General comments on the nature of the select, how to optimise it or
rewrite it, and how to document what it does would be most appreciated.

Rory

A rough sketch of the main tables:

           +----------+                   +----------+
           |  people  |   +-----------+   |  boards  |
           +----+-----+   |  objects  |   +----+-----+
                |         +-----+-----+        |
                |               |              |
           +----+---------------+--------------+-----+
           |              board   objects            |
           + ---------+--------------------+---------+
                      |                    |
              +-------+-------+     +------+-------+
              |  obj members  |     |   comments   |
              +---------------+     +--------------+

Example for a personal view as described above: (the query runs within a
function):

    FOR resulter IN
        SELECT
            o.t_text_id                            as itemid,
            pers.t_nickname                     as itemauthor,
            o.n_type                            as itemtype,
            o.t_name                            as itemtitle,
            o.t_description                     as itembody,
            to_char(o.dt_created,  'DD Mon'')   as itemcreated,
            to_char(botimer, ''DD  on'')        as itemupdated,
            COALESCE (c_count, 0)               as itemcommentcount,
            p.n_id                              as imageid,
            COALESCE(p.t_path, ''none'')        as imagesrc,
            p.n_width                           as imagewidth,
            p.n_height                          as imageheight
        FROM
            people pers,
            objects o
            LEFT OUTER JOIN photo p ON o.n_id_photo = p.n_id
            LEFT OUTER JOIN (
                SELECT
                    bo.n_object_id as boider,
                    max(bo.dt_modified) as botimer
                FROM
                    board_objects bo, board_object_members bom
                WHERE
                    bom.n_person_id = personid
                    AND
                    bom.n_board_object_id = bo.n_id
                GROUP BY
                    bo.n_object_id
            ) as timer
            ON o.n_id = timer.boider
            LEFT OUTER JOIN (
                SELECT
                    bo.n_object_id as c_bo_id,
                    count(comm.n_id) as c_count
                FROM
                    board_objects bo, comments comm
                WHERE
                    comm.n_board_object_id = bo.n_id
                    AND
                    bo.n_board_id IN
                        (SELECT
                            n_board_id
                        FROM
                            board_objects
                        WHERE
                            n_object_id = persobjid
                        )
                GROUP BY
                    bo.n_object_id, bo.n_object_id
                ) as counter
                ON o.n_id = counter.c_bo_id
        WHERE
            o.n_creator = pers.n_id
            AND
            o.n_id IN (
                SELECT DISTINCT ON (bo.dt_modified, bo.n_object_id)
                    bo.n_object_id as bo_obid
                FROM
                    board_objects bo, board_object_members bom
                WHERE
                    bo.n_id = bom.n_board_object_id
                    AND
                    bom.n_person_id = personid
            )
        ORDER BY
            botimer DESC
        LIMIT
            locallimit
        OFFSET
            localoffset LOOP

        RETURN NEXT
            resulter;

    END LOOP;

--
Rory Campbell-Lange
<rory@campbell-lange.net>
<www.campbell-lange.net>