From: Ronan Dunklau <ronan.dunklau@aiven.io>
To: David Rowley <dgrowleyml@gmail.com>, Justin Pryzby <pryzby@telsasoft.com>,
 Pavel Luzanov <p.luzanov@postgrespro.ru>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, Richard Guo <guofenglinux@gmail.com>,
 pgsql-hackers@lists.postgresql.org, Ranier Vilela <ranier.vf@gmail.com>
Subject: Re: Add proper planner support for ORDER BY / DISTINCT aggregates
Date: Mon, 07 Nov 2022 18:30:16 +0100
Message-ID: <3699971.kQq0lBPeGt@aivenlaptop>
In-Reply-To: <43b1c6fc-e2f4-c252-5ce5-3db98c6b71d4@postgrespro.ru>
References: <3163474.aeNJFYEL58@aivenronan> <2202180.iZASKD2KPV@aivenlaptop>
 <43b1c6fc-e2f4-c252-5ce5-3db98c6b71d4@postgrespro.ru>
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain; charset="iso-8859-1"
Archived-At: 
 <https://www.postgresql.org/message-id/3699971.kQq0lBPeGt%40aivenlaptop>
Precedence: bulk

Le lundi 7 novembre 2022, 17:58:50 CET Pavel Luzanov a =E9crit :
> > I can't see why an incrementalsort could be more expensive than a full
> > sort, using the same presorted path.
>=20
> The only reason I can see is the number of buffers to read. In the plan
> with incremental sort we read the whole index, ~190000 buffers.
> And the plan with seq scan only required ~5000 (I think due to buffer
> ring optimization).

What I meant here is that disabling seqscans, the planner still chooses a f=
ull=20
sort over a partial sort. The underlying index is the same, it is just a=20
matter of choosing a Sort node over an IncrementalSort node. This, I think,=
 is=20
wrong: I can't see how it could be worse to use an incrementalsort in that=
=20
case.=20

It makes sense to prefer a SeqScan over an IndexScan if you are going to so=
rt=20
the whole table anyway. But in that case we shouldn't. What happened before=
 is=20
that some sort of incremental sort was always chosen, because it was hidden=
 as=20
an implementation detail of the agg node. But now it has to compete on a co=
st=20
basis with the full sort, and that costing is wrong in that case.=20

Maybe the original costing code for incremental sort was a bit too=20
pessimistic.=20

=2D-
Ronan Dunklau