begriffs open source - ai-pg/blob - full-docs/txt/intagg.txt

   1
   2 F.18. intagg — integer aggregator and enumerator #
   3
   4    F.18.1. Functions
   5    F.18.2. Sample Uses
   6
   7    The intagg module provides an integer aggregator and an enumerator.
   8    intagg is now obsolete, because there are built-in functions that
   9    provide a superset of its capabilities. However, the module is still
  10    provided as a compatibility wrapper around the built-in functions.
  11
  12 F.18.1. Functions #
  13
  14    The aggregator is an aggregate function int_array_aggregate(integer)
  15    that produces an integer array containing exactly the integers it is
  16    fed. This is a wrapper around array_agg, which does the same thing for
  17    any array type.
  18
  19    The enumerator is a function int_array_enum(integer[]) that returns
  20    setof integer. It is essentially the reverse operation of the
  21    aggregator: given an array of integers, expand it into a set of rows.
  22    This is a wrapper around unnest, which does the same thing for any
  23    array type.
  24
  25 F.18.2. Sample Uses #
  26
  27    Many database systems have the notion of a many to many table. Such a
  28    table usually sits between two indexed tables, for example:
  29 CREATE TABLE left_table  (id INT PRIMARY KEY, ...);
  30 CREATE TABLE right_table (id INT PRIMARY KEY, ...);
  31 CREATE TABLE many_to_many(id_left  INT REFERENCES left_table,
  32                           id_right INT REFERENCES right_table);
  33
  34    It is typically used like this:
  35 SELECT right_table.*
  36 FROM right_table JOIN many_to_many ON (right_table.id = many_to_many.id_right)
  37 WHERE many_to_many.id_left = item;
  38
  39    This will return all the items in the right hand table for an entry in
  40    the left hand table. This is a very common construct in SQL.
  41
  42    Now, this methodology can be cumbersome with a very large number of
  43    entries in the many_to_many table. Often, a join like this would result
  44    in an index scan and a fetch for each right hand entry in the table for
  45    a particular left hand entry. If you have a very dynamic system, there
  46    is not much you can do. However, if you have some data which is fairly
  47    static, you can create a summary table with the aggregator.
  48 CREATE TABLE summary AS
  49   SELECT id_left, int_array_aggregate(id_right) AS rights
  50   FROM many_to_many
  51   GROUP BY id_left;
  52
  53    This will create a table with one row per left item, and an array of
  54    right items. Now this is pretty useless without some way of using the
  55    array; that's why there is an array enumerator. You can do
  56 SELECT id_left, int_array_enum(rights) FROM summary WHERE id_left = item;
  57
  58    The above query using int_array_enum produces the same results as
  59 SELECT id_left, id_right FROM many_to_many WHERE id_left = item;
  60
  61    The difference is that the query against the summary table has to get
  62    only one row from the table, whereas the direct query against
  63    many_to_many must index scan and fetch a row for each entry.
  64
  65    On one system, an EXPLAIN showed a query with a cost of 8488 was
  66    reduced to a cost of 329. The original query was a join involving the
  67    many_to_many table, which was replaced by:
  68 SELECT id_right, count(id_right) FROM
  69   ( SELECT id_left, int_array_enum(rights) AS id_right
  70     FROM summary
  71     JOIN (SELECT id FROM left_table
  72           WHERE id = item) AS lefts
  73     ON (summary.id_left = lefts.id)
  74   ) AS list
  75   GROUP BY id_right
  76   ORDER BY count DESC;