Home • Explore • Blog • CI
☰



postgresql
3rd chunk of `doc/src/sgml/release-18.sgml`
81b7c29e07e087762f04946f7765544dc7902f27591f76df0000000100000fa2
 cluster will
need to be initialized with --no-data-checksums in order to allow pg_upgrade
to succeed.
</para>
</listitem>

   </itemizedlist>

  </sect2>

  <sect2 id="release-18-changes">
   <title>Changes</title>

    <para>
     Below you will find a detailed account of the changes between
    <productname>PostgreSQL</productname> 18 and the previous major
    release.
    </para>

   <sect3 id="release-18-server">
    <title>Server</title>

    <sect4 id="release-18-optimizer">
     <title>Optimizer</title>

     <itemizedlist>

<!--
Author: Alexander Korotkov <akorotkov@postgresql.org>
2025-02-17 [fc069a3a6] Implement Self-Join Elimination
-->

<listitem>
<para>
Remove some unnecessary table self-joins (Andrey Lepikhov, Alexander Kuzmenkov, Alexander Korotkov, Alena Rybakina)
<ulink url="&commit_baseurl;fc069a3a6">&sect;</ulink>
</para>

<para>
This optimization can be disabled using server variable enable_self_join_elimination.
</para>
</listitem>

<!--
Author: Alexander Korotkov <akorotkov@postgresql.org>
2025-04-04 [c0962a113] Convert 'x IN (VALUES ...)' to 'x = ANY ...' then approp
-->

<listitem>
<para>
Convert some 'IN (VALUES ...)' to 'x = ANY ...' for better optimizer statistics (Alena Rybakina, Andrei Lepikhov)
<ulink url="&commit_baseurl;c0962a113">&sect;</ulink>
</para>
</listitem>

<!--
Author: Alexander Korotkov <akorotkov@postgresql.org>
2024-11-24 [ae4569161] Teach bitmap path generation about transforming OR-claus
-->

<listitem>
<para>
Allow transforming OR-clauses to arrays for faster index processing (Alexander Korotkov, Andrey Lepikhov)
<ulink url="&commit_baseurl;ae4569161">&sect;</ulink>
</para>
</listitem>

<!--
Author: Tom Lane <tgl@sss.pgh.pa.us>
2024-09-10 [52c707483] Use a hash table to de-duplicate column names in ruleuti
Author: Tom Lane <tgl@sss.pgh.pa.us>
2024-12-19 [276279295] Convert SetOp to read its inputs as outerPlan and innerP
Author: Tom Lane <tgl@sss.pgh.pa.us>
2024-12-19 [8d96f57d5] Improve planner's handling of SetOp plans.
Author: David Rowley <drowley@postgresql.org>
2024-09-05 [908a96861] Optimize WindowAgg's use of tuplestores
-->

<listitem>
<para>
Speed up the processing of INTERSECT, EXCEPT, window aggregates, and view column aliases (Tom Lane, David Rowley)
<ulink url="&commit_baseurl;52c707483">&sect;</ulink>
<ulink url="&commit_baseurl;276279295">&sect;</ulink>
<ulink url="&commit_baseurl;8d96f57d5">&sect;</ulink>
<ulink url="&commit_baseurl;908a96861">&sect;</ulink>
</para>
</listitem>

<!--
Author: Richard Guo <rguo@postgresql.org>
2024-11-26 [a8ccf4e93] Reordering DISTINCT keys to match input path's pathkeys
-->

<listitem>
<para>
Allow the keys of SELECT DISTINCT to be internally reordered to avoid sorting (Richard Guo)
<ulink url="&commit_baseurl;a8ccf4e93">&sect;</ulink>
</para>

<para>
This optimization can be disabled using enable_distinct_reordering.
</para>
</listitem>

<!--
Author: David Rowley <drowley@postgresql.org>
2024-12-12 [bd10ec529] Detect redundant GROUP BY columns using UNIQUE indexes
-->

<listitem>
<para>
Ignore GROUP BY columns that are functionally dependent on other columns (Zhang Mingli, Jian He, David Rowley)
<ulink url="&commit_baseurl;bd10ec529">&sect;</ulink>
</para>

<para>
If a GROUP BY clause includes all columns of a unique index, as well as other columns of the same table, those other columns are redundant and can be dropped
from the grouping.  This was already true for non-deferred primary keys.
</para>
</listitem>

<!--
Author: Richard Guo <rguo@postgresql.org>
2024-10-09 [67a54b9e8] Allow pushdown of HAVING clauses with grouping sets
Author: Richard Guo <rguo@postgresql.org>
2024-09-10 [247dea89f] Introduce an RTE for the grouping step
Author: Richard Guo <rguo@postgresql.org>
2024-09-10 [f5050f795] Mark expressions nullable by grouping sets
Author: Richard Guo <rguo@postgresql.org>
2025-03-13 [cc5d98525] Fix incorrect handling of subquery pullup
-->

<listitem>
<para>
Allow some HAVING clauses on GROUPING SETS to be pushed to WHERE
Title: PostgreSQL 18: Optimizer Improvements - Self-Joins, IN Lists, OR-Clauses, Set Operations, DISTINCT, GROUP BY, HAVING
Summary
This section highlights optimizer enhancements in PostgreSQL 18. It includes removing unnecessary table self-joins (configurable via enable_self_join_elimination), converting some 'IN (VALUES ...)' to 'x = ANY ...' for better statistics, allowing OR-clauses to be transformed into arrays for faster index processing, speeding up INTERSECT, EXCEPT, window aggregates, and view column alias processing, enabling internal reordering of SELECT DISTINCT keys to avoid sorting (configurable via enable_distinct_reordering), ignoring GROUP BY columns functionally dependent on other columns due to unique indexes, and allowing some HAVING clauses on GROUPING SETS to be pushed to WHERE.