Date: Mon, 31 May 2021 23:16:35 -0500
From: Justin Pryzby <pryzby@telsasoft.com>
To: "Dean Gibson (DB Administrator)" <postgresql@mailpen.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: AWS forcing PG upgrade from v9.6 a disaster
Message-ID: <20210601041635.GQ2082@telsasoft.com>
References: <88dec70b-eb63-0b72-0b5d-253851ef127b@mailpen.com>
 <92cc8563-195f-8727-d887-5f2bb63f231f@mailpen.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <92cc8563-195f-8727-d887-5f2bb63f231f@mailpen.com>
User-Agent: Mutt/1.9.4 (2018-02-28)
Archived-At: 
 <https://www.postgresql.org/message-id/20210601041635.GQ2082%40telsasoft.com>
Precedence: bulk

> Here's the FROM clause that bit me:
>
>    FROM lic_en
>      JOIN govt_region   USING (territory_id, country_id)
>      LEFT JOIN zip_code USING (territory_id, country_id, zip5)
>      LEFT JOIN "County" USING (territory_id, country_id, fips_county);

I'm guessing that there's a dependency/correlation between
territory/country/county, and that's probably related to a misestimate causing
a bad plan.

> The first two JOINs are not the problem, & are in fact retained in my
> solution.  The problem is the third JOIN, where "fips_county" from "County" is
> actually matched with the corresponding field from the "zip_code" VIEW.  Works
> fine, if you don't mind the performance impact in v10 & above.  It has now
> been rewritten, to be a sub-query for an output field.  Voila ! Back to
> sub-second query times.

What version of 9.6.X were you upgrading *from* ?

v9.6 added selectivity estimates based on FKs, so it's not surprising if there
was a plan change migrating *to* v9.6.

...but there were a number of fixes to that, and it seems possible the plans
changed between 9.6.0 and 9.6.22, and anything backpatched to 9.X would also be
in v10+.  So you might've gotten the bad plan on 9.6.22, also.

I found these commits that might be relevant.
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=1f184426b
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=7fa93eec4
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=770671062

ad1c36b07 wasn't backpatched and probably not relevant to your issue.

-- 
Justin