Re: query patterns for multipass aggregating

>>>>> "Ondřej" == Ondřej Bouda <obouda(at)email(dot)cz> writes:

Ondřej> What about subqueries?

Ondřej> SELECT
Ondřej> items.*,
Ondřej> (SELECT array_agg(color_name) FROM colors WHERE item_id =
Ondřej> items.id) AS color_names,
Ondřej> (SELECT array_agg(image_file) FROM images WHERE item_id =
Ondřej> items.id) AS image_files
Ondřej> FROM items

Ondřej> According to my experience, not only the code is readable (no
Ondřej> questions about how many rows are there for each item), but it
Ondřej> also leads to a better query plan.

This is (generally speaking) no improvement over the LATERAL method I
showed, and is less flexible (for example it's very painful to return
more than one value from the subqueries).

--
Andrew (irc:RhodiumToad)