EXPLAIN EXTENDED

In one of the previous articles:

Aggregate concatenation

, I described an aggregate function to concatenate strings in PostgreSQL, similar to GROUP_CONCAT in MySQL.

It's very useful if you have a complex GROUP BY query with multiple conditions.

But for some simple queries it's possible to emulate GROUP_CONCAT with pure SQL, avoiding custom functions at all.

Let's create a table to demonstrate our task:

CREATE TABLE t_grouper (
        id INT NOT NULL PRIMARY KEY,
        grouper INT NOT NULL,
        value VARCHAR(50) NOT NULL
        );

CREATE INDEX ix_limiter_grouper ON t_grouper (grouper);

BEGIN TRANSACTION;

SELECT  SETSEED(0.20090502);

INSERT
INTO    t_grouper (id, grouper, value)
SELECT  s, (s - 1) % 10 + 1,
        CASE FLOOR(RANDOM() * 10)
                WHEN 0 THEN 'lorem'
                WHEN 1 THEN 'ipsum'
                WHEN 2 THEN 'dolor'
                WHEN 3 THEN 'sit'
                WHEN 4 THEN 'amet'
                WHEN 5 THEN 'consecteur'
                WHEN 6 THEN 'adipisicing'
                WHEN 7 THEN 'elit'
                WHEN 8 THEN 'sed'
                WHEN 9 THEN 'do'
        END
FROM    generate_series(1, 100) s;

COMMIT;

ANALYZE t_grouper;

See what do we have in this table:

SELECT  *
FROM    t_grouper

There are 10 groups, with 10 random words in each. There is an index on grouper.

To emulate the GROUP_CONCAT, we just need to use PostgreSQL's array manupulation functions:

1. Select DISTINCT grouper's
2. For each grouper, select all value's in a subquery
3. Convert the subquery's resultset into an array
4. Use array_to_string to concatenate the value's

Here's the query to concatenate strings in a group:

SELECT  grouper,
        array_to_string
        (
        ARRAY   (
                SELECT  value
                FROM    t_grouper gi
                WHERE   gi.grouper = gd.grouper
                ),
        ', '
        ) AS group_concat
FROM    (
        SELECT  grouper
        FROM    t_grouper
        GROUP BY
                grouper
        ) gd
ORDER BY
        grouper

Sort  (cost=25.14..25.17 rows=10 width=4)
  Sort Key: gd.grouper
  ->  Subquery Scan gd  (cost=2.25..24.97 rows=10 width=4)
        ->  HashAggregate  (cost=2.25..2.35 rows=10 width=4)
              ->  Seq Scan on t_grouper  (cost=0.00..2.00 rows=100 width=4)
        SubPlan
          ->  Seq Scan on t_grouper gi  (cost=0.00..2.25 rows=10 width=6)
                Filter: (grouper = $0)

Note that string here come in no particular order (strictly speaking, they come in the table order, as the Seq Scan is chosen by the query optimizer).

To force order, we need to add an ORDER BY condition into the subquery:

SELECT  grouper,
        array_to_string
        (
        ARRAY   (
                SELECT  value
                FROM    t_grouper gi
                WHERE   gi.grouper = gd.grouper
                ORDER BY
                        value
                ),
        ', '
        ) AS group_concat
FROM    (
        SELECT  grouper
        FROM    t_grouper
        GROUP BY
                grouper
        ) gd
ORDER BY
        grouper

Sort  (cost=27.05..27.08 rows=10 width=4)
  Sort Key: gd.grouper
  ->  Subquery Scan gd  (cost=2.25..26.89 rows=10 width=4)
        ->  HashAggregate  (cost=2.25..2.35 rows=10 width=4)
              ->  Seq Scan on t_grouper  (cost=0.00..2.00 rows=100 width=4)
        SubPlan
          ->  Sort  (cost=2.42..2.44 rows=10 width=6)
                Sort Key: gi.value
                ->  Seq Scan on t_grouper gi  (cost=0.00..2.25 rows=10 width=6)
                      Filter: (grouper = $0)

If we want to avoid duplicates in the concatenated strings, we need to add DISTINCT or GROUP BY clause into the subquery:

SELECT  grouper,
        array_to_string
        (
        ARRAY   (
                SELECT  DISTINCT value
                FROM    t_grouper gi
                WHERE   gi.grouper = gd.grouper
                ORDER BY
                        value
                ),
        ', '
        ) AS group_concat
FROM    (
        SELECT  grouper
        FROM    t_grouper
        GROUP BY
                grouper
        ) gd
ORDER BY
        grouper

Sort  (cost=27.30..27.33 rows=10 width=4)
  Sort Key: gd.grouper
  ->  Subquery Scan gd  (cost=2.25..27.14 rows=10 width=4)
        ->  HashAggregate  (cost=2.25..2.35 rows=10 width=4)
              ->  Seq Scan on t_grouper  (cost=0.00..2.00 rows=100 width=4)
        SubPlan
          ->  Unique  (cost=2.42..2.47 rows=1 width=6)
                ->  Sort  (cost=2.42..2.44 rows=10 width=6)
                      Sort Key: gi.value
                      ->  Seq Scan on t_grouper gi  (cost=0.00..2.25 rows=10 width=6)
                            Filter: (grouper = $0)

Note that is there are lots of DISTINCT values in a subquery, DISTINCT is more efficient for queries with ORDER BY, while GROUP BY is more efficient for unordered queries.

That's because the former one uses Sort to eliminate non-UNIQUE values (and therefore ORDER BY is free), while the latter one uses HashAggregate, which is more efficient but the values come unsorted.

If there are few DISTINCT values, then the sorting is easy on the final, DISTINCT rowset, and HashAggregate efficiency overweights the advantages of presorted rows.

In this case, you should use GROUP BY even with ORDER BY.

One final note: this query is useful only for simple cases, where you can construct a simple subquery for all groups.

If the query is complex (like, it uses WHERE conditions or JOIN's), you'll need to propagate the conditions into the subquery too, and this can kill all performance and readability benefits.

In this case, it's better to create an aggregate function (as described in Aggregate concatenation) and use it with GROUP BY