Обсуждение: AWS forcing PG upgrade from v9.6 a disaster

Поиск
Список
Период
Сортировка

AWS forcing PG upgrade from v9.6 a disaster

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

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

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

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

Here's from v9.6:

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


Here's from v13.2: 

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


VIEW genclub_multi_:

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

VIEW GenLicClub:

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

TABLE "GenLic"."_Club":

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

VIEW licjb_:

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

VIEW lic_en_:

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

VIEW lic_en:

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

VIEW _lic_en:

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

TABLE "UlsLic"."_EN":

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


VIEW lic_hd_:

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

VIEW lic_hd:

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

VIEW _lic_hd:

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

TABLE  "UlsLic"."_HD":

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

VIEW lic_am_:

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

VIEW lic_am:

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

VIEW _lic_am:

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

TABLE "UlsLic"."_AM":

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

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

От
Adrian Klaver
Дата:
On 5/27/21 8:41 PM, Dean Gibson (DB Administrator) wrote:
> I started to use PostgreSQL v7.3 in 2003 on my home Linux systems (4 at 
> one point), gradually moving to v9.0 w/ replication in 2010.  In 2017 I 
> moved my 20GB database to AWS/RDS, gradually upgrading to v9.6, & was 
> entirely satisfied with the result.
> 
> In March of this year, AWS announced that v9.6 was nearing end of 
> support, & AWS would forcibly upgrade everyone to v12 on January 22, 
> 2022, if users did not perform the upgrade earlier.  My first attempt 
> was successful as far as the upgrade itself, but complex queries that 
> normally ran in a couple of seconds on v9.x, were taking minutes in v12.

Did you run a plain 
ANALYZE(https://www.postgresql.org/docs/12/sql-analyze.html) on the 
tables in the new install?

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




-- 
Adrian Klaver
adrian.klaver@aklaver.com

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

От
"Dean Gibson (DB Administrator)"
Дата:
On 2021-05-28 08:12, Adrian Klaver wrote:
On 5/27/21 8:41 PM, Dean Gibson (DB Administrator) wrote:
I started to use PostgreSQL v7.3 in 2003 on my home Linux systems (4 at one point), gradually moving to v9.0 w/ replication in 2010.  In 2017 I moved my 20GB database to AWS/RDS, gradually upgrading to v9.6, & was entirely satisfied with the result.

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

Did you run a plain ANALYZE(https://www.postgresql.org/docs/12/sql-analyze.html) on the tables in the new install?

After each upgrade (to 10, 11, 12, & 13), I did a "VACUUM FULL ANALYZE".  On 10 through 12, it took about 45 minutes & significant CPU activity, & temporarily doubled the size of the disk space required.  As you know, that disk space is not shrinkable under AWS's RDS.  On v13, it took 10 hours with limited CPU activity, & actually slightly less disk space required.

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

От
Ron
Дата:
On 5/28/21 1:40 PM, Dean Gibson (DB Administrator) wrote:
On 2021-05-28 08:12, Adrian Klaver wrote:
On 5/27/21 8:41 PM, Dean Gibson (DB Administrator) wrote:
I started to use PostgreSQL v7.3 in 2003 on my home Linux systems (4 at one point), gradually moving to v9.0 w/ replication in 2010.  In 2017 I moved my 20GB database to AWS/RDS, gradually upgrading to v9.6, & was entirely satisfied with the result.

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

Did you run a plain ANALYZE(https://www.postgresql.org/docs/12/sql-analyze.html) on the tables in the new install?

After each upgrade (to 10, 11, 12, & 13), I did a "VACUUM FULL ANALYZE".  On 10 through 12, it took about 45 minutes & significant CPU activity, & temporarily doubled the size of the disk space required.  As you know, that disk space is not shrinkable under AWS's RDS.  On v13, it took 10 hours with limited CPU activity, & actually slightly less disk space required.

Under normal conditions, VACUUM FULL is pointless on a freshly-loaded database; in RDS, it's anti-useful.

That's why Adrian asked if you did a plain ANALYZE.

--
Angular momentum makes the world go 'round.

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

От
"Dean Gibson (DB Administrator)"
Дата:
On 2021-05-28 12:38, Ron wrote:
On 5/28/21 1:40 PM, Dean Gibson (DB Administrator) wrote:
On 2021-05-28 08:12, Adrian Klaver wrote:
On 5/27/21 8:41 PM, Dean Gibson (DB Administrator) wrote:
I started to use PostgreSQL v7.3 in 2003 on my home Linux systems (4 at one point), gradually moving to v9.0 w/ replication in 2010.  In 2017 I moved my 20GB database to AWS/RDS, gradually upgrading to v9.6, & was entirely satisfied with the result.

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

Did you run a plain ANALYZE(https://www.postgresql.org/docs/12/sql-analyze.html) on the tables in the new install?

After each upgrade (to 10, 11, 12, & 13), I did a "VACUUM FULL ANALYZE".  On 10 through 12, it took about 45 minutes & significant CPU activity, & temporarily doubled the size of the disk space required.  As you know, that disk space is not shrinkable under AWS's RDS.  On v13, it took 10 hours with limited CPU activity, & actually slightly less disk space required.

Under normal conditions, VACUUM FULL is pointless on a freshly-loaded database; in RDS, it's anti-useful.

That's why Adrian asked if you did a plain ANALYZE.

Just now did.  No change in EXPLAIN ANALYZE output.

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

От
Ron
Дата:
On 5/28/21 5:06 PM, Dean Gibson (DB Administrator) wrote:
On 2021-05-28 12:38, Ron wrote:
On 5/28/21 1:40 PM, Dean Gibson (DB Administrator) wrote:
On 2021-05-28 08:12, Adrian Klaver wrote:
On 5/27/21 8:41 PM, Dean Gibson (DB Administrator) wrote:
I started to use PostgreSQL v7.3 in 2003 on my home Linux systems (4 at one point), gradually moving to v9.0 w/ replication in 2010.  In 2017 I moved my 20GB database to AWS/RDS, gradually upgrading to v9.6, & was entirely satisfied with the result.

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

Did you run a plain ANALYZE(https://www.postgresql.org/docs/12/sql-analyze.html) on the tables in the new install?

After each upgrade (to 10, 11, 12, & 13), I did a "VACUUM FULL ANALYZE".  On 10 through 12, it took about 45 minutes & significant CPU activity, & temporarily doubled the size of the disk space required.  As you know, that disk space is not shrinkable under AWS's RDS.  On v13, it took 10 hours with limited CPU activity, & actually slightly less disk space required.

Under normal conditions, VACUUM FULL is pointless on a freshly-loaded database; in RDS, it's anti-useful.

That's why Adrian asked if you did a plain ANALYZE.

Just now did.  No change in EXPLAIN ANALYZE output.

Did it run in less than 10 hours?

--
Angular momentum makes the world go 'round.

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

От
"Dean Gibson (DB Administrator)"
Дата:
On 2021-05-28 16:51, Ron wrote:
On 5/28/21 5:06 PM, Dean Gibson (DB Administrator) wrote:
On 2021-05-28 12:38, Ron wrote:
On 5/28/21 1:40 PM, Dean Gibson (DB Administrator) wrote:
On 2021-05-28 08:12, Adrian Klaver wrote:
On 5/27/21 8:41 PM, Dean Gibson (DB Administrator) wrote:
I started to use PostgreSQL v7.3 in 2003 on my home Linux systems (4 at one point), gradually moving to v9.0 w/ replication in 2010.  In 2017 I moved my 20GB database to AWS/RDS, gradually upgrading to v9.6, & was entirely satisfied with the result.

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

Did you run a plain ANALYZE(https://www.postgresql.org/docs/12/sql-analyze.html) on the tables in the new install?

After each upgrade (to 10, 11, 12, & 13), I did a "VACUUM FULL ANALYZE".  On 10 through 12, it took about 45 minutes & significant CPU activity, & temporarily doubled the size of the disk space required.  As you know, that disk space is not shrinkable under AWS's RDS.  On v13, it took 10 hours with limited CPU activity, & actually slightly less disk space required.

Under normal conditions, VACUUM FULL is pointless on a freshly-loaded database; in RDS, it's anti-useful.

That's why Adrian asked if you did a plain ANALYZE.

Just now did.  No change in EXPLAIN ANALYZE output.

Did it run in less than 10 hours?


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

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

От
Lionel Bouton
Дата:
Le 29/05/2021 à 02:38, Dean Gibson (DB Administrator) a écrit :
On 2021-05-28 16:51, Ron wrote:
On 5/28/21 5:06 PM, Dean Gibson (DB Administrator) wrote:
On 2021-05-28 12:38, Ron wrote:
On 5/28/21 1:40 PM, Dean Gibson (DB Administrator) wrote:
On 2021-05-28 08:12, Adrian Klaver wrote:
On 5/27/21 8:41 PM, Dean Gibson (DB Administrator) wrote:
I started to use PostgreSQL v7.3 in 2003 on my home Linux systems (4 at one point), gradually moving to v9.0 w/ replication in 2010.  In 2017 I moved my 20GB database to AWS/RDS, gradually upgrading to v9.6, & was entirely satisfied with the result.

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

Did you run a plain ANALYZE(https://www.postgresql.org/docs/12/sql-analyze.html) on the tables in the new install?

After each upgrade (to 10, 11, 12, & 13), I did a "VACUUM FULL ANALYZE".  On 10 through 12, it took about 45 minutes & significant CPU activity, & temporarily doubled the size of the disk space required.  As you know, that disk space is not shrinkable under AWS's RDS.  On v13, it took 10 hours with limited CPU activity, & actually slightly less disk space required.

Under normal conditions, VACUUM FULL is pointless on a freshly-loaded database; in RDS, it's anti-useful.

That's why Adrian asked if you did a plain ANALYZE.

Just now did.  No change in EXPLAIN ANALYZE output.

Did it run in less than 10 hours?


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

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

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

default_statistics_target = 500

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

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

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

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

От
Adrian Klaver
Дата:
On 5/28/21 5:38 PM, Dean Gibson (DB Administrator) wrote:

>>
>> Did it run in less than 10 hours?
>>
> 
> The original VACUUM FULL ANALYZE ran in 10 hours.  The plain ANALYZE ran 
> in 88 seconds.


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

https://explain.depesz.com/

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

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

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

-- 
Adrian Klaver
adrian.klaver@aklaver.com

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

От
Michael Lewis
Дата:


On Sat, May 29, 2021, 4:40 AM Lionel Bouton <lionel.bouton@jtek.fr> wrote:
The last time I had to use this setting to solve this kind of problem I ended with :

default_statistics_target = 500

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

It also increases planning time since those distribution statistics need to be consumed and decisions have to be made.