pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedAWS forcing PG upgrade from v9.6 a disaster
54+ messages / 20 participants
[nested] [flat]
* AWS forcing PG upgrade from v9.6 a disaster
@ 2021-05-28 03:41 Dean Gibson (DB Administrator) <postgresql@ultimeth.com>
2021-05-28 15:12 ` Re: AWS forcing PG upgrade from v9.6 a disaster Adrian Klaver <adrian.klaver@aklaver.com>
0 siblings, 1 reply; 54+ messages in thread
From: Dean Gibson (DB Administrator) @ 2021-05-28 03:41 UTC (permalink / raw)
To: pgsql-general <pgsql-general@postgresql.org>
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)
^ permalink raw reply [nested|flat] 54+ messages in thread
* Re: AWS forcing PG upgrade from v9.6 a disaster
2021-05-28 03:41 AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@ultimeth.com>
@ 2021-05-28 15:12 ` Adrian Klaver <adrian.klaver@aklaver.com>
2021-05-28 18:40 ` Re: AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
0 siblings, 1 reply; 54+ messages in thread
From: Adrian Klaver @ 2021-05-28 15:12 UTC (permalink / raw)
To: Dean Gibson (DB Administrator) <postgresql@ultimeth.com>; pgsql-general <pgsql-general@postgresql.org>
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
^ permalink raw reply [nested|flat] 54+ messages in thread
* Re: AWS forcing PG upgrade from v9.6 a disaster
2021-05-28 03:41 AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@ultimeth.com>
2021-05-28 15:12 ` Re: AWS forcing PG upgrade from v9.6 a disaster Adrian Klaver <adrian.klaver@aklaver.com>
@ 2021-05-28 18:40 ` Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-05-28 19:38 ` Re: AWS forcing PG upgrade from v9.6 a disaster Ron <ronljohnsonjr@gmail.com>
0 siblings, 1 reply; 54+ messages in thread
From: Dean Gibson (DB Administrator) @ 2021-05-28 18:40 UTC (permalink / raw)
To: pgsql-general <pgsql-general@postgresql.org>
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.
^ permalink raw reply [nested|flat] 54+ messages in thread
* Re: AWS forcing PG upgrade from v9.6 a disaster
2021-05-28 03:41 AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@ultimeth.com>
2021-05-28 15:12 ` Re: AWS forcing PG upgrade from v9.6 a disaster Adrian Klaver <adrian.klaver@aklaver.com>
2021-05-28 18:40 ` Re: AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
@ 2021-05-28 19:38 ` Ron <ronljohnsonjr@gmail.com>
2021-05-28 22:06 ` Re: AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
0 siblings, 1 reply; 54+ messages in thread
From: Ron @ 2021-05-28 19:38 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 54+ messages in thread
* Re: AWS forcing PG upgrade from v9.6 a disaster
2021-05-28 03:41 AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@ultimeth.com>
2021-05-28 15:12 ` Re: AWS forcing PG upgrade from v9.6 a disaster Adrian Klaver <adrian.klaver@aklaver.com>
2021-05-28 18:40 ` Re: AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-05-28 19:38 ` Re: AWS forcing PG upgrade from v9.6 a disaster Ron <ronljohnsonjr@gmail.com>
@ 2021-05-28 22:06 ` Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-05-28 23:51 ` Re: AWS forcing PG upgrade from v9.6 a disaster Ron <ronljohnsonjr@gmail.com>
0 siblings, 1 reply; 54+ messages in thread
From: Dean Gibson (DB Administrator) @ 2021-05-28 22:06 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 54+ messages in thread
* Re: AWS forcing PG upgrade from v9.6 a disaster
2021-05-28 03:41 AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@ultimeth.com>
2021-05-28 15:12 ` Re: AWS forcing PG upgrade from v9.6 a disaster Adrian Klaver <adrian.klaver@aklaver.com>
2021-05-28 18:40 ` Re: AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-05-28 19:38 ` Re: AWS forcing PG upgrade from v9.6 a disaster Ron <ronljohnsonjr@gmail.com>
2021-05-28 22:06 ` Re: AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
@ 2021-05-28 23:51 ` Ron <ronljohnsonjr@gmail.com>
2021-05-29 00:38 ` Re: AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
0 siblings, 1 reply; 54+ messages in thread
From: Ron @ 2021-05-28 23:51 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 54+ messages in thread
* Re: AWS forcing PG upgrade from v9.6 a disaster
2021-05-28 03:41 AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@ultimeth.com>
2021-05-28 15:12 ` Re: AWS forcing PG upgrade from v9.6 a disaster Adrian Klaver <adrian.klaver@aklaver.com>
2021-05-28 18:40 ` Re: AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-05-28 19:38 ` Re: AWS forcing PG upgrade from v9.6 a disaster Ron <ronljohnsonjr@gmail.com>
2021-05-28 22:06 ` Re: AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-05-28 23:51 ` Re: AWS forcing PG upgrade from v9.6 a disaster Ron <ronljohnsonjr@gmail.com>
@ 2021-05-29 00:38 ` Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-05-29 10:40 ` Re: AWS forcing PG upgrade from v9.6 a disaster Lionel Bouton <lionel.bouton@jtek.fr>
2021-05-29 16:25 ` Re: AWS forcing PG upgrade from v9.6 a disaster Adrian Klaver <adrian.klaver@aklaver.com>
0 siblings, 2 replies; 54+ messages in thread
From: Dean Gibson (DB Administrator) @ 2021-05-29 00:38 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 54+ messages in thread
* Re: AWS forcing PG upgrade from v9.6 a disaster
2021-05-28 03:41 AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@ultimeth.com>
2021-05-28 15:12 ` Re: AWS forcing PG upgrade from v9.6 a disaster Adrian Klaver <adrian.klaver@aklaver.com>
2021-05-28 18:40 ` Re: AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-05-28 19:38 ` Re: AWS forcing PG upgrade from v9.6 a disaster Ron <ronljohnsonjr@gmail.com>
2021-05-28 22:06 ` Re: AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-05-28 23:51 ` Re: AWS forcing PG upgrade from v9.6 a disaster Ron <ronljohnsonjr@gmail.com>
2021-05-29 00:38 ` Re: AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
@ 2021-05-29 10:40 ` Lionel Bouton <lionel.bouton@jtek.fr>
2021-05-29 22:34 ` Re: AWS forcing PG upgrade from v9.6 a disaster Michael Lewis <mlewis@entrata.com>
2021-05-29 22:47 ` Re: AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
1 sibling, 2 replies; 54+ messages in thread
From: Lionel Bouton @ 2021-05-29 10:40 UTC (permalink / raw)
To: pgsql-general <pgsql-general@lists.postgresql.org>
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/
^ permalink raw reply [nested|flat] 54+ messages in thread
* Re: AWS forcing PG upgrade from v9.6 a disaster
2021-05-28 03:41 AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@ultimeth.com>
2021-05-28 15:12 ` Re: AWS forcing PG upgrade from v9.6 a disaster Adrian Klaver <adrian.klaver@aklaver.com>
2021-05-28 18:40 ` Re: AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-05-28 19:38 ` Re: AWS forcing PG upgrade from v9.6 a disaster Ron <ronljohnsonjr@gmail.com>
2021-05-28 22:06 ` Re: AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-05-28 23:51 ` Re: AWS forcing PG upgrade from v9.6 a disaster Ron <ronljohnsonjr@gmail.com>
2021-05-29 00:38 ` Re: AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-05-29 10:40 ` Re: AWS forcing PG upgrade from v9.6 a disaster Lionel Bouton <lionel.bouton@jtek.fr>
@ 2021-05-29 22:34 ` Michael Lewis <mlewis@entrata.com>
1 sibling, 0 replies; 54+ messages in thread
From: Michael Lewis @ 2021-05-29 22:34 UTC (permalink / raw)
To: Lionel Bouton <lionel.bouton@jtek.fr>; +Cc: pgsql-general <pgsql-general@lists.postgresql.org>
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.
^ permalink raw reply [nested|flat] 54+ messages in thread
* Re: AWS forcing PG upgrade from v9.6 a disaster
2021-05-28 03:41 AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@ultimeth.com>
2021-05-28 15:12 ` Re: AWS forcing PG upgrade from v9.6 a disaster Adrian Klaver <adrian.klaver@aklaver.com>
2021-05-28 18:40 ` Re: AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-05-28 19:38 ` Re: AWS forcing PG upgrade from v9.6 a disaster Ron <ronljohnsonjr@gmail.com>
2021-05-28 22:06 ` Re: AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-05-28 23:51 ` Re: AWS forcing PG upgrade from v9.6 a disaster Ron <ronljohnsonjr@gmail.com>
2021-05-29 00:38 ` Re: AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-05-29 10:40 ` Re: AWS forcing PG upgrade from v9.6 a disaster Lionel Bouton <lionel.bouton@jtek.fr>
@ 2021-05-29 22:47 ` Dean Gibson (DB Administrator) <postgresql@mailpen.com>
1 sibling, 0 replies; 54+ messages in thread
From: Dean Gibson (DB Administrator) @ 2021-05-29 22:47 UTC (permalink / raw)
To: pgsql-performance
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.
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/
^ permalink raw reply [nested|flat] 54+ messages in thread
* Re: AWS forcing PG upgrade from v9.6 a disaster
2021-05-28 03:41 AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@ultimeth.com>
2021-05-28 15:12 ` Re: AWS forcing PG upgrade from v9.6 a disaster Adrian Klaver <adrian.klaver@aklaver.com>
2021-05-28 18:40 ` Re: AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-05-28 19:38 ` Re: AWS forcing PG upgrade from v9.6 a disaster Ron <ronljohnsonjr@gmail.com>
2021-05-28 22:06 ` Re: AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-05-28 23:51 ` Re: AWS forcing PG upgrade from v9.6 a disaster Ron <ronljohnsonjr@gmail.com>
2021-05-29 00:38 ` Re: AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
@ 2021-05-29 16:25 ` Adrian Klaver <adrian.klaver@aklaver.com>
2021-05-29 19:59 ` Re: AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
1 sibling, 1 reply; 54+ messages in thread
From: Adrian Klaver @ 2021-05-29 16:25 UTC (permalink / raw)
To: Dean Gibson (DB Administrator) <postgresql@mailpen.com>; pgsql-general@lists.postgresql.org
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:
https://explain.depesz.com/
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
^ permalink raw reply [nested|flat] 54+ messages in thread
* Re: AWS forcing PG upgrade from v9.6 a disaster
2021-05-28 03:41 AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@ultimeth.com>
2021-05-28 15:12 ` Re: AWS forcing PG upgrade from v9.6 a disaster Adrian Klaver <adrian.klaver@aklaver.com>
2021-05-28 18:40 ` Re: AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-05-28 19:38 ` Re: AWS forcing PG upgrade from v9.6 a disaster Ron <ronljohnsonjr@gmail.com>
2021-05-28 22:06 ` Re: AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-05-28 23:51 ` Re: AWS forcing PG upgrade from v9.6 a disaster Ron <ronljohnsonjr@gmail.com>
2021-05-29 00:38 ` Re: AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-05-29 16:25 ` Re: AWS forcing PG upgrade from v9.6 a disaster Adrian Klaver <adrian.klaver@aklaver.com>
@ 2021-05-29 19:59 ` Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-05-29 20:35 ` Re: AWS forcing PG upgrade from v9.6 a disaster Andrew Dunstan <andrew@dunslane.net>
0 siblings, 1 reply; 54+ messages in thread
From: Dean Gibson (DB Administrator) @ 2021-05-29 19:59 UTC (permalink / raw)
To: pgsql-performance
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:
>
> https://explain.depesz.com/
>
> 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?)...
^ permalink raw reply [nested|flat] 54+ messages in thread
* Re: AWS forcing PG upgrade from v9.6 a disaster
2021-05-28 03:41 AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@ultimeth.com>
2021-05-28 15:12 ` Re: AWS forcing PG upgrade from v9.6 a disaster Adrian Klaver <adrian.klaver@aklaver.com>
2021-05-28 18:40 ` Re: AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-05-28 19:38 ` Re: AWS forcing PG upgrade from v9.6 a disaster Ron <ronljohnsonjr@gmail.com>
2021-05-28 22:06 ` Re: AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-05-28 23:51 ` Re: AWS forcing PG upgrade from v9.6 a disaster Ron <ronljohnsonjr@gmail.com>
2021-05-29 00:38 ` Re: AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-05-29 16:25 ` Re: AWS forcing PG upgrade from v9.6 a disaster Adrian Klaver <adrian.klaver@aklaver.com>
2021-05-29 19:59 ` Re: AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
@ 2021-05-29 20:35 ` Andrew Dunstan <andrew@dunslane.net>
2021-06-06 23:49 ` Re: AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
0 siblings, 1 reply; 54+ messages in thread
From: Andrew Dunstan @ 2021-05-29 20:35 UTC (permalink / raw)
To: Dean Gibson (DB Administrator) <postgresql@mailpen.com>; pgsql-performance
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
^ permalink raw reply [nested|flat] 54+ messages in thread
* Re: AWS forcing PG upgrade from v9.6 a disaster
2021-05-28 03:41 AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@ultimeth.com>
2021-05-28 15:12 ` Re: AWS forcing PG upgrade from v9.6 a disaster Adrian Klaver <adrian.klaver@aklaver.com>
2021-05-28 18:40 ` Re: AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-05-28 19:38 ` Re: AWS forcing PG upgrade from v9.6 a disaster Ron <ronljohnsonjr@gmail.com>
2021-05-28 22:06 ` Re: AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-05-28 23:51 ` Re: AWS forcing PG upgrade from v9.6 a disaster Ron <ronljohnsonjr@gmail.com>
2021-05-29 00:38 ` Re: AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-05-29 16:25 ` Re: AWS forcing PG upgrade from v9.6 a disaster Adrian Klaver <adrian.klaver@aklaver.com>
2021-05-29 19:59 ` Re: AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-05-29 20:35 ` Re: AWS forcing PG upgrade from v9.6 a disaster Andrew Dunstan <andrew@dunslane.net>
@ 2021-06-06 23:49 ` Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-06-07 11:52 ` Re: AWS forcing PG upgrade from v9.6 a disaster Andrew Dunstan <andrew@dunslane.net>
0 siblings, 1 reply; 54+ messages in thread
From: Dean Gibson (DB Administrator) @ 2021-06-06 23:49 UTC (permalink / raw)
To: pgsql-performance
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().
^ permalink raw reply [nested|flat] 54+ messages in thread
* Re: AWS forcing PG upgrade from v9.6 a disaster
2021-05-28 03:41 AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@ultimeth.com>
2021-05-28 15:12 ` Re: AWS forcing PG upgrade from v9.6 a disaster Adrian Klaver <adrian.klaver@aklaver.com>
2021-05-28 18:40 ` Re: AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-05-28 19:38 ` Re: AWS forcing PG upgrade from v9.6 a disaster Ron <ronljohnsonjr@gmail.com>
2021-05-28 22:06 ` Re: AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-05-28 23:51 ` Re: AWS forcing PG upgrade from v9.6 a disaster Ron <ronljohnsonjr@gmail.com>
2021-05-29 00:38 ` Re: AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-05-29 16:25 ` Re: AWS forcing PG upgrade from v9.6 a disaster Adrian Klaver <adrian.klaver@aklaver.com>
2021-05-29 19:59 ` Re: AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-05-29 20:35 ` Re: AWS forcing PG upgrade from v9.6 a disaster Andrew Dunstan <andrew@dunslane.net>
2021-06-06 23:49 ` Re: AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
@ 2021-06-07 11:52 ` Andrew Dunstan <andrew@dunslane.net>
2021-06-07 17:27 ` Re: AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
0 siblings, 1 reply; 54+ messages in thread
From: Andrew Dunstan @ 2021-06-07 11:52 UTC (permalink / raw)
To: Dean Gibson (DB Administrator) <postgresql@mailpen.com>; pgsql-performance
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
^ permalink raw reply [nested|flat] 54+ messages in thread
* Re: AWS forcing PG upgrade from v9.6 a disaster
2021-05-28 03:41 AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@ultimeth.com>
2021-05-28 15:12 ` Re: AWS forcing PG upgrade from v9.6 a disaster Adrian Klaver <adrian.klaver@aklaver.com>
2021-05-28 18:40 ` Re: AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-05-28 19:38 ` Re: AWS forcing PG upgrade from v9.6 a disaster Ron <ronljohnsonjr@gmail.com>
2021-05-28 22:06 ` Re: AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-05-28 23:51 ` Re: AWS forcing PG upgrade from v9.6 a disaster Ron <ronljohnsonjr@gmail.com>
2021-05-29 00:38 ` Re: AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-05-29 16:25 ` Re: AWS forcing PG upgrade from v9.6 a disaster Adrian Klaver <adrian.klaver@aklaver.com>
2021-05-29 19:59 ` Re: AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-05-29 20:35 ` Re: AWS forcing PG upgrade from v9.6 a disaster Andrew Dunstan <andrew@dunslane.net>
2021-06-06 23:49 ` Re: AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-06-07 11:52 ` Re: AWS forcing PG upgrade from v9.6 a disaster Andrew Dunstan <andrew@dunslane.net>
@ 2021-06-07 17:27 ` Dean Gibson (DB Administrator) <postgresql@mailpen.com>
0 siblings, 0 replies; 54+ messages in thread
From: Dean Gibson (DB Administrator) @ 2021-06-07 17:27 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 54+ messages in thread
* AWS forcing PG upgrade from v9.6 a disaster
@ 2021-05-28 18:48 Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-05-28 19:08 ` Re: AWS forcing PG upgrade from v9.6 a disaster Andrew Dunstan <andrew@dunslane.net>
2021-05-28 20:23 ` Re: AWS forcing PG upgrade from v9.6 a disaster Jan Wieck <jan@wi3ck.info>
2021-05-31 03:07 ` Re: AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
0 siblings, 3 replies; 54+ messages in thread
From: Dean Gibson (DB Administrator) @ 2021-05-28 18:48 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
[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)
^ permalink raw reply [nested|flat] 54+ messages in thread
* Re: AWS forcing PG upgrade from v9.6 a disaster
2021-05-28 18:48 AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
@ 2021-05-28 19:08 ` Andrew Dunstan <andrew@dunslane.net>
2021-05-28 19:18 ` Re: AWS forcing PG upgrade from v9.6 a disaster Campbell, Lance <lance@illinois.edu>
2021-05-28 20:33 ` Re: AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2 siblings, 2 replies; 54+ messages in thread
From: Andrew Dunstan @ 2021-05-28 19:08 UTC (permalink / raw)
To: Dean Gibson (DB Administrator) <postgresql@mailpen.com>; pgsql-performance@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 54+ messages in thread
* Re: AWS forcing PG upgrade from v9.6 a disaster
2021-05-28 18:48 AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-05-28 19:08 ` Re: AWS forcing PG upgrade from v9.6 a disaster Andrew Dunstan <andrew@dunslane.net>
@ 2021-05-28 19:18 ` Campbell, Lance <lance@illinois.edu>
2021-05-28 19:38 ` Re: AWS forcing PG upgrade from v9.6 a disaster MichaelDBA <MichaelDBA@sqlexec.com>
2021-05-28 19:39 ` Re: AWS forcing PG upgrade from v9.6 a disaster Ryan Bair <ryandbair@gmail.com>
2021-05-28 20:37 ` Re: AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
1 sibling, 3 replies; 54+ messages in thread
From: Campbell, Lance @ 2021-05-28 19:18 UTC (permalink / raw)
To: Andrew Dunstan <andrew@dunslane.net>; Dean Gibson (DB Administrator) <postgresql@mailpen.com>; pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
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
--
Andrew Dunstan
EDB: https://urldefense.com/v3/__https://www.enterprisedb.com__;!!DZ3fjg!tiFTfkNeARuU_vwxOHZfrJvVXj8kYMPJ...;
^ permalink raw reply [nested|flat] 54+ messages in thread
* Re: AWS forcing PG upgrade from v9.6 a disaster
2021-05-28 18:48 AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-05-28 19:08 ` Re: AWS forcing PG upgrade from v9.6 a disaster Andrew Dunstan <andrew@dunslane.net>
2021-05-28 19:18 ` Re: AWS forcing PG upgrade from v9.6 a disaster Campbell, Lance <lance@illinois.edu>
@ 2021-05-28 19:38 ` MichaelDBA <MichaelDBA@sqlexec.com>
2 siblings, 0 replies; 54+ messages in thread
From: MichaelDBA @ 2021-05-28 19:38 UTC (permalink / raw)
To: Campbell, Lance <lance@illinois.edu>; +Cc: Andrew Dunstan <andrew@dunslane.net>; Dean Gibson (DB Administrator) <postgresql@mailpen.com>; pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
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:
>
> 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
>
>
>
> --
> Andrew Dunstan
> EDB:
> https://urldefense.com/v3/__https://www.enterprisedb.com__;!!DZ3fjg!tiFTfkNeARuU_vwxOHZfrJvVXj8kYMPJ...
> <https://urldefense.com/v3/__https:/www.enterprisedb.com__;%21%21DZ3fjg%21tiFTfkNeARuU_vwxOHZfrJvVXj8...;
>
>
>
^ permalink raw reply [nested|flat] 54+ messages in thread
* Re: AWS forcing PG upgrade from v9.6 a disaster
2021-05-28 18:48 AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-05-28 19:08 ` Re: AWS forcing PG upgrade from v9.6 a disaster Andrew Dunstan <andrew@dunslane.net>
2021-05-28 19:18 ` Re: AWS forcing PG upgrade from v9.6 a disaster Campbell, Lance <lance@illinois.edu>
@ 2021-05-28 19:39 ` Ryan Bair <ryandbair@gmail.com>
2021-05-28 20:11 ` Re: AWS forcing PG upgrade from v9.6 a disaster Michael Lewis <mlewis@entrata.com>
2021-05-28 20:38 ` Re: AWS forcing PG upgrade from v9.6 a disaster Pavel Stehule <pavel.stehule@gmail.com>
2 siblings, 2 replies; 54+ messages in thread
From: Ryan Bair @ 2021-05-28 19:39 UTC (permalink / raw)
To: Dean Gibson (DB Administrator) <postgresql@mailpen.com>; +Cc: Andrew Dunstan <andrew@dunslane.net>; pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>; lance@illinois.edu
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:
> 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
>
>
>
> --
> Andrew Dunstan
> EDB:
> https://urldefense.com/v3/__https://www.enterprisedb.com__;!!DZ3fjg!tiFTfkNeARuU_vwxOHZfrJvVXj8kYMPJ...
> <https://urldefense.com/v3/__https:/www.enterprisedb.com__;!!DZ3fjg!tiFTfkNeARuU_vwxOHZfrJvVXj8kYMPJq...;
>
>
>
^ permalink raw reply [nested|flat] 54+ messages in thread
* Re: AWS forcing PG upgrade from v9.6 a disaster
2021-05-28 18:48 AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-05-28 19:08 ` Re: AWS forcing PG upgrade from v9.6 a disaster Andrew Dunstan <andrew@dunslane.net>
2021-05-28 19:18 ` Re: AWS forcing PG upgrade from v9.6 a disaster Campbell, Lance <lance@illinois.edu>
2021-05-28 19:39 ` Re: AWS forcing PG upgrade from v9.6 a disaster Ryan Bair <ryandbair@gmail.com>
@ 2021-05-28 20:11 ` Michael Lewis <mlewis@entrata.com>
1 sibling, 0 replies; 54+ messages in thread
From: Michael Lewis @ 2021-05-28 20:11 UTC (permalink / raw)
To: Ryan Bair <ryandbair@gmail.com>; +Cc: Dean Gibson (DB Administrator) <postgresql@mailpen.com>; Andrew Dunstan <andrew@dunslane.net>; Pgsql Performance <pgsql-performance@lists.postgresql.org>; lance@illinois.edu
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.
^ permalink raw reply [nested|flat] 54+ messages in thread
* Re: AWS forcing PG upgrade from v9.6 a disaster
2021-05-28 18:48 AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-05-28 19:08 ` Re: AWS forcing PG upgrade from v9.6 a disaster Andrew Dunstan <andrew@dunslane.net>
2021-05-28 19:18 ` Re: AWS forcing PG upgrade from v9.6 a disaster Campbell, Lance <lance@illinois.edu>
2021-05-28 19:39 ` Re: AWS forcing PG upgrade from v9.6 a disaster Ryan Bair <ryandbair@gmail.com>
@ 2021-05-28 20:38 ` Pavel Stehule <pavel.stehule@gmail.com>
1 sibling, 0 replies; 54+ messages in thread
From: Pavel Stehule @ 2021-05-28 20:38 UTC (permalink / raw)
To: Ryan Bair <ryandbair@gmail.com>; +Cc: Dean Gibson (DB Administrator) <postgresql@mailpen.com>; Andrew Dunstan <andrew@dunslane.net>; pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>; lance@illinois.edu
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
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 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
>>
>>
>>
>> --
>> Andrew Dunstan
>> EDB:
>> https://urldefense.com/v3/__https://www.enterprisedb.com__;!!DZ3fjg!tiFTfkNeARuU_vwxOHZfrJvVXj8kYMPJ...
>> <https://urldefense.com/v3/__https:/www.enterprisedb.com__;!!DZ3fjg!tiFTfkNeARuU_vwxOHZfrJvVXj8kYMPJq...;
>>
>>
>>
^ permalink raw reply [nested|flat] 54+ messages in thread
* Re: AWS forcing PG upgrade from v9.6 a disaster
2021-05-28 18:48 AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-05-28 19:08 ` Re: AWS forcing PG upgrade from v9.6 a disaster Andrew Dunstan <andrew@dunslane.net>
2021-05-28 19:18 ` Re: AWS forcing PG upgrade from v9.6 a disaster Campbell, Lance <lance@illinois.edu>
@ 2021-05-28 20:37 ` Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2 siblings, 0 replies; 54+ messages in thread
From: Dean Gibson (DB Administrator) @ 2021-05-28 20:37 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
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.
^ permalink raw reply [nested|flat] 54+ messages in thread
* Re: AWS forcing PG upgrade from v9.6 a disaster
2021-05-28 18:48 AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-05-28 19:08 ` Re: AWS forcing PG upgrade from v9.6 a disaster Andrew Dunstan <andrew@dunslane.net>
@ 2021-05-28 20:33 ` Dean Gibson (DB Administrator) <postgresql@mailpen.com>
1 sibling, 0 replies; 54+ messages in thread
From: Dean Gibson (DB Administrator) @ 2021-05-28 20:33 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 54+ messages in thread
* Re: AWS forcing PG upgrade from v9.6 a disaster
2021-05-28 18:48 AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
@ 2021-05-28 20:23 ` Jan Wieck <jan@wi3ck.info>
2021-05-28 21:15 ` Re: AWS forcing PG upgrade from v9.6 a disaster Andrew Dunstan <andrew@dunslane.net>
2021-05-28 22:13 ` Re: AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2 siblings, 2 replies; 54+ messages in thread
From: Jan Wieck @ 2021-05-28 20:23 UTC (permalink / raw)
To: Dean Gibson (DB Administrator) <postgresql@mailpen.com>; pgsql-performance@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 54+ messages in thread
* Re: AWS forcing PG upgrade from v9.6 a disaster
2021-05-28 18:48 AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-05-28 20:23 ` Re: AWS forcing PG upgrade from v9.6 a disaster Jan Wieck <jan@wi3ck.info>
@ 2021-05-28 21:15 ` Andrew Dunstan <andrew@dunslane.net>
2021-05-28 21:30 ` Re: AWS forcing PG upgrade from v9.6 a disaster Bruce Momjian <bruce@momjian.us>
2021-05-28 22:09 ` Re: AWS forcing PG upgrade from v9.6 a disaster Bob Lunney <bob_lunney@yahoo.com>
2021-05-29 02:27 ` Re: AWS forcing PG upgrade from v9.6 a disaster Jan Wieck <jan@wi3ck.info>
1 sibling, 3 replies; 54+ messages in thread
From: Andrew Dunstan @ 2021-05-28 21:15 UTC (permalink / raw)
To: Jan Wieck <jan@wi3ck.info>; Dean Gibson (DB Administrator) <postgresql@mailpen.com>; pgsql-performance@lists.postgresql.org
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: 1550706
>
> 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 :-)
cheers
andrew
--
Andrew Dunstan
EDB: https://www.enterprisedb.com
^ permalink raw reply [nested|flat] 54+ messages in thread
* Re: AWS forcing PG upgrade from v9.6 a disaster
2021-05-28 18:48 AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-05-28 20:23 ` Re: AWS forcing PG upgrade from v9.6 a disaster Jan Wieck <jan@wi3ck.info>
2021-05-28 21:15 ` Re: AWS forcing PG upgrade from v9.6 a disaster Andrew Dunstan <andrew@dunslane.net>
@ 2021-05-28 21:30 ` Bruce Momjian <bruce@momjian.us>
2021-05-29 02:43 ` Re: AWS forcing PG upgrade from v9.6 a disaster Christophe Pettus <xof@thebuild.com>
2 siblings, 1 reply; 54+ messages in thread
From: Bruce Momjian @ 2021-05-28 21:30 UTC (permalink / raw)
To: Andrew Dunstan <andrew@dunslane.net>; +Cc: Jan Wieck <jan@wi3ck.info>; Dean Gibson (DB Administrator) <postgresql@mailpen.com>; pgsql-performance@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 54+ messages in thread
* Re: AWS forcing PG upgrade from v9.6 a disaster
2021-05-28 18:48 AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-05-28 20:23 ` Re: AWS forcing PG upgrade from v9.6 a disaster Jan Wieck <jan@wi3ck.info>
2021-05-28 21:15 ` Re: AWS forcing PG upgrade from v9.6 a disaster Andrew Dunstan <andrew@dunslane.net>
2021-05-28 21:30 ` Re: AWS forcing PG upgrade from v9.6 a disaster Bruce Momjian <bruce@momjian.us>
@ 2021-05-29 02:43 ` Christophe Pettus <xof@thebuild.com>
2021-05-29 04:08 ` Re: AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
0 siblings, 1 reply; 54+ messages in thread
From: Christophe Pettus @ 2021-05-29 02:43 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org; +Cc: Andrew Dunstan <andrew@dunslane.net>; Jan Wieck <jan@wi3ck.info>; Dean Gibson (DB Administrator) <postgresql@mailpen.com>; Bruce Momjian <bruce@momjian.us>
> 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.
^ permalink raw reply [nested|flat] 54+ messages in thread
* Re: AWS forcing PG upgrade from v9.6 a disaster
2021-05-28 18:48 AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-05-28 20:23 ` Re: AWS forcing PG upgrade from v9.6 a disaster Jan Wieck <jan@wi3ck.info>
2021-05-28 21:15 ` Re: AWS forcing PG upgrade from v9.6 a disaster Andrew Dunstan <andrew@dunslane.net>
2021-05-28 21:30 ` Re: AWS forcing PG upgrade from v9.6 a disaster Bruce Momjian <bruce@momjian.us>
2021-05-29 02:43 ` Re: AWS forcing PG upgrade from v9.6 a disaster Christophe Pettus <xof@thebuild.com>
@ 2021-05-29 04:08 ` Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-05-29 05:24 ` Re: AWS forcing PG upgrade from v9.6 a disaster Alexey M Boltenkov <padrebolt@yandex.ru>
0 siblings, 1 reply; 54+ messages in thread
From: Dean Gibson (DB Administrator) @ 2021-05-29 04:08 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 54+ messages in thread
* Re: AWS forcing PG upgrade from v9.6 a disaster
2021-05-28 18:48 AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-05-28 20:23 ` Re: AWS forcing PG upgrade from v9.6 a disaster Jan Wieck <jan@wi3ck.info>
2021-05-28 21:15 ` Re: AWS forcing PG upgrade from v9.6 a disaster Andrew Dunstan <andrew@dunslane.net>
2021-05-28 21:30 ` Re: AWS forcing PG upgrade from v9.6 a disaster Bruce Momjian <bruce@momjian.us>
2021-05-29 02:43 ` Re: AWS forcing PG upgrade from v9.6 a disaster Christophe Pettus <xof@thebuild.com>
2021-05-29 04:08 ` Re: AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
@ 2021-05-29 05:24 ` Alexey M Boltenkov <padrebolt@yandex.ru>
2021-05-29 20:17 ` Re: AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
0 siblings, 1 reply; 54+ messages in thread
From: Alexey M Boltenkov @ 2021-05-29 05:24 UTC (permalink / raw)
To: Dean Gibson (DB Administrator) <postgresql@mailpen.com>; pgsql-performance@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 54+ messages in thread
* Re: AWS forcing PG upgrade from v9.6 a disaster
2021-05-28 18:48 AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-05-28 20:23 ` Re: AWS forcing PG upgrade from v9.6 a disaster Jan Wieck <jan@wi3ck.info>
2021-05-28 21:15 ` Re: AWS forcing PG upgrade from v9.6 a disaster Andrew Dunstan <andrew@dunslane.net>
2021-05-28 21:30 ` Re: AWS forcing PG upgrade from v9.6 a disaster Bruce Momjian <bruce@momjian.us>
2021-05-29 02:43 ` Re: AWS forcing PG upgrade from v9.6 a disaster Christophe Pettus <xof@thebuild.com>
2021-05-29 04:08 ` Re: AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-05-29 05:24 ` Re: AWS forcing PG upgrade from v9.6 a disaster Alexey M Boltenkov <padrebolt@yandex.ru>
@ 2021-05-29 20:17 ` Dean Gibson (DB Administrator) <postgresql@mailpen.com>
0 siblings, 0 replies; 54+ messages in thread
From: Dean Gibson (DB Administrator) @ 2021-05-29 20:17 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
On 2021-05-28 22:24, Alexey M Boltenkov wrote:
> On 05/29/21 07:08, Dean Gibson (DB Administrator) wrote: [deleted]
>
> 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
>
Doing your REINDEX didn't help. Now in the process of reindexing the
entire database. When that's done, I'll let you know if there is any
improvement.
^ permalink raw reply [nested|flat] 54+ messages in thread
* Re: AWS forcing PG upgrade from v9.6 a disaster
2021-05-28 18:48 AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-05-28 20:23 ` Re: AWS forcing PG upgrade from v9.6 a disaster Jan Wieck <jan@wi3ck.info>
2021-05-28 21:15 ` Re: AWS forcing PG upgrade from v9.6 a disaster Andrew Dunstan <andrew@dunslane.net>
@ 2021-05-28 22:09 ` Bob Lunney <bob_lunney@yahoo.com>
2 siblings, 0 replies; 54+ messages in thread
From: Bob Lunney @ 2021-05-28 22:09 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org; +Cc: Jan Wieck <jan@wi3ck.info>; Dean Gibson (DB Administrator) <postgresql@mailpen.com>; Andrew Dunstan <andrew@dunslane.net>
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
> 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: 1550706
>>
>> 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 :-)
>
>
> cheers
>
>
> andrew
>
>
> --
> Andrew Dunstan
> EDB: https://www.enterprisedb.com
>
>
>
^ permalink raw reply [nested|flat] 54+ messages in thread
* Re: AWS forcing PG upgrade from v9.6 a disaster
2021-05-28 18:48 AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-05-28 20:23 ` Re: AWS forcing PG upgrade from v9.6 a disaster Jan Wieck <jan@wi3ck.info>
2021-05-28 21:15 ` Re: AWS forcing PG upgrade from v9.6 a disaster Andrew Dunstan <andrew@dunslane.net>
@ 2021-05-29 02:27 ` Jan Wieck <jan@wi3ck.info>
2021-05-29 02:41 ` Re: AWS forcing PG upgrade from v9.6 a disaster Andrew Dunstan <andrew@dunslane.net>
2 siblings, 1 reply; 54+ messages in thread
From: Jan Wieck @ 2021-05-29 02:27 UTC (permalink / raw)
To: Andrew Dunstan <andrew@dunslane.net>; +Cc: Dean Gibson (DB Administrator) <postgresql@mailpen.com>; pgsql-performance@lists.postgresql.org
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: 1550706
> >
> > 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 :-)
>
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
>
> cheers
>
>
> andrew
>
>
> --
> Andrew Dunstan
> EDB: https://www.enterprisedb.com
>
>
^ permalink raw reply [nested|flat] 54+ messages in thread
* Re: AWS forcing PG upgrade from v9.6 a disaster
2021-05-28 18:48 AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-05-28 20:23 ` Re: AWS forcing PG upgrade from v9.6 a disaster Jan Wieck <jan@wi3ck.info>
2021-05-28 21:15 ` Re: AWS forcing PG upgrade from v9.6 a disaster Andrew Dunstan <andrew@dunslane.net>
2021-05-29 02:27 ` Re: AWS forcing PG upgrade from v9.6 a disaster Jan Wieck <jan@wi3ck.info>
@ 2021-05-29 02:41 ` Andrew Dunstan <andrew@dunslane.net>
2021-05-29 11:39 ` Re: AWS forcing PG upgrade from v9.6 a disaster Jan Wieck <jan@wi3ck.info>
0 siblings, 1 reply; 54+ messages in thread
From: Andrew Dunstan @ 2021-05-29 02:41 UTC (permalink / raw)
To: Jan Wieck <jan@wi3ck.info>; +Cc: Dean Gibson (DB Administrator) <postgresql@mailpen.com>; pgsql-performance@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 54+ messages in thread
* Re: AWS forcing PG upgrade from v9.6 a disaster
2021-05-28 18:48 AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-05-28 20:23 ` Re: AWS forcing PG upgrade from v9.6 a disaster Jan Wieck <jan@wi3ck.info>
2021-05-28 21:15 ` Re: AWS forcing PG upgrade from v9.6 a disaster Andrew Dunstan <andrew@dunslane.net>
2021-05-29 02:27 ` Re: AWS forcing PG upgrade from v9.6 a disaster Jan Wieck <jan@wi3ck.info>
2021-05-29 02:41 ` Re: AWS forcing PG upgrade from v9.6 a disaster Andrew Dunstan <andrew@dunslane.net>
@ 2021-05-29 11:39 ` Jan Wieck <jan@wi3ck.info>
0 siblings, 0 replies; 54+ messages in thread
From: Jan Wieck @ 2021-05-29 11:39 UTC (permalink / raw)
To: Andrew Dunstan <andrew@dunslane.net>; +Cc: Dean Gibson (DB Administrator) <postgresql@mailpen.com>; pgsql-performance@lists.postgresql.org
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
>
> cheers
>
>
> andrew
>
> --
> Andrew Dunstan
> EDB: https://www.enterprisedb.com
>
>
^ permalink raw reply [nested|flat] 54+ messages in thread
* Re: AWS forcing PG upgrade from v9.6 a disaster
2021-05-28 18:48 AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-05-28 20:23 ` Re: AWS forcing PG upgrade from v9.6 a disaster Jan Wieck <jan@wi3ck.info>
@ 2021-05-28 22:13 ` Dean Gibson (DB Administrator) <postgresql@mailpen.com>
1 sibling, 0 replies; 54+ messages in thread
From: Dean Gibson (DB Administrator) @ 2021-05-28 22:13 UTC (permalink / raw)
To: Jan Wieck <jan@wi3ck.info>; pgsql-performance@lists.postgresql.org
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: 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
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.
^ permalink raw reply [nested|flat] 54+ messages in thread
* Re: AWS forcing PG upgrade from v9.6 a disaster
2021-05-28 18:48 AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
@ 2021-05-31 03:07 ` Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-05-31 03:41 ` Re: AWS forcing PG upgrade from v9.6 a disaster Christophe Pettus <xof@thebuild.com>
2021-06-01 04:16 ` Re: AWS forcing PG upgrade from v9.6 a disaster Justin Pryzby <pryzby@telsasoft.com>
2 siblings, 2 replies; 54+ messages in thread
From: Dean Gibson (DB Administrator) @ 2021-05-31 03:07 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
*SOLVED !!!* Below is the *new* EXPLAIN ANALYZE for *13.2* on AWS RDS
(with *no changes* to server parameters) along with the prior EXPLAIN
ANALYZE outputs for easy comparison.
While I didn't discount the significance & effect of optimizing the
server parameters, this problem always seemed to me like a fundamental
difference in how the PostgreSQL planner viewed the structure of the
query. In particular, I had a usage pattern of writing VIEWS that
worked very well with v9.6 & prior versions, but which made me suspect a
route of attack:
Since the FCC tables contain lots of one-character codes for different
conditions, to simplify maintenance & displays to humans, I created over
twenty tiny lookup tables (a dozen or so entries in each table), to
render a human-readable field as a replacement for the original
one-character field in many of the VIEWs. In some cases those
"humanized" fields were used as conditions in SELECT statements. Of
course, fields that are not referenced or selected for output from a
particular query, never get looked up (an advantage over using a JOIN
for each lookup). In some cases, for ease of handling multiple or
complex lookups, I indeed used a JOIN. All this worked fine until v10.
Here's the FROM clause that bit me:
FROM lic_en
JOIN govt_region USING (territory_id, country_id)
LEFT JOIN zip_code USING (territory_id, country_id, zip5)
LEFT JOIN "County" USING (territory_id, country_id, fips_county);
The first two JOINs are not the problem, & are in fact retained in my
solution. The problem is the third JOIN, where "fips_county" from
"County" is actually matched with the corresponding field from the
"zip_code" VIEW. Works fine, if you don't mind the performance impact
in v10 & above. It has now been rewritten, to be a sub-query for an
output field. Voila ! Back to sub-second query times.
This also solved performance issues with other queries as well. I also
now use lookup values as additional fields in the output, in addition to
the original fields, which should help some more (but means some changes
to some web pages that do queries).
-- Dean
ps: I wonder how many other RDS users of v9.6 are going to get a very
rude awakening *very soon*, as AWS is not allowing new instances of v9.6
after *August 2* (see https://forums.aws.amazon.com/ann.jspa?annID=8499
). Whether that milestone affects restores from snapshots, remains to
be seen (by others, not by me). In other words, users should plan to be
up & running on a newer version well before August. Total cost to me?
I"m in my *8th day* of dealing with this, & I still have a number of web
pages to update, due to changes in SQL field names to manage this mess.
This was certainly not a obvious solution.
*Here's from 13.2 (new):*
=> 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=457.77..457.77 rows=1 width=64) (actual
time=48.737..48.742 rows=43 loops=1)
Sort Key: "_Club".extra_count DESC, "_Club".club_count DESC,
"_EN".entity_name
Sort Method: quicksort Memory: 31kB
-> Nested Loop Left Join (cost=1.57..457.76 rows=1 width=64)
(actual time=1.796..48.635 rows=43 loops=1)
-> Nested Loop (cost=1.28..457.07 rows=1 width=71) (actual
time=1.736..48.239 rows=43 loops=1)
Join Filter: (("_EN".country_id =
"_GovtRegion".country_id) AND ("_EN".state = "_GovtRegion".territory_id))
Rows Removed by Join Filter: 1297
-> Nested Loop (cost=1.28..453.75 rows=1 width=70)
(actual time=1.720..47.778 rows=43 loops=1)
Join Filter: (("_HD".unique_system_identifier =
"_EN".unique_system_identifier) AND ("_HD".callsign = "_EN".callsign))
-> Nested Loop (cost=0.85..450.98 rows=1
width=65) (actual time=1.207..34.912 rows=43 loops=1)
-> Nested Loop (cost=0.43..376.57 rows=27
width=50) (actual time=0.620..20.956 rows=43 loops=1)
-> Seq Scan on "_Club"
(cost=0.00..4.44 rows=44 width=35) (actual time=0.037..0.067 rows=44
loops=1)
Filter: (club_count >= 5)
Rows Removed by Filter: 151
-> Index Scan using "_HD_callsign" on
"_HD" (cost=0.43..8.45 rows=1 width=15) (actual time=0.474..0.474
rows=1 loops=44)
Index Cond: (callsign =
"_Club".trustee_callsign)
Filter: (license_status =
'A'::bpchar)
Rows Removed by Filter: 0
-> Index Scan using "_AM_pkey" on "_AM"
(cost=0.43..2.75 rows=1 width=15) (actual time=0.323..0.323 rows=1 loops=43)
Index Cond: (unique_system_identifier
= "_HD".unique_system_identifier)
Filter: ("_HD".callsign = callsign)
-> Index Scan using "_EN_pkey" on "_EN"
(cost=0.43..2.75 rows=1 width=60) (actual time=0.298..0.298 rows=1 loops=43)
Index Cond: (unique_system_identifier =
"_AM".unique_system_identifier)
Filter: ("_AM".callsign = callsign)
-> Seq Scan on "_GovtRegion" (cost=0.00..1.93 rows=93
width=7) (actual time=0.002..0.004 rows=31 loops=43)
-> Nested Loop (cost=0.29..0.68 rows=1 width=7) (actual
time=0.008..0.008 rows=1 loops=43)
-> Index Only Scan using "_IsoCountry_iso_alpha2_key"
on "_IsoCountry" (cost=0.14..0.38 rows=1 width=3) (actual
time=0.004..0.004 rows=1 loops=43)
Index Cond: (iso_alpha2 = "_GovtRegion".country_id)
Heap Fetches: 43
-> Index Only Scan using "_Territory_pkey" on
"_Territory" (cost=0.14..0.29 rows=1 width=7) (actual time=0.003..0.003
rows=1 loops=43)
Index Cond: ((country_id =
"_IsoCountry".iso_alpha2) AND (territory_id = "_GovtRegion".territory_id))
Heap Fetches: 43
Planning Time: 4.017 ms
Execution Time: 48.822 ms
On 2021-05-28 11:48, Dean Gibson (DB Administrator) wrote:
> ...
>
> *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)
>
^ permalink raw reply [nested|flat] 54+ messages in thread
* Re: AWS forcing PG upgrade from v9.6 a disaster
2021-05-28 18:48 AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-05-31 03:07 ` Re: AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
@ 2021-05-31 03:41 ` Christophe Pettus <xof@thebuild.com>
2021-05-31 04:23 ` Re: AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
1 sibling, 1 reply; 54+ messages in thread
From: Christophe Pettus @ 2021-05-31 03:41 UTC (permalink / raw)
To: Dean Gibson (DB Administrator) <postgresql@mailpen.com>; +Cc: pgsql-performance@lists.postgresql.org
> On May 30, 2021, at 20:07, Dean Gibson (DB Administrator) <postgresql@mailpen.com> wrote:
> The first two JOINs are not the problem, & are in fact retained in my solution. The problem is the third JOIN, where "fips_county" from "County" is actually matched with the corresponding field from the "zip_code" VIEW. Works fine, if you don't mind the performance impact in v10 & above. It has now been rewritten, to be a sub-query for an output field. Voila ! Back to sub-second query times.
If, rather than a subquery, you explicitly called out the join criteria with ON, did it have the same performance benefit?
^ permalink raw reply [nested|flat] 54+ messages in thread
* Re: AWS forcing PG upgrade from v9.6 a disaster
2021-05-28 18:48 AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-05-31 03:07 ` Re: AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-05-31 03:41 ` Re: AWS forcing PG upgrade from v9.6 a disaster Christophe Pettus <xof@thebuild.com>
@ 2021-05-31 04:23 ` Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-05-31 04:44 ` Re: AWS forcing PG upgrade from v9.6 a disaster Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 54+ messages in thread
From: Dean Gibson (DB Administrator) @ 2021-05-31 04:23 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
On 2021-05-30 20:41, Christophe Pettus wrote:
> On May 30, 2021, at 20:07, Dean Gibson (DB Administrator)
> <postgresql@mailpen.com> wrote:
>> The first two JOINs are not the problem, & are in fact retained in my solution. The problem is the third JOIN, where "fips_county" from "County" is actually matched with the corresponding field from the "zip_code" VIEW. Works fine, if you don't mind the performance impact in v10 & above. It has now been rewritten, to be a sub-query for an output field. Voila ! Back to sub-second query times.
> If, rather than a subquery, you explicitly called out the join criteria with ON, did it have the same performance benefit?
I thought that having a "USING" clause, was semantically equivalent to
an "ON" clause with the equalities explicitly stated. So no, I didn't
try that.
The matching that occurred is *exactly *what I wanted. I just didn't
want the performance impact.
^ permalink raw reply [nested|flat] 54+ messages in thread
* Re: AWS forcing PG upgrade from v9.6 a disaster
2021-05-28 18:48 AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-05-31 03:07 ` Re: AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-05-31 03:41 ` Re: AWS forcing PG upgrade from v9.6 a disaster Christophe Pettus <xof@thebuild.com>
2021-05-31 04:23 ` Re: AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
@ 2021-05-31 04:44 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-05-31 05:24 ` Re: AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
0 siblings, 1 reply; 54+ messages in thread
From: Tom Lane @ 2021-05-31 04:44 UTC (permalink / raw)
To: Dean Gibson (DB Administrator) <postgresql@mailpen.com>; +Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
"Dean Gibson (DB Administrator)" <postgresql@mailpen.com> writes:
> I thought that having a "USING" clause, was semantically equivalent to
> an "ON" clause with the equalities explicitly stated. So no, I didn't
> try that.
USING is not that, or at least not only that ... read the manual.
I'm wondering if what you saw is some side-effect of the aliasing
that USING does.
regards, tom lane
^ permalink raw reply [nested|flat] 54+ messages in thread
* Re: AWS forcing PG upgrade from v9.6 a disaster
2021-05-28 18:48 AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-05-31 03:07 ` Re: AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-05-31 03:41 ` Re: AWS forcing PG upgrade from v9.6 a disaster Christophe Pettus <xof@thebuild.com>
2021-05-31 04:23 ` Re: AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-05-31 04:44 ` Re: AWS forcing PG upgrade from v9.6 a disaster Tom Lane <tgl@sss.pgh.pa.us>
@ 2021-05-31 05:24 ` Dean Gibson (DB Administrator) <postgresql@mailpen.com>
0 siblings, 0 replies; 54+ messages in thread
From: Dean Gibson (DB Administrator) @ 2021-05-31 05:24 UTC (permalink / raw)
To: pgsql-performance
On 2021-05-30 21:44, Tom Lane wrote:
> "Dean Gibson (DB Administrator)" <postgresql@mailpen.com> writes:
>> I thought that having a "USING" clause, was semantically equivalent to
>> an "ON" clause with the equalities explicitly stated. So no, I didn't
>> try that.
> USING is not that, or at least not only that ... read the manual.
>
> I'm wondering if what you saw is some side-effect of the aliasing
> that USING does.
>
> regards, tom lane
/|USING ( /|join_column|/ [, ...] )|/
/A clause of the form //|USING ( a, b, ... )|//is shorthand for
//|ON left_table.a = right_table.a AND left_table.b =
right_table.b ...|//. Also, //|USING|//implies that only one of
each pair of equivalent columns will be included in the join
output, not both./
/
/
/The //|USING|//clause is a shorthand that allows you to take
advantage of the specific situation where both sides of the join use
the same name for the joining column(s). It takes a comma-separated
list of the shared column names and forms a join condition that
includes an equality comparison for each one. For example, joining
//|T1|//and //|T2|//with //|USING (a, b)|//produces the join
condition //|ON /|T1|/.a = /|T2|/.a AND /|T1|/.b = /|T2|/.b|//./
/Furthermore, the output of //|JOIN USING|//suppresses redundant
columns: there is no need to print both of the matched columns,
since they must have equal values. While //|JOIN ON|//produces all
columns from //|T1|//followed by all columns from //|T2|//, //|JOIN
USING|//produces one output column for each of the listed column
pairs (in the listed order), followed by any remaining columns from
//|T1|//, followed by any remaining columns from //|T2|//./
/Finally, //|NATURAL|//is a shorthand form of //|USING|//: it forms
a //|USING|//list consisting of all column names that appear in both
input tables. As with //|USING|//, these columns appear only once in
the output table. If there are no common column names, //|NATURAL
JOIN|//behaves like //|JOIN ... ON TRUE|//, producing a
cross-product join./
I get that it's like NATURAL, in that only one column is included. Is
there some other side-effect? Is the fact that I was using a LEFT JOIN,
relevant? Is what I was doing, unusual (or risky)?
^ permalink raw reply [nested|flat] 54+ messages in thread
* Re: AWS forcing PG upgrade from v9.6 a disaster
2021-05-28 18:48 AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-05-31 03:07 ` Re: AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
@ 2021-06-01 04:16 ` Justin Pryzby <pryzby@telsasoft.com>
2021-06-01 17:44 ` Re: AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
1 sibling, 1 reply; 54+ messages in thread
From: Justin Pryzby @ 2021-06-01 04:16 UTC (permalink / raw)
To: Dean Gibson (DB Administrator) <postgresql@mailpen.com>; +Cc: pgsql-performance@lists.postgresql.org
> Here's the FROM clause that bit me:
>
> FROM lic_en
> JOIN govt_region USING (territory_id, country_id)
> LEFT JOIN zip_code USING (territory_id, country_id, zip5)
> LEFT JOIN "County" USING (territory_id, country_id, fips_county);
I'm guessing that there's a dependency/correlation between
territory/country/county, and that's probably related to a misestimate causing
a bad plan.
> The first two JOINs are not the problem, & are in fact retained in my
> solution. The problem is the third JOIN, where "fips_county" from "County" is
> actually matched with the corresponding field from the "zip_code" VIEW. Works
> fine, if you don't mind the performance impact in v10 & above. It has now
> been rewritten, to be a sub-query for an output field. Voila ! Back to
> sub-second query times.
What version of 9.6.X were you upgrading *from* ?
v9.6 added selectivity estimates based on FKs, so it's not surprising if there
was a plan change migrating *to* v9.6.
...but there were a number of fixes to that, and it seems possible the plans
changed between 9.6.0 and 9.6.22, and anything backpatched to 9.X would also be
in v10+. So you might've gotten the bad plan on 9.6.22, also.
I found these commits that might be relevant.
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=1f184426b
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=7fa93eec4
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=770671062
ad1c36b07 wasn't backpatched and probably not relevant to your issue.
--
Justin
^ permalink raw reply [nested|flat] 54+ messages in thread
* Re: AWS forcing PG upgrade from v9.6 a disaster
2021-05-28 18:48 AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-05-31 03:07 ` Re: AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-06-01 04:16 ` Re: AWS forcing PG upgrade from v9.6 a disaster Justin Pryzby <pryzby@telsasoft.com>
@ 2021-06-01 17:44 ` Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-06-10 01:50 ` Re: AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
0 siblings, 1 reply; 54+ messages in thread
From: Dean Gibson (DB Administrator) @ 2021-06-01 17:44 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
On 2021-05-31 21:16, Justin Pryzby wrote:
>> Here's the FROM clause that bit me:
>>
>> FROM lic_en
>> JOIN govt_region USING (territory_id, country_id)
>> LEFT JOIN zip_code USING (territory_id, country_id, zip5)
>> LEFT JOIN "County" USING (territory_id, country_id, fips_county);
> I'm guessing that there's a dependency/correlation between territory/country/county, and that's probably related to a misestimate causing a bad plan.
>
>> The first two JOINs are not the problem, & are in fact retained in my solution. The problem is the third JOIN, where "fips_county" from "County" is actually matched with the corresponding field from the "zip_code" VIEW. Works fine, if you don't mind the performance impact in v10 & above. It has now been rewritten, to be a sub-query for an output field. Voila ! Back to sub-second query times.
> What version of 9.6.X were you upgrading *from* ?
>
> v9.6 added selectivity estimates based on FKs, so it's not surprising if there was a plan change migrating *to* v9.6.
I originally upgraded from 9.6.20 to v12.6. When that (otherwise
successful) upgrade had performance problems, I upgraded the v9.6.20
copy to v9.6.21, & tried again, with the same result.
Interestingly, on v13.2 I have now run into another (similar)
performance issue. I've solved it by setting the following to values I
used with v9.x:
join_collapse_limit & from_collapse_limit = 16
geqo_threshold = 32
I pretty sure I tried those settings (on v10 & above) with the earlier
performance problem, to no avail. However, I now wonder what would have
been the result if I have doubled those values before re-architecting
some of my tables (moving from certain JOINs to specific sub-selects).
^ permalink raw reply [nested|flat] 54+ messages in thread
* Re: AWS forcing PG upgrade from v9.6 a disaster
2021-05-28 18:48 AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-05-31 03:07 ` Re: AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-06-01 04:16 ` Re: AWS forcing PG upgrade from v9.6 a disaster Justin Pryzby <pryzby@telsasoft.com>
2021-06-01 17:44 ` Re: AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
@ 2021-06-10 01:50 ` Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-06-10 10:29 ` Re: AWS forcing PG upgrade from v9.6 a disaster Andrew Dunstan <andrew@dunslane.net>
2021-06-10 14:36 ` Re: AWS forcing PG upgrade from v9.6 a disaster Joshua Drake <jd@commandprompt.com>
0 siblings, 2 replies; 54+ messages in thread
From: Dean Gibson (DB Administrator) @ 2021-06-10 01:50 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
Having now successfully migrated from PostgreSQL v9.6 to v13.2 in Amazon
RDS, I wondered, why I am paying AWS for an RDS-based version, when I
was forced by their POLICY to go through the effort I did? I'm not one
of the crowd who thinks, "It works OK, so I don't update anything". I'm
usually one who is VERY quick to apply upgrades, especially when there
is a fallback ability. However, the initial failure to successfully
upgrade from v9.6 to any more recent major version, put me in a
time-limited box that I really don't like to be in.
If I'm going to have to deal with maintenance issues, like I easily did
when I ran native PostgreSQL, why not go back to that? So, I've ported
my database back to native PostgreSQL v13.3 on an AWS EC2 instance. It
looks like I will save about 40% of the cost, which is in accord with
this article: https://www.iobasis.com/Strategies-to-reduce-Amazon-RDS-Costs/
Why am I mentioning this here? Because there were minor issues &
benefits in porting back to native PostgreSQL, that may be of interest here:
First, pg_dumpall (v13.3) errors out, because on RDS, you cannot be a
superuser, & it tries to dump protected stuff. If there is a way around
that, I'd like to know it, even though it's not an issue now. pg_dump
works OK, but of course you don't get the roles dumped. Fortunately, I
kept script files that have all the database setup, so I just ran them
to create all the relationships, & then used the pg_dump output. Worked
flawlessly.
Second, I noticed that the compressed ("-Z6" level) output from pg-dump
is less than one-tenth of the disk size of the restored database.
That's LOT less than the size of the backups that AWS was charging me for.
Third, once you increase your disk size in RDS, you can never decrease
it, unless you go through the above port to a brand new instance (RDS or
native PostgreSQL). RDS backups must be restored to the same size
volume (or larger) that they were created for. A VACUUM FULL ANALYZE on
RDS requires more than doubling the required disk size (I tried with
less several times). This is easily dealt with on an EC2 Linux
instance, requiring only a couple minutes of DB downtime.
Fourth, while AWS is forcing customers to upgrade from v9.6, but the
only PostgreSQL client tools that AWS currently provides in their
standard repository are for v9.6!!! That means when you want to use any
of their client tools on newer versions, you have problems. psql gives
you a warning on each startup, & pg_dump simply (& correctly) won't back
up a newer DB. If you add their "optional" repository, you can use
v12.6 tools, but v13.3 is only available by hand-editing the repo file
to include v13 (which I did). For this level of support, I pay extra?
I don't think so.
Finally, the AWS support forums are effectively "write-only." Most of
the questions asked there, never get ANY response from other users, &
AWS only uses them to post announcements, from what I can tell. I got a
LOT more help here in this thread, & last I looked, I don't pay anyone here.
^ permalink raw reply [nested|flat] 54+ messages in thread
* Re: AWS forcing PG upgrade from v9.6 a disaster
2021-05-28 18:48 AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-05-31 03:07 ` Re: AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-06-01 04:16 ` Re: AWS forcing PG upgrade from v9.6 a disaster Justin Pryzby <pryzby@telsasoft.com>
2021-06-01 17:44 ` Re: AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-06-10 01:50 ` Re: AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
@ 2021-06-10 10:29 ` Andrew Dunstan <andrew@dunslane.net>
2021-06-10 16:07 ` Re: AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
1 sibling, 1 reply; 54+ messages in thread
From: Andrew Dunstan @ 2021-06-10 10:29 UTC (permalink / raw)
To: Dean Gibson (DB Administrator) <postgresql@mailpen.com>; pgsql-performance@lists.postgresql.org
On 6/9/21 9:50 PM, Dean Gibson (DB Administrator) wrote:
> Having now successfully migrated from PostgreSQL v9.6 to v13.2 in
> Amazon RDS, I wondered, why I am paying AWS for an RDS-based version,
> when I was forced by their POLICY to go through the effort I did? I'm
> not one of the crowd who thinks, "It works OK, so I don't update
> anything". I'm usually one who is VERY quick to apply upgrades,
> especially when there is a fallback ability. However, the initial
> failure to successfully upgrade from v9.6 to any more recent major
> version, put me in a time-limited box that I really don't like to be in.
>
> If I'm going to have to deal with maintenance issues, like I easily
> did when I ran native PostgreSQL, why not go back to that? So, I've
> ported my database back to native PostgreSQL v13.3 on an AWS EC2
> instance. It looks like I will save about 40% of the cost, which is
> in accord with this article:
> https://www.iobasis.com/Strategies-to-reduce-Amazon-RDS-Costs/
>
> Why am I mentioning this here? Because there were minor issues &
> benefits in porting back to native PostgreSQL, that may be of interest
> here:
>
> First, pg_dumpall (v13.3) errors out, because on RDS, you cannot be a
> superuser, & it tries to dump protected stuff. If there is a way
> around that, I'd like to know it, even though it's not an issue now.
> pg_dump works OK, but of course you don't get the roles dumped.
> Fortunately, I kept script files that have all the database setup, so
> I just ran them to create all the relationships, & then used the
> pg_dump output. Worked flawlessly.
This was added in release 12 specifically with RDS in mind:
pg_dumpall --exclude-database
cheers
andrew
--
Andrew Dunstan
EDB: https://www.enterprisedb.com
^ permalink raw reply [nested|flat] 54+ messages in thread
* Re: AWS forcing PG upgrade from v9.6 a disaster
2021-05-28 18:48 AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-05-31 03:07 ` Re: AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-06-01 04:16 ` Re: AWS forcing PG upgrade from v9.6 a disaster Justin Pryzby <pryzby@telsasoft.com>
2021-06-01 17:44 ` Re: AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-06-10 01:50 ` Re: AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-06-10 10:29 ` Re: AWS forcing PG upgrade from v9.6 a disaster Andrew Dunstan <andrew@dunslane.net>
@ 2021-06-10 16:07 ` Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-06-10 16:54 ` Re: AWS forcing PG upgrade from v9.6 a disaster Ranier Vilela <ranier.vf@gmail.com>
0 siblings, 1 reply; 54+ messages in thread
From: Dean Gibson (DB Administrator) @ 2021-06-10 16:07 UTC (permalink / raw)
To: pgsql-performance
On 2021-06-10 03:29, Andrew Dunstan wrote:
> On 6/9/21 9:50 PM, Dean Gibson (DB Administrator) wrote:
>> First, pg_dumpall (v13.3) errors out, because on RDS, you cannot be a superuser, & it tries to dump protected stuff. If there is a way around that, I'd like to know it, even though it's not an issue now. pg_dump works OK, but of course you don't get the roles dumped. Fortunately, I kept script files that have all the database setup, so I just ran them to create all the relationships, & then used the pg_dump output. Worked flawlessly.
> This was added in release 12 specifically with RDS in mind:
>
> pg_dumpall --exclude-database
>
> cheers, andrew
I guess I don't understand what that option does:
=>pg_dumpall -U Admin --exclude-database MailPen >zzz.sql
pg_dump: error: could not write to output file: No space left on device
pg_dumpall: error: pg_dump failed on database "MailPen", exiting
I expected a tiny file, not 3.5GB. "MailPen" is the only database
(other than what's pre-installed). Do I need quotes on the command line?
^ permalink raw reply [nested|flat] 54+ messages in thread
* Re: AWS forcing PG upgrade from v9.6 a disaster
2021-05-28 18:48 AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-05-31 03:07 ` Re: AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-06-01 04:16 ` Re: AWS forcing PG upgrade from v9.6 a disaster Justin Pryzby <pryzby@telsasoft.com>
2021-06-01 17:44 ` Re: AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-06-10 01:50 ` Re: AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-06-10 10:29 ` Re: AWS forcing PG upgrade from v9.6 a disaster Andrew Dunstan <andrew@dunslane.net>
2021-06-10 16:07 ` Re: AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
@ 2021-06-10 16:54 ` Ranier Vilela <ranier.vf@gmail.com>
2021-06-10 17:43 ` Re: AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
0 siblings, 1 reply; 54+ messages in thread
From: Ranier Vilela @ 2021-06-10 16:54 UTC (permalink / raw)
To: Dean Gibson (DB Administrator) <postgresql@mailpen.com>; +Cc: pgsql-performance
Em qui., 10 de jun. de 2021 às 13:08, Dean Gibson (DB Administrator) <
postgresql@mailpen.com> escreveu:
> On 2021-06-10 03:29, Andrew Dunstan wrote:
>
> On 6/9/21 9:50 PM, Dean Gibson (DB Administrator) wrote:
>
> First, pg_dumpall (v13.3) errors out, because on RDS, you cannot be a superuser, & it tries to dump protected stuff. If there is a way around that, I'd like to know it, even though it's not an issue now. pg_dump works OK, but of course you don't get the roles dumped. Fortunately, I kept script files that have all the database setup, so I just ran them to create all the relationships, & then used the pg_dump output. Worked flawlessly.
>
> This was added in release 12 specifically with RDS in mind:
>
> pg_dumpall --exclude-database
>
> cheers, andrew
>
>
> I guess I don't understand what that option does:
>
> =>pg_dumpall -U Admin --exclude-database MailPen >zzz.sql
> pg_dump: error: could not write to output file: No space left on device
> pg_dumpall: error: pg_dump failed on database "MailPen", exiting
>
> I expected a tiny file, not 3.5GB. "MailPen" is the only database (other
> than what's pre-installed). Do I need quotes on the command line?
>
See at:
https://www.postgresql.org/docs/13/app-pg-dumpall.html
Your cmd lacks =
=>pg_dumpall -U Admin --exclude-database=MailPen >zzz.sql
regards,
Ranier Vilela
^ permalink raw reply [nested|flat] 54+ messages in thread
* Re: AWS forcing PG upgrade from v9.6 a disaster
2021-05-28 18:48 AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-05-31 03:07 ` Re: AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-06-01 04:16 ` Re: AWS forcing PG upgrade from v9.6 a disaster Justin Pryzby <pryzby@telsasoft.com>
2021-06-01 17:44 ` Re: AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-06-10 01:50 ` Re: AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-06-10 10:29 ` Re: AWS forcing PG upgrade from v9.6 a disaster Andrew Dunstan <andrew@dunslane.net>
2021-06-10 16:07 ` Re: AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-06-10 16:54 ` Re: AWS forcing PG upgrade from v9.6 a disaster Ranier Vilela <ranier.vf@gmail.com>
@ 2021-06-10 17:43 ` Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-06-10 18:00 ` Re: AWS forcing PG upgrade from v9.6 a disaster Tom Lane <tgl@sss.pgh.pa.us>
2021-06-10 21:46 ` Re: AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
0 siblings, 2 replies; 54+ messages in thread
From: Dean Gibson (DB Administrator) @ 2021-06-10 17:43 UTC (permalink / raw)
To: pgsql-performance
On 2021-06-10 09:54, Ranier Vilela wrote:
> Em qui., 10 de jun. de 2021 às 13:08, Dean Gibson (DB Administrator)
> <postgresql@mailpen.com <mailto:postgresql@mailpen.com>> escreveu:
>
>
> I guess I don't understand what that option does:
>
> =>pg_dumpall -U Admin --exclude-database MailPen >zzz.sql
> pg_dump: error: could not write to output file: No space left on
> device
> pg_dumpall: error: pg_dump failed on database "MailPen", exiting
>
> I expected a tiny file, not 3.5GB. "MailPen" is the only database
> (other than what's pre-installed). Do I need quotes on the
> command line?
>
> See at:
> https://www.postgresql.org/docs/13/app-pg-dumpall.html
> <https://www.postgresql.org/docs/13/app-pg-dumpall.html;
>
> Your cmd lacks =
> =>pg_dumpall -U Admin --exclude-database=MailPen >zzz.sql
>
> regards, Ranier Vilela
I read that before posting, but missed that. Old command line patterns
die hard!
However, the result was the same: 3.5GB before running out of space.
^ permalink raw reply [nested|flat] 54+ messages in thread
* Re: AWS forcing PG upgrade from v9.6 a disaster
2021-05-28 18:48 AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-05-31 03:07 ` Re: AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-06-01 04:16 ` Re: AWS forcing PG upgrade from v9.6 a disaster Justin Pryzby <pryzby@telsasoft.com>
2021-06-01 17:44 ` Re: AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-06-10 01:50 ` Re: AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-06-10 10:29 ` Re: AWS forcing PG upgrade from v9.6 a disaster Andrew Dunstan <andrew@dunslane.net>
2021-06-10 16:07 ` Re: AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-06-10 16:54 ` Re: AWS forcing PG upgrade from v9.6 a disaster Ranier Vilela <ranier.vf@gmail.com>
2021-06-10 17:43 ` Re: AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
@ 2021-06-10 18:00 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-06-10 18:23 ` Re: AWS forcing PG upgrade from v9.6 a disaster Andrew Dunstan <andrew@dunslane.net>
1 sibling, 1 reply; 54+ messages in thread
From: Tom Lane @ 2021-06-10 18:00 UTC (permalink / raw)
To: Dean Gibson (DB Administrator) <postgresql@mailpen.com>; +Cc: pgsql-performance
"Dean Gibson (DB Administrator)" <postgresql@mailpen.com> writes:
> On 2021-06-10 09:54, Ranier Vilela wrote:
>> Your cmd lacks =
>> =>pg_dumpall -U Admin --exclude-database=MailPen >zzz.sql
> I read that before posting, but missed that. Old command line patterns
> die hard!
> However, the result was the same: 3.5GB before running out of space.
[ experiments... ] Looks like you gotta do it like this:
pg_dumpall '--exclude-database="MailPen"' ...
This surprises me, as I thought it was project policy not to
case-fold command-line arguments (precisely because you end
up needing weird quoting to prevent that).
regards, tom lane
^ permalink raw reply [nested|flat] 54+ messages in thread
* Re: AWS forcing PG upgrade from v9.6 a disaster
2021-05-28 18:48 AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-05-31 03:07 ` Re: AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-06-01 04:16 ` Re: AWS forcing PG upgrade from v9.6 a disaster Justin Pryzby <pryzby@telsasoft.com>
2021-06-01 17:44 ` Re: AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-06-10 01:50 ` Re: AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-06-10 10:29 ` Re: AWS forcing PG upgrade from v9.6 a disaster Andrew Dunstan <andrew@dunslane.net>
2021-06-10 16:07 ` Re: AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-06-10 16:54 ` Re: AWS forcing PG upgrade from v9.6 a disaster Ranier Vilela <ranier.vf@gmail.com>
2021-06-10 17:43 ` Re: AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-06-10 18:00 ` Re: AWS forcing PG upgrade from v9.6 a disaster Tom Lane <tgl@sss.pgh.pa.us>
@ 2021-06-10 18:23 ` Andrew Dunstan <andrew@dunslane.net>
2021-06-10 19:29 ` Re: AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
0 siblings, 1 reply; 54+ messages in thread
From: Andrew Dunstan @ 2021-06-10 18:23 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; Dean Gibson (DB Administrator) <postgresql@mailpen.com>; +Cc: pgsql-performance
On 6/10/21 2:00 PM, Tom Lane wrote:
> "Dean Gibson (DB Administrator)" <postgresql@mailpen.com> writes:
>> On 2021-06-10 09:54, Ranier Vilela wrote:
>>> Your cmd lacks =
>>> =>pg_dumpall -U Admin --exclude-database=MailPen >zzz.sql
>> I read that before posting, but missed that. Old command line patterns
>> die hard!
>> However, the result was the same: 3.5GB before running out of space.
> [ experiments... ] Looks like you gotta do it like this:
>
> pg_dumpall '--exclude-database="MailPen"' ...
>
> This surprises me, as I thought it was project policy not to
> case-fold command-line arguments (precisely because you end
> up needing weird quoting to prevent that).
>
>
Ouch. That looks like a plain old bug. Let's fix it. IIRC I just used
the same logic that we use for pg_dump's --exclude-* options, so we need
to check if they have similar issues.
cheers
andrew
--
Andrew Dunstan
EDB: https://www.enterprisedb.com
^ permalink raw reply [nested|flat] 54+ messages in thread
* Re: AWS forcing PG upgrade from v9.6 a disaster
2021-05-28 18:48 AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-05-31 03:07 ` Re: AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-06-01 04:16 ` Re: AWS forcing PG upgrade from v9.6 a disaster Justin Pryzby <pryzby@telsasoft.com>
2021-06-01 17:44 ` Re: AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-06-10 01:50 ` Re: AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-06-10 10:29 ` Re: AWS forcing PG upgrade from v9.6 a disaster Andrew Dunstan <andrew@dunslane.net>
2021-06-10 16:07 ` Re: AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-06-10 16:54 ` Re: AWS forcing PG upgrade from v9.6 a disaster Ranier Vilela <ranier.vf@gmail.com>
2021-06-10 17:43 ` Re: AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-06-10 18:00 ` Re: AWS forcing PG upgrade from v9.6 a disaster Tom Lane <tgl@sss.pgh.pa.us>
2021-06-10 18:23 ` Re: AWS forcing PG upgrade from v9.6 a disaster Andrew Dunstan <andrew@dunslane.net>
@ 2021-06-10 19:29 ` Dean Gibson (DB Administrator) <postgresql@mailpen.com>
0 siblings, 0 replies; 54+ messages in thread
From: Dean Gibson (DB Administrator) @ 2021-06-10 19:29 UTC (permalink / raw)
To: pgsql-performance
On 2021-06-10 11:23, Andrew Dunstan wrote:
> On 6/10/21 2:00 PM, Tom Lane wrote:
>> "Dean Gibson (DB Administrator)" <postgresql@mailpen.com> writes:
>>> ... Do I need quotes on the command line?
>>> On 2021-06-10 09:54, Ranier Vilela wrote:
>>>> Your cmd lacks =
>>>> =>pg_dumpall -U Admin --exclude-database=MailPen >zzz.sql
>>> I read [the manual] before posting, but missed that. Old command line patterns die hard!
>>> However, the result was the same: 3.5GB before running out of space.
>> [ experiments... ] Looks like you gotta do it like this:
>>
>> pg_dumpall '--exclude-database="MailPen"' ...
>>
>> This surprises me, as I thought it was project policy not to case-fold command-line arguments (precisely because you end up needing weird quoting to prevent that).
> Ouch. That looks like a plain old bug. Let's fix it. IIRC I just used the same logic that we use for pg_dump's --exclude-* options, so we need to check if they have similar issues.
>
> cheers, andrew
That works! I thought it was a quoting/case issue! I was next going to
try single quotes just outside double quotes, & that works as well (& is
a bit more natural):
pg_dumpall -U Admin --exclude-database='"MailPen"' >zzz.sql
Using mixed case has bitten me before, but I am not deterred! I run
phpBB 3.0.14 (very old version) because upgrades to more current
versions fail on the mixed case of the DB name, as well as the use of
SCHEMAs to isolate the message board from the rest of the data. Yes, I
reported it years ago.
I use lower-case for column, VIEW, & function names; mixed (camel) case
for table, schema, & database names; & upper-case for SQL keywords. It
helps readability (as does murdering a couple semicolons in the prior
sentence).
^ permalink raw reply [nested|flat] 54+ messages in thread
* Re: AWS forcing PG upgrade from v9.6 a disaster
2021-05-28 18:48 AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-05-31 03:07 ` Re: AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-06-01 04:16 ` Re: AWS forcing PG upgrade from v9.6 a disaster Justin Pryzby <pryzby@telsasoft.com>
2021-06-01 17:44 ` Re: AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-06-10 01:50 ` Re: AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-06-10 10:29 ` Re: AWS forcing PG upgrade from v9.6 a disaster Andrew Dunstan <andrew@dunslane.net>
2021-06-10 16:07 ` Re: AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-06-10 16:54 ` Re: AWS forcing PG upgrade from v9.6 a disaster Ranier Vilela <ranier.vf@gmail.com>
2021-06-10 17:43 ` Re: AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
@ 2021-06-10 21:46 ` Dean Gibson (DB Administrator) <postgresql@mailpen.com>
1 sibling, 0 replies; 54+ messages in thread
From: Dean Gibson (DB Administrator) @ 2021-06-10 21:46 UTC (permalink / raw)
To: pgsql-performance
On 2021-06-10 10:43, Dean Gibson (DB Administrator) wrote:
> On 2021-06-10 09:54, Ranier Vilela wrote:
>> Em qui., 10 de jun. de 2021 às 13:08, Dean Gibson (DB Administrator)
>> <postgresql@mailpen.com <mailto:postgresql@mailpen.com>> escreveu:
>>
>>
>> ... Do I need quotes on the command line?
>>
>> See at:
>> https://www.postgresql.org/docs/13/app-pg-dumpall.html
>> <https://www.postgresql.org/docs/13/app-pg-dumpall.html;
>>
>> Your cmd lacks =
>> =>pg_dumpall -U Admin --exclude-database=MailPen >zzz.sql
>>
>> regards, Ranier Vilela
>
> ...
>
> However, the result was the same: 3.5GB before running out of space.
>
It turns out the "=" is not needed. The double-quoting is (this works):
pg_dumpall -U Admin --exclude-database '"MailPen"' >zzz.sql
^ permalink raw reply [nested|flat] 54+ messages in thread
* Re: AWS forcing PG upgrade from v9.6 a disaster
2021-05-28 18:48 AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-05-31 03:07 ` Re: AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-06-01 04:16 ` Re: AWS forcing PG upgrade from v9.6 a disaster Justin Pryzby <pryzby@telsasoft.com>
2021-06-01 17:44 ` Re: AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-06-10 01:50 ` Re: AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
@ 2021-06-10 14:36 ` Joshua Drake <jd@commandprompt.com>
1 sibling, 0 replies; 54+ messages in thread
From: Joshua Drake @ 2021-06-10 14:36 UTC (permalink / raw)
To: Dean Gibson (DB Administrator) <postgresql@mailpen.com>; +Cc: pgsql-performance@lists.postgresql.org
On Wed, Jun 9, 2021 at 6:50 PM Dean Gibson (DB Administrator) <
postgresql@mailpen.com> wrote:
> Having now successfully migrated from PostgreSQL v9.6 to v13.2 in Amazon
> RDS, I wondered, why I am paying AWS for an RDS-based version, when I was
> forced by their POLICY to go through the effort I did? I'm not one of the
> crowd who thinks, "It works OK, so I don't update anything". I'm usually
> one who is VERY quick to apply upgrades, especially when there is a
> fallback ability. However, the initial failure to successfully upgrade
> from v9.6 to any more recent major version, put me in a time-limited box
> that I really don't like to be in.
>
Right, and had you deployed on EC2 you would not have been forced to
upgrade. This is an argument against RDS for this particular problem.
>
> If I'm going to have to deal with maintenance issues, like I easily did
> when I ran native PostgreSQL, why not go back to that? So, I've ported my
> database back to native PostgreSQL v13.3 on an AWS EC2 instance. It looks
> like I will save about 40% of the cost, which is in accord with this
> article: https://www.iobasis.com/Strategies-to-reduce-Amazon-RDS-Costs/
>
That is correct, it is quite a bit less expensive to host your own EC2
instances. Where it is not cheaper is when you need to easily configure
backups, take a snapshot, or bring up a replica. For those in the know,
putting in some work upfront largely removes the burden that RDS corrects
but a lot of people who deploy RDS are *not* DBAs, or even Systems people.
They are front end developers.
Glad to see you were able to work things out.
JD
--
- Partner, Father, Explorer and Founder.
- Founder - https://commandprompt.com/ - 24x7x365 Postgres since 1997
- Founder and Co-Chair - https://postgresconf.org/
- Founder - https://postgresql.us - United States PostgreSQL
- Public speaker, published author, postgresql expert, and people
believer.
- Host - More than a refresh
<https://commandprompt.com/about/more-than-a-refresh/;: A podcast about
data and the people who wrangle it.
^ permalink raw reply [nested|flat] 54+ messages in thread
end of thread, other threads:[~2021-06-10 21:46 UTC | newest]
Thread overview: 54+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-05-28 03:41 AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@ultimeth.com>
2021-05-28 15:12 ` Adrian Klaver <adrian.klaver@aklaver.com>
2021-05-28 18:40 ` Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-05-28 19:38 ` Ron <ronljohnsonjr@gmail.com>
2021-05-28 22:06 ` Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-05-28 23:51 ` Ron <ronljohnsonjr@gmail.com>
2021-05-29 00:38 ` Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-05-29 10:40 ` Lionel Bouton <lionel.bouton@jtek.fr>
2021-05-29 22:34 ` Michael Lewis <mlewis@entrata.com>
2021-05-29 22:47 ` Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-05-29 16:25 ` Adrian Klaver <adrian.klaver@aklaver.com>
2021-05-29 19:59 ` Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-05-29 20:35 ` Andrew Dunstan <andrew@dunslane.net>
2021-06-06 23:49 ` Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-06-07 11:52 ` Andrew Dunstan <andrew@dunslane.net>
2021-06-07 17:27 ` Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-05-28 18:48 AWS forcing PG upgrade from v9.6 a disaster Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-05-28 19:08 ` Andrew Dunstan <andrew@dunslane.net>
2021-05-28 19:18 ` Campbell, Lance <lance@illinois.edu>
2021-05-28 19:38 ` MichaelDBA <MichaelDBA@sqlexec.com>
2021-05-28 19:39 ` Ryan Bair <ryandbair@gmail.com>
2021-05-28 20:11 ` Michael Lewis <mlewis@entrata.com>
2021-05-28 20:38 ` Pavel Stehule <pavel.stehule@gmail.com>
2021-05-28 20:37 ` Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-05-28 20:33 ` Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-05-28 20:23 ` Jan Wieck <jan@wi3ck.info>
2021-05-28 21:15 ` Andrew Dunstan <andrew@dunslane.net>
2021-05-28 21:30 ` Bruce Momjian <bruce@momjian.us>
2021-05-29 02:43 ` Christophe Pettus <xof@thebuild.com>
2021-05-29 04:08 ` Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-05-29 05:24 ` Alexey M Boltenkov <padrebolt@yandex.ru>
2021-05-29 20:17 ` Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-05-28 22:09 ` Bob Lunney <bob_lunney@yahoo.com>
2021-05-29 02:27 ` Jan Wieck <jan@wi3ck.info>
2021-05-29 02:41 ` Andrew Dunstan <andrew@dunslane.net>
2021-05-29 11:39 ` Jan Wieck <jan@wi3ck.info>
2021-05-28 22:13 ` Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-05-31 03:07 ` Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-05-31 03:41 ` Christophe Pettus <xof@thebuild.com>
2021-05-31 04:23 ` Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-05-31 04:44 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-05-31 05:24 ` Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-06-01 04:16 ` Justin Pryzby <pryzby@telsasoft.com>
2021-06-01 17:44 ` Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-06-10 01:50 ` Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-06-10 10:29 ` Andrew Dunstan <andrew@dunslane.net>
2021-06-10 16:07 ` Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-06-10 16:54 ` Ranier Vilela <ranier.vf@gmail.com>
2021-06-10 17:43 ` Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-06-10 18:00 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-06-10 18:23 ` Andrew Dunstan <andrew@dunslane.net>
2021-06-10 19:29 ` Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-06-10 21:46 ` Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-06-10 14:36 ` Joshua Drake <jd@commandprompt.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox