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