SELECT too complex?

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(at)campbell-lange(dot)net>
<www.campbell-lange.net>