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

[Reposted to the proper list]

I started to use PostgreSQL v7.3 in 2003 on my home Linux systems (4 at one point), gradually moving to v9.0 w/ replication in 2010.  In 2017 I moved my 20GB database to AWS/RDS, gradually upgrading to v9.6, & was entirely satisfied with the result.

In March of this year, AWS announced that v9.6 was nearing end of support, & AWS would forcibly upgrade everyone to v12 on January 22, 2022, if users did not perform the upgrade earlier.  My first attempt was successful as far as the upgrade itself, but complex queries that normally ran in a couple of seconds on v9.x, were taking minutes in v12.

I didn't have the time in March to diagnose the problem, other than some futile adjustments to server parameters, so I reverted back to a saved copy of my v9.6 data.

On Sunday, being retired, I decided to attempt to solve the issue in earnest.  I have now spent five days (about 14 hours a day), trying various things, including adding additional indexes.  Keeping the v9.6 data online for web users, I've "forked" the data into new copies, & updated them in turn to PostgreSQL v10, v11, v12, & v13.  All exhibit the same problem:  As you will see below, it appears that versions 10 & above are doing a sequential scan of some of the "large" (200K rows) tables.  Note that the expected & actual run times both differ for v9.6 & v13.2, by more than two orders of magnitude. Rather than post a huge eMail (ha ha), I'll start with this one, that shows an "EXPLAIN ANALYZE" from both v9.6 & v13.2, followed by the related table & view definitions.  With one exception, table definitions are from the FCC (Federal Communications Commission);  the view definitions are my own.

Here's from v9.6:

=> EXPLAIN ANALYZE SELECT club_count, extra_count, region_count, callsign AS trustee_callsign, applicant_type, entity_name, licensee_id AS _lid FROM genclub_multi_  WHERE club_count >= 5 ORDER BY extra_count DESC, club_count DESC, entity_name;
                                                                                             QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=407.13..407.13 rows=1 width=94) (actual time=348.850..348.859 rows=43 loops=1)
   Sort Key: "_Club".extra_count DESC, "_Club".club_count DESC, "_EN".entity_name
   Sort Method: quicksort  Memory: 31kB
   ->  Nested Loop  (cost=4.90..407.12 rows=1 width=94) (actual time=7.587..348.732 rows=43 loops=1)
         ->  Nested Loop  (cost=4.47..394.66 rows=1 width=94) (actual time=5.740..248.149 rows=43 loops=1)
               ->  Nested Loop Left Join  (cost=4.04..382.20 rows=1 width=79) (actual time=2.458..107.908 rows=55 loops=1)
                     ->  Hash Join  (cost=3.75..380.26 rows=1 width=86) (actual time=2.398..106.990 rows=55 loops=1)
                           Hash Cond: (("_EN".country_id = "_GovtRegion".country_id) AND ("_EN".state = "_GovtRegion".territory_id))
                           ->  Nested Loop  (cost=0.43..376.46 rows=47 width=94) (actual time=2.294..106.736 rows=55 loops=1)
                                 ->  Seq Scan on "_Club"  (cost=0.00..4.44 rows=44 width=35) (actual time=0.024..0.101 rows=44 loops=1)
                                       Filter: (club_count >= 5)
                                       Rows Removed by Filter: 151
                                 ->  Index Scan using "_EN_callsign" on "_EN"  (cost=0.43..8.45 rows=1 width=69) (actual time=2.179..2.420 rows=1 loops=44)
                                       Index Cond: (callsign = "_Club".trustee_callsign)
                           ->  Hash  (cost=1.93..1.93 rows=93 width=7) (actual time=0.071..0.071 rows=88 loops=1)
                                 Buckets: 1024  Batches: 1  Memory Usage: 12kB
                                 ->  Seq Scan on "_GovtRegion"  (cost=0.00..1.93 rows=93 width=7) (actual time=0.010..0.034 rows=93 loops=1)
                     ->  Nested Loop  (cost=0.29..1.93 rows=1 width=7) (actual time=0.012..0.014 rows=1 loops=55)
                           Join Filter: ("_IsoCountry".iso_alpha2 = "_Territory".country_id)
                           Rows Removed by Join Filter: 0
                           ->  Index Only Scan using "_IsoCountry_iso_alpha2_key" on "_IsoCountry"  (cost=0.14..1.62 rows=1 width=3) (actual time=0.006..0.006 rows=1 loops=55)
                                 Index Cond: (iso_alpha2 = "_GovtRegion".country_id)
                                 Heap Fetches: 55
                           ->  Index Only Scan using "_Territory_pkey" on "_Territory"  (cost=0.14..0.29 rows=1 width=7)
 (actual time=0.004..0.005 rows=1 loops=55)
                                 Index Cond: (territory_id = "_GovtRegion".territory_id)
                                 Heap Fetches: 59
               ->  Index Scan using "_HD_pkey" on "_HD"  (cost=0.43..12.45 rows=1 width=15) (actual time=2.548..2.548 rows=1 loops=55)
                     Index Cond: (unique_system_identifier = "_EN".unique_system_identifier)
                     Filter: (("_EN".callsign = callsign) AND (((((license_status)::text || ' - '::text) || (COALESCE((SubPlan 2), '???'::character varying))::text))::character(1) = 'A'::bpchar))
                     Rows Removed by Filter: 0
                     SubPlan 2
                       ->  Limit  (cost=0.15..8.17 rows=1 width=32) (actual time=0.006..0.007 rows=1 loops=55)
                             ->  Index Scan using "_LicStatus_pkey" on "_LicStatus"  (cost=0.15..8.17 rows=1 width=32) (actual time=0.005..0.005 rows=1 loops=55)
                                   Index Cond: ("_HD".license_status = status_id)
         ->  Index Scan using "_AM_pkey" on "_AM"  (cost=0.43..4.27 rows=1 width=15) (actual time=2.325..2.325 rows=1 loops=43)
               Index Cond: (unique_system_identifier = "_EN".unique_system_identifier)
               Filter: ("_EN".callsign = callsign)
         SubPlan 1
           ->  Limit  (cost=0.15..8.17 rows=1 width=32) (actual time=0.007..0.007 rows=1 loops=43)
                 ->  Index Scan using "_ApplicantType_pkey" on "_ApplicantType"  (cost=0.15..8.17 rows=1 width=32) (actual time=0.005..0.005 rows=1 loops=43)
                       Index Cond: ("_EN".applicant_type_code = app_type_id)
 Planning time: 13.490 ms
 Execution time: 349.182 ms
(43 rows)


Here's from v13.2: 

=> EXPLAIN ANALYZE SELECT club_count, extra_count, region_count, callsign AS trustee_callsign, applicant_type, entity_name, licensee_id AS _lid FROM genclub_multi_  WHERE club_count >= 5 ORDER BY extra_count DESC, club_count DESC, entity_name;
                                                                                             QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=144365.60..144365.60 rows=1 width=94) (actual time=31898.860..31901.922 rows=43 loops=1)
   Sort Key: "_Club".extra_count DESC, "_Club".club_count DESC, "_EN".entity_name
   Sort Method: quicksort  Memory: 31kB
   ->  Nested Loop  (cost=58055.66..144365.59 rows=1 width=94) (actual time=6132.403..31894.233 rows=43 loops=1)
         ->  Nested Loop  (cost=58055.51..144364.21 rows=1 width=62) (actual time=1226.085..30337.921 rows=837792 loops=1)
               ->  Nested Loop Left Join  (cost=58055.09..144360.38 rows=1 width=59) (actual time=1062.414..12471.456 rows=1487153 loops=1)
                     ->  Hash Join  (cost=58054.80..144359.69 rows=1 width=66) (actual time=1061.330..6635.041 rows=1487153 loops=1)
                           Hash Cond: (("_EN".unique_system_identifier = "_AM".unique_system_identifier) AND ("_EN".callsign = "_AM".callsign))
                           ->  Hash Join  (cost=3.33..53349.72 rows=1033046 width=51) (actual time=2.151..3433.178 rows=1487153 loops=1)
                                 Hash Cond: (("_EN".country_id = "_GovtRegion".country_id) AND ("_EN".state = "_GovtRegion".territory_id))
                                 ->  Seq Scan on "_EN"  (cost=0.00..45288.05 rows=1509005 width=60) (actual time=0.037..2737.054 rows=1508736 loops=1)
                                 ->  Hash  (cost=1.93..1.93 rows=93 width=7) (actual time=0.706..1.264 rows=88 loops=1)
                                       Buckets: 1024  Batches: 1  Memory Usage: 12kB
                                       ->  Seq Scan on "_GovtRegion"  (cost=0.00..1.93 rows=93 width=7) (actual time=0.013..0.577 rows=93 loops=1)
                           ->  Hash  (cost=28093.99..28093.99 rows=1506699 width=15) (actual time=1055.587..1055.588 rows=1506474 loops=1)
                                 Buckets: 131072  Batches: 32  Memory Usage: 3175kB
                                 ->  Seq Scan on "_AM"  (cost=0.00..28093.99 rows=1506699 width=15) (actual time=0.009..742.774 rows=1506474 loops=1)
                     ->  Nested Loop  (cost=0.29..0.68 rows=1 width=7) (actual time=0.003..0.004 rows=1 loops=1487153)
                           Join Filter: ("_IsoCountry".iso_alpha2 = "_Territory".country_id)
                           Rows Removed by Join Filter: 0
                           ->  Index Only Scan using "_IsoCountry_iso_alpha2_key" on "_IsoCountry"  (cost=0.14..0.38 rows=1 width=3) (actual time=0.001..0.002 rows=1 loops=1487153)
                                 Index Cond: (iso_alpha2 = "_GovtRegion".country_id)
                                 Heap Fetches: 1487153
                           ->  Index Only Scan using "_Territory_pkey" on "_Territory"  (cost=0.14..0.29 rows=1 width=7) (actual time=0.001..0.001 rows=1 loops=1487153)
                                 Index Cond: (territory_id = "_GovtRegion".territory_id)
                                 Heap Fetches: 1550706
               ->  Index Scan using "_HD_pkey" on "_HD"  (cost=0.43..3.82 rows=1 width=15) (actual time=0.012..0.012 rows=1 loops=1487153)
                     Index Cond: (unique_system_identifier = "_EN".unique_system_identifier)
                     Filter: (("_EN".callsign = callsign) AND (((((license_status)::text || ' - '::text) || (COALESCE((SubPlan 2), '???'::character varying))::text))::character(1) = 'A'::bpchar))
                     Rows Removed by Filter: 0
                     SubPlan 2
                       ->  Limit  (cost=0.00..1.07 rows=1 width=13) (actual time=0.001..0.001 rows=1 loops=1487153)
                             ->  Seq Scan on "_LicStatus"  (cost=0.00..1.07 rows=1 width=13) (actual time=0.000..0.000 rows=1 loops=1487153)
                                   Filter: ("_HD".license_status = status_id)
                                   Rows Removed by Filter: 1
         ->  Index Scan using "_Club_pkey" on "_Club"  (cost=0.14..0.17 rows=1 width=35) (actual time=0.002..0.002 rows=0 loops=837792)
               Index Cond: (trustee_callsign = "_EN".callsign)
               Filter: (club_count >= 5)
               Rows Removed by Filter: 0
         SubPlan 1
           ->  Limit  (cost=0.00..1.20 rows=1 width=15) (actual time=0.060..0.060 rows=1 loops=43)
                 ->  Seq Scan on "_ApplicantType"  (cost=0.00..1.20 rows=1 width=15) (actual time=0.016..0.016 rows=1 loops=43)
                       Filter: ("_EN".applicant_type_code = app_type_id)
                       Rows Removed by Filter: 7
 Planning Time: 173.753 ms
 Execution Time: 31919.601 ms
(46 rows)


VIEW genclub_multi_:

=> \d+ genclub_multi_
                                   View "Callsign.genclub_multi_"
      Column      |          Type          | Collation | Nullable | Default | Storage  | Description
------------------+------------------------+-----------+----------+---------+----------+-------------
 sys_id           | integer                |           |          |         | plain    |
 callsign         | character(10)          |           |          |         | extended |
 fcc_reg_num      | character(10)          |           |          |         | extended |
 licensee_id      | character(9)           |           |          |         | extended |
 subgroup_id_num  | character(3)           |           |          |         | extended |
 applicant_type   | text                   |           |          |         | extended |
 entity_type      | text                   |           |          |         | extended |
 entity_name      | character varying(200) |           |          |         | extended |
 attention        | character varying(35)  |           |          |         | extended |
 first_name       | character varying(20)  |           |          |         | extended |
 middle_init      | character(1)           |           |          |         | extended |
 last_name        | character varying(20)  |           |          |         | extended |
 name_suffix      | character(3)           |           |          |         | extended |
 street_address   | character varying(60)  |           |          |         | extended |
 po_box           | text                   |           |          |         | extended |
 locality         | character varying      |           |          |         | extended |
 locality_        | character varying      |           |          |         | extended |
 county           | character varying      |           |          |         | extended |
 state            | text                   |           |          |         | extended |
 postal_code      | text                   |           |          |         | extended |
 full_name        | text                   |           |          |         | extended |
 _entity_name     | text                   |           |          |         | extended |
 _first_name      | text                   |           |          |         | extended |
 _last_name       | text                   |           |          |         | extended |
 zip5             | character(5)           |           |          |         | extended |
 zip_location     | "GeoPosition"          |           |          |         | extended |
 maidenhead       | bpchar                 |           |          |         | extended |
 geo_region       | smallint               |           |          |         | plain    |
 uls_file_num     | character(14)          |           |          |         | extended |
 radio_service    | text                   |           |          |         | extended |
 license_status   | text                   |           |          |         | extended |
 grant_date       | date                   |           |          |         | plain    |
 effective_date   | date                   |           |          |         | plain    |
 cancel_date      | date                   |           |          |         | plain    |
 expire_date      | date                   |           |          |         | plain    |
 end_date         | date                   |           |          |         | plain    |
 available_date   | date                   |           |          |         | plain    |
 last_action_date | date                   |           |          |         | plain    |
 uls_region       | "MySql".tinyint        |           |          |         | plain    |
 callsign_group   | text                   |           |          |         | extended |
 operator_group   | text                   |           |          |         | extended |
 operator_class   | text                   |           |          |         | extended |
 prev_class       | text                   |           |          |         | extended |
 prev_callsign    | character(10)          |           |          |         | extended |
 vanity_type      | text                   |           |          |         | extended |
 is_trustee       | character(1)           |           |          |         | extended |
 trustee_callsign | character(10)          |           |          |         | extended |
 trustee_name     | character varying(50)  |           |          |         | extended |
 validity         | integer                |           |          |         | plain    |
 club_count       | bigint                 |           |          |         | plain    |
 extra_count      | bigint                 |           |          |         | plain    |
 region_count     | bigint                 |           |          |         | plain    |
View definition:
 SELECT licjb_.sys_id,
    licjb_.callsign,
    licjb_.fcc_reg_num,
    licjb_.licensee_id,
    licjb_.subgroup_id_num,
    licjb_.applicant_type,
    licjb_.entity_type,
    licjb_.entity_name,
    licjb_.attention,
    licjb_.first_name,
    licjb_.middle_init,
    licjb_.last_name,
    licjb_.name_suffix,
    licjb_.street_address,
    licjb_.po_box,
    licjb_.locality,
    licjb_.locality_,
    licjb_.county,
    licjb_.state,
    licjb_.postal_code,
    licjb_.full_name,
    licjb_._entity_name,
    licjb_._first_name,
    licjb_._last_name,
    licjb_.zip5,
    licjb_.zip_location,
    licjb_.maidenhead,
    licjb_.geo_region,
    licjb_.uls_file_num,
    licjb_.radio_service,
    licjb_.license_status,
    licjb_.grant_date,
    licjb_.effective_date,
    licjb_.cancel_date,
    licjb_.expire_date,
    licjb_.end_date,
    licjb_.available_date,
    licjb_.last_action_date,
    licjb_.uls_region,
    licjb_.callsign_group,
    licjb_.operator_group,
    licjb_.operator_class,
    licjb_.prev_class,
    licjb_.prev_callsign,
    licjb_.vanity_type,
    licjb_.is_trustee,
    licjb_.trustee_callsign,
    licjb_.trustee_name,
    licjb_.validity,
    gen.club_count,
    gen.extra_count,
    gen.region_count
   FROM licjb_,
    "GenLicClub" gen
  WHERE licjb_.callsign = gen.trustee_callsign AND licjb_.license_status::character(1) = 'A'::bpchar;

VIEW GenLicClub:

=> \d+ "GenLicClub"
                                 View "Callsign.GenLicClub"
      Column      |     Type      | Collation | Nullable | Default | Storage  | Description
------------------+---------------+-----------+----------+---------+----------+-------------
 trustee_callsign | character(10) |           |          |         | extended |
 club_count       | bigint        |           |          |         | plain    |
 extra_count      | bigint        |           |          |         | plain    |
 region_count     | bigint        |           |          |         | plain    |
View definition:
 SELECT "_Club".trustee_callsign,
    "_Club".club_count,
    "_Club".extra_count,
    "_Club".region_count
   FROM "GenLic"."_Club";

TABLE "GenLic"."_Club":

=> \d+ "GenLic"."_Club"
                                           Table "GenLic._Club"
      Column      |     Type      | Collation | Nullable | Default | Storage  | Stats target | Description
------------------+---------------+-----------+----------+---------+----------+--------------+-------------
 trustee_callsign | character(10) |           | not null |         | extended |              |
 club_count       | bigint        |           |          |         | plain    |              |
 extra_count      | bigint        |           |          |         | plain    |              |
 region_count     | bigint        |           |          |         | plain    |              |
Indexes:
    "_Club_pkey" PRIMARY KEY, btree (trustee_callsign)

VIEW licjb_:

=> \d+ licjb_
                                       View "Callsign.licjb_"
      Column      |          Type          | Collation | Nullable | Default | Storage  | Description
------------------+------------------------+-----------+----------+---------+----------+-------------
 sys_id           | integer                |           |          |         | plain    |
 callsign         | character(10)          |           |          |         | extended |
 fcc_reg_num      | character(10)          |           |          |         | extended |
 licensee_id      | character(9)           |           |          |         | extended |
 subgroup_id_num  | character(3)           |           |          |         | extended |
 applicant_type   | text                   |           |          |         | extended |
 entity_type      | text                   |           |          |         | extended |
 entity_name      | character varying(200) |           |          |         | extended |
 attention        | character varying(35)  |           |          |         | extended |
 first_name       | character varying(20)  |           |          |         | extended |
 middle_init      | character(1)           |           |          |         | extended |
 last_name        | character varying(20)  |           |          |         | extended |
 name_suffix      | character(3)           |           |          |         | extended |
 street_address   | character varying(60)  |           |          |         | extended |
 po_box           | text                   |           |          |         | extended |
 locality         | character varying      |           |          |         | extended |
 locality_        | character varying      |           |          |         | extended |
 county           | character varying      |           |          |         | extended |
 state            | text                   |           |          |         | extended |
 postal_code      | text                   |           |          |         | extended |
 full_name        | text                   |           |          |         | extended |
 _entity_name     | text                   |           |          |         | extended |
 _first_name      | text                   |           |          |         | extended |
 _last_name       | text                   |           |          |         | extended |
 zip5             | character(5)           |           |          |         | extended |
 zip_location     | "GeoPosition"          |           |          |         | extended |
 maidenhead       | bpchar                 |           |          |         | extended |
 geo_region       | smallint               |           |          |         | plain    |
 uls_file_num     | character(14)          |           |          |         | extended |
 radio_service    | text                   |           |          |         | extended |
 license_status   | text                   |           |          |         | extended |
 grant_date       | date                   |           |          |         | plain    |
 effective_date   | date                   |           |          |         | plain    |
 cancel_date      | date                   |           |          |         | plain    |
 expire_date      | date                   |           |          |         | plain    |
 end_date         | date                   |           |          |         | plain    |
 available_date   | date                   |           |          |         | plain    |
 last_action_date | date                   |           |          |         | plain    |
 uls_region       | "MySql".tinyint        |           |          |         | plain    |
 callsign_group   | text                   |           |          |         | extended |
 operator_group   | text                   |           |          |         | extended |
 operator_class   | text                   |           |          |         | extended |
 prev_class       | text                   |           |          |         | extended |
 prev_callsign    | character(10)          |           |          |         | extended |
 vanity_type      | text                   |           |          |         | extended |
 is_trustee       | character(1)           |           |          |         | extended |
 trustee_callsign | character(10)          |           |          |         | extended |
 trustee_name     | character varying(50)  |           |          |         | extended |
 validity         | integer                |           |          |         | plain    |
View definition:
 SELECT lic_en_.sys_id,
    lic_en_.callsign,
    lic_en_.fcc_reg_num,
    lic_en_.licensee_id,
    lic_en_.subgroup_id_num,
    lic_en_.applicant_type,
    lic_en_.entity_type,
    lic_en_.entity_name,
    lic_en_.attention,
    lic_en_.first_name,
    lic_en_.middle_init,
    lic_en_.last_name,
    lic_en_.name_suffix,
    lic_en_.street_address,
    lic_en_.po_box,
    lic_en_.locality,
    lic_en_.locality_,
    lic_en_.county,
    lic_en_.state,
    lic_en_.postal_code,
    lic_en_.full_name,
    lic_en_._entity_name,
    lic_en_._first_name,
    lic_en_._last_name,
    lic_en_.zip5,
    lic_en_.zip_location,
    lic_en_.maidenhead,
    lic_en_.geo_region,
    lic_hd_.uls_file_num,
    lic_hd_.radio_service,
    lic_hd_.license_status,
    lic_hd_.grant_date,
    lic_hd_.effective_date,
    lic_hd_.cancel_date,
    lic_hd_.expire_date,
    lic_hd_.end_date,
    lic_hd_.available_date,
    lic_hd_.last_action_date,
    lic_am_.uls_region,
    lic_am_.callsign_group,
    lic_am_.operator_group,
    lic_am_.operator_class,
    lic_am_.prev_class,
    lic_am_.prev_callsign,
    lic_am_.vanity_type,
    lic_am_.is_trustee,
    lic_am_.trustee_callsign,
    lic_am_.trustee_name,
        CASE
            WHEN lic_am_.vanity_type::character(1) = ANY (ARRAY['A'::bpchar, 'C'::bpchar]) THEN verify_callsign(lic_en_.callsign, lic_en_.licensee_id, lic_hd_.grant_date, lic_en_.state::bpchar, lic_am_.operator_class::bpchar, lic_en_.applicant_type::bpchar, lic_am_.trustee_callsign)
            ELSE NULL::integer
        END AS validity
   FROM lic_en_
     JOIN lic_hd_ USING (sys_id, callsign)
     JOIN lic_am_ USING (sys_id, callsign);

VIEW lic_en_:

=> \d+ lic_en_
                                      View "Callsign.lic_en_"
     Column      |          Type          | Collation | Nullable | Default | Storage  | Description
-----------------+------------------------+-----------+----------+---------+----------+-------------
 sys_id          | integer                |           |          |         | plain    |
 callsign        | character(10)          |           |          |         | extended |
 fcc_reg_num     | character(10)          |           |          |         | extended |
 licensee_id     | character(9)           |           |          |         | extended |
 subgroup_id_num | character(3)           |           |          |         | extended |
 applicant_type  | text                   |           |          |         | extended |
 entity_type     | text                   |           |          |         | extended |
 entity_name     | character varying(200) |           |          |         | extended |
 attention       | character varying(35)  |           |          |         | extended |
 first_name      | character varying(20)  |           |          |         | extended |
 middle_init     | character(1)           |           |          |         | extended |
 last_name       | character varying(20)  |           |          |         | extended |
 name_suffix     | character(3)           |           |          |         | extended |
 street_address  | character varying(60)  |           |          |         | extended |
 po_box          | text                   |           |          |         | extended |
 locality        | character varying      |           |          |         | extended |
 locality_       | character varying      |           |          |         | extended |
 county          | character varying      |           |          |         | extended |
 state           | text                   |           |          |         | extended |
 postal_code     | text                   |           |          |         | extended |
 full_name       | text                   |           |          |         | extended |
 _entity_name    | text                   |           |          |         | extended |
 _first_name     | text                   |           |          |         | extended |
 _last_name      | text                   |           |          |         | extended |
 zip5            | character(5)           |           |          |         | extended |
 zip_location    | "GeoPosition"          |           |          |         | extended |
 maidenhead      | bpchar                 |           |          |         | extended |
 geo_region      | smallint               |           |          |         | plain    |
View definition:
 SELECT lic_en.sys_id,
    lic_en.callsign,
    lic_en.fcc_reg_num,
    lic_en.licensee_id,
    lic_en.subgroup_id_num,
    (lic_en.applicant_type::text || ' - '::text) || COALESCE(( SELECT "ApplicantType".app_type_text
           FROM "ApplicantType"
          WHERE lic_en.applicant_type = "ApplicantType".app_type_id
         LIMIT 1), '???'::character varying)::text AS applicant_type,
    (lic_en.entity_type::text || ' - '::text) || COALESCE(( SELECT "EntityType".entity_text
           FROM "EntityType"
          WHERE lic_en.entity_type = "EntityType".entity_id
         LIMIT 1), '???'::character varying)::text AS entity_type,
    lic_en.entity_name,
    lic_en.attention,
    lic_en.first_name,
    lic_en.middle_init,
    lic_en.last_name,
    lic_en.name_suffix,
    lic_en.street_address,
    lic_en.po_box,
    lic_en.locality,
    zip_code.locality_text AS locality_,
    "County".county_text AS county,
    (territory_id::text || ' - '::text) || COALESCE(govt_region.territory_text, '???'::character varying)::text AS state,
    zip9_format(lic_en.postal_code::text) AS postal_code,
    lic_en.full_name,
    lic_en._entity_name,
    lic_en._first_name,
    lic_en._last_name,
    lic_en.zip5,
    zip_code.zip_location,
    maidenhead(zip_code.zip_location) AS maidenhead,
    govt_region.geo_region
   FROM lic_en
     JOIN govt_region USING (territory_id, country_id)
     LEFT JOIN zip_code USING (territory_id, country_id, zip5)
     LEFT JOIN "County" USING (territory_id, country_id, fips_county);

VIEW lic_en:

=> \d+ lic_en
                                       View "Callsign.lic_en"
     Column      |          Type          | Collation | Nullable | Default | Storage  | Description
-----------------+------------------------+-----------+----------+---------+----------+-------------
 sys_id          | integer                |           |          |         | plain    |
 callsign        | character(10)          |           |          |         | extended |
 fcc_reg_num     | character(10)          |           |          |         | extended |
 licensee_id     | character(9)           |           |          |         | extended |
 subgroup_id_num | character(3)           |           |          |         | extended |
 applicant_type  | character(1)           |           |          |         | extended |
 entity_type     | character(2)           |           |          |         | extended |
 entity_name     | character varying(200) |           |          |         | extended |
 attention       | character varying(35)  |           |          |         | extended |
 first_name      | character varying(20)  |           |          |         | extended |
 middle_init     | character(1)           |           |          |         | extended |
 last_name       | character varying(20)  |           |          |         | extended |
 name_suffix     | character(3)           |           |          |         | extended |
 street_address  | character varying(60)  |           |          |         | extended |
 po_box          | text                   |           |          |         | extended |
 locality        | character varying      |           |          |         | extended |
 territory_id    | character(2)           |           |          |         | extended |
 postal_code     | character(9)           |           |          |         | extended |
 full_name       | text                   |           |          |         | extended |
 _entity_name    | text                   |           |          |         | extended |
 _first_name     | text                   |           |          |         | extended |
 _last_name      | text                   |           |          |         | extended |
 zip5            | character(5)           |           |          |         | extended |
 country_id      | character(2)           |           |          |         | extended |
View definition:
 SELECT _lic_en.sys_id,
    _lic_en.callsign,
    _lic_en.fcc_reg_num,
    _lic_en.licensee_id,
    _lic_en.subgroup_id_num,
    _lic_en.applicant_type,
    _lic_en.entity_type,
    _lic_en.entity_name,
    _lic_en.attention,
    _lic_en.first_name,
    _lic_en.middle_init,
    _lic_en.last_name,
    _lic_en.name_suffix,
    _lic_en.street_address,
    _lic_en.po_box,
    _lic_en.locality,
    _lic_en.territory_id,
    _lic_en.postal_code,
    _lic_en.full_name,
    _lic_en._entity_name,
    _lic_en._first_name,
    _lic_en._last_name,
    _lic_en.zip5,
    _lic_en.country_id
   FROM _lic_en;

VIEW _lic_en:

=> \d+ _lic_en
                                      View "Callsign._lic_en"
     Column      |          Type          | Collation | Nullable | Default | Storage  | Description
-----------------+------------------------+-----------+----------+---------+----------+-------------
 sys_id          | integer                |           |          |         | plain    |
 callsign        | character(10)          |           |          |         | extended |
 fcc_reg_num     | character(10)          |           |          |         | extended |
 licensee_id     | character(9)           |           |          |         | extended |
 subgroup_id_num | character(3)           |           |          |         | extended |
 applicant_type  | character(1)           |           |          |         | extended |
 entity_type     | character(2)           |           |          |         | extended |
 entity_name     | character varying(200) |           |          |         | extended |
 attention       | character varying(35)  |           |          |         | extended |
 first_name      | character varying(20)  |           |          |         | extended |
 middle_init     | character(1)           |           |          |         | extended |
 last_name       | character varying(20)  |           |          |         | extended |
 name_suffix     | character(3)           |           |          |         | extended |
 street_address  | character varying(60)  |           |          |         | extended |
 po_box          | text                   |           |          |         | extended |
 locality        | character varying      |           |          |         | extended |
 territory_id    | character(2)           |           |          |         | extended |
 postal_code     | character(9)           |           |          |         | extended |
 full_name       | text                   |           |          |         | extended |
 _entity_name    | text                   |           |          |         | extended |
 _first_name     | text                   |           |          |         | extended |
 _last_name      | text                   |           |          |         | extended |
 zip5            | character(5)           |           |          |         | extended |
 country_id      | character(2)           |           |          |         | extended |
View definition:
 SELECT "_EN".unique_system_identifier AS sys_id,
    "_EN".callsign,
    "_EN".frn AS fcc_reg_num,
    "_EN".licensee_id,
    "_EN".sgin AS subgroup_id_num,
    "_EN".applicant_type_code AS applicant_type,
    "_EN".entity_type,
    "_EN".entity_name,
    "_EN".attention_line AS attention,
    "_EN".first_name,
    "_EN".mi AS middle_init,
    "_EN".last_name,
    "_EN".suffix AS name_suffix,
    "_EN".street_address,
    po_box_format("_EN".po_box::text) AS po_box,
    "_EN".city AS locality,
    "_EN".state AS territory_id,
    "_EN".zip_code AS postal_code,
    initcap(((COALESCE("_EN".first_name::text || ' '::text, ''::text) || COALESCE("_EN".mi::text || ' '::text, ''::text)) || "_EN".last_name::text) || COALESCE(' '::text || "_EN".suffix::text, ''::text)) AS full_name,
    initcap("_EN".entity_name::text) AS _entity_name,
    initcap("_EN".first_name::text) AS _first_name,
    initcap("_EN".last_name::text) AS _last_name,
    "_EN".zip_code::character(5) AS zip5,
    "_EN".country_id
   FROM "UlsLic"."_EN";

TABLE "UlsLic"."_EN":

=> \d+ "UlsLic"."_EN"
                                                     Table "UlsLic._EN"
          Column          |          Type          | Collation | Nullable | Default | Storage  | Stats target | Description
--------------------------+------------------------+-----------+----------+---------+----------+--------------+-------------
 record_type              | character(2)           |           | not null |         | extended |              |
 unique_system_identifier | integer                |           | not null |         | plain    |              |
 uls_file_number          | character(14)          |           |          |         | extended |              |
 ebf_number               | character varying(30)  |           |          |         | extended |              |
 callsign                 | character(10)          |           |          |         | extended |              |
 entity_type              | character(2)           |           |          |         | extended |              |
 licensee_id              | character(9)           |           |          |         | extended |              |
 entity_name              | character varying(200) |           |          |         | extended |              |
 first_name               | character varying(20)  |           |          |         | extended |              |
 mi                       | character(1)           |           |          |         | extended |              |
 last_name                | character varying(20)  |           |          |         | extended |              |
 suffix                   | character(3)           |           |          |         | extended |              |
 phone                    | character(10)          |           |          |         | extended |              |
 fax                      | character(10)          |           |          |         | extended |              |
 email                    | character varying(50)  |           |          |         | extended |              |
 street_address           | character varying(60)  |           |          |         | extended |              |
 city                     | character varying      |           |          |         | extended |              |
 state                    | character(2)           |           |          |         | extended |              |
 zip_code                 | character(9)           |           |          |         | extended |              |
 po_box                   | character varying(20)  |           |          |         | extended |              |
 attention_line           | character varying(35)  |           |          |         | extended |              |
 sgin                     | character(3)           |           |          |         | extended |              |
 frn                      | character(10)          |           |          |         | extended |              |
 applicant_type_code      | character(1)           |           |          |         | extended |              |
 applicant_type_other     | character(40)          |           |          |         | extended |              |
 status_code              | character(1)           |           |          |         | extended |              |
 status_date              | "MySql".datetime       |           |          |         | plain    |              |
 lic_category_code        | character(1)           |           |          |         | extended |              |
 linked_license_id        | numeric(9,0)           |           |          |         | main     |              |
 linked_callsign          | character(10)          |           |          |         | extended |              |
 country_id               | character(2)           |           |          |         | extended |              |
Indexes:
    "_EN_pkey" PRIMARY KEY, btree (unique_system_identifier)
    "_EN__entity_name" btree (initcap(entity_name::text))
    "_EN__first_name" btree (initcap(first_name::text))
    "_EN__last_name" btree (initcap(last_name::text))
    "_EN__zip5" btree ((zip_code::character(5)))
    "_EN_callsign" btree (callsign)
    "_EN_fcc_reg_num" btree (frn)
    "_EN_licensee_id" btree (licensee_id)
Check constraints:
    "_EN_record_type_check" CHECK (record_type = 'EN'::bpchar)
Foreign-key constraints:
    "_EN_applicant_type_code_fkey" FOREIGN KEY (applicant_type_code) REFERENCES "FccLookup"."_ApplicantType"(app_type_id
)
    "_EN_entity_type_fkey" FOREIGN KEY (entity_type) REFERENCES "FccLookup"."_EntityType"(entity_id)
    "_EN_state_fkey" FOREIGN KEY (state, country_id) REFERENCES "BaseLookup"."_Territory"(territory_id, country_id)
    "_EN_unique_system_identifier_fkey" FOREIGN KEY (unique_system_identifier) REFERENCES "UlsLic"."_HD"(unique_system_i
dentifier) ON UPDATE CASCADE ON DELETE CASCADE


VIEW lic_hd_:

=> \d+ lic_hd_
                                  View "Callsign.lic_hd_"
      Column      |     Type      | Collation | Nullable | Default | Storage  | Description
------------------+---------------+-----------+----------+---------+----------+-------------
 sys_id           | integer       |           |          |         | plain    |
 callsign         | character(10) |           |          |         | extended |
 uls_file_num     | character(14) |           |          |         | extended |
 radio_service    | text          |           |          |         | extended |
 license_status   | text          |           |          |         | extended |
 grant_date       | date          |           |          |         | plain    |
 effective_date   | date          |           |          |         | plain    |
 cancel_date      | date          |           |          |         | plain    |
 expire_date      | date          |           |          |         | plain    |
 end_date         | date          |           |          |         | plain    |
 available_date   | date          |           |          |         | plain    |
 last_action_date | date          |           |          |         | plain    |
View definition:
 SELECT lic_hd.sys_id,
    lic_hd.callsign,
    lic_hd.uls_file_num,
    (lic_hd.radio_service::text || ' - '::text) || COALESCE(( SELECT "RadioService".service_text
           FROM "RadioService"
          WHERE lic_hd.radio_service = "RadioService".service_id
         LIMIT 1), '???'::character varying)::text AS radio_service,
    (lic_hd.license_status::text || ' - '::text) || COALESCE(( SELECT "LicStatus".status_text
           FROM "LicStatus"
          WHERE lic_hd.license_status = "LicStatus".status_id
         LIMIT 1), '???'::character varying)::text AS license_status,
    lic_hd.grant_date,
    lic_hd.effective_date,
    lic_hd.cancel_date,
    lic_hd.expire_date,
    LEAST(lic_hd.cancel_date, lic_hd.expire_date) AS end_date,
        CASE
            WHEN lic_hd.cancel_date < lic_hd.expire_date THEN GREATEST((lic_hd.cancel_date + '2 years'::interval)::date, lic_hd.last_action_date + 30)
            WHEN lic_hd.license_status = 'A'::bpchar AND uls_date() > (lic_hd.expire_date + '2 years'::interval)::date THEN NULL::date
            ELSE (lic_hd.expire_date + '2 years'::interval)::date
        END + 1 AS available_date,
    lic_hd.last_action_date
   FROM lic_hd;

VIEW lic_hd:

=> \d+ lic_hd
                                   View "Callsign.lic_hd"
      Column      |     Type      | Collation | Nullable | Default | Storage  | Description
------------------+---------------+-----------+----------+---------+----------+-------------
 sys_id           | integer       |           |          |         | plain    |
 callsign         | character(10) |           |          |         | extended |
 uls_file_num     | character(14) |           |          |         | extended |
 radio_service    | character(2)  |           |          |         | extended |
 license_status   | character(1)  |           |          |         | extended |
 grant_date       | date          |           |          |         | plain    |
 effective_date   | date          |           |          |         | plain    |
 cancel_date      | date          |           |          |         | plain    |
 expire_date      | date          |           |          |         | plain    |
 last_action_date | date          |           |          |         | plain    |
View definition:
 SELECT _lic_hd.sys_id,
    _lic_hd.callsign,
    _lic_hd.uls_file_num,
    _lic_hd.radio_service,
    _lic_hd.license_status,
    _lic_hd.grant_date,
    _lic_hd.effective_date,
    _lic_hd.cancel_date,
    _lic_hd.expire_date,
    _lic_hd.last_action_date
   FROM _lic_hd;

VIEW _lic_hd:

=> \d+ _lic_hd
                                  View "Callsign._lic_hd"
      Column      |     Type      | Collation | Nullable | Default | Storage  | Description
------------------+---------------+-----------+----------+---------+----------+-------------
 sys_id           | integer       |           |          |         | plain    |
 callsign         | character(10) |           |          |         | extended |
 uls_file_num     | character(14) |           |          |         | extended |
 radio_service    | character(2)  |           |          |         | extended |
 license_status   | character(1)  |           |          |         | extended |
 grant_date       | date          |           |          |         | plain    |
 effective_date   | date          |           |          |         | plain    |
 cancel_date      | date          |           |          |         | plain    |
 expire_date      | date          |           |          |         | plain    |
 last_action_date | date          |           |          |         | plain    |
View definition:
 SELECT "_HD".unique_system_identifier AS sys_id,
    "_HD".callsign,
    "_HD".uls_file_number AS uls_file_num,
    "_HD".radio_service_code AS radio_service,
    "_HD".license_status,
    "_HD".grant_date,
    "_HD".effective_date,
    "_HD".cancellation_date AS cancel_date,
    "_HD".expired_date AS expire_date,
    "_HD".last_action_date
   FROM "UlsLic"."_HD";

TABLE  "UlsLic"."_HD":

=> \d+ "UlsLic"."_HD"
                                                      Table "UlsLic._HD"
            Column            |         Type          | Collation | Nullable | Default | Storage  | Stats target | Descr
iption
------------------------------+-----------------------+-----------+----------+---------+----------+--------------+------
-------
 record_type                  | character(2)          |           | not null |         | extended |              |
 unique_system_identifier     | integer               |           | not null |         | plain    |              |
 uls_file_number              | character(14)         |           |          |         | extended |              |
 ebf_number                   | character varying(30) |           |          |         | extended |              |
 callsign                     | character(10)         |           |          |         | extended |              |
 license_status               | character(1)          |           |          |         | extended |              |
 radio_service_code           | character(2)          |           |          |         | extended |              |
 grant_date                   | date                  |           |          |         | plain    |              |
 expired_date                 | date                  |           |          |         | plain    |              |
 cancellation_date            | date                  |           |          |         | plain    |              |
 eligibility_rule_num         | character(10)         |           |          |         | extended |              |
 applicant_type_code_reserved | character(1)          |           |          |         | extended |              |
 alien                        | character(1)          |           |          |         | extended |              |
 alien_government             | character(1)          |           |          |         | extended |              |
 alien_corporation            | character(1)          |           |          |         | extended |              |
 alien_officer                | character(1)          |           |          |         | extended |              |
 alien_control                | character(1)          |           |          |         | extended |              |
 revoked                      | character(1)          |           |          |         | extended |              |
 convicted                    | character(1)          |           |          |         | extended |              |
 adjudged                     | character(1)          |           |          |         | extended |              |
 involved_reserved            | character(1)          |           |          |         | extended |              |
 common_carrier               | character(1)          |           |          |         | extended |              |
 non_common_carrier           | character(1)          |           |          |         | extended |              |
 private_comm                 | character(1)          |           |          |         | extended |              |
 fixed                        | character(1)          |           |          |         | extended |              |
 mobile                       | character(1)          |           |          |         | extended |              |
 radiolocation                | character(1)          |           |          |         | extended |              |
 satellite                    | character(1)          |           |          |         | extended |              |
 developmental_or_sta         | character(1)          |           |          |         | extended |              |
 interconnected_service       | character(1)          |           |          |         | extended |              |
 certifier_first_name         | character varying(20) |           |          |         | extended |              |
 certifier_mi                 | character varying     |           |          |         | extended |              |
 certifier_last_name          | character varying     |           |          |         | extended |              |
 certifier_suffix             | character(3)          |           |          |         | extended |              |
 certifier_title              | character(40)         |           |          |         | extended |              |
 gender                       | character(1)          |           |          |         | extended |              |
 african_american             | character(1)          |           |          |         | extended |              |
 native_american              | character(1)          |           |          |         | extended |              |
 hawaiian                     | character(1)          |           |          |         | extended |              |
 asian                        | character(1)          |           |          |         | extended |              |
 white                        | character(1)          |           |          |         | extended |              |
 ethnicity                    | character(1)          |           |          |         | extended |              |
 effective_date               | date                  |           |          |         | plain    |              |
 last_action_date             | date                  |           |          |         | plain    |              |
 auction_id                   | integer               |           |          |         | plain    |              |
 reg_stat_broad_serv          | character(1)          |           |          |         | extended |              |
 band_manager                 | character(1)          |           |          |         | extended |              |
 type_serv_broad_serv         | character(1)          |           |          |         | extended |              |
 alien_ruling                 | character(1)          |           |          |         | extended |              |
 licensee_name_change         | character(1)          |           |          |         | extended |              |
 whitespace_ind               | character(1)          |           |          |         | extended |              |
 additional_cert_choice       | character(1)          |           |          |         | extended |              |
 additional_cert_answer       | character(1)          |           |          |         | extended |              |
 discontinuation_ind          | character(1)          |           |          |         | extended |              |
 regulatory_compliance_ind    | character(1)          |           |          |         | extended |              |
 dummy1                       | character varying     |           |          |         | extended |              |
 dummy2                       | character varying     |           |          |         | extended |              |
 dummy3                       | character varying     |           |          |         | extended |              |
 dummy4                       | character varying     |           |          |         | extended |              |
Indexes:
    "_HD_pkey" PRIMARY KEY, btree (unique_system_identifier)
    "_HD_callsign" btree (callsign)
    "_HD_grant_date" btree (grant_date)
    "_HD_last_action_date" btree (last_action_date)
    "_HD_uls_file_num" btree (uls_file_number)
Check constraints:
    "_HD_record_type_check" CHECK (record_type = 'HD'::bpchar)
Foreign-key constraints:
    "_HD_license_status_fkey" FOREIGN KEY (license_status) REFERENCES "FccLookup"."_LicStatus"(status_id)
    "_HD_radio_service_code_fkey" FOREIGN KEY (radio_service_code) REFERENCES "FccLookup"."_RadioService"(service_id)
Referenced by:
    TABLE ""UlsLic"."_AM"" CONSTRAINT "_AM_unique_system_identifier_fkey" FOREIGN KEY (unique_system_identifier) REFEREN
CES "UlsLic"."_HD"(unique_system_identifier) ON UPDATE CASCADE ON DELETE CASCADE
    TABLE ""UlsLic"."_CO"" CONSTRAINT "_CO_unique_system_identifier_fkey" FOREIGN KEY (unique_system_identifier) REFEREN
CES "UlsLic"."_HD"(unique_system_identifier) ON UPDATE CASCADE ON DELETE CASCADE
    TABLE ""UlsLic"."_EN"" CONSTRAINT "_EN_unique_system_identifier_fkey" FOREIGN KEY (unique_system_identifier) REFEREN
CES "UlsLic"."_HD"(unique_system_identifier) ON UPDATE CASCADE ON DELETE CASCADE
    TABLE ""UlsLic"."_HS"" CONSTRAINT "_HS_unique_system_identifier_fkey" FOREIGN KEY (unique_system_identifier) REFEREN
CES "UlsLic"."_HD"(unique_system_identifier) ON UPDATE CASCADE ON DELETE CASCADE
    TABLE ""UlsLic"."_LA"" CONSTRAINT "_LA_unique_system_identifier_fkey" FOREIGN KEY (unique_system_identifier) REFEREN
CES "UlsLic"."_HD"(unique_system_identifier) ON UPDATE CASCADE ON DELETE CASCADE
    TABLE ""UlsLic"."_SC"" CONSTRAINT "_SC_unique_system_identifier_fkey" FOREIGN KEY (unique_system_identifier) REFEREN
CES "UlsLic"."_HD"(unique_system_identifier) ON UPDATE CASCADE ON DELETE CASCADE
    TABLE ""UlsLic"."_SF"" CONSTRAINT "_SF_unique_system_identifier_fkey" FOREIGN KEY (unique_system_identifier) REFEREN
CES "UlsLic"."_HD"(unique_system_identifier) ON UPDATE CASCADE ON DELETE CASCADE

VIEW lic_am_:

=> \d+ lic_am_
                                      View "Callsign.lic_am_"
      Column      |         Type          | Collation | Nullable | Default | Storage  | Description
------------------+-----------------------+-----------+----------+---------+----------+-------------
 sys_id           | integer               |           |          |         | plain    |
 callsign         | character(10)         |           |          |         | extended |
 uls_region       | "MySql".tinyint       |           |          |         | plain    |
 callsign_group   | text                  |           |          |         | extended |
 operator_group   | text                  |           |          |         | extended |
 operator_class   | text                  |           |          |         | extended |
 prev_class       | text                  |           |          |         | extended |
 prev_callsign    | character(10)         |           |          |         | extended |
 vanity_type      | text                  |           |          |         | extended |
 is_trustee       | character(1)          |           |          |         | extended |
 trustee_callsign | character(10)         |           |          |         | extended |
 trustee_name     | character varying(50) |           |          |         | extended |
View definition:
 SELECT lic_am.sys_id,
    lic_am.callsign,
    lic_am.uls_region,
    ( SELECT ("CallsignGroup".group_id::text || ' - '::text) || "CallsignGroup".match_text::text
           FROM "CallsignGroup"
          WHERE lic_am.callsign ~ "CallsignGroup".pattern::text
         LIMIT 1) AS callsign_group,
    ( SELECT (oper_group.group_id::text || ' - '::text) || oper_group.group_text::text
           FROM oper_group
          WHERE lic_am.operator_class = oper_group.class_id
         LIMIT 1) AS operator_group,
    (lic_am.operator_class::text || ' - '::text) || COALESCE(( SELECT "OperatorClass".class_text
           FROM "OperatorClass"
          WHERE lic_am.operator_class = "OperatorClass".class_id
         LIMIT 1), '???'::character varying)::text AS operator_class,
    (lic_am.prev_class::text || ' - '::text) || COALESCE(( SELECT "OperatorClass".class_text
           FROM "OperatorClass"
          WHERE lic_am.prev_class = "OperatorClass".class_id
         LIMIT 1), '???'::character varying)::text AS prev_class,
    lic_am.prev_callsign,
    (lic_am.vanity_type::text || ' - '::text) || COALESCE(( SELECT "VanityType".vanity_text
           FROM "VanityType"
          WHERE lic_am.vanity_type = "VanityType".vanity_id
         LIMIT 1), '???'::character varying)::text AS vanity_type,
    lic_am.is_trustee,
    lic_am.trustee_callsign,
    lic_am.trustee_name
   FROM lic_am;

VIEW lic_am:

=> \d+ lic_am
                                       View "Callsign.lic_am"
      Column      |         Type          | Collation | Nullable | Default | Storage  | Description
------------------+-----------------------+-----------+----------+---------+----------+-------------
 sys_id           | integer               |           |          |         | plain    |
 callsign         | character(10)         |           |          |         | extended |
 uls_region       | "MySql".tinyint       |           |          |         | plain    |
 uls_group        | character(1)          |           |          |         | extended |
 operator_class   | character(1)          |           |          |         | extended |
 prev_callsign    | character(10)         |           |          |         | extended |
 prev_class       | character(1)          |           |          |         | extended |
 vanity_type      | character(1)          |           |          |         | extended |
 is_trustee       | character(1)          |           |          |         | extended |
 trustee_callsign | character(10)         |           |          |         | extended |
 trustee_name     | character varying(50) |           |          |         | extended |
View definition:
 SELECT _lic_am.sys_id,
    _lic_am.callsign,
    _lic_am.uls_region,
    _lic_am.uls_group,
    _lic_am.operator_class,
    _lic_am.prev_callsign,
    _lic_am.prev_class,
    _lic_am.vanity_type,
    _lic_am.is_trustee,
    _lic_am.trustee_callsign,
    _lic_am.trustee_name
   FROM _lic_am;

VIEW _lic_am:

=> \d+ _lic_am
                                      View "Callsign._lic_am"
      Column      |         Type          | Collation | Nullable | Default | Storage  | Description
------------------+-----------------------+-----------+----------+---------+----------+-------------
 sys_id           | integer               |           |          |         | plain    |
 callsign         | character(10)         |           |          |         | extended |
 uls_region       | "MySql".tinyint       |           |          |         | plain    |
 uls_group        | character(1)          |           |          |         | extended |
 operator_class   | character(1)          |           |          |         | extended |
 prev_callsign    | character(10)         |           |          |         | extended |
 prev_class       | character(1)          |           |          |         | extended |
 vanity_type      | character(1)          |           |          |         | extended |
 is_trustee       | character(1)          |           |          |         | extended |
 trustee_callsign | character(10)         |           |          |         | extended |
 trustee_name     | character varying(50) |           |          |         | extended |
View definition:
 SELECT "_AM".unique_system_identifier AS sys_id,
    "_AM".callsign,
    "_AM".region_code AS uls_region,
    "_AM".group_code AS uls_group,
    "_AM".operator_class,
    "_AM".previous_callsign AS prev_callsign,
    "_AM".previous_operator_class AS prev_class,
    "_AM".vanity_callsign_change AS vanity_type,
    "_AM".trustee_indicator AS is_trustee,
    "_AM".trustee_callsign,
    "_AM".trustee_name
   FROM "UlsLic"."_AM";

TABLE "UlsLic"."_AM":

=> \d+ "UlsLic"."_AM"
                                                     Table "UlsLic._AM"
           Column           |         Type          | Collation | Nullable | Default | Storage  | Stats target | Description
----------------------------+-----------------------+-----------+----------+---------+----------+--------------+-------------
 record_type                | character(2)          |           | not null |         | extended |              |
 unique_system_identifier   | integer               |           | not null |         | plain    |              |
 uls_file_number            | character(14)         |           |          |         | extended |              |
 ebf_number                 | character varying(30) |           |          |         | extended |              |
 callsign                   | character(10)         |           |          |         | extended |              |
 operator_class             | character(1)          |           |          |         | extended |              |
 group_code                 | character(1)          |           |          |         | extended |              |
 region_code                | "MySql".tinyint       |           |          |         | plain    |              |
 trustee_callsign           | character(10)         |           |          |         | extended |              |
 trustee_indicator          | character(1)          |           |          |         | extended |              |
 physician_certification    | character(1)          |           |          |         | extended |              |
 ve_signature               | character(1)          |           |          |         | extended |              |
 systematic_callsign_change | character(1)          |           |          |         | extended |              |
 vanity_callsign_change     | character(1)          |           |          |         | extended |              |
 vanity_relationship        | character(12)         |           |          |         | extended |              |
 previous_callsign          | character(10)         |           |          |         | extended |              |
 previous_operator_class    | character(1)          |           |          |         | extended |              |
 trustee_name               | character varying(50) |           |          |         | extended |              |
Indexes:
    "_AM_pkey" PRIMARY KEY, btree (unique_system_identifier)
    "_AM_callsign" btree (callsign)
    "_AM_prev_callsign" btree (previous_callsign)
    "_AM_trustee_callsign" btree (trustee_callsign)
Check constraints:
    "_AM_record_type_check" CHECK (record_type = 'AM'::bpchar)
Foreign-key constraints:
    "_AM_operator_class_fkey" FOREIGN KEY (operator_class) REFERENCES "FccLookup"."_OperatorClass"(class_id)
    "_AM_previous_operator_class_fkey" FOREIGN KEY (previous_operator_class) REFERENCES "FccLookup"."_OperatorClass"(cla
ss_id)
    "_AM_unique_system_identifier_fkey" FOREIGN KEY (unique_system_identifier) REFERENCES "UlsLic"."_HD"(unique_system_i
dentifier) ON UPDATE CASCADE ON DELETE CASCADE
    "_AM_vanity_callsign_change_fkey" FOREIGN KEY (vanity_callsign_change) REFERENCES "FccLookup"."_VanityType"(vanity_i
d)

On 5/28/21 2:48 PM, Dean Gibson (DB Administrator) wrote:
> [Reposted to the proper list]
>
> I started to use PostgreSQL v7.3 in 2003 on my home Linux systems (4
> at one point), gradually moving to v9.0 w/ replication in 2010.  In
> 2017 I moved my 20GB database to AWS/RDS, gradually upgrading to v9.6,
> & was entirely satisfied with the result.
>
> In March of this year, AWS announced that v9.6 was nearing end of
> support, & AWS would forcibly upgrade everyone to v12 on January 22,
> 2022, if users did not perform the upgrade earlier.  My first attempt
> was successful as far as the upgrade itself, but complex queries that
> normally ran in a couple of seconds on v9.x, were taking minutes in v12.
>
> I didn't have the time in March to diagnose the problem, other than
> some futile adjustments to server parameters, so I reverted back to a
> saved copy of my v9.6 data.
>
> On Sunday, being retired, I decided to attempt to solve the issue in
> earnest.  I have now spent five days (about 14 hours a day), trying
> various things, including adding additional indexes.  Keeping the v9.6
> data online for web users, I've "forked" the data into new copies, &
> updated them in turn to PostgreSQL v10, v11, v12, & v13.  All exhibit
> the same problem:  As you will see below, it appears that versions 10
> & above are doing a sequential scan of some of the "large" (200K rows)
> tables.  Note that the expected & actual run times both differ for
> v9.6 & v13.2, by more than *two orders of magnitude*. Rather than post
> a huge eMail (ha ha), I'll start with this one, that shows an "EXPLAIN
> ANALYZE" from both v9.6 & v13.2, followed by the related table & view
> definitions.  With one exception, table definitions are from the FCC
> (Federal Communications Commission);  the view definitions are my own.
>
>
>

Have you tried reproducing these results outside RDS, say on an EC2
instance running vanilla PostgreSQL?


cheers


andrew



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

Hi Lance,

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

Are you using the same AWS Instance Class?

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

Regards,
Michael Vitale

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

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

 

Lance

 

From: Andrew Dunstan <andrew@dunslane.net>
Date: Friday, May 28, 2021 at 2:08 PM
To: Dean Gibson (DB Administrator) <postgresql@mailpen.com>, pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Subject: Re: AWS forcing PG upgrade from v9.6 a disaster

On 5/28/21 2:48 PM, Dean Gibson (DB Administrator) wrote:
> [Reposted to the proper list]
>
> I started to use PostgreSQL v7.3 in 2003 on my home Linux systems (4
> at one point), gradually moving to v9.0 w/ replication in 2010.  In
> 2017 I moved my 20GB database to AWS/RDS, gradually upgrading to v9.6,
> & was entirely satisfied with the result.
>
> In March of this year, AWS announced that v9.6 was nearing end of
> support, & AWS would forcibly upgrade everyone to v12 on January 22,
> 2022, if users did not perform the upgrade earlier.  My first attempt
> was successful as far as the upgrade itself, but complex queries that
> normally ran in a couple of seconds on v9.x, were taking minutes in v12.
>
> I didn't have the time in March to diagnose the problem, other than
> some futile adjustments to server parameters, so I reverted back to a
> saved copy of my v9.6 data.
>
> On Sunday, being retired, I decided to attempt to solve the issue in
> earnest.  I have now spent five days (about 14 hours a day), trying
> various things, including adding additional indexes.  Keeping the v9.6
> data online for web users, I've "forked" the data into new copies, &
> updated them in turn to PostgreSQL v10, v11, v12, & v13.  All exhibit
> the same problem:  As you will see below, it appears that versions 10
> & above are doing a sequential scan of some of the "large" (200K rows)
> tables.  Note that the expected & actual run times both differ for
> v9.6 & v13.2, by more than *two orders of magnitude*. Rather than post
> a huge eMail (ha ha), I'll start with this one, that shows an "EXPLAIN
> ANALYZE" from both v9.6 & v13.2, followed by the related table & view
> definitions.  With one exception, table definitions are from the FCC
> (Federal Communications Commission);  the view definitions are my own.
>
>
>

Have you tried reproducing these results outside RDS, say on an EC2
instance running vanilla PostgreSQL?


cheers


andrew



--
Andrew Dunstan
EDB: https://urldefense.com/v3/__https://www.enterprisedb.com__;!!DZ3fjg!tiFTfkNeARuU_vwxOHZfrJvVXj8kYMPJqa1tO5Fnv75UbERS8ZAmUoNFl_g2EVyL$

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

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

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

How did you load the database? pg_dump -> psql/pg_restore?

If so, did you perform a VACUUM FREEZE after the load?


Regards, Jan

-- 
Jan Wieck
Postgres User since 1994

On 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

@font-face {font-family:"Cambria Math"; panose-1:2 4 5 3 5 4 6 3 2 4;}@font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4;}p.MsoNormal, li.MsoNormal, div.MsoNormal {margin:0in; font-size:11.0pt; font-family:"Calibri",sans-serif;}a:link, span.MsoHyperlink {mso-style-priority:99; color:blue; text-decoration:underline;}span.EmailStyle19 {mso-style-type:personal-reply; font-family:"Calibri",sans-serif; color:windowtext;}.MsoChpDefault {mso-style-type:export-only; font-size:10.0pt;}div.WordSection1 {page:WordSection1;}

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.

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

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
knowif AWS modified it though.  

Bob

Sent from my PDP11

> On May 28, 2021, at 5:15 PM, Andrew Dunstan <andrew@dunslane.net> wrote:
>
> 
>> On 5/28/21 4:23 PM, Jan Wieck wrote:
>> On 5/28/21 2:48 PM, Dean Gibson (DB Administrator) wrote:
>>
>> What sticks out for me are these two scans, which balloon from 50-60
>> heap fetches to 1.5M each.
>>
>>>                       ->  Nested Loop  (cost=0.29..0.68 rows=1
>>> width=7) (actual time=0.003..0.004 rows=1 loops=1487153)
>>>                             Join Filter: ("_IsoCountry".iso_alpha2 =
>>> "_Territory".country_id)
>>>                             Rows Removed by Join Filter: 0
>>>                             ->  Index Only Scan using
>>> "_IsoCountry_iso_alpha2_key" on "_IsoCountry"  (cost=0.14..0.38
>>> rows=1 width=3) (actual time=0.001..0.002 rows=1 loops=1487153)
>>>                                   Index Cond: (iso_alpha2 =
>>> "_GovtRegion".country_id)
>>>                                   Heap Fetches: 1487153
>>>                             ->  Index Only Scan using
>>> "_Territory_pkey" on "_Territory"  (cost=0.14..0.29 rows=1 width=7)
>>> (actual time=0.001..0.001 rows=1 loops=1487153)
>>>                                   Index Cond: (territory_id =
>>> "_GovtRegion".territory_id)
>>>                                   Heap Fetches: 1550706
>>
>> How did you load the database? pg_dump -> psql/pg_restore?
>>
>> If so, did you perform a VACUUM FREEZE after the load?
>>
>>
>>
>
> Jan
>
>
> AIUI he did an RDS upgrade. Surely that's not doing a dump/restore? I
> assume you would know better than him or me what it actually does do :-)
>
>
> cheers
>
>
> andrew
>
>
> --
> Andrew Dunstan
> EDB: https://www.enterprisedb.com
>
>
>

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.

On 5/28/21 10:27 PM, Jan Wieck wrote:
>
>
> On Fri, May 28, 2021, 17:15 Andrew Dunstan <andrew@dunslane.net
> <mailto:andrew@dunslane.net>> wrote:
>
>
>
>
>     AIUI he did an RDS upgrade. Surely that's not doing a dump/restore? I
>     assume you would know better than him or me what it actually does
>     do :-)
>
>
> Since I am not working at AWS I can't tell for sure. ;)


Apologies, my mistake then.


cheers


andrew

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

> On May 28, 2021, at 14:30, Bruce Momjian <bruce@momjian.us> wrote:
> I think it uses pg_upgrade.

It does.  It does not, however, do the vacuum analyze step afterwards.  A VACUUM (FULL, ANALYZE) should take care of
that,and I believe the OP said he had done that after the pg_upgrade. 

The most common reason for this kind of inexplicable stuff after an RDS upgrade is, as others have said, parameter
changes,since you get a new default parameter group after the upgrade. 

That being said, this does look like something happened to the planner to cause it to pick a worse plan in v13.  The
deeplynested 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
atthe top level predicates. 

...

The most common reason for this kind of inexplicable stuff after an RDS upgrade is, as others have said, parameter changes, since you get a new default parameter group after the upgrade.

That being said, this does look like something happened to the planner to cause it to pick a worse plan in v13.  The deeply nested views make it kind of hard to pin down, but the core issue appears to be in the "good" plan, it evaluates the _Club.club_count > 5 relatively early, which greatly limits the number of rows that it handles elsewhere in the query.    Why the plan change, I can't say.

It might be worth creating a materialized CTE that grabs the "club_count > 5" set and uses that, instead of having it at the top level predicates.

I spent quite a bit of time over the past five days experimenting with various parameter values, to no avail, but I don't mind trying some more.

I have other queries that fail even more spectacularly, & they all seem to involve a generated table like the "club" one in my example.  I have an idea that I might try, in effectively changing the order of evaluation.  I'll have to think about that.  Thanks for the suggestion!  However, one "shouldn't" have to tinker with the order of stuff in SQL;  that's one of the beauties of the language:  the "compiler" (planner) is supposed to figure that all out.  And for me, that's been true for the past 15 years with PostgreSQL.

Note that this problem is not unique to v13.  It happened with upgrades to v10, 11, &12.  So, some fundamental change was made back then (at least in the RDS version).  Since I need a bulletproof backup past next January, I think my next task will be to get an EC2 instance running v9.6, where AWS can't try to upgrade it.  Then, at my leisure, I can fiddle with upgrading.

On 2021-05-28 19:43, Christophe Pettus wrote:

...

The most common reason for this kind of inexplicable stuff after an RDS upgrade is, as others have said, parameter changes, since you get a new default parameter group after the upgrade.

That being said, this does look like something happened to the planner to cause it to pick a worse plan in v13.  The deeply nested views make it kind of hard to pin down, but the core issue appears to be in the "good" plan, it evaluates the _Club.club_count > 5 relatively early, which greatly limits the number of rows that it handles elsewhere in the query.    Why the plan change, I can't say.

It might be worth creating a materialized CTE that grabs the "club_count > 5" set and uses that, instead of having it at the top level predicates.

I spent quite a bit of time over the past five days experimenting with various parameter values, to no avail, but I don't mind trying some more.

I have other queries that fail even more spectacularly, & they all seem to involve a generated table like the "club" one in my example.  I have an idea that I might try, in effectively changing the order of evaluation.  I'll have to think about that.  Thanks for the suggestion!  However, one "shouldn't" have to tinker with the order of stuff in SQL;  that's one of the beauties of the language:  the "compiler" (planner) is supposed to figure that all out.  And for me, that's been true for the past 15 years with PostgreSQL.

Note that this problem is not unique to v13.  It happened with upgrades to v10, 11, &12.  So, some fundamental change was made back then (at least in the RDS version).  Since I need a bulletproof backup past next January, I think my next task will be to get an EC2 instance running v9.6, where AWS can't try to upgrade it.  Then, at my leisure, I can fiddle with upgrading.

BTW what is the planner reason to not use index in v13.2? Is index in corrupted state? Have you try to reindex index "FccLookup"."_LicStatus_pkey" ?

1.5M of seqscan's are looking really bad.

                     SubPlan 2
                       ->  Limit  (cost=0.15..8.17 rows=1 width=32) (actual time=0.006..0.007 rows=1 loops=55)
                             ->  Index Scan using "_LicStatus_pkey" on "_LicStatus"  (cost=0.15..8.17 rows=1 width=32) (actual time=0.005..0.005 rows=1 loops=55)
                                   Index Cond: ("_HD".license_status = status_id)

                     SubPlan 2
                       ->  Limit  (cost=0.00..1.07 rows=1 width=13) (actual time=0.001..0.001 rows=1 loops=1487153)
                             ->  Seq Scan on "_LicStatus"  (cost=0.00..1.07 rows=1 width=13) (actual time=0.000..0.000 rows=1 loops=1487153)
                                   Filter: ("_HD".license_status = status_id)
                                   Rows Removed by Filter: 1

On 5/28/21 5:38 PM, Dean Gibson (DB Administrator) wrote:

Can you repeat your EXPLAIN (ANALYZE, BUFFERS) of the query from your first post and post them here:

https://explain.depesz.com/

Other information:
1) A diff of your configuration settings between 9.6 and 13.2.

2) Are you running on the same AWS instance type for the two versions of Postgres?

It is not necessary to repeat the table/view definitions as they are available in the first post.

Done.

1.There's probably about a hundred, but almost all are differences in the default values.  The most interesting (from my point of view) is my setting work_mem in 8000 on v9.6, & 16000 (after 8000 didn't help) on v13.  Doing a compare right now between the DEFAULT parameters for 9.6 & 13, RDS reports 93 differences in the default parameters between the two.

2. For v13, I moved from db.t2.micro to db.t3.micro, because RDS required that for v13.  However, for the v10, 11, 12 upgrades, I kept db.t2.micro.

Meanwhile, I've been doing some checking.  If I remove "CAST( license_status AS CHAR ) = 'A'", the problem disappears.  Changing the JOIN to a RIGHT JOIN, & replacing WHERE with ON, also "solves" the problem, but there is an extra row where license_status is NULL, due to the RIGHT JOIN.  Currently trying to figure that out (why did the CAST ... match 'A', if it is null?)...

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.

On 5/29/21 3:59 PM, Dean Gibson (DB Administrator) wrote:
>
>
> Meanwhile, I've been doing some checking.  If I remove "CAST(
> license_status AS CHAR ) = 'A'", the problem disappears.  Changing the
> JOIN to a RIGHT JOIN, & replacing WHERE with ON, also "solves" the
> problem, but there is an extra row where license_status is NULL, due
> to the RIGHT JOIN.  Currently trying to figure that out (why did the
> CAST ... match 'A', if it is null?)...


Why are you using this expression? It's something you almost never want
to do in my experience. Why not use the substr() function to get the
first character?


cheers


andrew


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

I tried 500, to no avail.  Since each change involves a delay as RDS readjusts, I'm going down a different path at the moment.

Le 29/05/2021 à 02:38, Dean Gibson (DB Administrator) a écrit :

The original VACUUM FULL ANALYZE ran in 10 hours.  The plain ANALYZE ran in 88 seconds.

One possibility is that your data has a distribution that defeats the ANALYZE sampling strategy.

If that is the case you can force ANALYZE to do a better job by increasing the default_statistics_target value (100 by default) and reload the configuration. This will sample more data from your table which should help the planner find out what the value distribution looks like for a column and why using an index for conditions involving it is a better solution.
The last time I had to use this setting to solve this kind of problem I ended with :

default_statistics_target = 500

But obviously the value suited to your case could be different (I'd increase it until the planner uses the correct index). Note that increasing it increases the costs of maintaining statistics (so you don't want to increase this by several orders of magnitude blindly) but the default value seems fairly conservative to me.

For reference and more fine-tuned settings using per table statistics configuration and multi-column statistics for complex situations, see :
- https://www.postgresql.org/docs/13/runtime-config-query.html
- https://www.postgresql.org/docs/13/planner-stats.html

-- 
Lionel Bouton
gérant de JTEK SARL
https://www.linkedin.com/in/lionelbouton/

...

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)

> 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
youdon'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?

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.

"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

"Dean Gibson (DB Administrator)" <postgresql@mailpen.com> writes:

I thought that having a "USING" clause, was semantically equivalent to 
an "ON" clause with the equalities explicitly stated.  So no, I didn't 
try that.

USING is not that, or at least not only that ... read the manual.

I'm wondering if what you saw is some side-effect of the aliasing
that USING does.
			regards, tom lane

USING ( join_column [, ...] )

A clause of the form USING ( a, b, ... ) is shorthand for ON left_table.a = right_table.a AND left_table.b = right_table.b .... Also, USING implies that only one of each pair of equivalent columns will be included in the join output, not both.

The USING clause is a shorthand that allows you to take advantage of the specific situation where both sides of the join use the same name for the joining column(s). It takes a comma-separated list of the shared column names and forms a join condition that includes an equality comparison for each one. For example, joining T1 and T2 with USING (a, b) produces the join condition ON T1.a = T2.a AND T1.b = T2.b.

Furthermore, the output of JOIN USING suppresses redundant columns: there is no need to print both of the matched columns, since they must have equal values. While JOIN ON produces all columns from T1 followed by all columns from T2, JOIN USING produces one output column for each of the listed column pairs (in the listed order), followed by any remaining columns from T1, followed by any remaining columns from T2.

Finally, NATURAL is a shorthand form of USING: it forms a USING list consisting of all column names that appear in both input tables. As with USING, these columns appear only once in the output table. If there are no common column names, NATURAL JOIN behaves like JOIN ... ON TRUE, producing a cross-product join.

I get that it's like NATURAL, in that only one column is included.  Is there some other side-effect?  Is the fact that I was using a LEFT JOIN, relevant?  Is what I was doing, unusual (or risky)?

> 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

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

On 5/29/21 3:59 PM, Dean Gibson (DB Administrator) wrote:

Meanwhile, I've been doing some checking.  If I remove "CAST(
license_status AS CHAR ) = 'A'", the problem disappears.  Changing the
JOIN to a RIGHT JOIN, & replacing WHERE with ON, also "solves" the
problem, but there is an extra row where license_status is NULL, due
to the RIGHT JOIN.  Currently trying to figure that out (why did the
CAST ... match 'A', if it is null?)...

Why are you using this expression? It's something you almost never want
to do in my experience. Why not use the substr() function to get the
first character?

cheers

andrew

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

Although it doesn't matter in this case, I do it because in general, it changes the type of the value from CHAR to bptext or whatever it is, & that has causes comparison issues in the past.  It's just a matter of habit for me when working with CHAR() types.

But this case, where it doesn't matter, I'd use LEFT().

On 6/6/21 7:49 PM, Dean Gibson (DB Administrator) wrote:
> On 2021-05-29 13:35, Andrew Dunstan wrote:
>> On 5/29/21 3:59 PM, Dean Gibson (DB Administrator) wrote:
>>> Meanwhile, I've been doing some checking.  If I remove "CAST(
>>> license_status AS CHAR ) = 'A'", the problem disappears.  Changing the
>>> JOIN to a RIGHT JOIN, & replacing WHERE with ON, also "solves" the
>>> problem, but there is an extra row where license_status is NULL, due
>>> to the RIGHT JOIN.  Currently trying to figure that out (why did the
>>> CAST ... match 'A', if it is null?)...
>> Why are you using this expression? It's something you almost never want
>> to do in my experience. Why not use the substr() function to get the
>> first character?
>>
>
> Although it doesn't matter in this case, I do it because in general,
> it changes the type of the value from CHAR to bptext or whatever it
> is, & that has causes comparison issues in the past.  It's just a
> matter of habit for me when working with CHAR() types.
>
> But this case, where it doesn't matter, I'd use LEFT().



That raises the issue of why you're using CHAR(n) fields. Just about
every consultant I know advises simply avoiding them. :-)


cheers


andrew

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

On 6/6/21 7:49 PM, Dean Gibson (DB Administrator) wrote:

On 2021-05-29 13:35, Andrew Dunstan wrote:

On 5/29/21 3:59 PM, Dean Gibson (DB Administrator) wrote:

...  If I remove "CAST( license_status AS CHAR ) = 'A'", ...

Why are you using this expression? It's something you almost never want to do in my experience. Why not use the substr() function to get the
first character?

Although it doesn't matter in this case, I do it because in general, it changes the type of the value from CHAR to bptext or whatever it is, & that has caused comparison issues in the past.  It's just a matter of habit for me when working with CHAR() types.

But this case, where it doesn't matter, I'd use LEFT().


That raises the issue of why you're using CHAR(n) fields. Just about every consultant I know advises simply avoiding them. :-)

cheers, andrew

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

As I mentioned earlier, both the data & the table definitions come from the FCC, the latter in the form of text files containing their formal SQL definitions.  These often change (like two weeks ago).  There are 18 tables currently of interest to me, with between 30 & 60 fields in each table.   Further, the entire data set is replaced every Sunday, with daily updates during the week.  About 1/6th of the text fields are defined as VARCHAR;  the rest are CHAR.  All of the text fields that are used as indexes, are CHAR.

Being mindful of the fact that trailing blanks are significant in CHAR fields, I find it easier to keep the original FCC table definitions, & remap them to VIEWs containing the fields I am interested in.  I've been doing this with the FCC data for over 15 years, starting with PostgreSQL 7.3.

As far as needing a consultant in DB design, the FCC is planning a new DB architecture "soon", & they sorely need one.  When they export the data to the public (delimited by "|"), they don't escape some characters like "|", "\", & <cr>.  That makes it fun ...

-- Dean

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.

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

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?

Em qui., 10 de jun. de 2021 às 13:08, Dean Gibson (DB Administrator) <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

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.

"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

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

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

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

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

...

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

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

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