13

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"

17

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 much more versatile in that it can be integrated in 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.

  • array_agg must keep track of the order of its inputs where the ARRAY constructor seems to be doing something roughly equivalent to a UNION as an expression internally. If I had to venture a guess, array_agg would likely require more memory. I wasn't able to exhaustively test this but on PostgreSQL 9.6 running on Ubuntu 16.04 the ARRAY() query with ORDER BY used an external merge and was slower than the array_agg query. As you said, short of reading the code your answer is the best explanation we have. – Jeffrey Jan 4 '17 at 17:07
  • @Jeffrey: You found a test case where array_agg() is faster than the array constructor? For a simple case? Very unlikely, but if so probably because Postgres based its decision for a query plan on inaccurate statistics of cost settings. I have never seen array_agg() outperform an array constructor and I have tested many times. – Erwin Brandstetter Jan 4 '17 at 17:15
  • 1
    @Jeffrey: No misleading caching effects? Did you run each query more than once? I would need to see table definition, cardinalities and exact query to say more. – Erwin Brandstetter Jan 4 '17 at 23:37
  • 1
    This is not a real answer. Many versatile tools can perform as well as more specific tools. If being versatile is indeed what's making it slower, what about its versatility is it? – Gavin Wahl Jan 5 '17 at 0:02
  • 1
    @Jeffrey: Seems like Postgres chooses a different sort algorithm for each variant (based on cost estimates and table statistics). And it ends up choosing an inferior method for the ARRAY constructor, which indicates that one or more factors in the calculation of the estimated cost are too far off. This is on a temp table? Did you VACUUM ANALYZE it before you run the queries? Consider: dba.stackexchange.com/a/18694/3684 – Erwin Brandstetter Jan 5 '17 at 15:54
4

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.

  • 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
  • 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.