AWS forcing PG upgrade from v9.6 a disaster
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. Keeping the v9.6 data online for web users, I've
"forked" the data into a new copy, & updated it 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 for v9.6 & v13.2 both differ 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/27/21 8:41 PM, Dean Gibson (DB Administrator) wrote:
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.
Did you run a plain
ANALYZE(https://www.postgresql.org/docs/12/sql-analyze.html) on the
tables in the new install?
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. Keeping the v9.6 data online for web users, I've
"forked" the data into a new copy, & updated it 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 for v9.6 & v13.2 both differ 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.
--
Adrian Klaver
adrian.klaver@aklaver.com
On 2021-05-28 08:12, Adrian Klaver wrote:
On 5/27/21 8:41 PM, Dean Gibson (DB Administrator) wrote:
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.Did you run a plain
ANALYZE(https://www.postgresql.org/docs/12/sql-analyze.html) on the
tables in the new install?
After each upgrade (to 10, 11, 12, & 13), I did a "VACUUM FULL
ANALYZE". On 10 through 12, it took about 45 minutes & significant CPU
activity, & temporarily doubled the size of the disk space required. As
you know, that disk space is not shrinkable under AWS's RDS. On v13, it
took 10 hours with limited CPU activity, & actually slightly less disk
space required.
On 5/28/21 1:40 PM, Dean Gibson (DB Administrator) wrote:
On 2021-05-28 08:12, Adrian Klaver wrote:
On 5/27/21 8:41 PM, Dean Gibson (DB Administrator) wrote:
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.Did you run a plain
ANALYZE(https://www.postgresql.org/docs/12/sql-analyze.html) on the
tables in the new install?After each upgrade (to 10, 11, 12, & 13), I did a "VACUUM FULL ANALYZE".
On 10 through 12, it took about 45 minutes & significant CPU activity, &
temporarily doubled the size of the disk space required. As you know,
that disk space is not shrinkable under AWS's RDS. On v13, it took 10
hours with limited CPU activity, & actually slightly less disk space
required.
Under normal conditions, VACUUM FULL is pointless on a freshly-loaded
database; in RDS, it's *anti-useful*.
That's why Adrian asked if you did a plain ANALYZE.
--
Angular momentum makes the world go 'round.
On 2021-05-28 12:38, Ron wrote:
On 5/28/21 1:40 PM, Dean Gibson (DB Administrator) wrote:
On 2021-05-28 08:12, Adrian Klaver wrote:
On 5/27/21 8:41 PM, Dean Gibson (DB Administrator) wrote:
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.Did you run a plain
ANALYZE(https://www.postgresql.org/docs/12/sql-analyze.html) on the
tables in the new install?After each upgrade (to 10, 11, 12, & 13), I did a "VACUUM FULL
ANALYZE". On 10 through 12, it took about 45 minutes & significant
CPU activity, & temporarily doubled the size of the disk space
required. As you know, that disk space is not shrinkable under AWS's
RDS. On v13, it took 10 hours with limited CPU activity, & actually
slightly less disk space required.Under normal conditions, VACUUM FULL is pointless on a freshly-loaded
database; in RDS, it's *anti-useful*.That's why Adrian asked if you did a plain ANALYZE.
Just now did. No change in EXPLAIN ANALYZE output.
On 5/28/21 5:06 PM, Dean Gibson (DB Administrator) wrote:
On 2021-05-28 12:38, Ron wrote:
On 5/28/21 1:40 PM, Dean Gibson (DB Administrator) wrote:
On 2021-05-28 08:12, Adrian Klaver wrote:
On 5/27/21 8:41 PM, Dean Gibson (DB Administrator) wrote:
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.Did you run a plain
ANALYZE(https://www.postgresql.org/docs/12/sql-analyze.html) on the
tables in the new install?After each upgrade (to 10, 11, 12, & 13), I did a "VACUUM FULL
ANALYZE". On 10 through 12, it took about 45 minutes & significant CPU
activity, & temporarily doubled the size of the disk space required. As
you know, that disk space is not shrinkable under AWS's RDS. On v13, it
took 10 hours with limited CPU activity, & actually slightly less disk
space required.Under normal conditions, VACUUM FULL is pointless on a freshly-loaded
database; in RDS, it's *anti-useful*.That's why Adrian asked if you did a plain ANALYZE.
Just now did. No change in EXPLAIN ANALYZE output.
Did it run in less than 10 hours?
--
Angular momentum makes the world go 'round.
On 2021-05-28 16:51, Ron wrote:
On 5/28/21 5:06 PM, Dean Gibson (DB Administrator) wrote:
On 2021-05-28 12:38, Ron wrote:
On 5/28/21 1:40 PM, Dean Gibson (DB Administrator) wrote:
On 2021-05-28 08:12, Adrian Klaver wrote:
On 5/27/21 8:41 PM, Dean Gibson (DB Administrator) wrote:
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.Did you run a plain
ANALYZE(https://www.postgresql.org/docs/12/sql-analyze.html) on
the tables in the new install?After each upgrade (to 10, 11, 12, & 13), I did a "VACUUM FULL
ANALYZE". On 10 through 12, it took about 45 minutes & significant
CPU activity, & temporarily doubled the size of the disk space
required. As you know, that disk space is not shrinkable under
AWS's RDS. On v13, it took 10 hours with limited CPU activity, &
actually slightly less disk space required.Under normal conditions, VACUUM FULL is pointless on a
freshly-loaded database; in RDS, it's *anti-useful*.That's why Adrian asked if you did a plain ANALYZE.
Just now did. No change in EXPLAIN ANALYZE output.
Did it run in less than 10 hours?
The original VACUUM FULL ANALYZE ran in 10 hours. The plain ANALYZE ran
in 88 seconds.
Le 29/05/2021 à 02:38, Dean Gibson (DB Administrator) a écrit :
On 2021-05-28 16:51, Ron wrote:
On 5/28/21 5:06 PM, Dean Gibson (DB Administrator) wrote:
On 2021-05-28 12:38, Ron wrote:
On 5/28/21 1:40 PM, Dean Gibson (DB Administrator) wrote:
On 2021-05-28 08:12, Adrian Klaver wrote:
On 5/27/21 8:41 PM, Dean Gibson (DB Administrator) wrote:
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.Did you run a plain
ANALYZE(https://www.postgresql.org/docs/12/sql-analyze.html) on
the tables in the new install?After each upgrade (to 10, 11, 12, & 13), I did a "VACUUM FULL
ANALYZE". On 10 through 12, it took about 45 minutes &
significant CPU activity, & temporarily doubled the size of the
disk space required. As you know, that disk space is not
shrinkable under AWS's RDS. On v13, it took 10 hours with limited
CPU activity, & actually slightly less disk space required.Under normal conditions, VACUUM FULL is pointless on a
freshly-loaded database; in RDS, it's *anti-useful*.That's why Adrian asked if you did a plain ANALYZE.
Just now did. No change in EXPLAIN ANALYZE output.
Did it run in less than 10 hours?
The original VACUUM FULL ANALYZE ran in 10 hours. The plain ANALYZE
ran in 88 seconds.
One possibility is that your data has a distribution that defeats the
ANALYZE sampling strategy.
If that is the case you can force ANALYZE to do a better job by
increasing the default_statistics_target value (100 by default) and
reload the configuration. This will sample more data from your table
which should help the planner find out what the value distribution looks
like for a column and why using an index for conditions involving it is
a better solution.
The last time I had to use this setting to solve this kind of problem I
ended with :
default_statistics_target = 500
But obviously the value suited to your case could be different (I'd
increase it until the planner uses the correct index). Note that
increasing it increases the costs of maintaining statistics (so you
don't want to increase this by several orders of magnitude blindly) but
the default value seems fairly conservative to me.
For reference and more fine-tuned settings using per table statistics
configuration and multi-column statistics for complex situations, see :
- https://www.postgresql.org/docs/13/runtime-config-query.html
- https://www.postgresql.org/docs/13/planner-stats.html
--
Lionel Bouton
gérant de JTEK SARL
https://www.linkedin.com/in/lionelbouton/
On 5/28/21 5:38 PM, Dean Gibson (DB Administrator) wrote:
Did it run in less than 10 hours?
The original VACUUM FULL ANALYZE ran in 10 hours. The plain ANALYZE ran
in 88 seconds.
Can you repeat your EXPLAIN (ANALYZE, BUFFERS) of the query from your
first post and post them here:
Other information:
1) A diff of your configuration settings between 9.6 and 13.2.
2) Are you running on the same AWS instance type for the two versions of
Postgres?
It is not necessary to repeat the table/view definitions as they are
available in the first post.
--
Adrian Klaver
adrian.klaver@aklaver.com
On 2021-05-29 09:25, Adrian Klaver wrote:
On 5/28/21 5:38 PM, Dean Gibson (DB Administrator) wrote:
Can you repeat your EXPLAIN (ANALYZE, BUFFERS) of the query from your
first post and post them here:Other information:
1) A diff of your configuration settings between 9.6 and 13.2.2) Are you running on the same AWS instance type for the two versions
of Postgres?It is not necessary to repeat the table/view definitions as they are
available in the first post.
Done.
1.There's probably about a hundred, but almost all are differences in
the default values. The most interesting (from my point of view) is my
setting work_mem in 8000 on v9.6, & 16000 (after 8000 didn't help) on
v13. Doing a compare right now between the DEFAULT parameters for 9.6 &
13, RDS reports 93 differences in the default parameters between the two.
2. For v13, I moved from db.t2.micro to db.t3.micro, because RDS
required that for v13. However, for the v10, 11, 12 upgrades, I kept
db.t2.micro.
Meanwhile, I've been doing some checking. If I remove "CAST(
license_status AS CHAR ) = 'A'", the problem disappears. Changing the
JOIN to a RIGHT JOIN, & replacing WHERE with ON, also "solves" the
problem, but there is an extra row where license_status is NULL, due to
the RIGHT JOIN. Currently trying to figure that out (why did the CAST
... match 'A', if it is null?)...
On 5/29/21 3:59 PM, Dean Gibson (DB Administrator) wrote:
Meanwhile, I've been doing some checking. If I remove "CAST(
license_status AS CHAR ) = 'A'", the problem disappears. Changing the
JOIN to a RIGHT JOIN, & replacing WHERE with ON, also "solves" the
problem, but there is an extra row where license_status is NULL, due
to the RIGHT JOIN. Currently trying to figure that out (why did the
CAST ... match 'A', if it is null?)...
Why are you using this expression? It's something you almost never want
to do in my experience. Why not use the substr() function to get the
first character?
cheers
andrew
--
Andrew Dunstan
EDB: https://www.enterprisedb.com
On Sat, May 29, 2021, 4:40 AM Lionel Bouton <lionel.bouton@jtek.fr> wrote:
The last time I had to use this setting to solve this kind of problem I
ended with :default_statistics_target = 500
But obviously the value suited to your case could be different (I'd
increase it until the planner uses the correct index). Note that increasing
it increases the costs of maintaining statistics (so you don't want to
increase this by several orders of magnitude blindly) but the default value
seems fairly conservative to me.
It also increases planning time since those distribution statistics need to
be consumed and decisions have to be made.
I tried 500, to no avail. Since each change involves a delay as RDS
readjusts, I'm going down a different path at the moment.
Show quoted text
On 2021-05-29 03:40, Lionel Bouton wrote:
Le 29/05/2021 à 02:38, Dean Gibson (DB Administrator) a écrit :
The original VACUUM FULL ANALYZE ran in 10 hours. The plain ANALYZE
ran in 88 seconds.One possibility is that your data has a distribution that defeats the
ANALYZE sampling strategy.If that is the case you can force ANALYZE to do a better job by
increasing the default_statistics_target value (100 by default) and
reload the configuration. This will sample more data from your table
which should help the planner find out what the value distribution
looks like for a column and why using an index for conditions
involving it is a better solution.
The last time I had to use this setting to solve this kind of problem
I ended with :default_statistics_target = 500
But obviously the value suited to your case could be different (I'd
increase it until the planner uses the correct index). Note that
increasing it increases the costs of maintaining statistics (so you
don't want to increase this by several orders of magnitude blindly)
but the default value seems fairly conservative to me.For reference and more fine-tuned settings using per table statistics
configuration and multi-column statistics for complex situations, see :
- https://www.postgresql.org/docs/13/runtime-config-query.html
- https://www.postgresql.org/docs/13/planner-stats.html--
Lionel Bouton
gérant de JTEK SARL
https://www.linkedin.com/in/lionelbouton/
On 2021-05-29 13:35, Andrew Dunstan wrote:
On 5/29/21 3:59 PM, Dean Gibson (DB Administrator) wrote:
Meanwhile, I've been doing some checking. If I remove "CAST(
license_status AS CHAR ) = 'A'", the problem disappears. Changing the
JOIN to a RIGHT JOIN, & replacing WHERE with ON, also "solves" the
problem, but there is an extra row where license_status is NULL, due
to the RIGHT JOIN. Currently trying to figure that out (why did the
CAST ... match 'A', if it is null?)...Why are you using this expression? It's something you almost never want
to do in my experience. Why not use the substr() function to get the
first character?cheers
andrew
--
Andrew Dunstan
EDB: https://www.enterprisedb.com
Although it doesn't matter in this case, I do it because in general, it
changes the type of the value from CHAR to bptext or whatever it is, &
that has causes comparison issues in the past. It's just a matter of
habit for me when working with CHAR() types.
But this case, where it doesn't matter, I'd use LEFT().
On 6/6/21 7:49 PM, Dean Gibson (DB Administrator) wrote:
On 2021-05-29 13:35, Andrew Dunstan wrote:
On 5/29/21 3:59 PM, Dean Gibson (DB Administrator) wrote:
Meanwhile, I've been doing some checking. If I remove "CAST(
license_status AS CHAR ) = 'A'", the problem disappears. Changing the
JOIN to a RIGHT JOIN, & replacing WHERE with ON, also "solves" the
problem, but there is an extra row where license_status is NULL, due
to the RIGHT JOIN. Currently trying to figure that out (why did the
CAST ... match 'A', if it is null?)...Why are you using this expression? It's something you almost never want
to do in my experience. Why not use the substr() function to get the
first character?Although it doesn't matter in this case, I do it because in general,
it changes the type of the value from CHAR to bptext or whatever it
is, & that has causes comparison issues in the past. It's just a
matter of habit for me when working with CHAR() types.But this case, where it doesn't matter, I'd use LEFT().
That raises the issue of why you're using CHAR(n) fields. Just about
every consultant I know advises simply avoiding them. :-)
cheers
andrew
--
Andrew Dunstan
EDB: https://www.enterprisedb.com
On 2021-06-07 04:52, Andrew Dunstan wrote:
On 6/6/21 7:49 PM, Dean Gibson (DB Administrator) wrote:
On 2021-05-29 13:35, Andrew Dunstan wrote:
On 5/29/21 3:59 PM, Dean Gibson (DB Administrator) wrote:
... If I remove "CAST( license_status AS CHAR ) = 'A'", ...
Why are you using this expression? It's something you almost never want to do in my experience. Why not use the substr() function to get the
first character?Although it doesn't matter in this case, I do it because in general, it changes the type of the value from CHAR to bptext or whatever it is, & that has caused comparison issues in the past. It's just a matter of habit for me when working with CHAR() types.
But this case, where it doesn't matter, I'd use LEFT().
That raises the issue of why you're using CHAR(n) fields. Just about every consultant I know advises simply avoiding them. :-)
cheers, andrew
--
Andrew Dunstan
EDB: https://www.enterprisedb.com
As I mentioned earlier, both the data & the table definitions come from
the FCC, the latter in the form of text files containing their formal
SQL definitions. These often change (like two weeks ago). There are 18
tables currently of interest to me, with between 30 & 60 fields in each
table. Further, the entire data set is replaced every Sunday, with
daily updates during the week. About 1/6th of the text fields are
defined as VARCHAR; the rest are CHAR. All of the text fields that are
used as indexes, are CHAR.
Being mindful of the fact that trailing blanks are significant in CHAR
fields, I find it easier to keep the original FCC table definitions, &
remap them to VIEWs containing the fields I am interested in. I've been
doing this with the FCC data for over 15 years, starting with PostgreSQL
7.3.
As far as needing a consultant in DB design, the FCC is planning a new
DB architecture "soon", & they sorely need one. When they export the
data to the public (delimited by "|"), they don't escape some characters
like "|", "\", & <cr>. That makes it fun ...
-- Dean