intagg

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

1. Functions

The aggregator is an aggregate function int_array_aggregate(integer) that produces an integer array completely containing the input integers. This is a wrapper for 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 inverse of the aggregator: given an integer array, it expands it into a set of rows. This is a wrapper for unnest, which does the same thing for any array type.

2. Usage Examples

Many database systems have a concept of a one-to-many table. Such a table is typically located between two indexed tables. For example:

CREATE TABLE left (id INT PRIMARY KEY, ...);

CREATE TABLE right (id INT PRIMARY KEY, ...);

CREATE TABLE one_to_many(left INT REFERENCES left, right INT REFERENCES right);

It is typically used like this:

SELECT right.* from right JOIN one_to_many ON (right.id = one_to_many.right) WHERE one_to_many.left = item;

This will return all items in the right table for an item in the left table. Now, this approach can be problematic for one_to_many tables with very many items. Typically, such a join will result in one index scan for a particular left item and one fetch for each right item. If you have a very dynamic system, there is not much you can do. However, if your data is relatively static, you can use the aggregator to create a summary table.

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 for each left item and an array of right items. Now this would be useless without some way to use the array. This is where the array enumerator comes in handy. You can:

SELECT left, int_array_enum(right) FROM summary WHERE left = item; The above query using int_array_enum produces the same results as the following statement:

SELECT left, right FROM one_to_many WHERE left = item;

The difference is that the query against the summary table only needs to fetch one row from the table, while the direct query against one_to_many must perform an index scan and fetch one row for each item.

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

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;