AWS forcing PG upgrade from v9.6 a disaster

Started by Dean Gibson (DB Administrator)over 4 years ago41 messages

postgresql@mailpen.com

over 4 years ago

[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_:*

*TABLE "GenLic"."_Club":*

*VIEW licjb_:*

*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:*

*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"**:*

*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:*

*VIEW _lic_hd:*

*TABLE **"UlsLic"."_HD"**:*

*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:*

*VIEW _lic_am:*

*TABLE **"UlsLic"."_AM"**:*

Andrew Dunstan

andrew@dunslane.net

over 4 years ago

In reply to: Dean Gibson (DB Administrator) (#1)

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://www.enterprisedb.com

Campbell, Lance

lance@illinois.edu

over 4 years ago

In reply to: Andrew Dunstan (#2)

Re: AWS forcing PG upgrade from v9.6 a disaster

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_vwxOHZfrJvVXj8kYMPJqa1tO5Fnv75UbERS8ZAmUoNFl_g2EVyL$<https://urldefense.com/v3/__https:/www.enterprisedb.com__;!!DZ3fjg!tiFTfkNeARuU_vwxOHZfrJvVXj8kYMPJqa1tO5Fnv75UbERS8ZAmUoNFl_g2EVyL$>

MichaelDBA

MichaelDBA@sqlexec.com

over 4 years ago

In reply to: Campbell, Lance (#3)

Re: AWS forcing PG upgrade from v9.6 a disaster

Hi Lance,

Did you customize the PG 12 DB Parameter group to be in sync as much as
possible with the 9.6 RDS version?ï¿½ Or are you using PG12 default DB
Parameter group?

Are you using the same AWS Instance Class?

Did you vacuum analyze all your tables after the upgrade to 12?

Regards,
Michael Vitale

Campbell, Lance wrote on 5/28/2021 3:18 PM:

Show quoted text

Also, did you check your RDS setting in AWS after upgrading?ï¿½ I run
four databases in AWS.ï¿½ I found that the work_mem was set way low
after an upgrade.ï¿½ I had to tweak many of my settings.

Lance

*From: *Andrew Dunstan <andrew@dunslane.net>
*Date: *Friday, May 28, 2021 at 2:08 PM
*To: *Dean Gibson (DB Administrator) <postgresql@mailpen.com>,
pgsql-performance@lists.postgresql.org
<pgsql-performance@lists.postgresql.org>
*Subject: *Re: AWS forcing PG upgrade from v9.6 a 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_vwxOHZfrJvVXj8kYMPJqa1tO5Fnv75UbERS8ZAmUoNFl_g2EVyL$
<https://urldefense.com/v3/__https:/www.enterprisedb.com__;%21%21DZ3fjg%21tiFTfkNeARuU_vwxOHZfrJvVXj8kYMPJqa1tO5Fnv75UbERS8ZAmUoNFl_g2EVyL$>

Ryan Bair

ryandbair@gmail.com

over 4 years ago

In reply to: Campbell, Lance (#3)

Re: AWS forcing PG upgrade from v9.6 a disaster

The problem is the plan. The planner massively underestimated the number of
rows arising from the _EN/_AM join.

Usually postgres is pretty good about running ANALYZE as needed, but it
might be a good idea to run it manually to rule that out as a potential
culprit.

On Fri, May 28, 2021 at 3:19 PM Campbell, Lance <lance@illinois.edu> wrote:

Show quoted text

Also, did you check your RDS setting in AWS after upgrading? I run four
databases in AWS. I found that the work_mem was set way low after an
upgrade. I had to tweak many of my settings.

Lance

*From: *Andrew Dunstan <andrew@dunslane.net>
*Date: *Friday, May 28, 2021 at 2:08 PM
*To: *Dean Gibson (DB Administrator) <postgresql@mailpen.com>,
pgsql-performance@lists.postgresql.org <
pgsql-performance@lists.postgresql.org>
*Subject: *Re: AWS forcing PG upgrade from v9.6 a 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_vwxOHZfrJvVXj8kYMPJqa1tO5Fnv75UbERS8ZAmUoNFl_g2EVyL$
<https://urldefense.com/v3/__https:/www.enterprisedb.com__;!!DZ3fjg!tiFTfkNeARuU_vwxOHZfrJvVXj8kYMPJqa1tO5Fnv75UbERS8ZAmUoNFl_g2EVyL$>

Michael Lewis

mlewis@entrata.com

over 4 years ago

In reply to: Ryan Bair (#5)

Re: AWS forcing PG upgrade from v9.6 a disaster

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.

Jan Wieck

jan@wi3ck.info

over 4 years ago

In reply to: Dean Gibson (DB Administrator) (#1)

Re: AWS forcing PG upgrade from v9.6 a disaster

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

Dean Gibson (DB Administrator)

postgresql@mailpen.com

over 4 years ago

In reply to: Andrew Dunstan (#2)

Re: AWS forcing PG upgrade from v9.6 a disaster

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

Dean Gibson (DB Administrator)

postgresql@mailpen.com

over 4 years ago

In reply to: Campbell, Lance (#3)

Re: AWS forcing PG upgrade from v9.6 a disaster

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.

#10

Pavel Stehule

pavel.stehule@gmail.com

over 4 years ago

In reply to: Ryan Bair (#5)

Re: AWS forcing PG upgrade from v9.6 a disaster

pá 28. 5. 2021 v 21:39 odesílatel Ryan Bair <ryandbair@gmail.com> napsal:

The problem is the plan. The planner massively underestimated the number
of rows arising from the _EN/_AM join.

Usually postgres is pretty good about running ANALYZE as needed, but it
might be a good idea to run it manually to rule that out as a potential
culprit.

yes

the very strange is pretty high planning time

Planning Time: 173.753 ms

This is unusually high number - maybe the server has bad CPU or maybe some
indexes bloating

Regards

Pavel

Show quoted text

On Fri, May 28, 2021 at 3:19 PM Campbell, Lance <lance@illinois.edu> wrote:

Also, did you check your RDS setting in AWS after upgrading? I run four
databases in AWS. I found that the work_mem was set way low after an
upgrade. I had to tweak many of my settings.

Lance

*From: *Andrew Dunstan <andrew@dunslane.net>
*Date: *Friday, May 28, 2021 at 2:08 PM
*To: *Dean Gibson (DB Administrator) <postgresql@mailpen.com>,
pgsql-performance@lists.postgresql.org <
pgsql-performance@lists.postgresql.org>
*Subject: *Re: AWS forcing PG upgrade from v9.6 a 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_vwxOHZfrJvVXj8kYMPJqa1tO5Fnv75UbERS8ZAmUoNFl_g2EVyL$
<https://urldefense.com/v3/__https:/www.enterprisedb.com__;!!DZ3fjg!tiFTfkNeARuU_vwxOHZfrJvVXj8kYMPJqa1tO5Fnv75UbERS8ZAmUoNFl_g2EVyL$>

#11

Andrew Dunstan

andrew@dunslane.net

over 4 years ago

In reply to: Jan Wieck (#7)

Re: AWS forcing PG upgrade from v9.6 a disaster

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

#12

Bruce Momjian

bruce@momjian.us

over 4 years ago

In reply to: Andrew Dunstan (#11)

Re: AWS forcing PG upgrade from v9.6 a disaster

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.

#13

Bob Lunney

bob_lunney@yahoo.com

over 4 years ago

In reply to: Andrew Dunstan (#11)

Re: AWS forcing PG upgrade from v9.6 a disaster

I recently did 20 upgrades from 9.6 to 12.4 and 12.5. No issues and the upgrade process uses pg_upgrade. I don’t know if AWS modified it though.

Bob

Sent from my PDP11

Show quoted text

On May 28, 2021, at 5:15 PM, Andrew Dunstan <andrew@dunslane.net> wrote:

On 5/28/21 4:23 PM, Jan Wieck wrote:
On 5/28/21 2:48 PM, Dean Gibson (DB Administrator) wrote:

What sticks out for me are these two scans, which balloon from 50-60
heap fetches to 1.5M each.

-> Nested Loop (cost=0.29..0.68 rows=1
width=7) (actual time=0.003..0.004 rows=1 loops=1487153)
Join Filter: ("_IsoCountry".iso_alpha2 =
"_Territory".country_id)
Rows Removed by Join Filter: 0
-> Index Only Scan using
"_IsoCountry_iso_alpha2_key" on "_IsoCountry" (cost=0.14..0.38
rows=1 width=3) (actual time=0.001..0.002 rows=1 loops=1487153)
Index Cond: (iso_alpha2 =
"_GovtRegion".country_id)
Heap Fetches: 1487153
-> Index Only Scan using
"_Territory_pkey" on "_Territory" (cost=0.14..0.29 rows=1 width=7)
(actual time=0.001..0.001 rows=1 loops=1487153)
Index Cond: (territory_id =
"_GovtRegion".territory_id)
Heap Fetches: 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

#14

Dean Gibson (DB Administrator)

postgresql@mailpen.com

over 4 years ago

In reply to: Jan Wieck (#7)

Re: AWS forcing PG upgrade from v9.6 a disaster

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.

#15

Jan Wieck

jan@wi3ck.info

over 4 years ago

In reply to: Andrew Dunstan (#11)

Re: AWS forcing PG upgrade from v9.6 a disaster

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

Show quoted text

cheers

andrew

--
Andrew Dunstan
EDB: https://www.enterprisedb.com

#16

Andrew Dunstan

andrew@dunslane.net

over 4 years ago

In reply to: Jan Wieck (#15)

Re: AWS forcing PG upgrade from v9.6 a disaster

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

#17

Christophe Pettus

xof@thebuild.com

over 4 years ago

In reply to: Bruce Momjian (#12)

Re: AWS forcing PG upgrade from v9.6 a disaster

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.

#18

Dean Gibson (DB Administrator)

postgresql@mailpen.com

over 4 years ago

In reply to: Christophe Pettus (#17)

Re: AWS forcing PG upgrade from v9.6 a disaster

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.

#19

Alexey M Boltenkov

padrebolt@yandex.ru

over 4 years ago

In reply to: Dean Gibson (DB Administrator) (#18)

Re: AWS forcing PG upgrade from v9.6 a disaster

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

#20

Jan Wieck

jan@wi3ck.info

over 4 years ago

In reply to: Andrew Dunstan (#16)

Re: AWS forcing PG upgrade from v9.6 a disaster

On Fri, May 28, 2021, 22:41 Andrew Dunstan <andrew@dunslane.net> wrote:

On 5/28/21 10:27 PM, Jan Wieck wrote:

On Fri, May 28, 2021, 17:15 Andrew Dunstan <andrew@dunslane.net
<mailto:andrew@dunslane.net>> wrote:

AIUI he did an RDS upgrade. Surely that's not doing a dump/restore? I
assume you would know better than him or me what it actually does
do :-)

Since I am not working at AWS I can't tell for sure. ;)

Apologies, my mistake then.

No need to apologize, you were correct two months ago.

Best Regards, Jan

Show quoted text

cheers

andrew

--
Andrew Dunstan
EDB: https://www.enterprisedb.com

#21

Dean Gibson (DB Administrator)

postgresql@mailpen.com

over 4 years ago

In reply to: Alexey M Boltenkov (#19)

Re: AWS forcing PG upgrade from v9.6 a disaster

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.

#22

Dean Gibson (DB Administrator)

postgresql@mailpen.com

over 4 years ago

In reply to: Dean Gibson (DB Administrator) (#1)

Re: AWS forcing PG upgrade from v9.6 a disaster

*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

Show quoted text

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)

#23

Christophe Pettus

xof@thebuild.com

over 4 years ago

In reply to: Dean Gibson (DB Administrator) (#22)

Re: AWS forcing PG upgrade from v9.6 a disaster

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?

#24

Dean Gibson (DB Administrator)

postgresql@mailpen.com

over 4 years ago

In reply to: Christophe Pettus (#23)

Re: AWS forcing PG upgrade from v9.6 a disaster

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.

#25

Tom Lane

tgl@sss.pgh.pa.us

over 4 years ago

In reply to: Dean Gibson (DB Administrator) (#24)

Re: AWS forcing PG upgrade from v9.6 a disaster

"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

#26

Dean Gibson (DB Administrator)

postgresql@mailpen.com

over 4 years ago

In reply to: Tom Lane (#25)

Re: AWS forcing PG upgrade from v9.6 a disaster

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|/ [, ...] )|/

/
/

/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|//./

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)?

#27

Justin Pryzby

pryzby@telsasoft.com

over 4 years ago

In reply to: Dean Gibson (DB Administrator) (#22)

Re: AWS forcing PG upgrade from v9.6 a disaster

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

#28

Dean Gibson (DB Administrator)

postgresql@mailpen.com

over 4 years ago

In reply to: Justin Pryzby (#27)

Re: AWS forcing PG upgrade from v9.6 a disaster

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).

#29

Dean Gibson (DB Administrator)

postgresql@mailpen.com

over 4 years ago

In reply to: Dean Gibson (DB Administrator) (#28)

Re: AWS forcing PG upgrade from v9.6 a disaster

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.

#30

Andrew Dunstan

andrew@dunslane.net

over 4 years ago

In reply to: Dean Gibson (DB Administrator) (#29)

Re: AWS forcing PG upgrade from v9.6 a disaster

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

#31

Joshua Drake

jd@commandprompt.com

over 4 years ago

In reply to: Dean Gibson (DB Administrator) (#29)

Re: AWS forcing PG upgrade from v9.6 a disaster

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.

- 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.

#32

Dean Gibson (DB Administrator)

postgresql@mailpen.com

over 4 years ago

In reply to: Andrew Dunstan (#30)

Re: AWS forcing PG upgrade from v9.6 a disaster

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?

#33

Ranier Vilela

ranier.vf@gmail.com

over 4 years ago

In reply to: Dean Gibson (DB Administrator) (#32)

Re: AWS forcing PG upgrade from v9.6 a disaster

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

#34

Dean Gibson (DB Administrator)

postgresql@mailpen.com

over 4 years ago

In reply to: Ranier Vilela (#33)

Re: AWS forcing PG upgrade from v9.6 a disaster

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.

#35

Tom Lane

tgl@sss.pgh.pa.us

over 4 years ago

In reply to: Dean Gibson (DB Administrator) (#34)

Re: AWS forcing PG upgrade from v9.6 a disaster

"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

#36

Andrew Dunstan

andrew@dunslane.net

over 4 years ago

In reply to: Tom Lane (#35)

Re: AWS forcing PG upgrade from v9.6 a disaster

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

#37

Dean Gibson (DB Administrator)

postgresql@mailpen.com

over 4 years ago

In reply to: Andrew Dunstan (#36)

Re: AWS forcing PG upgrade from v9.6 a disaster

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).

#38

Dean Gibson (DB Administrator)

postgresql@mailpen.com

over 4 years ago

In reply to: Dean Gibson (DB Administrator) (#34)

Re: AWS forcing PG upgrade from v9.6 a disaster

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

#39

Andrew Dunstan

andrew@dunslane.net

over 4 years ago

In reply to: Andrew Dunstan (#36)

pg_dumpall --exclude-database case folding, was Re: AWS forcing PG upgrade from v9.6 a disaster

On 6/10/21 2:23 PM, Andrew Dunstan wrote:

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.

Peter Eisentraut has pointed out to me that this is documented, albeit a
bit obscurely for pg_dumpall. But it is visible on the pg_dump page.

Nevertheless, it's a bit of a POLA violation as we've seen above, and
I'd like to get it fixed, if there's agreement, both for this pg_dumpall
option and for pg_dump's pattern matching options.

cheers

andrew

--
Andrew Dunstan
EDB: https://www.enterprisedb.com

#40

Tom Lane

tgl@sss.pgh.pa.us

over 4 years ago

In reply to: Andrew Dunstan (#39)

Re: pg_dumpall --exclude-database case folding, was Re: AWS forcing PG upgrade from v9.6 a disaster

Andrew Dunstan <andrew@dunslane.net> writes:

On 6/10/21 2:23 PM, Andrew Dunstan wrote:

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.

Peter Eisentraut has pointed out to me that this is documented, albeit a
bit obscurely for pg_dumpall. But it is visible on the pg_dump page.

Hmm.

Nevertheless, it's a bit of a POLA violation as we've seen above, and
I'd like to get it fixed, if there's agreement, both for this pg_dumpall
option and for pg_dump's pattern matching options.

+1, but the -performance list isn't really where to hold that discussion.
Please start a thread on -hackers.

regards, tom lane

#41

Andrew Dunstan

andrew@dunslane.net

over 4 years ago

In reply to: Tom Lane (#40)

pg_dumpall --exclude-database case folding

[discussion transferred from psql-performance]

Summary: pg_dumpall and pg_dump fold non-quoted commandline patterns to
lower case

Tom lane writes:

Andrew Dunstan <andrew@dunslane.net> writes:

On 6/10/21 2:23 PM, Andrew Dunstan wrote:

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.

Peter Eisentraut has pointed out to me that this is documented, albeit a
bit obscurely for pg_dumpall. But it is visible on the pg_dump page.

Hmm.

Nevertheless, it's a bit of a POLA violation as we've seen above, and
I'd like to get it fixed, if there's agreement, both for this pg_dumpall
option and for pg_dump's pattern matching options.

+1, but the -performance list isn't really where to hold that discussion.
Please start a thread on -hackers.

regards, tom lane