query patterns for multipass aggregating

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

query patterns for multipass aggregating

Rob Nikander
Hi,

I’ve run into this pattern a few times, and I usually get a little confused. I’m wondering if there are some common solutions or techniques.

Simplified example:

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.

If there were only one other table, it’s a simple join and group…

    select items.*, array_agg(color_name)
    from items join colors on items.id = colors.item_id
    group by items.id    

Now to get images too, my first try uses a CTE…

    // Tables:
    // items (id, name, foo)
    // colors (item_id, color_name, color_foo)
    // images (item_id, image_file, image_foo)

    with items_with_colors as (
      // This is the same query as above
      select items.*, array_agg(colors.color_name) as color_names
      from items
      join colors on items.id = colors.item_id
      group by items.id
    )
    // Same idea repeated but now joining to images table
    select items.*, array_agg(images.image_file) as image_files
    from items_with_colors items
    join images on items.id = images.item_id
    group by items.id, items.name, items.foo, items.color_names;  // mmm :(

One first problem is on the last line. I have to list out all the columns. In my real situation there are many more. Listing them is only an inconvenience, but I’m more worried that it is internally comparing all the columns, when really it could compare only `items.id`.

So… are there some other techniques that usually (or might) work better? It seems like the kind of thing that could run in parallel, if I wrote it right.

Rob


Reply | Threaded
Open this post in threaded view
|

Re: query patterns for multipass aggregating

Andrew Gierth
>>>>> "Rob" == Rob Nikander <[hidden email]> writes:

 Rob> I want a query to list items, with their colors and images. Each
 Rob> result row is an item, so the colors and images must be aggregated
 Rob> into arrays or json.

 Rob> If there were only one other table, it’s a simple join and group…

 Rob>     select items.*, array_agg(color_name)
 Rob>     from items join colors on items.id = colors.item_id
 Rob>     group by items.id    

Method 1:

select items.*, c.colors, i.images
  from items
       left join (select item_id, array_agg(color_name) as colors
                    from colors
                   group by item_id) c
         on c.item_id=items.id
       left join (select item_id, array_agg(image) as images
                    from images
                   group by item_id) i
         on i.item_id=items.id;

Method 2:

select items.*, c.colors, i.images
  from items
       left join lateral (select array_agg(c0.color_name) as colors
                            from colors c0
                           where c0.item_id=items.id) c
         on true
       left join lateral (select array_agg(i0.image) as images
                            from images i0
                           where i0.item_id=items.id) i
         on true;

Unfortunately, the planner isn't smart enough yet to know that these two
are equivalent, so they generate different sets of possible query plans.
Method 1 gets plans that work well if the entire items table is being
selected, since it will read the whole of the images and colors tables
in one go, and it will also get plans that work well for reading a
_single_ item selected by WHERE items.id=? because equivalence-class
processing will propagate a copy of that condition down to below the
grouping clauses. It will _not_ get a good plan for reading any other
small subset of items (e.g. selected by other criteria); for this you
need method 2, which in turn doesn't get very good plans when you fetch
the whole items table.

Don't be tempted to use CTEs for the subqueries in either plan; that'll
only make it much worse.

--
Andrew (irc:RhodiumToad)

Reply | Threaded
Open this post in threaded view
|

Re: query patterns for multipass aggregating

Andrew Gierth
>>>>> "Andrew" == Andrew Gierth <[hidden email]> writes:

 Andrew> Unfortunately, the planner isn't smart enough yet to know that
 Andrew> these two are equivalent,

oops, I edited the second one before posting in a way that made them not
be equivalent: adding a "group by x0.item_id" in both subqueries in
method 2 makes them equivalent again. Without that, the result differs
slightly if there are no matching color or image rows (NULL vs. empty
array).

--
Andrew (irc:RhodiumToad)

Reply | Threaded
Open this post in threaded view
|

Re: query patterns for multipass aggregating

Rob Nikander


> On Nov 11, 2018, at 12:54 PM, Andrew Gierth <[hidden email]> wrote:
> …

Thank you that is very helpful.

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.

Rob
Reply | Threaded
Open this post in threaded view
|

Re: query patterns for multipass aggregating

Ondřej Bouda
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

Reply | Threaded
Open this post in threaded view
|

Re: query patterns for multipass aggregating

Andrew Gierth
>>>>> "Ondřej" == Ondřej Bouda <[hidden email]> 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)