From: Tom Lane <tgl@sss.pgh.pa.us>
To: David Rowley <dgrowleyml@gmail.com>
cc: nikhil raj <nikhilraj474@gmail.com>,
        "pgsql-generallists.postgresql.org" <pgsql-general@lists.postgresql.org>,
        NIKITA PATEL <patelnikita1411@gmail.com>,
        Patel Khushbu <patelkhushbu2067@gmail.com>
Subject: Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables.
In-reply-to: <CAApHDvpRxyw1G2HVkA=PM5Uqh59moOFqPXyMTsEDUadKo2fQTA@mail.gmail.com>
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>
Comments: In-reply-to David Rowley <dgrowleyml@gmail.com>
	message dated "Tue, 27 Aug 2024 13:50:56 +1200"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <2965759.1724724227.1@sss.pgh.pa.us>
Date: Mon, 26 Aug 2024 22:03:47 -0400
Message-ID: <2965760.1724724227@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/2965760.1724724227%40sss.pgh.pa.us>
Precedence: bulk

David Rowley <dgrowleyml@gmail.com> writes:
> On Tue, 27 Aug 2024 at 13:40, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Yeah, it looks like that condition on "table_name" is not getting
>> pushed down to the scan level anymore.  I'm not sure why not,
>> but will look closer tomorrow.

> So looks like it was the "Make Vars be outer-join-aware." commit that
> changed this.

Yeah, I got that same result by bisecting.  It seems like it's
somehow related to the cast to information_schema.sql_identifier:
we are able to get rid of that normally but seem to fail to do so
in this query.

There was a smaller increase in the runtime at dfb75e478 "Add primary
keys and unique constraints to system catalogs", but that seems to
just be due to there being more rows in the relevant catalogs.
(That's from testing the query in an empty database; probably the
effect of dfb75e478 would be swamped in a production DB anyway.)

			regards, tom lane