AWS forcing PG upgrade from v9.6 a disaster
[Reposted to the proper list]
I started to use PostgreSQL v7.3 in 2003 on my home Linux systems (4 at
one point), gradually moving to v9.0 w/ replication in 2010. In 2017 I
moved my 20GB database to AWS/RDS, gradually upgrading to v9.6, & was
entirely satisfied with the result.
In March of this year, AWS announced that v9.6 was nearing end of
support, & AWS would forcibly upgrade everyone to v12 on January 22,
2022, if users did not perform the upgrade earlier. My first attempt
was successful as far as the upgrade itself, but complex queries that
normally ran in a couple of seconds on v9.x, were taking minutes in v12.
I didn't have the time in March to diagnose the problem, other than some
futile adjustments to server parameters, so I reverted back to a saved
copy of my v9.6 data.
On Sunday, being retired, I decided to attempt to solve the issue in
earnest. I have now spent five days (about 14 hours a day), trying
various things, including adding additional indexes. Keeping the v9.6
data online for web users, I've "forked" the data into new copies, &
updated them in turn to PostgreSQL v10, v11, v12, & v13. All exhibit
the same problem: As you will see below, it appears that versions 10 &
above are doing a sequential scan of some of the "large" (200K rows)
tables. Note that the expected & actual run times both differ for v9.6
& v13.2, by more than *two orders of magnitude*. Rather than post a huge
eMail (ha ha), I'll start with this one, that shows an "EXPLAIN ANALYZE"
from both v9.6 & v13.2, followed by the related table & view
definitions. With one exception, table definitions are from the FCC
(Federal Communications Commission); the view definitions are my own.
*Here's from v9.6:*
=> EXPLAIN ANALYZE SELECT club_count, extra_count, region_count,
callsign AS trustee_callsign, applicant_type, entity_name, licensee_id
AS _lid FROM genclub_multi_ WHERE club_count >= 5 ORDER BY extra_count
DESC, club_count DESC, entity_name;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=407.13..407.13 rows=1 width=94) (actual
time=348.850..348.859 rows=43 loops=1)
Sort Key: "_Club".extra_count DESC, "_Club".club_count DESC,
"_EN".entity_name
Sort Method: quicksort Memory: 31kB
-> Nested Loop (cost=4.90..407.12 rows=1 width=94) (actual
time=7.587..348.732 rows=43 loops=1)
-> Nested Loop (cost=4.47..394.66 rows=1 width=94) (actual
time=5.740..248.149 rows=43 loops=1)
-> Nested Loop Left Join (cost=4.04..382.20 rows=1
width=79) (actual time=2.458..107.908 rows=55 loops=1)
-> Hash Join (cost=3.75..380.26 rows=1 width=86)
(actual time=2.398..106.990 rows=55 loops=1)
Hash Cond: (("_EN".country_id =
"_GovtRegion".country_id) AND ("_EN".state = "_GovtRegion".territory_id))
-> Nested Loop (cost=0.43..376.46 rows=47
width=94) (actual time=2.294..106.736 rows=55 loops=1)
-> Seq Scan on "_Club"
(cost=0.00..4.44 rows=44 width=35) (actual time=0.024..0.101 rows=44
loops=1)
Filter: (club_count >= 5)
Rows Removed by Filter: 151
-> Index Scan using "_EN_callsign" on
"_EN" (cost=0.43..8.45 rows=1 width=69) (actual time=2.179..2.420
rows=1 loops=44)
Index Cond: (callsign =
"_Club".trustee_callsign)
-> Hash (cost=1.93..1.93 rows=93 width=7)
(actual time=0.071..0.071 rows=88 loops=1)
Buckets: 1024 Batches: 1 Memory
Usage: 12kB
-> Seq Scan on "_GovtRegion"
(cost=0.00..1.93 rows=93 width=7) (actual time=0.010..0.034 rows=93 loops=1)
-> Nested Loop (cost=0.29..1.93 rows=1 width=7)
(actual time=0.012..0.014 rows=1 loops=55)
Join Filter: ("_IsoCountry".iso_alpha2 =
"_Territory".country_id)
Rows Removed by Join Filter: 0
-> Index Only Scan using
"_IsoCountry_iso_alpha2_key" on "_IsoCountry" (cost=0.14..1.62 rows=1
width=3) (actual time=0.006..0.006 rows=1 loops=55)
Index Cond: (iso_alpha2 =
"_GovtRegion".country_id)
Heap Fetches: 55
-> Index Only Scan using "_Territory_pkey"
on "_Territory" (cost=0.14..0.29 rows=1 width=7)
(actual time=0.004..0.005 rows=1 loops=55)
Index Cond: (territory_id =
"_GovtRegion".territory_id)
Heap Fetches: 59
-> Index Scan using "_HD_pkey" on "_HD"
(cost=0.43..12.45 rows=1 width=15) (actual time=2.548..2.548 rows=1
loops=55)
Index Cond: (unique_system_identifier =
"_EN".unique_system_identifier)
Filter: (("_EN".callsign = callsign) AND
(((((license_status)::text || ' - '::text) || (COALESCE((SubPlan 2),
'???'::character varying))::text))::character(1) = 'A'::bpchar))
Rows Removed by Filter: 0
SubPlan 2
-> Limit (cost=0.15..8.17 rows=1 width=32)
(actual time=0.006..0.007 rows=1 loops=55)
-> Index Scan using "_LicStatus_pkey" on
"_LicStatus" (cost=0.15..8.17 rows=1 width=32) (actual
time=0.005..0.005 rows=1 loops=55)
Index Cond: ("_HD".license_status =
status_id)
-> Index Scan using "_AM_pkey" on "_AM" (cost=0.43..4.27
rows=1 width=15) (actual time=2.325..2.325 rows=1 loops=43)
Index Cond: (unique_system_identifier =
"_EN".unique_system_identifier)
Filter: ("_EN".callsign = callsign)
SubPlan 1
-> Limit (cost=0.15..8.17 rows=1 width=32) (actual
time=0.007..0.007 rows=1 loops=43)
-> Index Scan using "_ApplicantType_pkey" on
"_ApplicantType" (cost=0.15..8.17 rows=1 width=32) (actual
time=0.005..0.005 rows=1 loops=43)
Index Cond: ("_EN".applicant_type_code =
app_type_id)
Planning time: 13.490 ms
Execution time: 349.182 ms
(43 rows)
*Here's from v13.2:*
=> EXPLAIN ANALYZE SELECT club_count, extra_count, region_count,
callsign AS trustee_callsign, applicant_type, entity_name, licensee_id
AS _lid FROM genclub_multi_ WHERE club_count >= 5 ORDER BY extra_count
DESC, club_count DESC, entity_name;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=144365.60..144365.60 rows=1 width=94) (actual
time=31898.860..31901.922 rows=43 loops=1)
Sort Key: "_Club".extra_count DESC, "_Club".club_count DESC,
"_EN".entity_name
Sort Method: quicksort Memory: 31kB
-> Nested Loop (cost=58055.66..144365.59 rows=1 width=94) (actual
time=6132.403..31894.233 rows=43 loops=1)
-> Nested Loop (cost=58055.51..144364.21 rows=1 width=62)
(actual time=1226.085..30337.921 rows=837792 loops=1)
-> Nested Loop Left Join (cost=58055.09..144360.38
rows=1 width=59) (actual time=1062.414..12471.456 rows=1487153 loops=1)
-> Hash Join (cost=58054.80..144359.69 rows=1
width=66) (actual time=1061.330..6635.041 rows=1487153 loops=1)
Hash Cond: (("_EN".unique_system_identifier
= "_AM".unique_system_identifier) AND ("_EN".callsign = "_AM".callsign))
-> Hash Join (cost=3.33..53349.72
rows=1033046 width=51) (actual time=2.151..3433.178 rows=1487153 loops=1)
Hash Cond: (("_EN".country_id =
"_GovtRegion".country_id) AND ("_EN".state = "_GovtRegion".territory_id))
-> Seq Scan on "_EN"
(cost=0.00..45288.05 rows=1509005 width=60) (actual time=0.037..2737.054
rows=1508736 loops=1)
-> Hash (cost=1.93..1.93 rows=93
width=7) (actual time=0.706..1.264 rows=88 loops=1)
Buckets: 1024 Batches: 1 Memory
Usage: 12kB
-> Seq Scan on "_GovtRegion"
(cost=0.00..1.93 rows=93 width=7) (actual time=0.013..0.577 rows=93 loops=1)
-> Hash (cost=28093.99..28093.99
rows=1506699 width=15) (actual time=1055.587..1055.588 rows=1506474 loops=1)
Buckets: 131072 Batches: 32 Memory
Usage: 3175kB
-> Seq Scan on "_AM"
(cost=0.00..28093.99 rows=1506699 width=15) (actual time=0.009..742.774
rows=1506474 loops=1)
-> Nested Loop (cost=0.29..0.68 rows=1 width=7)
(actual time=0.003..0.004 rows=1 loops=1487153)
Join Filter: ("_IsoCountry".iso_alpha2 =
"_Territory".country_id)
Rows Removed by Join Filter: 0
-> Index Only Scan using
"_IsoCountry_iso_alpha2_key" on "_IsoCountry" (cost=0.14..0.38 rows=1
width=3) (actual time=0.001..0.002 rows=1 loops=1487153)
Index Cond: (iso_alpha2 =
"_GovtRegion".country_id)
Heap Fetches: 1487153
-> Index Only Scan using "_Territory_pkey"
on "_Territory" (cost=0.14..0.29 rows=1 width=7) (actual
time=0.001..0.001 rows=1 loops=1487153)
Index Cond: (territory_id =
"_GovtRegion".territory_id)
Heap Fetches: 1550706
-> Index Scan using "_HD_pkey" on "_HD"
(cost=0.43..3.82 rows=1 width=15) (actual time=0.012..0.012 rows=1
loops=1487153)
Index Cond: (unique_system_identifier =
"_EN".unique_system_identifier)
Filter: (("_EN".callsign = callsign) AND
(((((license_status)::text || ' - '::text) || (COALESCE((SubPlan 2),
'???'::character varying))::text))::character(1) = 'A'::bpchar))
Rows Removed by Filter: 0
SubPlan 2
-> Limit (cost=0.00..1.07 rows=1 width=13)
(actual time=0.001..0.001 rows=1 loops=1487153)
-> Seq Scan on "_LicStatus"
(cost=0.00..1.07 rows=1 width=13) (actual time=0.000..0.000 rows=1
loops=1487153)
Filter: ("_HD".license_status =
status_id)
Rows Removed by Filter: 1
-> Index Scan using "_Club_pkey" on "_Club" (cost=0.14..0.17
rows=1 width=35) (actual time=0.002..0.002 rows=0 loops=837792)
Index Cond: (trustee_callsign = "_EN".callsign)
Filter: (club_count >= 5)
Rows Removed by Filter: 0
SubPlan 1
-> Limit (cost=0.00..1.20 rows=1 width=15) (actual
time=0.060..0.060 rows=1 loops=43)
-> Seq Scan on "_ApplicantType" (cost=0.00..1.20
rows=1 width=15) (actual time=0.016..0.016 rows=1 loops=43)
Filter: ("_EN".applicant_type_code = app_type_id)
Rows Removed by Filter: 7
Planning Time: 173.753 ms
Execution Time: 31919.601 ms
(46 rows)
*VIEW genclub_multi_:*
=> \d+ genclub_multi_
View "Callsign.genclub_multi_"
Column | Type | Collation | Nullable |
Default | Storage | Description
------------------+------------------------+-----------+----------+---------+----------+-------------
sys_id | integer | | | |
plain |
callsign | character(10) | | | |
extended |
fcc_reg_num | character(10) | | | |
extended |
licensee_id | character(9) | | | |
extended |
subgroup_id_num | character(3) | | | |
extended |
applicant_type | text | | | |
extended |
entity_type | text | | | |
extended |
entity_name | character varying(200) | | | |
extended |
attention | character varying(35) | | | |
extended |
first_name | character varying(20) | | | |
extended |
middle_init | character(1) | | | |
extended |
last_name | character varying(20) | | | |
extended |
name_suffix | character(3) | | | |
extended |
street_address | character varying(60) | | | |
extended |
po_box | text | | | |
extended |
locality | character varying | | | |
extended |
locality_ | character varying | | | |
extended |
county | character varying | | | |
extended |
state | text | | | |
extended |
postal_code | text | | | |
extended |
full_name | text | | | |
extended |
_entity_name | text | | | |
extended |
_first_name | text | | | |
extended |
_last_name | text | | | |
extended |
zip5 | character(5) | | | |
extended |
zip_location | "GeoPosition" | | | |
extended |
maidenhead | bpchar | | | |
extended |
geo_region | smallint | | | |
plain |
uls_file_num | character(14) | | | |
extended |
radio_service | text | | | |
extended |
license_status | text | | | |
extended |
grant_date | date | | | |
plain |
effective_date | date | | | |
plain |
cancel_date | date | | | |
plain |
expire_date | date | | | |
plain |
end_date | date | | | |
plain |
available_date | date | | | |
plain |
last_action_date | date | | | |
plain |
uls_region | "MySql".tinyint | | | |
plain |
callsign_group | text | | | |
extended |
operator_group | text | | | |
extended |
operator_class | text | | | |
extended |
prev_class | text | | | |
extended |
prev_callsign | character(10) | | | |
extended |
vanity_type | text | | | |
extended |
is_trustee | character(1) | | | |
extended |
trustee_callsign | character(10) | | | |
extended |
trustee_name | character varying(50) | | | |
extended |
validity | integer | | | |
plain |
club_count | bigint | | | |
plain |
extra_count | bigint | | | |
plain |
region_count | bigint | | | |
plain |
View definition:
SELECT licjb_.sys_id,
licjb_.callsign,
licjb_.fcc_reg_num,
licjb_.licensee_id,
licjb_.subgroup_id_num,
licjb_.applicant_type,
licjb_.entity_type,
licjb_.entity_name,
licjb_.attention,
licjb_.first_name,
licjb_.middle_init,
licjb_.last_name,
licjb_.name_suffix,
licjb_.street_address,
licjb_.po_box,
licjb_.locality,
licjb_.locality_,
licjb_.county,
licjb_.state,
licjb_.postal_code,
licjb_.full_name,
licjb_._entity_name,
licjb_._first_name,
licjb_._last_name,
licjb_.zip5,
licjb_.zip_location,
licjb_.maidenhead,
licjb_.geo_region,
licjb_.uls_file_num,
licjb_.radio_service,
licjb_.license_status,
licjb_.grant_date,
licjb_.effective_date,
licjb_.cancel_date,
licjb_.expire_date,
licjb_.end_date,
licjb_.available_date,
licjb_.last_action_date,
licjb_.uls_region,
licjb_.callsign_group,
licjb_.operator_group,
licjb_.operator_class,
licjb_.prev_class,
licjb_.prev_callsign,
licjb_.vanity_type,
licjb_.is_trustee,
licjb_.trustee_callsign,
licjb_.trustee_name,
licjb_.validity,
gen.club_count,
gen.extra_count,
gen.region_count
FROM licjb_,
"GenLicClub" gen
WHERE licjb_.callsign = gen.trustee_callsign AND
licjb_.license_status::character(1) = 'A'::bpchar;
*
**VIEW GenLicClub:*
=> \d+ "GenLicClub"
View "Callsign.GenLicClub"
Column | Type | Collation | Nullable | Default |
Storage | Description
------------------+---------------+-----------+----------+---------+----------+-------------
trustee_callsign | character(10) | | | | extended |
club_count | bigint | | | | plain |
extra_count | bigint | | | | plain |
region_count | bigint | | | | plain |
View definition:
SELECT "_Club".trustee_callsign,
"_Club".club_count,
"_Club".extra_count,
"_Club".region_count
FROM "GenLic"."_Club";
*TABLE "GenLic"."_Club":*
=> \d+ "GenLic"."_Club"
Table "GenLic._Club"
Column | Type | Collation | Nullable | Default |
Storage | Stats target | Description
------------------+---------------+-----------+----------+---------+----------+--------------+-------------
trustee_callsign | character(10) | | not null | | extended
| |
club_count | bigint | | | | plain
| |
extra_count | bigint | | | | plain
| |
region_count | bigint | | | | plain
| |
Indexes:
"_Club_pkey" PRIMARY KEY, btree (trustee_callsign)
*VIEW licjb_:*
=> \d+ licjb_
View "Callsign.licjb_"
Column | Type | Collation | Nullable |
Default | Storage | Description
------------------+------------------------+-----------+----------+---------+----------+-------------
sys_id | integer | | | |
plain |
callsign | character(10) | | | |
extended |
fcc_reg_num | character(10) | | | |
extended |
licensee_id | character(9) | | | |
extended |
subgroup_id_num | character(3) | | | |
extended |
applicant_type | text | | | |
extended |
entity_type | text | | | |
extended |
entity_name | character varying(200) | | | |
extended |
attention | character varying(35) | | | |
extended |
first_name | character varying(20) | | | |
extended |
middle_init | character(1) | | | |
extended |
last_name | character varying(20) | | | |
extended |
name_suffix | character(3) | | | |
extended |
street_address | character varying(60) | | | |
extended |
po_box | text | | | |
extended |
locality | character varying | | | |
extended |
locality_ | character varying | | | |
extended |
county | character varying | | | |
extended |
state | text | | | |
extended |
postal_code | text | | | |
extended |
full_name | text | | | |
extended |
_entity_name | text | | | |
extended |
_first_name | text | | | |
extended |
_last_name | text | | | |
extended |
zip5 | character(5) | | | |
extended |
zip_location | "GeoPosition" | | | |
extended |
maidenhead | bpchar | | | |
extended |
geo_region | smallint | | | |
plain |
uls_file_num | character(14) | | | |
extended |
radio_service | text | | | |
extended |
license_status | text | | | |
extended |
grant_date | date | | | |
plain |
effective_date | date | | | |
plain |
cancel_date | date | | | |
plain |
expire_date | date | | | |
plain |
end_date | date | | | |
plain |
available_date | date | | | |
plain |
last_action_date | date | | | |
plain |
uls_region | "MySql".tinyint | | | |
plain |
callsign_group | text | | | |
extended |
operator_group | text | | | |
extended |
operator_class | text | | | |
extended |
prev_class | text | | | |
extended |
prev_callsign | character(10) | | | |
extended |
vanity_type | text | | | |
extended |
is_trustee | character(1) | | | |
extended |
trustee_callsign | character(10) | | | |
extended |
trustee_name | character varying(50) | | | |
extended |
validity | integer | | | |
plain |
View definition:
SELECT lic_en_.sys_id,
lic_en_.callsign,
lic_en_.fcc_reg_num,
lic_en_.licensee_id,
lic_en_.subgroup_id_num,
lic_en_.applicant_type,
lic_en_.entity_type,
lic_en_.entity_name,
lic_en_.attention,
lic_en_.first_name,
lic_en_.middle_init,
lic_en_.last_name,
lic_en_.name_suffix,
lic_en_.street_address,
lic_en_.po_box,
lic_en_.locality,
lic_en_.locality_,
lic_en_.county,
lic_en_.state,
lic_en_.postal_code,
lic_en_.full_name,
lic_en_._entity_name,
lic_en_._first_name,
lic_en_._last_name,
lic_en_.zip5,
lic_en_.zip_location,
lic_en_.maidenhead,
lic_en_.geo_region,
lic_hd_.uls_file_num,
lic_hd_.radio_service,
lic_hd_.license_status,
lic_hd_.grant_date,
lic_hd_.effective_date,
lic_hd_.cancel_date,
lic_hd_.expire_date,
lic_hd_.end_date,
lic_hd_.available_date,
lic_hd_.last_action_date,
lic_am_.uls_region,
lic_am_.callsign_group,
lic_am_.operator_group,
lic_am_.operator_class,
lic_am_.prev_class,
lic_am_.prev_callsign,
lic_am_.vanity_type,
lic_am_.is_trustee,
lic_am_.trustee_callsign,
lic_am_.trustee_name,
CASE
WHEN lic_am_.vanity_type::character(1) = ANY
(ARRAY['A'::bpchar, 'C'::bpchar]) THEN verify_callsign(lic_en_.callsign,
lic_en_.licensee_id, lic_hd_.grant_date, lic_en_.state::bpchar,
lic_am_.operator_class::bpchar, lic_en_.applicant_type::bpchar,
lic_am_.trustee_callsign)
ELSE NULL::integer
END AS validity
FROM lic_en_
JOIN lic_hd_ USING (sys_id, callsign)
JOIN lic_am_ USING (sys_id, callsign);
*VIEW lic_en_:*
=> \d+ lic_en_
View "Callsign.lic_en_"
Column | Type | Collation | Nullable |
Default | Storage | Description
-----------------+------------------------+-----------+----------+---------+----------+-------------
sys_id | integer | | | |
plain |
callsign | character(10) | | | |
extended |
fcc_reg_num | character(10) | | | |
extended |
licensee_id | character(9) | | | |
extended |
subgroup_id_num | character(3) | | | |
extended |
applicant_type | text | | | |
extended |
entity_type | text | | | |
extended |
entity_name | character varying(200) | | | |
extended |
attention | character varying(35) | | | |
extended |
first_name | character varying(20) | | | |
extended |
middle_init | character(1) | | | |
extended |
last_name | character varying(20) | | | |
extended |
name_suffix | character(3) | | | |
extended |
street_address | character varying(60) | | | |
extended |
po_box | text | | | |
extended |
locality | character varying | | | |
extended |
locality_ | character varying | | | |
extended |
county | character varying | | | |
extended |
state | text | | | |
extended |
postal_code | text | | | |
extended |
full_name | text | | | |
extended |
_entity_name | text | | | |
extended |
_first_name | text | | | |
extended |
_last_name | text | | | |
extended |
zip5 | character(5) | | | |
extended |
zip_location | "GeoPosition" | | | |
extended |
maidenhead | bpchar | | | |
extended |
geo_region | smallint | | | |
plain |
View definition:
SELECT lic_en.sys_id,
lic_en.callsign,
lic_en.fcc_reg_num,
lic_en.licensee_id,
lic_en.subgroup_id_num,
(lic_en.applicant_type::text || ' - '::text) || COALESCE(( SELECT
"ApplicantType".app_type_text
FROM "ApplicantType"
WHERE lic_en.applicant_type = "ApplicantType".app_type_id
LIMIT 1), '???'::character varying)::text AS applicant_type,
(lic_en.entity_type::text || ' - '::text) || COALESCE(( SELECT
"EntityType".entity_text
FROM "EntityType"
WHERE lic_en.entity_type = "EntityType".entity_id
LIMIT 1), '???'::character varying)::text AS entity_type,
lic_en.entity_name,
lic_en.attention,
lic_en.first_name,
lic_en.middle_init,
lic_en.last_name,
lic_en.name_suffix,
lic_en.street_address,
lic_en.po_box,
lic_en.locality,
zip_code.locality_text AS locality_,
"County".county_text AS county,
(territory_id::text || ' - '::text) ||
COALESCE(govt_region.territory_text, '???'::character varying)::text AS
state,
zip9_format(lic_en.postal_code::text) AS postal_code,
lic_en.full_name,
lic_en._entity_name,
lic_en._first_name,
lic_en._last_name,
lic_en.zip5,
zip_code.zip_location,
maidenhead(zip_code.zip_location) AS maidenhead,
govt_region.geo_region
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);
*VIEW lic_en:*
=> \d+ lic_en
View "Callsign.lic_en"
Column | Type | Collation | Nullable |
Default | Storage | Description
-----------------+------------------------+-----------+----------+---------+----------+-------------
sys_id | integer | | | |
plain |
callsign | character(10) | | | |
extended |
fcc_reg_num | character(10) | | | |
extended |
licensee_id | character(9) | | | |
extended |
subgroup_id_num | character(3) | | | |
extended |
applicant_type | character(1) | | | |
extended |
entity_type | character(2) | | | |
extended |
entity_name | character varying(200) | | | |
extended |
attention | character varying(35) | | | |
extended |
first_name | character varying(20) | | | |
extended |
middle_init | character(1) | | | |
extended |
last_name | character varying(20) | | | |
extended |
name_suffix | character(3) | | | |
extended |
street_address | character varying(60) | | | |
extended |
po_box | text | | | |
extended |
locality | character varying | | | |
extended |
territory_id | character(2) | | | |
extended |
postal_code | character(9) | | | |
extended |
full_name | text | | | |
extended |
_entity_name | text | | | |
extended |
_first_name | text | | | |
extended |
_last_name | text | | | |
extended |
zip5 | character(5) | | | |
extended |
country_id | character(2) | | | |
extended |
View definition:
SELECT _lic_en.sys_id,
_lic_en.callsign,
_lic_en.fcc_reg_num,
_lic_en.licensee_id,
_lic_en.subgroup_id_num,
_lic_en.applicant_type,
_lic_en.entity_type,
_lic_en.entity_name,
_lic_en.attention,
_lic_en.first_name,
_lic_en.middle_init,
_lic_en.last_name,
_lic_en.name_suffix,
_lic_en.street_address,
_lic_en.po_box,
_lic_en.locality,
_lic_en.territory_id,
_lic_en.postal_code,
_lic_en.full_name,
_lic_en._entity_name,
_lic_en._first_name,
_lic_en._last_name,
_lic_en.zip5,
_lic_en.country_id
FROM _lic_en;
*VIEW _lic_en:*
=> \d+ _lic_en
View "Callsign._lic_en"
Column | Type | Collation | Nullable |
Default | Storage | Description
-----------------+------------------------+-----------+----------+---------+----------+-------------
sys_id | integer | | | |
plain |
callsign | character(10) | | | |
extended |
fcc_reg_num | character(10) | | | |
extended |
licensee_id | character(9) | | | |
extended |
subgroup_id_num | character(3) | | | |
extended |
applicant_type | character(1) | | | |
extended |
entity_type | character(2) | | | |
extended |
entity_name | character varying(200) | | | |
extended |
attention | character varying(35) | | | |
extended |
first_name | character varying(20) | | | |
extended |
middle_init | character(1) | | | |
extended |
last_name | character varying(20) | | | |
extended |
name_suffix | character(3) | | | |
extended |
street_address | character varying(60) | | | |
extended |
po_box | text | | | |
extended |
locality | character varying | | | |
extended |
territory_id | character(2) | | | |
extended |
postal_code | character(9) | | | |
extended |
full_name | text | | | |
extended |
_entity_name | text | | | |
extended |
_first_name | text | | | |
extended |
_last_name | text | | | |
extended |
zip5 | character(5) | | | |
extended |
country_id | character(2) | | | |
extended |
View definition:
SELECT "_EN".unique_system_identifier AS sys_id,
"_EN".callsign,
"_EN".frn AS fcc_reg_num,
"_EN".licensee_id,
"_EN".sgin AS subgroup_id_num,
"_EN".applicant_type_code AS applicant_type,
"_EN".entity_type,
"_EN".entity_name,
"_EN".attention_line AS attention,
"_EN".first_name,
"_EN".mi AS middle_init,
"_EN".last_name,
"_EN".suffix AS name_suffix,
"_EN".street_address,
po_box_format("_EN".po_box::text) AS po_box,
"_EN".city AS locality,
"_EN".state AS territory_id,
"_EN".zip_code AS postal_code,
initcap(((COALESCE("_EN".first_name::text || ' '::text, ''::text)
|| COALESCE("_EN".mi::text || ' '::text, ''::text)) ||
"_EN".last_name::text) || COALESCE(' '::text || "_EN".suffix::text,
''::text)) AS full_name,
initcap("_EN".entity_name::text) AS _entity_name,
initcap("_EN".first_name::text) AS _first_name,
initcap("_EN".last_name::text) AS _last_name,
"_EN".zip_code::character(5) AS zip5,
"_EN".country_id
FROM "UlsLic"."_EN";
*TABLE "UlsLic"."_EN"**:*
=> \d+ "UlsLic"."_EN"
Table "UlsLic._EN"
Column | Type | Collation |
Nullable | Default | Storage | Stats target | Description
--------------------------+------------------------+-----------+----------+---------+----------+--------------+-------------
record_type | character(2) | | not
null | | extended | |
unique_system_identifier | integer | | not
null | | plain | |
uls_file_number | character(14) | |
| | extended | |
ebf_number | character varying(30) | |
| | extended | |
callsign | character(10) | |
| | extended | |
entity_type | character(2) | |
| | extended | |
licensee_id | character(9) | |
| | extended | |
entity_name | character varying(200) | |
| | extended | |
first_name | character varying(20) | |
| | extended | |
mi | character(1) | |
| | extended | |
last_name | character varying(20) | |
| | extended | |
suffix | character(3) | |
| | extended | |
phone | character(10) | |
| | extended | |
fax | character(10) | |
| | extended | |
email | character varying(50) | |
| | extended | |
street_address | character varying(60) | |
| | extended | |
city | character varying | |
| | extended | |
state | character(2) | |
| | extended | |
zip_code | character(9) | |
| | extended | |
po_box | character varying(20) | |
| | extended | |
attention_line | character varying(35) | |
| | extended | |
sgin | character(3) | |
| | extended | |
frn | character(10) | |
| | extended | |
applicant_type_code | character(1) | |
| | extended | |
applicant_type_other | character(40) | |
| | extended | |
status_code | character(1) | |
| | extended | |
status_date | "MySql".datetime | |
| | plain | |
lic_category_code | character(1) | |
| | extended | |
linked_license_id | numeric(9,0) | |
| | main | |
linked_callsign | character(10) | |
| | extended | |
country_id | character(2) | |
| | extended | |
Indexes:
"_EN_pkey" PRIMARY KEY, btree (unique_system_identifier)
"_EN__entity_name" btree (initcap(entity_name::text))
"_EN__first_name" btree (initcap(first_name::text))
"_EN__last_name" btree (initcap(last_name::text))
"_EN__zip5" btree ((zip_code::character(5)))
"_EN_callsign" btree (callsign)
"_EN_fcc_reg_num" btree (frn)
"_EN_licensee_id" btree (licensee_id)
Check constraints:
"_EN_record_type_check" CHECK (record_type = 'EN'::bpchar)
Foreign-key constraints:
"_EN_applicant_type_code_fkey" FOREIGN KEY (applicant_type_code)
REFERENCES "FccLookup"."_ApplicantType"(app_type_id
)
"_EN_entity_type_fkey" FOREIGN KEY (entity_type) REFERENCES
"FccLookup"."_EntityType"(entity_id)
"_EN_state_fkey" FOREIGN KEY (state, country_id) REFERENCES
"BaseLookup"."_Territory"(territory_id, country_id)
"_EN_unique_system_identifier_fkey" FOREIGN KEY
(unique_system_identifier) REFERENCES "UlsLic"."_HD"(unique_system_i
dentifier) ON UPDATE CASCADE ON DELETE CASCADE
*VIEW lic_hd_:*
=> \d+ lic_hd_
View "Callsign.lic_hd_"
Column | Type | Collation | Nullable | Default |
Storage | Description
------------------+---------------+-----------+----------+---------+----------+-------------
sys_id | integer | | | | plain |
callsign | character(10) | | | | extended |
uls_file_num | character(14) | | | | extended |
radio_service | text | | | | extended |
license_status | text | | | | extended |
grant_date | date | | | | plain |
effective_date | date | | | | plain |
cancel_date | date | | | | plain |
expire_date | date | | | | plain |
end_date | date | | | | plain |
available_date | date | | | | plain |
last_action_date | date | | | | plain |
View definition:
SELECT lic_hd.sys_id,
lic_hd.callsign,
lic_hd.uls_file_num,
(lic_hd.radio_service::text || ' - '::text) || COALESCE(( SELECT
"RadioService".service_text
FROM "RadioService"
WHERE lic_hd.radio_service = "RadioService".service_id
LIMIT 1), '???'::character varying)::text AS radio_service,
(lic_hd.license_status::text || ' - '::text) || COALESCE(( SELECT
"LicStatus".status_text
FROM "LicStatus"
WHERE lic_hd.license_status = "LicStatus".status_id
LIMIT 1), '???'::character varying)::text AS license_status,
lic_hd.grant_date,
lic_hd.effective_date,
lic_hd.cancel_date,
lic_hd.expire_date,
LEAST(lic_hd.cancel_date, lic_hd.expire_date) AS end_date,
CASE
WHEN lic_hd.cancel_date < lic_hd.expire_date THEN
GREATEST((lic_hd.cancel_date + '2 years'::interval)::date,
lic_hd.last_action_date + 30)
WHEN lic_hd.license_status = 'A'::bpchar AND uls_date() >
(lic_hd.expire_date + '2 years'::interval)::date THEN NULL::date
ELSE (lic_hd.expire_date + '2 years'::interval)::date
END + 1 AS available_date,
lic_hd.last_action_date
FROM lic_hd;
*VIEW lic_hd:*
=> \d+ lic_hd
View "Callsign.lic_hd"
Column | Type | Collation | Nullable | Default |
Storage | Description
------------------+---------------+-----------+----------+---------+----------+-------------
sys_id | integer | | | | plain |
callsign | character(10) | | | | extended |
uls_file_num | character(14) | | | | extended |
radio_service | character(2) | | | | extended |
license_status | character(1) | | | | extended |
grant_date | date | | | | plain |
effective_date | date | | | | plain |
cancel_date | date | | | | plain |
expire_date | date | | | | plain |
last_action_date | date | | | | plain |
View definition:
SELECT _lic_hd.sys_id,
_lic_hd.callsign,
_lic_hd.uls_file_num,
_lic_hd.radio_service,
_lic_hd.license_status,
_lic_hd.grant_date,
_lic_hd.effective_date,
_lic_hd.cancel_date,
_lic_hd.expire_date,
_lic_hd.last_action_date
FROM _lic_hd;
*VIEW _lic_hd:*
=> \d+ _lic_hd
View "Callsign._lic_hd"
Column | Type | Collation | Nullable | Default |
Storage | Description
------------------+---------------+-----------+----------+---------+----------+-------------
sys_id | integer | | | | plain |
callsign | character(10) | | | | extended |
uls_file_num | character(14) | | | | extended |
radio_service | character(2) | | | | extended |
license_status | character(1) | | | | extended |
grant_date | date | | | | plain |
effective_date | date | | | | plain |
cancel_date | date | | | | plain |
expire_date | date | | | | plain |
last_action_date | date | | | | plain |
View definition:
SELECT "_HD".unique_system_identifier AS sys_id,
"_HD".callsign,
"_HD".uls_file_number AS uls_file_num,
"_HD".radio_service_code AS radio_service,
"_HD".license_status,
"_HD".grant_date,
"_HD".effective_date,
"_HD".cancellation_date AS cancel_date,
"_HD".expired_date AS expire_date,
"_HD".last_action_date
FROM "UlsLic"."_HD";
*TABLE **"UlsLic"."_HD"**:*
=> \d+ "UlsLic"."_HD"
Table "UlsLic._HD"
Column | Type | Collation |
Nullable | Default | Storage | Stats target | Descr
iption
------------------------------+-----------------------+-----------+----------+---------+----------+--------------+------
-------
record_type | character(2) | | not null
| | extended | |
unique_system_identifier | integer | | not null
| | plain | |
uls_file_number | character(14) | |
| | extended | |
ebf_number | character varying(30) | |
| | extended | |
callsign | character(10) | |
| | extended | |
license_status | character(1) | |
| | extended | |
radio_service_code | character(2) | |
| | extended | |
grant_date | date | |
| | plain | |
expired_date | date | |
| | plain | |
cancellation_date | date | |
| | plain | |
eligibility_rule_num | character(10) | |
| | extended | |
applicant_type_code_reserved | character(1) | |
| | extended | |
alien | character(1) | |
| | extended | |
alien_government | character(1) | |
| | extended | |
alien_corporation | character(1) | |
| | extended | |
alien_officer | character(1) | |
| | extended | |
alien_control | character(1) | |
| | extended | |
revoked | character(1) | |
| | extended | |
convicted | character(1) | |
| | extended | |
adjudged | character(1) | |
| | extended | |
involved_reserved | character(1) | |
| | extended | |
common_carrier | character(1) | |
| | extended | |
non_common_carrier | character(1) | |
| | extended | |
private_comm | character(1) | |
| | extended | |
fixed | character(1) | |
| | extended | |
mobile | character(1) | |
| | extended | |
radiolocation | character(1) | |
| | extended | |
satellite | character(1) | |
| | extended | |
developmental_or_sta | character(1) | |
| | extended | |
interconnected_service | character(1) | |
| | extended | |
certifier_first_name | character varying(20) | |
| | extended | |
certifier_mi | character varying | |
| | extended | |
certifier_last_name | character varying | |
| | extended | |
certifier_suffix | character(3) | |
| | extended | |
certifier_title | character(40) | |
| | extended | |
gender | character(1) | |
| | extended | |
african_american | character(1) | |
| | extended | |
native_american | character(1) | |
| | extended | |
hawaiian | character(1) | |
| | extended | |
asian | character(1) | |
| | extended | |
white | character(1) | |
| | extended | |
ethnicity | character(1) | |
| | extended | |
effective_date | date | |
| | plain | |
last_action_date | date | |
| | plain | |
auction_id | integer | |
| | plain | |
reg_stat_broad_serv | character(1) | |
| | extended | |
band_manager | character(1) | |
| | extended | |
type_serv_broad_serv | character(1) | |
| | extended | |
alien_ruling | character(1) | |
| | extended | |
licensee_name_change | character(1) | |
| | extended | |
whitespace_ind | character(1) | |
| | extended | |
additional_cert_choice | character(1) | |
| | extended | |
additional_cert_answer | character(1) | |
| | extended | |
discontinuation_ind | character(1) | |
| | extended | |
regulatory_compliance_ind | character(1) | |
| | extended | |
dummy1 | character varying | |
| | extended | |
dummy2 | character varying | |
| | extended | |
dummy3 | character varying | |
| | extended | |
dummy4 | character varying | |
| | extended | |
Indexes:
"_HD_pkey" PRIMARY KEY, btree (unique_system_identifier)
"_HD_callsign" btree (callsign)
"_HD_grant_date" btree (grant_date)
"_HD_last_action_date" btree (last_action_date)
"_HD_uls_file_num" btree (uls_file_number)
Check constraints:
"_HD_record_type_check" CHECK (record_type = 'HD'::bpchar)
Foreign-key constraints:
"_HD_license_status_fkey" FOREIGN KEY (license_status) REFERENCES
"FccLookup"."_LicStatus"(status_id)
"_HD_radio_service_code_fkey" FOREIGN KEY (radio_service_code)
REFERENCES "FccLookup"."_RadioService"(service_id)
Referenced by:
TABLE ""UlsLic"."_AM"" CONSTRAINT
"_AM_unique_system_identifier_fkey" FOREIGN KEY
(unique_system_identifier) REFEREN
CES "UlsLic"."_HD"(unique_system_identifier) ON UPDATE CASCADE ON DELETE
CASCADE
TABLE ""UlsLic"."_CO"" CONSTRAINT
"_CO_unique_system_identifier_fkey" FOREIGN KEY
(unique_system_identifier) REFEREN
CES "UlsLic"."_HD"(unique_system_identifier) ON UPDATE CASCADE ON DELETE
CASCADE
TABLE ""UlsLic"."_EN"" CONSTRAINT
"_EN_unique_system_identifier_fkey" FOREIGN KEY
(unique_system_identifier) REFEREN
CES "UlsLic"."_HD"(unique_system_identifier) ON UPDATE CASCADE ON DELETE
CASCADE
TABLE ""UlsLic"."_HS"" CONSTRAINT
"_HS_unique_system_identifier_fkey" FOREIGN KEY
(unique_system_identifier) REFEREN
CES "UlsLic"."_HD"(unique_system_identifier) ON UPDATE CASCADE ON DELETE
CASCADE
TABLE ""UlsLic"."_LA"" CONSTRAINT
"_LA_unique_system_identifier_fkey" FOREIGN KEY
(unique_system_identifier) REFEREN
CES "UlsLic"."_HD"(unique_system_identifier) ON UPDATE CASCADE ON DELETE
CASCADE
TABLE ""UlsLic"."_SC"" CONSTRAINT
"_SC_unique_system_identifier_fkey" FOREIGN KEY
(unique_system_identifier) REFEREN
CES "UlsLic"."_HD"(unique_system_identifier) ON UPDATE CASCADE ON DELETE
CASCADE
TABLE ""UlsLic"."_SF"" CONSTRAINT
"_SF_unique_system_identifier_fkey" FOREIGN KEY
(unique_system_identifier) REFEREN
CES "UlsLic"."_HD"(unique_system_identifier) ON UPDATE CASCADE ON DELETE
CASCADE
*VIEW lic_am_:*
=> \d+ lic_am_
View "Callsign.lic_am_"
Column | Type | Collation | Nullable |
Default | Storage | Description
------------------+-----------------------+-----------+----------+---------+----------+-------------
sys_id | integer | | | |
plain |
callsign | character(10) | | | |
extended |
uls_region | "MySql".tinyint | | | |
plain |
callsign_group | text | | | |
extended |
operator_group | text | | | |
extended |
operator_class | text | | | |
extended |
prev_class | text | | | |
extended |
prev_callsign | character(10) | | | |
extended |
vanity_type | text | | | |
extended |
is_trustee | character(1) | | | |
extended |
trustee_callsign | character(10) | | | |
extended |
trustee_name | character varying(50) | | | |
extended |
View definition:
SELECT lic_am.sys_id,
lic_am.callsign,
lic_am.uls_region,
( SELECT ("CallsignGroup".group_id::text || ' - '::text) ||
"CallsignGroup".match_text::text
FROM "CallsignGroup"
WHERE lic_am.callsign ~ "CallsignGroup".pattern::text
LIMIT 1) AS callsign_group,
( SELECT (oper_group.group_id::text || ' - '::text) ||
oper_group.group_text::text
FROM oper_group
WHERE lic_am.operator_class = oper_group.class_id
LIMIT 1) AS operator_group,
(lic_am.operator_class::text || ' - '::text) || COALESCE(( SELECT
"OperatorClass".class_text
FROM "OperatorClass"
WHERE lic_am.operator_class = "OperatorClass".class_id
LIMIT 1), '???'::character varying)::text AS operator_class,
(lic_am.prev_class::text || ' - '::text) || COALESCE(( SELECT
"OperatorClass".class_text
FROM "OperatorClass"
WHERE lic_am.prev_class = "OperatorClass".class_id
LIMIT 1), '???'::character varying)::text AS prev_class,
lic_am.prev_callsign,
(lic_am.vanity_type::text || ' - '::text) || COALESCE(( SELECT
"VanityType".vanity_text
FROM "VanityType"
WHERE lic_am.vanity_type = "VanityType".vanity_id
LIMIT 1), '???'::character varying)::text AS vanity_type,
lic_am.is_trustee,
lic_am.trustee_callsign,
lic_am.trustee_name
FROM lic_am;
*VIEW lic_am:*
=> \d+ lic_am
View "Callsign.lic_am"
Column | Type | Collation | Nullable |
Default | Storage | Description
------------------+-----------------------+-----------+----------+---------+----------+-------------
sys_id | integer | | | |
plain |
callsign | character(10) | | | |
extended |
uls_region | "MySql".tinyint | | | |
plain |
uls_group | character(1) | | | |
extended |
operator_class | character(1) | | | |
extended |
prev_callsign | character(10) | | | |
extended |
prev_class | character(1) | | | |
extended |
vanity_type | character(1) | | | |
extended |
is_trustee | character(1) | | | |
extended |
trustee_callsign | character(10) | | | |
extended |
trustee_name | character varying(50) | | | |
extended |
View definition:
SELECT _lic_am.sys_id,
_lic_am.callsign,
_lic_am.uls_region,
_lic_am.uls_group,
_lic_am.operator_class,
_lic_am.prev_callsign,
_lic_am.prev_class,
_lic_am.vanity_type,
_lic_am.is_trustee,
_lic_am.trustee_callsign,
_lic_am.trustee_name
FROM _lic_am;
*VIEW _lic_am:*
=> \d+ _lic_am
View "Callsign._lic_am"
Column | Type | Collation | Nullable |
Default | Storage | Description
------------------+-----------------------+-----------+----------+---------+----------+-------------
sys_id | integer | | | |
plain |
callsign | character(10) | | | |
extended |
uls_region | "MySql".tinyint | | | |
plain |
uls_group | character(1) | | | |
extended |
operator_class | character(1) | | | |
extended |
prev_callsign | character(10) | | | |
extended |
prev_class | character(1) | | | |
extended |
vanity_type | character(1) | | | |
extended |
is_trustee | character(1) | | | |
extended |
trustee_callsign | character(10) | | | |
extended |
trustee_name | character varying(50) | | | |
extended |
View definition:
SELECT "_AM".unique_system_identifier AS sys_id,
"_AM".callsign,
"_AM".region_code AS uls_region,
"_AM".group_code AS uls_group,
"_AM".operator_class,
"_AM".previous_callsign AS prev_callsign,
"_AM".previous_operator_class AS prev_class,
"_AM".vanity_callsign_change AS vanity_type,
"_AM".trustee_indicator AS is_trustee,
"_AM".trustee_callsign,
"_AM".trustee_name
FROM "UlsLic"."_AM";
*TABLE **"UlsLic"."_AM"**:*
=> \d+ "UlsLic"."_AM"
Table "UlsLic._AM"
Column | Type | Collation |
Nullable | Default | Storage | Stats target | Description
----------------------------+-----------------------+-----------+----------+---------+----------+--------------+-------------
record_type | character(2) | | not
null | | extended | |
unique_system_identifier | integer | | not
null | | plain | |
uls_file_number | character(14) | |
| | extended | |
ebf_number | character varying(30) | |
| | extended | |
callsign | character(10) | |
| | extended | |
operator_class | character(1) | |
| | extended | |
group_code | character(1) | |
| | extended | |
region_code | "MySql".tinyint | |
| | plain | |
trustee_callsign | character(10) | |
| | extended | |
trustee_indicator | character(1) | |
| | extended | |
physician_certification | character(1) | |
| | extended | |
ve_signature | character(1) | |
| | extended | |
systematic_callsign_change | character(1) | |
| | extended | |
vanity_callsign_change | character(1) | |
| | extended | |
vanity_relationship | character(12) | |
| | extended | |
previous_callsign | character(10) | |
| | extended | |
previous_operator_class | character(1) | |
| | extended | |
trustee_name | character varying(50) | |
| | extended | |
Indexes:
"_AM_pkey" PRIMARY KEY, btree (unique_system_identifier)
"_AM_callsign" btree (callsign)
"_AM_prev_callsign" btree (previous_callsign)
"_AM_trustee_callsign" btree (trustee_callsign)
Check constraints:
"_AM_record_type_check" CHECK (record_type = 'AM'::bpchar)
Foreign-key constraints:
"_AM_operator_class_fkey" FOREIGN KEY (operator_class) REFERENCES
"FccLookup"."_OperatorClass"(class_id)
"_AM_previous_operator_class_fkey" FOREIGN KEY
(previous_operator_class) REFERENCES "FccLookup"."_OperatorClass"(cla
ss_id)
"_AM_unique_system_identifier_fkey" FOREIGN KEY
(unique_system_identifier) REFERENCES "UlsLic"."_HD"(unique_system_i
dentifier) ON UPDATE CASCADE ON DELETE CASCADE
"_AM_vanity_callsign_change_fkey" FOREIGN KEY
(vanity_callsign_change) REFERENCES "FccLookup"."_VanityType"(vanity_i
d)
On 5/28/21 2:48 PM, Dean Gibson (DB Administrator) wrote:
[Reposted to the proper list]
I started to use PostgreSQL v7.3 in 2003 on my home Linux systems (4
at one point), gradually moving to v9.0 w/ replication in 2010. In
2017 I moved my 20GB database to AWS/RDS, gradually upgrading to v9.6,
& was entirely satisfied with the result.In March of this year, AWS announced that v9.6 was nearing end of
support, & AWS would forcibly upgrade everyone to v12 on January 22,
2022, if users did not perform the upgrade earlier. My first attempt
was successful as far as the upgrade itself, but complex queries that
normally ran in a couple of seconds on v9.x, were taking minutes in v12.I didn't have the time in March to diagnose the problem, other than
some futile adjustments to server parameters, so I reverted back to a
saved copy of my v9.6 data.On Sunday, being retired, I decided to attempt to solve the issue in
earnest. I have now spent five days (about 14 hours a day), trying
various things, including adding additional indexes. Keeping the v9.6
data online for web users, I've "forked" the data into new copies, &
updated them in turn to PostgreSQL v10, v11, v12, & v13. All exhibit
the same problem: As you will see below, it appears that versions 10
& above are doing a sequential scan of some of the "large" (200K rows)
tables. Note that the expected & actual run times both differ for
v9.6 & v13.2, by more than *two orders of magnitude*. Rather than post
a huge eMail (ha ha), I'll start with this one, that shows an "EXPLAIN
ANALYZE" from both v9.6 & v13.2, followed by the related table & view
definitions. With one exception, table definitions are from the FCC
(Federal Communications Commission); the view definitions are my own.
Have you tried reproducing these results outside RDS, say on an EC2
instance running vanilla PostgreSQL?
cheers
andrew
--
Andrew Dunstan
EDB: https://www.enterprisedb.com
Also, did you check your RDS setting in AWS after upgrading? I run four databases in AWS. I found that the work_mem was set way low after an upgrade. I had to tweak many of my settings.
Lance
From: Andrew Dunstan <andrew@dunslane.net>
Date: Friday, May 28, 2021 at 2:08 PM
To: Dean Gibson (DB Administrator) <postgresql@mailpen.com>, pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Subject: Re: AWS forcing PG upgrade from v9.6 a disaster
On 5/28/21 2:48 PM, Dean Gibson (DB Administrator) wrote:
[Reposted to the proper list]
I started to use PostgreSQL v7.3 in 2003 on my home Linux systems (4
at one point), gradually moving to v9.0 w/ replication in 2010. In
2017 I moved my 20GB database to AWS/RDS, gradually upgrading to v9.6,
& was entirely satisfied with the result.In March of this year, AWS announced that v9.6 was nearing end of
support, & AWS would forcibly upgrade everyone to v12 on January 22,
2022, if users did not perform the upgrade earlier. My first attempt
was successful as far as the upgrade itself, but complex queries that
normally ran in a couple of seconds on v9.x, were taking minutes in v12.I didn't have the time in March to diagnose the problem, other than
some futile adjustments to server parameters, so I reverted back to a
saved copy of my v9.6 data.On Sunday, being retired, I decided to attempt to solve the issue in
earnest. I have now spent five days (about 14 hours a day), trying
various things, including adding additional indexes. Keeping the v9.6
data online for web users, I've "forked" the data into new copies, &
updated them in turn to PostgreSQL v10, v11, v12, & v13. All exhibit
the same problem: As you will see below, it appears that versions 10
& above are doing a sequential scan of some of the "large" (200K rows)
tables. Note that the expected & actual run times both differ for
v9.6 & v13.2, by more than *two orders of magnitude*. Rather than post
a huge eMail (ha ha), I'll start with this one, that shows an "EXPLAIN
ANALYZE" from both v9.6 & v13.2, followed by the related table & view
definitions. With one exception, table definitions are from the FCC
(Federal Communications Commission); the view definitions are my own.
Have you tried reproducing these results outside RDS, say on an EC2
instance running vanilla PostgreSQL?
cheers
andrew
Hi Lance,
Did you customize the PG 12 DB Parameter group to be in sync as much as
possible with the 9.6 RDS version?� Or are you using PG12 default DB
Parameter group?
Are you using the same AWS Instance Class?
Did you vacuum analyze all your tables after the upgrade to 12?
Regards,
Michael Vitale
Campbell, Lance wrote on 5/28/2021 3:18 PM:
Show quoted text
Also, did you check your RDS setting in AWS after upgrading?� I run
four databases in AWS.� I found that the work_mem was set way low
after an upgrade.� I had to tweak many of my settings.Lance
*From: *Andrew Dunstan <andrew@dunslane.net>
*Date: *Friday, May 28, 2021 at 2:08 PM
*To: *Dean Gibson (DB Administrator) <postgresql@mailpen.com>,
pgsql-performance@lists.postgresql.org
<pgsql-performance@lists.postgresql.org>
*Subject: *Re: AWS forcing PG upgrade from v9.6 a disasterOn 5/28/21 2:48 PM, Dean Gibson (DB Administrator) wrote:
[Reposted to the proper list]
I started to use PostgreSQL v7.3 in 2003 on my home Linux systems (4
at one point), gradually moving to v9.0 w/ replication in 2010.� In
2017 I moved my 20GB database to AWS/RDS, gradually upgrading to v9.6,
& was entirely satisfied with the result.In March of this year, AWS announced that v9.6 was nearing end of
support, & AWS would forcibly upgrade everyone to v12 on January 22,
2022, if users did not perform the upgrade earlier.� My first attempt
was successful as far as the upgrade itself, but complex queries that
normally ran in a couple of seconds on v9.x, were taking minutes in v12.I didn't have the time in March to diagnose the problem, other than
some futile adjustments to server parameters, so I reverted back to a
saved copy of my v9.6 data.On Sunday, being retired, I decided to attempt to solve the issue in
earnest.� I have now spent five days (about 14 hours a day), trying
various things, including adding additional indexes.� Keeping the v9.6
data online for web users, I've "forked" the data into new copies, &
updated them in turn to PostgreSQL v10, v11, v12, & v13.� All exhibit
the same problem:� As you will see below, it appears that versions 10
& above are doing a sequential scan of some of the "large" (200K rows)
tables.� Note that the expected & actual run times both differ for
v9.6 & v13.2, by more than *two orders of magnitude*. Rather than post
a huge eMail (ha ha), I'll start with this one, that shows an "EXPLAIN
ANALYZE" from both v9.6 & v13.2, followed by the related table & view
definitions.� With one exception, table definitions are from the FCC
(Federal Communications Commission);� the view definitions are my own.Have you tried reproducing these results outside RDS, say on an EC2
instance running vanilla PostgreSQL?cheers
andrew
--
Andrew Dunstan
EDB:
https://urldefense.com/v3/__https://www.enterprisedb.com__;!!DZ3fjg!tiFTfkNeARuU_vwxOHZfrJvVXj8kYMPJqa1tO5Fnv75UbERS8ZAmUoNFl_g2EVyL$
<https://urldefense.com/v3/__https:/www.enterprisedb.com__;%21%21DZ3fjg%21tiFTfkNeARuU_vwxOHZfrJvVXj8kYMPJqa1tO5Fnv75UbERS8ZAmUoNFl_g2EVyL$>
The problem is the plan. The planner massively underestimated the number of
rows arising from the _EN/_AM join.
Usually postgres is pretty good about running ANALYZE as needed, but it
might be a good idea to run it manually to rule that out as a potential
culprit.
On Fri, May 28, 2021 at 3:19 PM Campbell, Lance <lance@illinois.edu> wrote:
Show quoted text
Also, did you check your RDS setting in AWS after upgrading? I run four
databases in AWS. I found that the work_mem was set way low after an
upgrade. I had to tweak many of my settings.Lance
*From: *Andrew Dunstan <andrew@dunslane.net>
*Date: *Friday, May 28, 2021 at 2:08 PM
*To: *Dean Gibson (DB Administrator) <postgresql@mailpen.com>,
pgsql-performance@lists.postgresql.org <
pgsql-performance@lists.postgresql.org>
*Subject: *Re: AWS forcing PG upgrade from v9.6 a disasterOn 5/28/21 2:48 PM, Dean Gibson (DB Administrator) wrote:
[Reposted to the proper list]
I started to use PostgreSQL v7.3 in 2003 on my home Linux systems (4
at one point), gradually moving to v9.0 w/ replication in 2010. In
2017 I moved my 20GB database to AWS/RDS, gradually upgrading to v9.6,
& was entirely satisfied with the result.In March of this year, AWS announced that v9.6 was nearing end of
support, & AWS would forcibly upgrade everyone to v12 on January 22,
2022, if users did not perform the upgrade earlier. My first attempt
was successful as far as the upgrade itself, but complex queries that
normally ran in a couple of seconds on v9.x, were taking minutes in v12.I didn't have the time in March to diagnose the problem, other than
some futile adjustments to server parameters, so I reverted back to a
saved copy of my v9.6 data.On Sunday, being retired, I decided to attempt to solve the issue in
earnest. I have now spent five days (about 14 hours a day), trying
various things, including adding additional indexes. Keeping the v9.6
data online for web users, I've "forked" the data into new copies, &
updated them in turn to PostgreSQL v10, v11, v12, & v13. All exhibit
the same problem: As you will see below, it appears that versions 10
& above are doing a sequential scan of some of the "large" (200K rows)
tables. Note that the expected & actual run times both differ for
v9.6 & v13.2, by more than *two orders of magnitude*. Rather than post
a huge eMail (ha ha), I'll start with this one, that shows an "EXPLAIN
ANALYZE" from both v9.6 & v13.2, followed by the related table & view
definitions. With one exception, table definitions are from the FCC
(Federal Communications Commission); the view definitions are my own.Have you tried reproducing these results outside RDS, say on an EC2
instance running vanilla PostgreSQL?cheers
andrew
--
Andrew Dunstan
EDB:
https://urldefense.com/v3/__https://www.enterprisedb.com__;!!DZ3fjg!tiFTfkNeARuU_vwxOHZfrJvVXj8kYMPJqa1tO5Fnv75UbERS8ZAmUoNFl_g2EVyL$
<https://urldefense.com/v3/__https:/www.enterprisedb.com__;!!DZ3fjg!tiFTfkNeARuU_vwxOHZfrJvVXj8kYMPJqa1tO5Fnv75UbERS8ZAmUoNFl_g2EVyL$>
The plan is also influenced by cost related and memory related config
settings such as random_page_cost and work_mem, right? Hence the questions
if configs are matching or newer versions are using very conservative
(default) settings.
On 5/28/21 2:48 PM, Dean Gibson (DB Administrator) wrote:
What sticks out for me are these two scans, which balloon from 50-60
heap fetches to 1.5M each.
-> Nested Loop (cost=0.29..0.68 rows=1 width=7)
(actual time=0.003..0.004 rows=1 loops=1487153)
Join Filter: ("_IsoCountry".iso_alpha2 =
"_Territory".country_id)
Rows Removed by Join Filter: 0
-> Index Only Scan using
"_IsoCountry_iso_alpha2_key" on "_IsoCountry" (cost=0.14..0.38 rows=1
width=3) (actual time=0.001..0.002 rows=1 loops=1487153)
Index Cond: (iso_alpha2 =
"_GovtRegion".country_id)
Heap Fetches: 1487153
-> Index Only Scan using "_Territory_pkey"
on "_Territory" (cost=0.14..0.29 rows=1 width=7) (actual
time=0.001..0.001 rows=1 loops=1487153)
Index Cond: (territory_id =
"_GovtRegion".territory_id)
Heap Fetches: 1550706
How did you load the database? pg_dump -> psql/pg_restore?
If so, did you perform a VACUUM FREEZE after the load?
Regards, Jan
--
Jan Wieck
Postgres User since 1994
On 2021-05-28 12:08, Andrew Dunstan wrote:
On 5/28/21 2:48 PM, Dean Gibson (DB Administrator) wrote:
[Reposted to the proper list]
...
Have you tried reproducing these results outside RDS, say on an EC2 instance running vanilla PostgreSQL?
cheers, andrew
--
Andrew Dunstan
EDB: https://www.enterprisedb.com
That is step #2 of my backup plan:
1. Create an EC2 instance running community v9.6. Once that is done
& running successfully, I'm golden for a long, long time.
2. If I am curious (& not worn out), take a snapshot of #1 & update it
to v13.
-- Dean
On 2021-05-28 12:18, Campbell, Lance wrote:
Also, did you check your RDS setting in AWS after upgrading?� I run
four databases in AWS.� I found that the work_mem was set way low
after an upgrade.� I had to tweak many of my settings.Lance
I've wondered a lot about work_mem.� The default setting (which I've
tried) involves a formula, so I have no idea what the actual value is.�
Since I have a db.t2.micro (now db.t3.micro) instance with only 1GB of
RAM, I've tried a value of 8000. No difference.
pá 28. 5. 2021 v 21:39 odesílatel Ryan Bair <ryandbair@gmail.com> napsal:
The problem is the plan. The planner massively underestimated the number
of rows arising from the _EN/_AM join.Usually postgres is pretty good about running ANALYZE as needed, but it
might be a good idea to run it manually to rule that out as a potential
culprit.
yes
the very strange is pretty high planning time
Planning Time: 173.753 ms
This is unusually high number - maybe the server has bad CPU or maybe some
indexes bloating
Regards
Pavel
Show quoted text
On Fri, May 28, 2021 at 3:19 PM Campbell, Lance <lance@illinois.edu> wrote:
Also, did you check your RDS setting in AWS after upgrading? I run four
databases in AWS. I found that the work_mem was set way low after an
upgrade. I had to tweak many of my settings.Lance
*From: *Andrew Dunstan <andrew@dunslane.net>
*Date: *Friday, May 28, 2021 at 2:08 PM
*To: *Dean Gibson (DB Administrator) <postgresql@mailpen.com>,
pgsql-performance@lists.postgresql.org <
pgsql-performance@lists.postgresql.org>
*Subject: *Re: AWS forcing PG upgrade from v9.6 a disasterOn 5/28/21 2:48 PM, Dean Gibson (DB Administrator) wrote:
[Reposted to the proper list]
I started to use PostgreSQL v7.3 in 2003 on my home Linux systems (4
at one point), gradually moving to v9.0 w/ replication in 2010. In
2017 I moved my 20GB database to AWS/RDS, gradually upgrading to v9.6,
& was entirely satisfied with the result.In March of this year, AWS announced that v9.6 was nearing end of
support, & AWS would forcibly upgrade everyone to v12 on January 22,
2022, if users did not perform the upgrade earlier. My first attempt
was successful as far as the upgrade itself, but complex queries that
normally ran in a couple of seconds on v9.x, were taking minutes in v12.I didn't have the time in March to diagnose the problem, other than
some futile adjustments to server parameters, so I reverted back to a
saved copy of my v9.6 data.On Sunday, being retired, I decided to attempt to solve the issue in
earnest. I have now spent five days (about 14 hours a day), trying
various things, including adding additional indexes. Keeping the v9.6
data online for web users, I've "forked" the data into new copies, &
updated them in turn to PostgreSQL v10, v11, v12, & v13. All exhibit
the same problem: As you will see below, it appears that versions 10
& above are doing a sequential scan of some of the "large" (200K rows)
tables. Note that the expected & actual run times both differ for
v9.6 & v13.2, by more than *two orders of magnitude*. Rather than post
a huge eMail (ha ha), I'll start with this one, that shows an "EXPLAIN
ANALYZE" from both v9.6 & v13.2, followed by the related table & view
definitions. With one exception, table definitions are from the FCC
(Federal Communications Commission); the view definitions are my own.Have you tried reproducing these results outside RDS, say on an EC2
instance running vanilla PostgreSQL?cheers
andrew
--
Andrew Dunstan
EDB:
https://urldefense.com/v3/__https://www.enterprisedb.com__;!!DZ3fjg!tiFTfkNeARuU_vwxOHZfrJvVXj8kYMPJqa1tO5Fnv75UbERS8ZAmUoNFl_g2EVyL$
<https://urldefense.com/v3/__https:/www.enterprisedb.com__;!!DZ3fjg!tiFTfkNeARuU_vwxOHZfrJvVXj8kYMPJqa1tO5Fnv75UbERS8ZAmUoNFl_g2EVyL$>
On 5/28/21 4:23 PM, Jan Wieck wrote:
On 5/28/21 2:48 PM, Dean Gibson (DB Administrator) wrote:
What sticks out for me are these two scans, which balloon from 50-60
heap fetches to 1.5M each.-> Nested Loop (cost=0.29..0.68 rows=1
width=7) (actual time=0.003..0.004 rows=1 loops=1487153)
Join Filter: ("_IsoCountry".iso_alpha2 =
"_Territory".country_id)
Rows Removed by Join Filter: 0
-> Index Only Scan using
"_IsoCountry_iso_alpha2_key" on "_IsoCountry" (cost=0.14..0.38
rows=1 width=3) (actual time=0.001..0.002 rows=1 loops=1487153)
Index Cond: (iso_alpha2 =
"_GovtRegion".country_id)
Heap Fetches: 1487153
-> Index Only Scan using
"_Territory_pkey" on "_Territory" (cost=0.14..0.29 rows=1 width=7)
(actual time=0.001..0.001 rows=1 loops=1487153)
Index Cond: (territory_id =
"_GovtRegion".territory_id)
Heap Fetches: 1550706How did you load the database? pg_dump -> psql/pg_restore?
If so, did you perform a VACUUM FREEZE after the load?
Jan
AIUI he did an RDS upgrade. Surely that's not doing a dump/restore? I
assume you would know better than him or me what it actually does do :-)
cheers
andrew
--
Andrew Dunstan
EDB: https://www.enterprisedb.com
On Fri, May 28, 2021 at 05:15:33PM -0400, Andrew Dunstan wrote:
How did you load the database? pg_dump -> psql/pg_restore?
If so, did you perform a VACUUM FREEZE after the load?
Jan
AIUI he did an RDS upgrade. Surely that's not doing a dump/restore? I
assume you would know better than him or me what it actually does do :-)
I think it uses pg_upgrade.
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
If only the physical world exists, free will is an illusion.
I recently did 20 upgrades from 9.6 to 12.4 and 12.5. No issues and the upgrade process uses pg_upgrade. I don’t know if AWS modified it though.
Bob
Sent from my PDP11
Show quoted text
On May 28, 2021, at 5:15 PM, Andrew Dunstan <andrew@dunslane.net> wrote:
On 5/28/21 4:23 PM, Jan Wieck wrote:
On 5/28/21 2:48 PM, Dean Gibson (DB Administrator) wrote:What sticks out for me are these two scans, which balloon from 50-60
heap fetches to 1.5M each.-> Nested Loop (cost=0.29..0.68 rows=1
width=7) (actual time=0.003..0.004 rows=1 loops=1487153)
Join Filter: ("_IsoCountry".iso_alpha2 =
"_Territory".country_id)
Rows Removed by Join Filter: 0
-> Index Only Scan using
"_IsoCountry_iso_alpha2_key" on "_IsoCountry" (cost=0.14..0.38
rows=1 width=3) (actual time=0.001..0.002 rows=1 loops=1487153)
Index Cond: (iso_alpha2 =
"_GovtRegion".country_id)
Heap Fetches: 1487153
-> Index Only Scan using
"_Territory_pkey" on "_Territory" (cost=0.14..0.29 rows=1 width=7)
(actual time=0.001..0.001 rows=1 loops=1487153)
Index Cond: (territory_id =
"_GovtRegion".territory_id)
Heap Fetches: 1550706How did you load the database? pg_dump -> psql/pg_restore?
If so, did you perform a VACUUM FREEZE after the load?
Jan
AIUI he did an RDS upgrade. Surely that's not doing a dump/restore? I
assume you would know better than him or me what it actually does do :-)cheers
andrew
--
Andrew Dunstan
EDB: https://www.enterprisedb.com
On 2021-05-28 13:23, Jan Wieck wrote:
On 5/28/21 2:48 PM, Dean Gibson (DB Administrator) wrote:
What sticks out for me are these two scans, which balloon from 50-60
heap fetches to 1.5M each.-> Nested Loop (cost=0.29..0.68 rows=1
width=7) (actual time=0.003..0.004 rows=1 loops=1487153)
Join Filter: ("_IsoCountry".iso_alpha2 =
"_Territory".country_id)
Rows Removed by Join Filter: 0
-> Index Only Scan using
"_IsoCountry_iso_alpha2_key" on "_IsoCountry" (cost=0.14..0.38
rows=1 width=3) (actual time=0.001..0.002 rows=1 loops=1487153)
Index Cond: (iso_alpha2 =
"_GovtRegion".country_id)
Heap Fetches: 1487153
-> Index Only Scan using
"_Territory_pkey" on "_Territory" (cost=0.14..0.29 rows=1 width=7)
(actual time=0.001..0.001 rows=1 loops=1487153)
Index Cond: (territory_id =
"_GovtRegion".territory_id)
Heap Fetches: 1550706How did you load the database? pg_dump -> psql/pg_restore?
If so, did you perform a VACUUM FREEZE after the load?
Regards, Jan
It was RDS's "upgrade in place". According to the PostgreSQL site, for
v9.4 & v12: /"Aggressive freezing is always performed when the table is
rewritten, so this option is redundant when //|FULL|//is specified."/
I did a VACUUM FULL.
On Fri, May 28, 2021, 17:15 Andrew Dunstan <andrew@dunslane.net> wrote:
On 5/28/21 4:23 PM, Jan Wieck wrote:
On 5/28/21 2:48 PM, Dean Gibson (DB Administrator) wrote:
What sticks out for me are these two scans, which balloon from 50-60
heap fetches to 1.5M each.-> Nested Loop (cost=0.29..0.68 rows=1
width=7) (actual time=0.003..0.004 rows=1 loops=1487153)
Join Filter: ("_IsoCountry".iso_alpha2 =
"_Territory".country_id)
Rows Removed by Join Filter: 0
-> Index Only Scan using
"_IsoCountry_iso_alpha2_key" on "_IsoCountry" (cost=0.14..0.38
rows=1 width=3) (actual time=0.001..0.002 rows=1 loops=1487153)
Index Cond: (iso_alpha2 =
"_GovtRegion".country_id)
Heap Fetches: 1487153
-> Index Only Scan using
"_Territory_pkey" on "_Territory" (cost=0.14..0.29 rows=1 width=7)
(actual time=0.001..0.001 rows=1 loops=1487153)
Index Cond: (territory_id =
"_GovtRegion".territory_id)
Heap Fetches: 1550706How did you load the database? pg_dump -> psql/pg_restore?
If so, did you perform a VACUUM FREEZE after the load?
Jan
AIUI he did an RDS upgrade. Surely that's not doing a dump/restore? I
assume you would know better than him or me what it actually does do :-)
Since I am not working at AWS I can't tell for sure. ;)
It used to perform a binary pgupgrade. But that also has issues with xids
and freezing. So I would throw a cluster wide vac-freeze in there for good
measure, Sir.
Best Regards, Jan
Show quoted text
cheers
andrew
--
Andrew Dunstan
EDB: https://www.enterprisedb.com
On 5/28/21 10:27 PM, Jan Wieck wrote:
On Fri, May 28, 2021, 17:15 Andrew Dunstan <andrew@dunslane.net
<mailto:andrew@dunslane.net>> wrote:AIUI he did an RDS upgrade. Surely that's not doing a dump/restore? I
assume you would know better than him or me what it actually does
do :-)Since I am not working at AWS I can't tell for sure. ;)
Apologies, my mistake then.
cheers
andrew
--
Andrew Dunstan
EDB: https://www.enterprisedb.com
On May 28, 2021, at 14:30, Bruce Momjian <bruce@momjian.us> wrote:
I think it uses pg_upgrade.
It does. It does not, however, do the vacuum analyze step afterwards. A VACUUM (FULL, ANALYZE) should take care of that, and I believe the OP said he had done that after the pg_upgrade.
The most common reason for this kind of inexplicable stuff after an RDS upgrade is, as others have said, parameter changes, since you get a new default parameter group after the upgrade.
That being said, this does look like something happened to the planner to cause it to pick a worse plan in v13. The deeply nested views make it kind of hard to pin down, but the core issue appears to be in the "good" plan, it evaluates the _Club.club_count > 5 relatively early, which greatly limits the number of rows that it handles elsewhere in the query. Why the plan change, I can't say.
It might be worth creating a materialized CTE that grabs the "club_count > 5" set and uses that, instead of having it at the top level predicates.
On 2021-05-28 19:43, Christophe Pettus wrote:
...
The most common reason for this kind of inexplicable stuff after an RDS upgrade is, as others have said, parameter changes, since you get a new default parameter group after the upgrade.That being said, this does look like something happened to the planner to cause it to pick a worse plan in v13. The deeply nested views make it kind of hard to pin down, but the core issue appears to be in the "good" plan, it evaluates the _Club.club_count > 5 relatively early, which greatly limits the number of rows that it handles elsewhere in the query. Why the plan change, I can't say.
It might be worth creating a materialized CTE that grabs the "club_count > 5" set and uses that, instead of having it at the top level predicates.
I spent quite a bit of time over the past five days experimenting with
various parameter values, to no avail, but I don't mind trying some more.
I have other queries that fail even more spectacularly, & they all seem
to involve a generated table like the "club" one in my example. I have
an idea that I might try, in effectively changing the order of
evaluation. I'll have to think about that. Thanks for the suggestion!
However, one "shouldn't" have to tinker with the order of stuff in SQL;
that's one of the beauties of the language: the "compiler" (planner) is
supposed to figure that all out. And for me, that's been true for the
past 15 years with PostgreSQL.
Note that this problem is not unique to v13. It happened with upgrades
to v10, 11, &12. So, some fundamental change was made back then (at
least in the RDS version). Since I need a bulletproof backup past next
January, I think my next task will be to get an EC2 instance running
v9.6, where AWS can't try to upgrade it. Then, at my leisure, I can
fiddle with upgrading.
On 05/29/21 07:08, Dean Gibson (DB Administrator) wrote:
On 2021-05-28 19:43, Christophe Pettus wrote:
...
The most common reason for this kind of inexplicable stuff after an RDS upgrade is, as others have said, parameter changes, since you get a new default parameter group after the upgrade.That being said, this does look like something happened to the planner to cause it to pick a worse plan in v13. The deeply nested views make it kind of hard to pin down, but the core issue appears to be in the "good" plan, it evaluates the _Club.club_count > 5 relatively early, which greatly limits the number of rows that it handles elsewhere in the query. Why the plan change, I can't say.
It might be worth creating a materialized CTE that grabs the "club_count > 5" set and uses that, instead of having it at the top level predicates.
I spent quite a bit of time over the past five days experimenting with
various parameter values, to no avail, but I don't mind trying some more.I have other queries that fail even more spectacularly, & they all
seem to involve a generated table like the "club" one in my example.
I have an idea that I might try, in effectively changing the order of
evaluation. I'll have to think about that. Thanks for the
suggestion! However, one "shouldn't" have to tinker with the order of
stuff in SQL; that's one of the beauties of the language: the
"compiler" (planner) is supposed to figure that all out. And for me,
that's been true for the past 15 years with PostgreSQL.Note that this problem is not unique to v13. It happened with
upgrades to v10, 11, &12. So, some fundamental change was made back
then (at least in the RDS version). Since I need a bulletproof backup
past next January, I think my next task will be to get an EC2 instance
running v9.6, where AWS can't try to upgrade it. Then, at my leisure,
I can fiddle with upgrading.
BTW what is the planner reason to not use index in v13.2? Is index in
corrupted state? Have you try to reindex index
"FccLookup"."_LicStatus_pkey" ?
1.5M of seqscan's are looking really bad.
SubPlan 2
-> Limit (cost=0.15..8.17 rows=1 width=32)
(actual time=0.006..0.007 rows=1 loops=55)
-> *Index Scan using "_LicStatus_pkey" on
"_LicStatus"* (cost=0.15..8.17 rows=1 width=32) (actual
time=0.005..0.005 rows=1 loops=55)
Index Cond: ("_HD".license_status =
status_id)
SubPlan 2
-> Limit (cost=0.00..1.07 rows=1 width=13)
(actual time=0.001..0.001 rows=1 loops=1487153)
-> *Seq Scan on "_LicStatus"*
(cost=0.00..1.07 rows=1 width=13) (actual time=0.000..0.000 rows=1
loops=1487153)
Filter: ("_HD".license_status =
status_id)
Rows Removed by Filter: 1
On Fri, May 28, 2021, 22:41 Andrew Dunstan <andrew@dunslane.net> wrote:
On 5/28/21 10:27 PM, Jan Wieck wrote:
On Fri, May 28, 2021, 17:15 Andrew Dunstan <andrew@dunslane.net
<mailto:andrew@dunslane.net>> wrote:AIUI he did an RDS upgrade. Surely that's not doing a dump/restore? I
assume you would know better than him or me what it actually does
do :-)Since I am not working at AWS I can't tell for sure. ;)
Apologies, my mistake then.
No need to apologize, you were correct two months ago.
Best Regards, Jan
Show quoted text
cheers
andrew
--
Andrew Dunstan
EDB: https://www.enterprisedb.com
