Timeline for Why is array_agg() slower than the non-aggregate ARRAY() constructor?
Current License: CC BY-SA 4.0
10 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Oct 22, 2018 at 12:49 | comment | added | Erwin Brandstetter | It is a good addition. | |
| Oct 21, 2018 at 14:28 | comment | added | pbillen | 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. | |
| Oct 21, 2018 at 14:25 | comment | added | pbillen | 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. | |
| Oct 21, 2018 at 14:12 | history | edited | pbillen | CC BY-SA 4.0 |
edited body
|
| Oct 21, 2018 at 11:14 | comment | added | Erwin Brandstetter |
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
|
|
| Oct 20, 2018 at 11:28 | history | edited | pbillen | CC BY-SA 4.0 |
added 1 character in body
|
| Oct 20, 2018 at 11:21 | history | edited | pbillen | CC BY-SA 4.0 |
added 1 character in body
|
| Oct 20, 2018 at 11:16 | history | edited | pbillen | CC BY-SA 4.0 |
added 1 character in body
|
| Oct 20, 2018 at 11:15 | review | First posts | |||
| Oct 20, 2018 at 17:19 | |||||
| Oct 20, 2018 at 11:11 | history | answered | pbillen | CC BY-SA 4.0 |