14

I was just reviewing some old code written for pre-8.4 PostgreSQL, and I saw something really nifty. I remember having a custom function do some of this back in the day, but I forgot what pre-array_agg() looked like. For review, modern aggregation is written like this.

SELECT array_agg(x ORDER BY x DESC) FROM foobar;

However, once upon a time, it was written like this,

SELECT ARRAY(SELECT x FROM foobar ORDER BY x DESC);

So, I tried it with some test data..

CREATE TEMP TABLE foobar AS
SELECT * FROM generate_series(1,1e7)
  AS t(x);

The results were surprising.. The #OldSchoolCool way was massively faster: a 25% speedup. Moreover, simplifying it without the ORDER, showed the same slowness.

# EXPLAIN ANALYZE SELECT ARRAY(SELECT x FROM foobar);
                                                         QUERY PLAN                                                          
-----------------------------------------------------------------------------------------------------------------------------
 Result  (cost=104425.28..104425.29 rows=1 width=0) (actual time=1665.948..1665.949 rows=1 loops=1)
   InitPlan 1 (returns $0)
     ->  Seq Scan on foobar  (cost=0.00..104425.28 rows=6017728 width=32) (actual time=0.032..716.793 rows=10000000 loops=1)
 Planning time: 0.068 ms
 Execution time: 1671.482 ms
(5 rows)

test=# EXPLAIN ANALYZE SELECT array_agg(x) FROM foobar;
                                                        QUERY PLAN                                                         
---------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=119469.60..119469.61 rows=1 width=32) (actual time=2155.154..2155.154 rows=1 loops=1)
   ->  Seq Scan on foobar  (cost=0.00..104425.28 rows=6017728 width=32) (actual time=0.031..717.831 rows=10000000 loops=1)
 Planning time: 0.054 ms
 Execution time: 2174.753 ms
(4 rows)

So, what's going on here. Why is array_agg, an internal function so much slower than the planner's SQL voodoo?

Using "PostgreSQL 9.5.5 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 6.2.0-5ubuntu12) 6.2.0 20161005, 64-bit"

18

There is nothing "old school" or "outdated" about an ARRAY constructor (That's what ARRAY(SELECT x FROM foobar) is). It's modern as ever. Use it for simple array aggregation.

The manual:

It is also possible to construct an array from the results of a subquery. In this form, the array constructor is written with the key word ARRAY followed by a parenthesized (not bracketed) subquery.

The aggregate function array_agg() is more versatile in that it can be integrated in a SELECT list with more columns, possibly more aggregations in the same SELECT, and arbitrary groups can be formed with GROUP BY. While an ARRAY constructor can only return a single array from a SELECT returning a single column.

I did not study the source code, but it would seem obvious that a much more versatile tool is also more expensive.

One notable difference: the ARRAY constructor returns an empty array ({}) if no rows qualify. array_agg() returns NULL for the same.

| improve this answer | |
7

I believe the accepted answer by Erwin could be added with the following.

Usually, we are working with regular tables with indices, instead of temporary tables (without indices) as in the original question. It's useful to note that aggregations, such as ARRAY_AGG, cannot leverage existing indices when the sorting is done during the aggregation.

For example, assume the following query:

SELECT ARRAY(SELECT c FROM t ORDER BY id)

If we have an index on t(id, ...), the index could be used, in favor of a sequential scan on t followed by a sort on t.id. Additionally, if the output column being wrapped in the array (here c) is part of the index (such as an index on t(id, c) or an include index on t(id) include(c)), this could even be an index-only scan.

Now, let's rewrite that query as following:

SELECT ARRAY_AGG(c ORDER BY id) FROM t

Now, the aggregation will not use the index and it has to sort the rows in memory (or even worse for large data sets, on disk). This will always be a sequential scan on t followed by aggregation+sort.

As far as I know, this is not documented in the official documentation, but can be derived from the source. This should be the case for all current versions, v11 included.

| improve this answer | |
  • 2
    Good point. But in all fairness, queries with array_agg() or similar aggregate functions can still leverage indexes with a subquery like: SELECT ARRAY_AGG(c) FROM (SELECT c FROM t ORDER BY id) sub. The per-aggregate ORDER BY clause is what precludes index usage in your example. An array constructor is faster than array_agg() when either can use the same index (or neither). It's just not as versatile. See: dba.stackexchange.com/a/213724/3684 – Erwin Brandstetter Oct 21 '18 at 11:14
  • 1
    Right, that's an important distinction to make. I slightly altered my answer to make clear that this remark only holds when the aggregation function has to sort. You could indeed still profit from the index in the simple case, because PostgreSQL seems to give some guarantee that the aggregation will happen in the same order as defined in the subquery, as explained in the link. That's quite cool. I'm wondering though if this still holds in the case of partitioned tables and/or FDW tables and/or parallel workers - and if PostgreSQL can keep up this promise in future releases. – pbillen Oct 21 '18 at 14:25
  • For the record, I had by no means the intention to doubt on the accepted answer. I only thought it was a good addition to reason about the existence and the usage of indices in combination with aggregation. – pbillen Oct 21 '18 at 14:28
  • 1
    It is a good addition. – Erwin Brandstetter Oct 22 '18 at 12:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.