Re: query patterns for multipass aggregating

Dne 11.11.2018 v 17:20 Rob Nikander napsal(a):
> I have tables `items`, `colors`, and `images`. Items have many
colors, and many images.
>
> I want a query to list items, with their colors and images. Each
result row is an item, so the colors and images must be aggregated into
arrays or json.

What about subqueries?

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

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

Dne 11.11.2018 v 19:23 Rob Nikander napsal(a):
> Could the CTE’s theoretically be optimized in a future version of PG, to work like the subqueries? I like to use them to give names to intermediate results, but I’ll stay away from them for now.

It is on the todo list (for quite some time already):
https://blog.2ndquadrant.com/postgresql-ctes-are-optimization-fences/
Until then, using CTEs in situations as yours leads to poor query plans
as Postgres will compute the whole CTE separately (i.e., all rows
involved) and only then picks some of them to the inner table.

Regards,
Ondřej Bouda