MIME-Version: 1.0
References: <CALNXOfqDr5jVRZHThyXyNftDuezjxOJB1mqw=4x1T8YBibi6uw@mail.gmail.com>
 <CAG1ps1w61XD1LxUf3EBCZ=VR3S_orRj_dk6cxmO5KusBVyrgMg@mail.gmail.com>
 <CAAvG1pChk3W9MWkK-_JPyUdThSoJhv4MueH5i8cZS2T_HUP5dA@mail.gmail.com>
 <CAG1ps1xvnTZceKK24OUfMKLPvDP2vjT-d+F2AOCWbw_v3KeEgg@mail.gmail.com>
 <2962669.1724722813@sss.pgh.pa.us> <CAApHDvpRxyw1G2HVkA=PM5Uqh59moOFqPXyMTsEDUadKo2fQTA@mail.gmail.com>
 <fa70f9fbd7a0bbfb78316dc8ac2ffddb@postgresql.org> <CAApHDvr6igx9SPQs3n_K5WwvTdYwhZ1LzP6qOfiGFNbUHXOCyQ@mail.gmail.com>
 <7b97171e276c41ec6ae4eeb8c2e8b47d@postgresql.org>
In-Reply-To: <7b97171e276c41ec6ae4eeb8c2e8b47d@postgresql.org>
From: David Rowley <dgrowleyml@gmail.com>
Date: Thu, 29 Aug 2024 18:49:21 +1200
Message-ID: <CAApHDvpKUtAjGHQhCVVfnNBLnWepgkz4Ze2daMpGeKaQz2Prag@mail.gmail.com>
Subject: Re: Significant Execution Time Difference Between PG13.14 and PG16.4
 for Query on information_schema Tables.
To: Justin Clift <justin@postgresql.org>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, nikhil raj <nikhilraj474@gmail.com>, 
	"pgsql-generallists.postgresql.org" <pgsql-general@lists.postgresql.org>, 
	NIKITA PATEL <patelnikita1411@gmail.com>, Patel Khushbu <patelkhushbu2067@gmail.com>
Content-Type: text/plain; charset="UTF-8"
Archived-At: <https://www.postgresql.org/message-id/CAApHDvpKUtAjGHQhCVVfnNBLnWepgkz4Ze2daMpGeKaQz2Prag%40mail.gmail.com>
Precedence: bulk

On Wed, 28 Aug 2024 at 18:59, Justin Clift <justin@postgresql.org> wrote:
> Any idea who normally does those, and if it would be reasonable to add
> test(s) for the internal information tables?

These tend to get added along with features and along with of bug
fixes.  I imagine any tests for the information_schema views would be
for the results of the views rather than for the expected plans.
However, that seems very separate from this as the bug has nothing to
do with information_schema. It just happens to be a query to an
information_schema view that helped highlight the bug.  Those views
are often quite complex and so are the resulting plans.  With tests
checking the expected EXPLAIN output, it's much better to give these a
very narrow focus otherwise the expected output could be too unstable
and the purpose of the test harder to determine for anyone working on
a new patch which results in a plan change of a preexisting test.
I've seen tests before rendered useless by people blindly accepting
the plan change without properly determining what the test is supposed
to be testing. That's much more likely to happen when the purpose of
the test is less clear due to some unwieldy and complex expected plan.
I managed to get a reproducer for this down to something quite simple.
Probably that or something similar would be a better test to make sure
this bug stays gone.

David