The intagg module provides an
integer aggregator and an enumerator. intagg is now obsolete, because there are
built-in functions that provide a superset of its capabilities.
However, the module is still provided as a compatibility wrapper
around the built-in functions.

The aggregator is an aggregate function int_array_aggregate(integer) that produces an
integer array containing exactly the integers it is fed. This
is a wrapper around array_agg,
which does the same thing for any array type.

The enumerator is a function int_array_enum(integer[]) that returns
setof integer. It is essentially the
reverse operation of the aggregator: given an array of
integers, expand it into a set of rows. This is a wrapper
around unnest, which does the
same thing for any array type.

This will return all the items in the right hand table for
an entry in the left hand table. This is a very common
construct in SQL.

Now, this methodology can be cumbersome with a very large
number of entries in the one_to_many table. Often, a join like this
would result in an index scan and a fetch for each right hand
entry in the table for a particular left hand entry. If you
have a very dynamic system, there is not much you can do.
However, if you have some data which is fairly static, you can
create a summary table with the aggregator.

CREATE TABLE summary AS
SELECT left, int_array_aggregate(right) AS right
FROM one_to_many
GROUP BY left;

This will create a table with one row per left item, and an
array of right items. Now this is pretty useless without some
way of using the array; that's why there is an array
enumerator. You can do

SELECT left, int_array_enum(right) FROM summary WHERE left = item;

The above query using int_array_enum produces the same results
as

SELECT left, right FROM one_to_many WHERE left = item;

The difference is that the query against the summary table
has to get only one row from the table, whereas the direct
query against one_to_many must
index scan and fetch a row for each entry.

On one system, an EXPLAIN showed a
query with a cost of 8488 was reduced to a cost of 329. The
original query was a join involving the one_to_many table, which was replaced by:

SELECT right, count(right) FROM
( SELECT left, int_array_enum(right) AS right
FROM summary JOIN (SELECT left FROM left_table WHERE left = item) AS lefts
ON (summary.left = lefts.left)
) AS list
GROUP BY right
ORDER BY count DESC;