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>
In-Reply-To: <2962669.1724722813@sss.pgh.pa.us>
From: David Rowley <dgrowleyml@gmail.com>
Date: Tue, 27 Aug 2024 13:50:56 +1200
Message-ID: <CAApHDvpRxyw1G2HVkA=PM5Uqh59moOFqPXyMTsEDUadKo2fQTA@mail.gmail.com>
Subject: Re: Significant Execution Time Difference Between PG13.14 and PG16.4
 for Query on information_schema Tables.
To: Tom Lane <tgl@sss.pgh.pa.us>
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>
Content-Type: text/plain; charset="UTF-8"
Archived-At: <https://www.postgresql.org/message-id/CAApHDvpRxyw1G2HVkA%3DPM5Uqh59moOFqPXyMTsEDUadKo2fQTA%40mail.gmail.com>
Precedence: bulk

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.

I was looking for the offending commit as at first I thought it might
be related to Memoize. It does not seem to be.

I get the following up until 2489d76c, and from then on, it's a subquery filter.

 ->  Index Scan using pg_class_relname_nsp_index on pg_class r_2
(cost=0.27..8.30 rows=1 width=8) (actual time=0.004..0.004 rows=0
loops=1)
        Index Cond: (relname = 't_c56ng1_repository'::name)
        Filter: ((relkind = ANY ('{r,p}'::"char"[])) AND
pg_has_role(relowner, 'USAGE'::text))

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

David