mirror of
https://github.com/dylanbenzi/hurricane_normalization_app.git
synced 2026-07-30 05:08:57 +00:00
646 lines
29 KiB
R
646 lines
29 KiB
R
source("app/queries.R")
|
|
|
|
# --- Test fixtures ---
|
|
|
|
test_storm <- list(
|
|
storm_basin = "AL",
|
|
storm_year = 1972,
|
|
storm_name = "AGNES"
|
|
)
|
|
|
|
lf_parts <- split_full_lf_id("LF1")
|
|
dil_parts <- split_full_lf_id("DIL1")
|
|
id_parts <- split_full_lf_id("ID1")
|
|
|
|
# --- Profiling helpers ---
|
|
|
|
# Runs EXPLAIN (ANALYZE, BUFFERS) on a lazy dplyr query and returns DB execution time in ms.
|
|
# This measures only server-side execution time, excluding network/R overhead.
|
|
db_exec_time_ms <- function(query) {
|
|
real_con <- pool::poolCheckout(con)
|
|
on.exit(pool::poolReturn(real_con))
|
|
sql <- dbplyr::sql_render(query, real_con)
|
|
rows <- dbGetQuery(
|
|
real_con,
|
|
paste("EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)", sql)
|
|
)[[1]]
|
|
exec_line <- rows[grepl("Execution Time:", rows)]
|
|
as.numeric(gsub(".*Execution Time: ([0-9.]+) ms.*", "\\1", exec_line))
|
|
}
|
|
|
|
profile_query <- function(label, query) {
|
|
ms <- tryCatch(
|
|
db_exec_time_ms(query),
|
|
error = function(e) {
|
|
message(" [ERROR] ", label, ": ", conditionMessage(e))
|
|
NA_real_
|
|
}
|
|
)
|
|
cat(sprintf(" %-65s %8.2f ms\n", label, ms))
|
|
invisible(ms)
|
|
}
|
|
|
|
# Prints the full EXPLAIN (ANALYZE, BUFFERS, VERBOSE) plan.
|
|
# Use this to diagnose a slow query — shows per-node timing, row estimates vs
|
|
# actuals, sequential vs index scans, and shared buffer hits/misses.
|
|
print_plan <- function(query) {
|
|
real_con <- pool::poolCheckout(con)
|
|
on.exit(pool::poolReturn(real_con))
|
|
sql <- dbplyr::sql_render(query, real_con)
|
|
rows <- dbGetQuery(
|
|
real_con,
|
|
paste("EXPLAIN (ANALYZE, BUFFERS, VERBOSE, FORMAT TEXT)", sql)
|
|
)[[1]]
|
|
cat(paste(rows, collapse = "\n"), "\n")
|
|
}
|
|
|
|
# --- Run profiles ---
|
|
|
|
cat("\n=== Query Performance Profile (DB execution time only) ===\n\n")
|
|
cat(sprintf(" %-65s %s\n", "Query", "Exec Time"))
|
|
cat(" ", strrep("-", 80), "\n", sep = "")
|
|
|
|
# Simple table scans / filtered selects
|
|
|
|
# 0.34 ms
|
|
profile_query(
|
|
"get_storm_data_coverage()",
|
|
tbl(con, "storm_data_coverage")
|
|
)
|
|
|
|
# 0.06 ms
|
|
profile_query(
|
|
"get_all_loss_storms()",
|
|
tbl(con, "all_loss_storms")
|
|
)
|
|
|
|
# 23.12 ms
|
|
profile_query(
|
|
"get_all_hurdat_ids()",
|
|
tbl(con, "hurdat_ids")
|
|
)
|
|
|
|
# 0.06 ms
|
|
profile_query(
|
|
"get_latest_aggregate_losses()",
|
|
tbl(con, "all_normalized_losses")
|
|
)
|
|
|
|
# 25.54 ms
|
|
profile_query(
|
|
"get_all_conus_landfalls()",
|
|
tbl(con, "all_conus_landfalls") %>%
|
|
select(storm_year, storm_name, lon, lat, rmw_meters)
|
|
)
|
|
|
|
# 35.48 ms
|
|
profile_query(
|
|
"get_all_hurdat_tracks()",
|
|
tbl(con, "all_loss_storms_tracks")
|
|
)
|
|
|
|
# 2.40 ms
|
|
profile_query(
|
|
"get_all_lf_type_landfalls()",
|
|
tbl(con, "lf_id_location")
|
|
)
|
|
|
|
# 6.00 ms
|
|
profile_query(
|
|
"get_all_lf_type_factors()",
|
|
tbl(con, "lf_landfall_gis")
|
|
)
|
|
|
|
# Year-filtered queries
|
|
|
|
# 1.76 ms
|
|
profile_query(
|
|
"get_yearly_economics(1972)",
|
|
tbl(con, I("econ.usa_yearly")) %>% filter(year == 1972)
|
|
)
|
|
|
|
# 2.42 ms
|
|
profile_query(
|
|
"get_yearly_usa_pop_hu(1972)",
|
|
tbl(con, I("metrics.usa_pop_hu")) %>% filter(year == 1972)
|
|
)
|
|
|
|
# Aggregate
|
|
|
|
# 201.96 ms w ERROR, rerun for 7.80 ms
|
|
|
|
#REMOVED
|
|
profile_query(
|
|
"get_latest_normalization_year()",
|
|
tbl(con, "all_yearly_normalized_losses") %>%
|
|
summarize(latest_year = max(normalization_year))
|
|
)
|
|
|
|
# Single-storm queries
|
|
|
|
# 139.30 ms
|
|
|
|
# after index on best_track
|
|
# 66.11 ms
|
|
profile_query(
|
|
"get_best_track_summary(AGNES 1972)",
|
|
tbl(con, "best_track_summary") %>%
|
|
filter(
|
|
storm_basin == test_storm$storm_basin,
|
|
storm_year == test_storm$storm_year,
|
|
storm_name == test_storm$storm_name
|
|
)
|
|
)
|
|
|
|
# 11.25 ms
|
|
profile_query(
|
|
"get_hurdat_id(AGNES 1972)",
|
|
tbl(con, "hurdat_ids") %>%
|
|
filter(
|
|
storm_basin == test_storm$storm_basin,
|
|
storm_year == test_storm$storm_year,
|
|
storm_name == test_storm$storm_name
|
|
)
|
|
)
|
|
|
|
# ERROR, rerun for 0.07 ms
|
|
|
|
# 0.72 ms
|
|
profile_query(
|
|
"get_latest_aggregate_loss(AGNES 1972)",
|
|
tbl(con, "all_normalized_losses") %>%
|
|
filter(
|
|
storm_year == test_storm$storm_year,
|
|
storm_name == test_storm$storm_name
|
|
)
|
|
)
|
|
|
|
# 0.22 ms
|
|
profile_query(
|
|
"get_unique_lf_ids(AGNES 1972)",
|
|
tbl(con, "all_loss_landfalls") %>%
|
|
filter(
|
|
storm_basin == test_storm$storm_basin,
|
|
storm_year == test_storm$storm_year,
|
|
storm_name == test_storm$storm_name
|
|
) %>%
|
|
distinct(full_lf_id, .keep_all = TRUE) %>%
|
|
arrange(full_lf_id) %>%
|
|
select(storm_basin, storm_year, storm_name, lf_type, lf_id, full_lf_id)
|
|
)
|
|
|
|
# 2.06 ms
|
|
profile_query(
|
|
"get_normalized_cost_index(AGNES 1972, LF1)",
|
|
tbl(con, "all_yearly_normalized_losses") %>%
|
|
filter(
|
|
storm_basin == test_storm$storm_basin,
|
|
storm_year == test_storm$storm_year,
|
|
storm_name == test_storm$storm_name,
|
|
lf_type == lf_parts$lf_type,
|
|
lf_id == lf_parts$lf_id
|
|
) %>%
|
|
select(normalization_year, mmh, mmp)
|
|
)
|
|
|
|
# 5.50 ms
|
|
profile_query(
|
|
"get_all_normalized_cost_index(AGNES 1972)",
|
|
tbl(con, "all_yearly_normalized_losses") %>%
|
|
filter(
|
|
storm_basin == test_storm$storm_basin,
|
|
storm_year == test_storm$storm_year,
|
|
storm_name == test_storm$storm_name
|
|
) %>%
|
|
mutate(full_lf_id = paste0(lf_type, lf_id)) %>%
|
|
select(
|
|
normalization_year,
|
|
mmh_index,
|
|
mmp_index,
|
|
mmh_loss = mmh,
|
|
mmp_loss = mmp,
|
|
full_lf_id
|
|
)
|
|
)
|
|
|
|
# 0.83 ms
|
|
profile_query(
|
|
"get_hurdat_landfalls(AGNES 1972)",
|
|
tbl(con, "hurdat_track") %>%
|
|
filter(
|
|
storm_basin == test_storm$storm_basin,
|
|
storm_year == test_storm$storm_year,
|
|
storm_name == test_storm$storm_name,
|
|
record_identifier == "L"
|
|
) %>%
|
|
select(datetime, lon, lat, rmw, pressure, windspeed)
|
|
)
|
|
|
|
# 4.84 ms
|
|
profile_query(
|
|
"get_hurdat_track(AGNES 1972)",
|
|
tbl(con, "hurdat_track") %>%
|
|
filter(
|
|
storm_basin == test_storm$storm_basin,
|
|
storm_year == test_storm$storm_year,
|
|
storm_name == test_storm$storm_name
|
|
) %>%
|
|
select(
|
|
datetime,
|
|
storm_status,
|
|
lon,
|
|
lat,
|
|
rmw,
|
|
pressure,
|
|
windspeed,
|
|
record_identifier,
|
|
rmw_meters
|
|
)
|
|
)
|
|
|
|
# 0.29 ms
|
|
profile_query(
|
|
"get_lf_type_factors(AGNES 1972, LF1)",
|
|
tbl(con, "lf_landfall_gis") %>%
|
|
filter(
|
|
storm_basin == test_storm$storm_basin,
|
|
storm_year == test_storm$storm_year,
|
|
storm_name == test_storm$storm_name,
|
|
full_lf_id == "LF1"
|
|
)
|
|
)
|
|
|
|
# GIS / geometry queries
|
|
|
|
# 32.63 ms
|
|
profile_query(
|
|
"get_fatality_heatmap_data(AGNES 1972)",
|
|
tbl(con, I("fatal.conus_direct_fatalities")) %>%
|
|
filter(
|
|
storm_basin == test_storm$storm_basin,
|
|
storm_year == test_storm$storm_year,
|
|
storm_name == test_storm$storm_name
|
|
) %>%
|
|
select(state_fips, fatality_type, n = fatality_count)
|
|
)
|
|
|
|
# 292.02 ms
|
|
|
|
# after index on conus_direct_fatalities
|
|
# 15.09 ms
|
|
profile_query(
|
|
"get_fatality_map(AGNES 1972)",
|
|
tbl(con, I("fatal.conus_direct_fatalities")) %>%
|
|
filter(
|
|
storm_basin == test_storm$storm_basin,
|
|
storm_year == test_storm$storm_year,
|
|
storm_name == test_storm$storm_name
|
|
) %>%
|
|
inner_join(
|
|
tbl(con, I("gis.us_states_boundary")) %>% rename(state_fips = statefp),
|
|
by = "state_fips"
|
|
)
|
|
)
|
|
|
|
# get_normalized_metric_growth — LF branch (county-level join + geometry)
|
|
affected_counties <- tbl(con, I("gis.affected_area_landfalls")) %>%
|
|
filter(
|
|
storm_basin == test_storm$storm_basin,
|
|
storm_year == test_storm$storm_year,
|
|
storm_name == test_storm$storm_name,
|
|
lf_type == lf_parts$lf_type,
|
|
lf_id == lf_parts$lf_id
|
|
) %>%
|
|
select(state_fips, county_fips)
|
|
|
|
# 6945.16 ms
|
|
|
|
# after precomputed simplified geom, index on metrics
|
|
# 58.67 ms
|
|
profile_query(
|
|
"get_normalized_metric_growth(AGNES 1972, LF1) — LF branch",
|
|
tbl(con, I("metrics.pop_housing_normalized_growth")) %>%
|
|
inner_join(affected_counties, by = c("state_fips", "county_fips")) %>%
|
|
inner_join(
|
|
tbl(con, I("public.counties")),
|
|
by = c("state_fips" = "statefp", "county_fips" = "countyfp")
|
|
) %>%
|
|
select(
|
|
state_fips,
|
|
county_fips,
|
|
year,
|
|
name,
|
|
population,
|
|
housing_units,
|
|
base_year_population,
|
|
base_year_housing,
|
|
normalized_population = population_normalized,
|
|
normalized_housing = housing_units_normalized,
|
|
geom_wkt
|
|
) %>%
|
|
arrange(state_fips, county_fips, year)
|
|
)
|
|
|
|
# get_normalized_metric_growth — IL branch (state-level join + geometry)
|
|
# Helper to build the IL-branch query for a given lf_parts
|
|
il_branch_query <- function(parts) {
|
|
affected_state <- tbl(con, I("gis.indirect_landfalls")) %>%
|
|
filter(
|
|
storm_basin == test_storm$storm_basin,
|
|
storm_year == test_storm$storm_year,
|
|
storm_name == test_storm$storm_name,
|
|
lf_type == parts$lf_type,
|
|
lf_id == parts$lf_id
|
|
) %>%
|
|
select(state_fips)
|
|
|
|
baseline_metrics <- tbl(con, I("metrics.yearly_state_metrics")) %>%
|
|
filter(year == test_storm$storm_year) %>%
|
|
inner_join(affected_state, by = "state_fips") %>%
|
|
select(
|
|
state_fips,
|
|
baseline_population = state_population,
|
|
baseline_housing = state_housing_units
|
|
)
|
|
|
|
tbl(con, I("metrics.yearly_state_metrics")) %>%
|
|
filter(year >= test_storm$storm_year) %>%
|
|
inner_join(affected_state, by = "state_fips") %>%
|
|
inner_join(baseline_metrics, by = "state_fips") %>%
|
|
mutate(
|
|
normalized_population = as.numeric(state_population) /
|
|
as.numeric(baseline_population),
|
|
normalized_housing = as.numeric(state_housing_units) /
|
|
as.numeric(baseline_housing)
|
|
) %>%
|
|
select(
|
|
state_fips,
|
|
year,
|
|
population = state_population,
|
|
housing_units = state_housing_units,
|
|
normalized_population,
|
|
normalized_housing
|
|
) %>%
|
|
inner_join(
|
|
tbl(con, I("gis.us_states_boundary")) %>% rename(state_fips = statefp),
|
|
by = "state_fips"
|
|
) %>%
|
|
select(
|
|
state_fips,
|
|
year,
|
|
name,
|
|
population,
|
|
housing_units,
|
|
normalized_population,
|
|
normalized_housing,
|
|
geom_wkt
|
|
) %>%
|
|
arrange(state_fips, year)
|
|
}
|
|
|
|
# 1973.89 ms w pool ERROR, make sure to poolReturn all objects retrieved with poolCheckout
|
|
|
|
# after precomputed simplified geom, index on metrics
|
|
# 2537.70 ms
|
|
|
|
# after materialized state metrics view
|
|
# 19.57 ms
|
|
profile_query(
|
|
"get_normalized_metric_growth(AGNES 1972, DIL1) — IL branch",
|
|
il_branch_query(dil_parts)
|
|
)
|
|
|
|
# 1220.43 ms
|
|
|
|
# after precomputed simplified geom, index on metrics
|
|
# 307.39 ms
|
|
|
|
# after materialized state metrics view
|
|
# 10.38 ms
|
|
profile_query(
|
|
"get_normalized_metric_growth(AGNES 1972, ID1) — ID branch",
|
|
il_branch_query(id_parts)
|
|
)
|
|
|
|
# get_county_and_state — profiles the two sub-queries independently since
|
|
# the final join happens in R after both collect() calls.
|
|
# Agnes hit Virginia (51), Maryland (24), and Pennsylvania (42) among others.
|
|
|
|
# 64.05 ms
|
|
profile_query(
|
|
"get_county_and_state() — counties sub-query (VA/MD/PA sample)",
|
|
tbl(con, I("public.counties")) %>%
|
|
filter(statefp %in% c("51", "24", "42")) %>%
|
|
select(statefp, countyfp, name, namelsad)
|
|
)
|
|
|
|
# 0.04 ms
|
|
profile_query(
|
|
"get_county_and_state() — states sub-query (VA/MD/PA sample)",
|
|
tbl(con, I("fips.states")) %>%
|
|
filter(state_fips %in% c("51", "24", "42")) %>%
|
|
select(state_fips, state_name, state_abbreviation)
|
|
)
|
|
|
|
cat("\n")
|
|
|
|
# --- Deep plan analysis for slow queries ---
|
|
|
|
cat("\n=== Full Query Plan: get_normalized_metric_growth (LF branch) ===\n\n")
|
|
print_plan(
|
|
tbl(con, I("metrics.pop_housing_normalized_growth")) %>%
|
|
inner_join(affected_counties, by = c("state_fips", "county_fips")) %>%
|
|
inner_join(
|
|
tbl(con, I("public.counties")),
|
|
by = c("state_fips" = "statefp", "county_fips" = "countyfp")
|
|
) %>%
|
|
select(
|
|
state_fips,
|
|
county_fips,
|
|
year,
|
|
name,
|
|
population,
|
|
housing_units,
|
|
base_year_population,
|
|
base_year_housing,
|
|
normalized_population = population_normalized,
|
|
normalized_housing = housing_units_normalized,
|
|
geom_wkt
|
|
) %>%
|
|
arrange(state_fips, county_fips, year)
|
|
)
|
|
|
|
#Gather Merge (cost=25081.90..25096.16 rows=124 width=100) (actual time=5691.759..5741.885 rows=2350 loops=1)
|
|
# Output: pop_housing_normalized_growth.state_fips, pop_housing_normalized_growth.county_fips, pop_housing_normalized_growth.year, counties.name, pop_housing_normalized_growth.population, pop_housing_normalized_growth.housing_units, pop_housing_normalized_growth.base_year_population, pop_housing_normalized_growth.base_year_housing, pop_housing_normalized_growth.population_normalized, pop_housing_normalized_growth.housing_units_normalized, (st_astext(st_simplifypreservetopology(st_transform(counties.geom, 4326), '0.001'::double precision)))
|
|
# Workers Planned: 1
|
|
# Workers Launched: 1
|
|
# Buffers: shared hit=23088 read=7100, temp read=970 written=973
|
|
# -> Sort (cost=24081.89..24082.20 rows=124 width=100) (actual time=5537.265..5538.447 rows=1175 loops=2)
|
|
# Output: pop_housing_normalized_growth.state_fips, pop_housing_normalized_growth.county_fips, pop_housing_normalized_growth.year, counties.name, pop_housing_normalized_growth.population, pop_housing_normalized_growth.housing_units, pop_housing_normalized_growth.base_year_population, pop_housing_normalized_growth.base_year_housing, pop_housing_normalized_growth.population_normalized, pop_housing_normalized_growth.housing_units_normalized, (st_astext(st_simplifypreservetopology(st_transform(counties.geom, 4326), '0.001'::double precision)))
|
|
# Sort Key: pop_housing_normalized_growth.state_fips, pop_housing_normalized_growth.county_fips, pop_housing_normalized_growth.year
|
|
# Sort Method: external merge Disk: 3496kB
|
|
# Buffers: shared hit=23088 read=7100, temp read=970 written=973
|
|
# Worker 0: actual time=5613.399..5614.951 rows=1225 loops=1
|
|
# Sort Method: external merge Disk: 4264kB
|
|
# Buffers: shared hit=10549 read=2188, temp read=533 written=535
|
|
# -> Hash Join (cost=39.73..24077.58 rows=124 width=100) (actual time=322.002..5524.735 rows=1175 loops=2)
|
|
# Output: pop_housing_normalized_growth.state_fips, pop_housing_normalized_growth.county_fips, pop_housing_normalized_growth.year, counties.name, pop_housing_normalized_growth.population, pop_housing_normalized_growth.housing_units, pop_housing_normalized_growth.base_year_population, pop_housing_normalized_growth.base_year_housing, pop_housing_normalized_growth.population_normalized, pop_housing_normalized_growth.housing_units_normalized, st_astext(st_simplifypreservetopology(st_transform(counties.geom, 4326), '0.001'::double precision))
|
|
# Hash Cond: (((pop_housing_normalized_growth.state_fips)::text = (affected_area_landfalls.state_fips)::text) AND ((pop_housing_normalized_growth.county_fips)::text = (affected_area_landfalls.county_fips)::text))
|
|
# Buffers: shared hit=23073 read=7100
|
|
# Worker 0: actual time=285.125..5597.933 rows=1225 loops=1
|
|
# Buffers: shared hit=10534 read=2188
|
|
# -> Parallel Seq Scan on metrics.pop_housing_normalized_growth (cost=0.00..14412.56 rows=435856 width=60) (actual time=0.865..300.889 rows=370478 loops=2)
|
|
# Output: pop_housing_normalized_growth.state_fips, pop_housing_normalized_growth.county_fips, pop_housing_normalized_growth.year, pop_housing_normalized_growth.population, pop_housing_normalized_growth.housing_units, pop_housing_normalized_growth.base_year_population, pop_housing_normalized_growth.base_population, pop_housing_normalized_growth.base_year_housing, pop_housing_normalized_growth.base_housing_units, pop_housing_normalized_growth.population_normalized, pop_housing_normalized_growth.housing_units_normalized
|
|
# Buffers: shared hit=2954 read=7100
|
|
# Worker 0: actual time=0.593..239.999 rows=200573 loops=1
|
|
# Buffers: shared hit=520 read=2188
|
|
# -> Hash (cost=39.71..39.71 rows=1 width=40864) (actual time=0.333..0.335 rows=10 loops=2)
|
|
# Output: affected_area_landfalls.state_fips, affected_area_landfalls.county_fips, counties.name, counties.geom, counties.statefp, counties.countyfp
|
|
# Buckets: 1024 Batches: 1 Memory Usage: 9kB
|
|
# Buffers: shared hit=88
|
|
# Worker 0: actual time=0.425..0.427 rows=10 loops=1
|
|
# Buffers: shared hit=45
|
|
# -> Nested Loop (cost=0.56..39.71 rows=1 width=40864) (actual time=0.130..0.322 rows=10 loops=2)
|
|
# Output: affected_area_landfalls.state_fips, affected_area_landfalls.county_fips, counties.name, counties.geom, counties.statefp, counties.countyfp
|
|
# Buffers: shared hit=88
|
|
# Worker 0: actual time=0.167..0.409 rows=10 loops=1
|
|
# Buffers: shared hit=45
|
|
# -> Index Only Scan using idx_affected_landfalls_composite on gis.affected_area_landfalls (cost=0.28..37.20 rows=1 width=7) (actual time=0.106..0.237 rows=10 loops=2)
|
|
# Output: affected_area_landfalls.storm_basin, affected_area_landfalls.storm_year, affected_area_landfalls.storm_name, affected_area_landfalls.lf_type, affected_area_landfalls.lf_id, affected_area_landfalls.state_fips, affected_area_landfalls.county_fips
|
|
# Index Cond: ((affected_area_landfalls.storm_basin = 'AL'::text) AND (affected_area_landfalls.storm_name = 'AGNES'::text) AND (affected_area_landfalls.lf_type = 'LF'::text) AND (affected_area_landfalls.lf_id = 1))
|
|
# Filter: ((affected_area_landfalls.storm_year)::numeric = 1972.0)
|
|
# Heap Fetches: 0
|
|
# Buffers: shared hit=27
|
|
# Worker 0: actual time=0.136..0.300 rows=10 loops=1
|
|
# Buffers: shared hit=14
|
|
# -> Index Scan using idx_counties_fips_composite on public.counties (cost=0.28..2.50 rows=1 width=40857) (actual time=0.007..0.007 rows=1 loops=20)
|
|
# Output: counties.gid, counties.statefp, counties.countyfp, counties.countyns, counties.geoid, counties.geoidfq, counties.name, counties.namelsad, counties.lsad, counties.classfp, counties.mtfcc, counties.csafp, counties.cbsafp, counties.metdivfp, counties.funcstat, counties.aland, counties.awater, counties.intptlat, counties.intptlon, counties.geom
|
|
# Index Cond: (((counties.statefp)::text = (affected_area_landfalls.state_fips)::text) AND ((counties.countyfp)::text = (affected_area_landfalls.county_fips)::text))
|
|
# Buffers: shared hit=61
|
|
# Worker 0: actual time=0.009..0.009 rows=1 loops=10
|
|
# Buffers: shared hit=31
|
|
#Query Identifier: -293891050420029873
|
|
#Planning:
|
|
# Buffers: shared hit=24
|
|
#Planning Time: 1.163 ms
|
|
#Execution Time: 5742.835 ms
|
|
|
|
cat(
|
|
"\n=== Full Query Plan: get_normalized_metric_growth (DIL1 — IL branch) ===\n\n"
|
|
)
|
|
print_plan(il_branch_query(dil_parts))
|
|
|
|
#Sort (cost=28932.59..28932.59 rows=1 width=177) (actual time=337.827..337.960 rows=53 loops=1)
|
|
# Output: pah_1.state_fips, pah_1.year, us_states_boundary.name, (sum(pah_1.population)), (sum(pah_1.housing_units)), (((sum(pah_1.population)) / (sum(pah.population)))), (((sum(pah_1.housing_units)) / (sum(pah.housing_units)))), us_states_boundary.geom_wkt
|
|
# Sort Key: pah_1.state_fips, pah_1.year
|
|
# Sort Method: quicksort Memory: 31kB
|
|
# Buffers: shared hit=10093
|
|
# -> Nested Loop (cost=28583.26..28932.58 rows=1 width=177) (actual time=336.294..337.903 rows=53 loops=1)
|
|
# Output: pah_1.state_fips, pah_1.year, us_states_boundary.name, (sum(pah_1.population)), (sum(pah_1.housing_units)), ((sum(pah_1.population)) / (sum(pah.population))), ((sum(pah_1.housing_units)) / (sum(pah.housing_units))), us_states_boundary.geom_wkt
|
|
# Join Filter: ((pah_1.state_fips)::text = (pah.state_fips)::text)
|
|
# Buffers: shared hit=10093
|
|
# -> Merge Join (cost=12988.45..12988.85 rows=4 width=144) (actual time=206.254..206.344 rows=1 loops=1)
|
|
# Output: (sum(pah.population)), (sum(pah.housing_units)), pah.state_fips, indirect_landfalls_1.state_fips, us_states_boundary.name, us_states_boundary.geom_wkt, us_states_boundary.statefp
|
|
# Merge Cond: ((pah.state_fips)::text = (us_states_boundary.statefp)::text)
|
|
# Buffers: shared hit=5049
|
|
# -> Sort (cost=12981.27..12981.31 rows=16 width=99) (actual time=205.550..205.633 rows=1 loops=1)
|
|
# Output: (sum(pah.population)), (sum(pah.housing_units)), pah.state_fips, indirect_landfalls_1.state_fips
|
|
# Sort Key: pah.state_fips
|
|
# Sort Method: quicksort Memory: 25kB
|
|
# Buffers: shared hit=5044
|
|
# -> Hash Join (cost=12888.96..12980.95 rows=16 width=99) (actual time=205.532..205.627 rows=1 loops=1)
|
|
# Output: (sum(pah.population)), (sum(pah.housing_units)), pah.state_fips, indirect_landfalls_1.state_fips
|
|
# Hash Cond: ((pah.state_fips)::text = (indirect_landfalls_1.state_fips)::text)
|
|
# Buffers: shared hit=5044
|
|
# -> Finalize HashAggregate (cost=12885.90..12933.81 rows=3194 width=71) (actual time=204.420..204.541 rows=51 loops=1)
|
|
# Output: pah.state_fips, pah.year, sum(pah.population), sum(pah.housing_units)
|
|
# Group Key: pah.state_fips, pah.year
|
|
# Batches: 1 Memory Usage: 129kB
|
|
# Buffers: shared hit=5043
|
|
# -> Gather (cost=12602.63..12853.21 rows=2179 width=71) (actual time=142.165..204.421 rows=51 loops=1)
|
|
# Output: pah.state_fips, pah.year, (PARTIAL sum(pah.population)), (PARTIAL sum(pah.housing_units))
|
|
# Workers Planned: 1
|
|
# Workers Launched: 1
|
|
# Buffers: shared hit=5043
|
|
# -> Partial HashAggregate (cost=11602.63..11635.31 rows=2179 width=71) (actual time=69.296..69.318 rows=26 loops=2)
|
|
# Output: pah.state_fips, pah.year, PARTIAL sum(pah.population), PARTIAL sum(pah.housing_units)
|
|
# Group Key: pah.state_fips, pah.year
|
|
# Batches: 1 Memory Usage: 129kB
|
|
# Buffers: shared hit=5043
|
|
# Worker 0: actual time=0.015..0.015 rows=0 loops=1
|
|
# Batches: 1 Memory Usage: 121kB
|
|
# -> Parallel Seq Scan on metrics.pop_and_housing pah (cost=0.00..11580.84 rows=2179 width=23) (actual time=0.023..68.345 rows=1576 loops=2)
|
|
# Output: pah.state_fips, pah.county_fips, pah.year, pah.population, pah.housing_units
|
|
# Filter: ((pah.year)::numeric = 1972.0)
|
|
# Rows Removed by Filter: 368901
|
|
# Buffers: shared hit=5043
|
|
# Worker 0: actual time=0.001..0.001 rows=0 loops=1
|
|
# -> Hash (cost=3.05..3.05 rows=1 width=32) (actual time=0.510..0.511 rows=1 loops=1)
|
|
# Output: indirect_landfalls_1.state_fips
|
|
# Buckets: 1024 Batches: 1 Memory Usage: 9kB
|
|
# Buffers: shared hit=1
|
|
# -> Seq Scan on gis.indirect_landfalls indirect_landfalls_1 (cost=0.00..3.05 rows=1 width=32) (actual time=0.497..0.507 rows=1 loops=1)
|
|
# Output: indirect_landfalls_1.state_fips
|
|
# Filter: (((indirect_landfalls_1.storm_basin)::text = 'AL'::text) AND ((indirect_landfalls_1.storm_name)::text = 'AGNES'::text) AND ((indirect_landfalls_1.lf_type)::text = 'DIL'::text) AND (indirect_landfalls_1.lf_id = 1) AND ((indirect_landfalls_1.storm_year)::numeric = 1972.0))
|
|
# Rows Removed by Filter: 81
|
|
# Buffers: shared hit=1
|
|
# -> Sort (cost=7.19..7.33 rows=56 width=45) (actual time=0.692..0.694 rows=12 loops=1)
|
|
# Output: us_states_boundary.name, us_states_boundary.geom_wkt, us_states_boundary.statefp
|
|
# Sort Key: us_states_boundary.statefp
|
|
# Sort Method: quicksort Memory: 29kB
|
|
# Buffers: shared hit=5
|
|
# -> Seq Scan on gis.us_states_boundary (cost=0.00..5.56 rows=56 width=45) (actual time=0.013..0.034 rows=56 loops=1)
|
|
# Output: us_states_boundary.name, us_states_boundary.geom_wkt, us_states_boundary.statefp
|
|
# Buffers: shared hit=5
|
|
# -> Materialize (cost=15594.80..15940.27 rows=60 width=103) (actual time=130.034..131.519 rows=53 loops=1)
|
|
# Output: pah_1.state_fips, pah_1.year, (sum(pah_1.population)), (sum(pah_1.housing_units)), indirect_landfalls.state_fips
|
|
# Buffers: shared hit=5044
|
|
# -> Hash Join (cost=15594.80..15939.97 rows=60 width=103) (actual time=130.030..131.495 rows=53 loops=1)
|
|
# Output: pah_1.state_fips, pah_1.year, (sum(pah_1.population)), (sum(pah_1.housing_units)), indirect_landfalls.state_fips
|
|
# Hash Cond: ((pah_1.state_fips)::text = (indirect_landfalls.state_fips)::text)
|
|
# Buffers: shared hit=5044
|
|
# -> Finalize HashAggregate (cost=15591.74..15771.51 rows=11985 width=71) (actual time=129.337..130.433 rows=2703 loops=1)
|
|
# Output: pah_1.state_fips, pah_1.year, sum(pah_1.population), sum(pah_1.housing_units)
|
|
# Group Key: pah_1.state_fips, pah_1.year
|
|
# Batches: 1 Memory Usage: 1169kB
|
|
# Buffers: shared hit=5043
|
|
# -> Gather (cost=14033.69..15411.96 rows=11985 width=71) (actual time=122.549..124.420 rows=4770 loops=1)
|
|
# Output: pah_1.state_fips, pah_1.year, (PARTIAL sum(pah_1.population)), (PARTIAL sum(pah_1.housing_units))
|
|
# Workers Planned: 1
|
|
# Workers Launched: 1
|
|
# Buffers: shared hit=5043
|
|
# -> Partial HashAggregate (cost=13033.69..13213.46 rows=11985 width=71) (actual time=101.846..102.999 rows=2385 loops=2)
|
|
# Output: pah_1.state_fips, pah_1.year, PARTIAL sum(pah_1.population), PARTIAL sum(pah_1.housing_units)
|
|
# Group Key: pah_1.state_fips, pah_1.year
|
|
# Batches: 1 Memory Usage: 1169kB
|
|
# Buffers: shared hit=5043
|
|
# Worker 0: actual time=81.432..82.423 rows=2067 loops=1
|
|
# Batches: 1 Memory Usage: 1041kB
|
|
# Buffers: shared hit=2054
|
|
# -> Parallel Seq Scan on metrics.pop_and_housing pah_1 (cost=0.00..11580.84 rows=145285 width=23) (actual time=0.033..75.476 rows=83554 loops=2)
|
|
# Output: pah_1.state_fips, pah_1.county_fips, pah_1.year, pah_1.population, pah_1.housing_units
|
|
# Filter: ((pah_1.year)::numeric >= 1972.0)
|
|
# Rows Removed by Filter: 286923
|
|
# Buffers: shared hit=5043
|
|
# Worker 0: actual time=0.025..60.219 rows=68601 loops=1
|
|
# Buffers: shared hit=2054
|
|
# -> Hash (cost=3.05..3.05 rows=1 width=32) (actual time=0.029..0.030 rows=1 loops=1)
|
|
# Output: indirect_landfalls.state_fips
|
|
# Buckets: 1024 Batches: 1 Memory Usage: 9kB
|
|
# Buffers: shared hit=1
|
|
# -> Seq Scan on gis.indirect_landfalls (cost=0.00..3.05 rows=1 width=32) (actual time=0.018..0.027 rows=1 loops=1)
|
|
# Output: indirect_landfalls.state_fips
|
|
# Filter: (((indirect_landfalls.storm_basin)::text = 'AL'::text) AND ((indirect_landfalls.storm_name)::text = 'AGNES'::text) AND ((indirect_landfalls.lf_type)::text = 'DIL'::text) AND (indirect_landfalls.lf_id = 1) AND ((indirect_landfalls.storm_year)::numeric = 1972.0))
|
|
# Rows Removed by Filter: 81
|
|
# Buffers: shared hit=1
|
|
#Query Identifier: 6872451688649199607
|
|
#Planning:
|
|
# Buffers: shared hit=6
|
|
#Planning Time: 2.495 ms
|
|
#Execution Time: 338.681 ms
|
|
|
|
cat(
|
|
"\n=== Full Query Plan: get_normalized_metric_growth (ID1 — IL branch) ===\n\n"
|
|
)
|
|
print_plan(il_branch_query(id_parts))
|