Re: SELECT too complex?

Rory,

> 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.

Keep in mind that (2) is not in sync with (1) or (3); I have frequently run
into situations where huge, messy-looking versions of queries run much faster
than simpler-looking queries that produce the same results. i.e. "simpler"
to a human is not necessarity "simpler" to the planner.

> 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.

Sure. My general experience with complex queries has been that:
1) The more complex a query, the more possible ways there are to write it.
2) With a query which can be written many ways, the best way to
"performance-tune" it is to try different query structures and keep
re-executing it against the back end until you get something you're happy
with (EXPLAIN ANALYZE can help)

As for simplifying, I'm afraid that I don't really understand what you're
trying to get with this query. If I'm clueless, send me a version of the
query with each clause commented.

> 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

You have a lot of left outer joins on aggregate subselects in this query.
try putting the subselect in the SELECT clause, and using ORDER BY ____ DESC
LIMIT 1 instead of MAX
Also, I fail to see the reason you need to left outer join on Board_Objects,
when as subselect to board_objects is in your WHERE criteria. Presumably, if
there are no records in board_objects, you'll lose the row from person
anyway.
What about doing a straight join to board_objects and simplifing your outer
joins thereby?

> 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

You *definitely* don't want to use IN in this subquery, unless you're on 7.4.
Use EXISTS instead. I'm also unclear on why you need this IN subquery at all.
You're checking board_objects against itself; why not just put it in the main
subselect?

> (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 (

Once again, through 7.3, use EXISTS, not IN.

> SELECT DISTINCT ON (bo.dt_modified, bo.n_object_id)
> bo.n_object_id as bo_obid

Why do you need a SELECT DISTINCT if you're just checking for existence?

> 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

Overall, yeah, I think you're making the query too complex. You have 3
subselects consulting the same table; surely at least 2 of these could be
consolidated.

--
Josh Berkus
Aglio Database Solutions
San Francisco