diff options
Diffstat (limited to 'doc/src/sgml/html/intagg.html')
-rw-r--r-- | doc/src/sgml/html/intagg.html | 89 |
1 files changed, 89 insertions, 0 deletions
diff --git a/doc/src/sgml/html/intagg.html b/doc/src/sgml/html/intagg.html new file mode 100644 index 0000000..0b1735b --- /dev/null +++ b/doc/src/sgml/html/intagg.html @@ -0,0 +1,89 @@ +<?xml version="1.0" encoding="UTF-8" standalone="no"?> +<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>F.19. intagg</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="hstore.html" title="F.18. hstore" /><link rel="next" href="intarray.html" title="F.20. intarray" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">F.19. intagg</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="hstore.html" title="F.18. hstore">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="contrib.html" title="Appendix F. Additional Supplied Modules">Up</a></td><th width="60%" align="center">Appendix F. Additional Supplied Modules</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 15.5 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="intarray.html" title="F.20. intarray">Next</a></td></tr></table><hr /></div><div class="sect1" id="INTAGG"><div class="titlepage"><div><div><h2 class="title" style="clear: both">F.19. intagg</h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="intagg.html#id-1.11.7.28.4">F.19.1. Functions</a></span></dt><dt><span class="sect2"><a href="intagg.html#id-1.11.7.28.5">F.19.2. Sample Uses</a></span></dt></dl></div><a id="id-1.11.7.28.2" class="indexterm"></a><p> + The <code class="filename">intagg</code> module provides an integer aggregator and an + enumerator. <code class="filename">intagg</code> 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. + </p><div class="sect2" id="id-1.11.7.28.4"><div class="titlepage"><div><div><h3 class="title">F.19.1. Functions</h3></div></div></div><a id="id-1.11.7.28.4.2" class="indexterm"></a><a id="id-1.11.7.28.4.3" class="indexterm"></a><p> + The aggregator is an aggregate function + <code class="function">int_array_aggregate(integer)</code> + that produces an integer array + containing exactly the integers it is fed. + This is a wrapper around <code class="function">array_agg</code>, + which does the same thing for any array type. + </p><a id="id-1.11.7.28.4.5" class="indexterm"></a><p> + The enumerator is a function + <code class="function">int_array_enum(integer[])</code> + that returns <code class="type">setof integer</code>. 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 <code class="function">unnest</code>, + which does the same thing for any array type. + </p></div><div class="sect2" id="id-1.11.7.28.5"><div class="titlepage"><div><div><h3 class="title">F.19.2. Sample Uses</h3></div></div></div><p> + Many database systems have the notion of a one to many table. Such a table + usually sits between two indexed tables, for example: + +</p><pre class="programlisting"> +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); +</pre><p> + + It is typically used like this: + +</p><pre class="programlisting"> +SELECT right.* from right JOIN one_to_many ON (right.id = one_to_many.right) + WHERE one_to_many.left = <em class="replaceable"><code>item</code></em>; +</pre><p> + + 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. + </p><p> + Now, this methodology can be cumbersome with a very large number of + entries in the <code class="structname">one_to_many</code> 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. + +</p><pre class="programlisting"> +CREATE TABLE summary AS + SELECT left, int_array_aggregate(right) AS right + FROM one_to_many + GROUP BY left; +</pre><p> + + 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 + +</p><pre class="programlisting"> +SELECT left, int_array_enum(right) FROM summary WHERE left = <em class="replaceable"><code>item</code></em>; +</pre><p> + + The above query using <code class="function">int_array_enum</code> produces the same results + as + +</p><pre class="programlisting"> +SELECT left, right FROM one_to_many WHERE left = <em class="replaceable"><code>item</code></em>; +</pre><p> + + The difference is that the query against the summary table has to get + only one row from the table, whereas the direct query against + <code class="structname">one_to_many</code> must index scan and fetch a row for each entry. + </p><p> + On one system, an <code class="command">EXPLAIN</code> showed a query with a cost of 8488 was + reduced to a cost of 329. The original query was a join involving the + <code class="structname">one_to_many</code> table, which was replaced by: + +</p><pre class="programlisting"> +SELECT right, count(right) FROM + ( SELECT left, int_array_enum(right) AS right + FROM summary JOIN (SELECT left FROM left_table WHERE left = <em class="replaceable"><code>item</code></em>) AS lefts + ON (summary.left = lefts.left) + ) AS list + GROUP BY right + ORDER BY count DESC; +</pre><p> + </p></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="hstore.html" title="F.18. hstore">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="contrib.html" title="Appendix F. Additional Supplied Modules">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="intarray.html" title="F.20. intarray">Next</a></td></tr><tr><td width="40%" align="left" valign="top">F.18. hstore </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 15.5 Documentation">Home</a></td><td width="40%" align="right" valign="top"> F.20. intarray</td></tr></table></div></body></html>
\ No newline at end of file |