doc/src/sgml/intagg.sgml

   1 <!-- $PostgreSQL$ -->
   2
   3 <sect1 id="intagg">
   4  <title>intagg</title>
   5
   6  <indexterm zone="intagg">
   7   <primary>intagg</primary>
   8  </indexterm>
   9
  10  <para>
  11   The <filename>intagg</filename> module provides an integer aggregator and an
  12   enumerator.  <filename>intagg</filename> is now obsolete, because there
  13   are built-in functions that provide a superset of its capabilities.
  14   However, the module is still provided as a compatibility wrapper around
  15   the built-in functions.
  16  </para>
  17
  18  <sect2>
  19   <title>Functions</title>
  20
  21  <para>
  22   The aggregator is an aggregate function
  23   <function>int_array_aggregate(integer)</>
  24   that produces an integer array
  25   containing exactly the integers it is fed.
  26   This is a wrapper around <function>array_agg</>,
  27   which does the same thing for any array type.
  28  </para>
  29
  30  <para>
  31   The enumerator is a function
  32   <function>int_array_enum(integer[])</>
  33   that returns <type>setof integer</>.  It is essentially the reverse
  34   operation of the aggregator: given an array of integers, expand it
  35   into a set of rows.  This is a wrapper around <function>unnest</>,
  36   which does the same thing for any array type.
  37  </para>
  38
  39  </sect2>
  40
  41  <sect2>
  42   <title>Sample Uses</title>
  43
  44   <para>
  45    Many database systems have the notion of a one to many table. Such a table
  46    usually sits between two indexed tables, for example:
  47   </para>
  48
  49  <programlisting>
  50 CREATE TABLE left (id INT PRIMARY KEY, ...);
  51 CREATE TABLE right (id INT PRIMARY KEY, ...);
  52 CREATE TABLE one_to_many(left INT REFERENCES left, right INT REFERENCES right);
  53  </programlisting>
  54
  55  <para>
  56   It is typically used like this:
  57  </para>
  58
  59  <programlisting>
  60   SELECT right.* from right JOIN one_to_many ON (right.id = one_to_many.right)
  61     WHERE one_to_many.left = <replaceable>item</>;
  62  </programlisting>
  63
  64  <para>
  65   This will return all the items in the right hand table for an entry
  66   in the left hand table. This is a very common construct in SQL.
  67  </para>
  68
  69  <para>
  70   Now, this methodology can be cumbersome with a very large number of
  71   entries in the <structname>one_to_many</> table.  Often,
  72   a join like this would result in an index scan
  73   and a fetch for each right hand entry in the table for a particular
  74   left hand entry. If you have a very dynamic system, there is not much you
  75   can do. However, if you have some data which is fairly static, you can
  76   create a summary table with the aggregator.
  77  </para>
  78
  79  <programlisting>
  80 CREATE TABLE summary as
  81   SELECT left, int_array_aggregate(right) AS right
  82   FROM one_to_many
  83   GROUP BY left;
  84  </programlisting>
  85
  86  <para>
  87   This will create a table with one row per left item, and an array
  88   of right items. Now this is pretty useless without some way of using
  89   the array; that's why there is an array enumerator.  You can do
  90  </para>
  91
  92  <programlisting>
  93 SELECT left, int_array_enum(right) FROM summary WHERE left = <replaceable>item</>;
  94  </programlisting>
  95
  96  <para>
  97   The above query using <function>int_array_enum</> produces the same results
  98   as
  99  </para>
 100
 101  <programlisting>
 102 SELECT left, right FROM one_to_many WHERE left = <replaceable>item</>;
 103  </programlisting>
 104
 105  <para>
 106   The difference is that the query against the summary table has to get
 107   only one row from the table, whereas the direct query against
 108   <structname>one_to_many</> must index scan and fetch a row for each entry.
 109  </para>
 110
 111  <para>
 112   On one system, an <command>EXPLAIN</> showed a query with a cost of 8488 was
 113   reduced to a cost of 329.  The original query was a join involving the
 114   <structname>one_to_many</> table, which was replaced by:
 115  </para>
 116
 117  <programlisting>
 118 SELECT right, count(right) FROM
 119   ( SELECT left, int_array_enum(right) AS right
 120     FROM summary JOIN (SELECT left FROM left_table WHERE left = <replaceable>item</>) AS lefts
 121          ON (summary.left = lefts.left)
 122   ) AS list
 123   GROUP BY right
 124   ORDER BY count DESC;
 125  </programlisting>
 126
 127  </sect2>
 128
 129 </sect1>