AWS forcing PG upgrade from v9.6 a disaster
| От | Dean Gibson (DB Administrator) |
|---|---|
| Тема | AWS forcing PG upgrade from v9.6 a disaster |
| Дата | |
| Msg-id | 3fa6fd38-1f92-98ab-0055-3e0eb7e4e772@ultimeth.com обсуждение исходный текст |
| Ответы |
Re: AWS forcing PG upgrade from v9.6 a disaster
(Adrian Klaver <adrian.klaver@aklaver.com>)
|
| Список | pgsql-general |
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)
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)
В списке pgsql-general по дате отправления:
Следующее
От: "Dean Gibson (DB Administrator)"Дата:
Сообщение: Re: How long to get a password reset ???