QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Subquery Scan region_in_region (cost=1455.18..1455.21 rows=1 width=45) (actual time=27966.381..27966.400 rows=1 loops=1) InitPlan -> Limit (cost=1.65..1.65 rows=1 width=36) (actual time=1.449..1.453 rows=1 loops=1) InitPlan -> Hash Join (cost=1.06..1.32 rows=1 width=4) (actual time=0.598..0.931 rows=1 loops=1) Hash Cond: ("outer".region_class = ("inner"."class")::text) -> Subquery Scan region_classes (cost=0.00..0.21 rows=7 width=36) (actual time=0.121..0.549 rows=7 loops=1) -> Append (cost=0.00..0.14 rows=7 width=0) (actual time=0.103..0.452 rows=7 loops=1) -> Subquery Scan "*SELECT* 1" (cost=0.00..0.02 rows=1 width=0) (actual time=0.092..0.106 rows=1 loops=1) -> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.072..0.076 rows=1 loops=1) -> Subquery Scan "*SELECT* 2" (cost=0.00..0.02 rows=1 width=0) (actual time=0.027..0.041 rows=1 loops=1) -> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.012..0.016 rows=1 loops=1) -> Subquery Scan "*SELECT* 3" (cost=0.00..0.02 rows=1 width=0) (actual time=0.026..0.041 rows=1 loops=1) -> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.010..0.015 rows=1 loops=1) -> Subquery Scan "*SELECT* 4" (cost=0.00..0.02 rows=1 width=0) (actual time=0.026..0.039 rows=1 loops=1) -> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.011..0.016 rows=1 loops=1) -> Subquery Scan "*SELECT* 5" (cost=0.00..0.02 rows=1 width=0) (actual time=0.027..0.041 rows=1 loops=1) -> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.012..0.017 rows=1 loops=1) -> Subquery Scan "*SELECT* 6" (cost=0.00..0.02 rows=1 width=0) (actual time=0.026..0.040 rows=1 loops=1) -> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.012..0.017 rows=1 loops=1) -> Subquery Scan "*SELECT* 7" (cost=0.00..0.02 rows=1 width=0) (actual time=0.025..0.039 rows=1 loops=1) -> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.012..0.016 rows=1 loops=1) -> Hash (cost=1.06..1.06 rows=1 width=14) (actual time=0.125..0.125 rows=0 loops=1) -> Seq Scan on region (cost=0.00..1.06 rows=1 width=14) (actual time=0.065..0.097 rows=1 loops=1) Filter: (region_id = 1129::numeric) -> Sort (cost=0.33..0.34 rows=7 width=36) (actual time=1.434..1.434 rows=1 loops=1) Sort Key: "order" -> Subquery Scan region_classes (cost=0.00..0.23 rows=7 width=36) (actual time=1.102..1.364 rows=4 loops=1) -> Append (cost=0.00..0.16 rows=7 width=0) (actual time=1.083..1.300 rows=4 loops=1) -> Subquery Scan "*SELECT* 1" (cost=0.00..0.02 rows=1 width=0) (actual time=0.976..0.976 rows=0 loops=1) -> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.963..0.963 rows=0 loops=1) One-Time Filter: (10 > $0) -> Subquery Scan "*SELECT* 2" (cost=0.00..0.02 rows=1 width=0) (actual time=0.025..0.025 rows=0 loops=1) -> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.014..0.014 rows=0 loops=1) One-Time Filter: (20 > $0) -> Subquery Scan "*SELECT* 3" (cost=0.00..0.02 rows=1 width=0) (actual time=0.021..0.021 rows=0 loops=1) -> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.010..0.010 rows=0 loops=1) One-Time Filter: (30 > $0) -> Subquery Scan "*SELECT* 4" (cost=0.00..0.02 rows=1 width=0) (actual time=0.033..0.047 rows=1 loops=1) -> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.017..0.022 rows=1 loops=1) One-Time Filter: (40 > $0) -> Subquery Scan "*SELECT* 5" (cost=0.00..0.02 rows=1 width=0) (actual time=0.035..0.050 rows=1 loops=1) -> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.019..0.024 rows=1 loops=1) One-Time Filter: (50 > $0) -> Subquery Scan "*SELECT* 6" (cost=0.00..0.02 rows=1 width=0) (actual time=0.033..0.048 rows=1 loops=1) -> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.017..0.022 rows=1 loops=1) One-Time Filter: (60 > $0) -> Subquery Scan "*SELECT* 7" (cost=0.00..0.02 rows=1 width=0) (actual time=0.035..0.049 rows=1 loops=1) -> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.018..0.023 rows=1 loops=1) One-Time Filter: (70 > $0) -> Group (cost=1453.53..1453.55 rows=1 width=68) (actual time=27966.350..27966.356 rows=1 loops=1) -> Sort (cost=1453.53..1453.54 rows=1 width=68) (actual time=27965.006..27965.345 rows=67 loops=1) Sort Key: bric_extension.region.region_id, bric_extension.region.region_title, (bric_extension.region."class")::text, bric_extension.region.region_id, bric_extension.region.region_title, (bric_extension.region."class")::text -> Nested Loop (cost=0.00..1453.52 rows=1 width=68) (actual time=126.819..27963.077 rows=67 loops=1) Join Filter: ((COALESCE("outer".geom, "outer".geom) @ COALESCE("inner".geom, "inner".geom)) AND ("outer".region_id <> "inner".region_id)) -> Nested Loop Left Join (cost=0.00..726.75 rows=1 width=410) (actual time=3.573..464.620 rows=33 loops=1) Join Filter: ("outer".region_id = "inner".region_id) -> Nested Loop Left Join (cost=0.00..725.73 rows=1 width=66) (actual time=3.443..453.466 rows=33 loops=1) Join Filter: (("outer".name = ("inner".name)::text) AND (("outer"."class")::text = "inner"."class")) -> Nested Loop Left Join (cost=0.00..2.15 rows=1 width=43) (actual time=1.704..1.800 rows=1 loops=1) Join Filter: ("outer".region_id = "inner".region_id) -> Seq Scan on region (cost=0.00..1.06 rows=1 width=34) (actual time=1.557..1.584 rows=1 loops=1) Filter: (("class")::text = $1) -> Seq Scan on region_import (cost=0.00..1.04 rows=4 width=19) (actual time=0.022..0.056 rows=4 loops=1) -> Subquery Scan all_imported (cost=0.00..649.48 rows=4940 width=96) (actual time=0.151..407.063 rows=4920 loops=1) -> Append (cost=0.00..600.08 rows=4940 width=720) (actual time=0.131..328.703 rows=4920 loops=1) -> Subquery Scan "*SELECT* 1" (cost=0.00..194.63 rows=1292 width=639) (actual time=0.121..70.803 rows=1294 loops=1) -> Seq Scan on "north_am_v1.0" (cost=0.00..181.71 rows=1292 width=639) (actual time=0.100..47.057 rows=1294 loops=1) Filter: (((name)::text <> 'water/agua/d\'eau'::text) AND (stateabb IS NOT NULL)) -> Subquery Scan "*SELECT* 2" (cost=0.00..199.84 rows=1813 width=627) (actual time=0.121..103.195 rows=1802 loops=1) -> Seq Scan on "north_am_v1.0" (cost=0.00..181.71 rows=1813 width=627) (actual time=0.096..72.714 rows=1802 loops=1) Filter: ((name)::text <> 'water/agua/d\'eau'::text) -> Subquery Scan "*SELECT* 3" (cost=0.00..199.84 rows=1813 width=621) (actual time=0.122..100.573 rows=1802 loops=1) -> Seq Scan on "north_am_v1.0" (cost=0.00..181.71 rows=1813 width=621) (actual time=0.098..69.127 rows=1802 loops=1) Filter: ((name)::text <> 'water/agua/d\'eau'::text) -> Subquery Scan "*SELECT* 4" (cost=0.00..0.02 rows=1 width=0) (actual time=0.100..0.114 rows=1 loops=1) -> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.079..0.083 rows=1 loops=1) -> Subquery Scan "*SELECT* 5" (cost=0.00..5.74 rows=21 width=720) (actual time=0.100..1.398 rows=21 loops=1) -> Seq Scan on oncity_2m_prod (cost=0.00..5.53 rows=21 width=720) (actual time=0.074..0.997 rows=21 loops=1) Filter: (name IS NOT NULL) -> Seq Scan on region_lookup (cost=0.00..1.01 rows=1 width=354) (actual time=0.042..0.052 rows=1 loops=33) -> Nested Loop Left Join (cost=0.00..726.75 rows=1 width=410) (actual time=118.512..597.403 rows=712 loops=33) Join Filter: ("outer".region_id = "inner".region_id) -> Nested Loop Left Join (cost=0.00..725.73 rows=1 width=66) (actual time=118.197..475.212 rows=712 loops=33) Join Filter: (("outer".name = ("inner".name)::text) AND (("outer"."class")::text = "inner"."class")) -> Nested Loop Left Join (cost=0.00..2.15 rows=1 width=43) (actual time=0.173..0.329 rows=1 loops=33) Join Filter: ("outer".region_id = "inner".region_id) -> Seq Scan on region (cost=0.00..1.06 rows=1 width=34) (actual time=0.042..0.106 rows=1 loops=33) Filter: (region_id = 1129::numeric) -> Seq Scan on region_import (cost=0.00..1.04 rows=4 width=19) (actual time=0.016..0.050 rows=4 loops=33) -> Subquery Scan all_imported (cost=0.00..649.48 rows=4940 width=96) (actual time=0.212..418.636 rows=4920 loops=33) -> Append (cost=0.00..600.08 rows=4940 width=720) (actual time=0.192..337.362 rows=4920 loops=33) -> Subquery Scan "*SELECT* 1" (cost=0.00..194.63 rows=1292 width=639) (actual time=0.182..71.460 rows=1294 loops=33) -> Seq Scan on "north_am_v1.0" (cost=0.00..181.71 rows=1292 width=639) (actual time=0.162..48.908 rows=1294 loops=33) Filter: (((name)::text <> 'water/agua/d\'eau'::text) AND (stateabb IS NOT NULL)) -> Subquery Scan "*SELECT* 2" (cost=0.00..199.84 rows=1813 width=627) (actual time=0.105..115.662 rows=1802 loops=33) -> Seq Scan on "north_am_v1.0" (cost=0.00..181.71 rows=1813 width=627) (actual time=0.077..80.654 rows=1802 loops=33) Filter: ((name)::text <> 'water/agua/d\'eau'::text) -> Subquery Scan "*SELECT* 3" (cost=0.00..199.84 rows=1813 width=621) (actual time=0.109..97.106 rows=1802 loops=33) -> Seq Scan on "north_am_v1.0" (cost=0.00..181.71 rows=1813 width=621) (actual time=0.082..67.252 rows=1802 loops=33) Filter: ((name)::text <> 'water/agua/d\'eau'::text) -> Subquery Scan "*SELECT* 4" (cost=0.00..0.02 rows=1 width=0) (actual time=0.251..0.267 rows=1 loops=33) -> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.224..0.229 rows=1 loops=33) -> Subquery Scan "*SELECT* 5" (cost=0.00..5.74 rows=21 width=720) (actual time=0.111..1.279 rows=21 loops=33) -> Seq Scan on oncity_2m_prod (cost=0.00..5.53 rows=21 width=720) (actual time=0.075..0.860 rows=21 loops=33) Filter: (name IS NOT NULL) -> Seq Scan on region_lookup (cost=0.00..1.01 rows=1 width=354) (actual time=0.031..0.041 rows=1 loops=23496)