diff options
author | Tom Lane <tgl@sss.pgh.pa.us> | 2019-11-11 14:39:54 -0500 |
---|---|---|
committer | Tom Lane <tgl@sss.pgh.pa.us> | 2019-11-11 14:39:54 -0500 |
commit | b3c762a45a14e119acf371f60eb01c8d3607f714 (patch) | |
tree | b986159100343b2bc25e8afb87d1a75161659b12 | |
parent | e72b6c39f0fb455b48632afa0c6e3bed9ecf941e (diff) | |
download | postgresql-b3c762a45a14e119acf371f60eb01c8d3607f714.tar.gz |
Doc: fix ancient mistake, or at least obsolete info, in rules example.
The example of expansion of multiple views claimed that the resulting
subquery nest would not get fully flattened because of an aggregate
function. There's no aggregate in the example, though, only a user
defined function confusingly named MIN(). In a modern server, the
reason for the non-flattening is that MIN() is volatile, but I'm
unsure whether that was true back when this text was written.
Let's reduce the confusion level by using LEAST() instead (which
we didn't have at the time this example was created). And then
we can just say that the planner will flatten the sub-queries, so
the rewrite system doesn't have to.
Noted by Paul Jungwirth. This text is old enough to vote, so
back-patch to all supported branches.
Discussion: https://postgr.es/m/CA+renyXZFnmp9PcvX1EVR2dR=XG5e6E-AELr8AHCNZ8RYrpnPw@mail.gmail.com
-rw-r--r-- | doc/src/sgml/rules.sgml | 32 |
1 files changed, 8 insertions, 24 deletions
diff --git a/doc/src/sgml/rules.sgml b/doc/src/sgml/rules.sgml index 4e20664ea1..2610645663 100644 --- a/doc/src/sgml/rules.sgml +++ b/doc/src/sgml/rules.sgml @@ -342,17 +342,6 @@ CREATE RULE "_RETURN" AS ON SELECT TO myview DO INSTEAD </para> <para> -For the example, we need a little <literal>min</literal> function that -returns the lower of 2 integer values. We create that as: - -<programlisting> -CREATE FUNCTION min(integer, integer) RETURNS integer AS $$ - SELECT CASE WHEN $1 < $2 THEN $1 ELSE $2 END -$$ LANGUAGE SQL STRICT; -</programlisting> -</para> - -<para> The real tables we need in the first two rule system descriptions are these: @@ -414,7 +403,7 @@ CREATE VIEW shoe_ready AS rsh.sh_avail, rsl.sl_name, rsl.sl_avail, - min(rsh.sh_avail, rsl.sl_avail) AS total_avail + least(rsh.sh_avail, rsl.sl_avail) AS total_avail FROM shoe rsh, shoelace rsl WHERE rsl.sl_color = rsh.slcolor AND rsl.sl_len_cm >= rsh.slminlen_cm @@ -593,7 +582,7 @@ SELECT shoe_ready.shoename, shoe_ready.sh_avail, rsh.sh_avail, rsl.sl_name, rsl.sl_avail, - min(rsh.sh_avail, rsl.sl_avail) AS total_avail + least(rsh.sh_avail, rsl.sl_avail) AS total_avail FROM shoe rsh, shoelace rsl WHERE rsl.sl_color = rsh.slcolor AND rsl.sl_len_cm >= rsh.slminlen_cm @@ -613,7 +602,7 @@ SELECT shoe_ready.shoename, shoe_ready.sh_avail, rsh.sh_avail, rsl.sl_name, rsl.sl_avail, - min(rsh.sh_avail, rsl.sl_avail) AS total_avail + least(rsh.sh_avail, rsl.sl_avail) AS total_avail FROM (SELECT sh.shoename, sh.sh_avail, sh.slcolor, @@ -640,16 +629,11 @@ SELECT shoe_ready.shoename, shoe_ready.sh_avail, </para> <para> - It turns out that the planner will collapse this tree into a - two-level query tree: the bottommost <command>SELECT</command> - commands will be <quote>pulled up</quote> into the middle - <command>SELECT</command> since there's no need to process them - separately. But the middle <command>SELECT</command> will remain - separate from the top, because it contains aggregate functions. - If we pulled those up it would change the behavior of the topmost - <command>SELECT</command>, which we don't want. However, - collapsing the query tree is an optimization that the rewrite - system doesn't have to concern itself with. + This might look inefficient, but the planner will collapse this into a + single-level query tree by <quote>pulling up</quote> the subqueries, + and then it will plan the joins just as if we'd written them out + manually. So collapsing the query tree is an optimization that the + rewrite system doesn't have to concern itself with. </para> </sect2> |