QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Subquery Scan region_in_region (cost=1719.94..1721.62 rows=1 width=45) (actual time=122063.674..122065.316 rows=1 loops=1) Filter: ("class" = (subplan)) -> Group (cost=1719.94..1719.96 rows=1 width=68) (actual time=122057.842..122059.262 rows=3 loops=1) -> Sort (cost=1719.94..1719.95 rows=1 width=68) (actual time=122057.667..122058.001 rows=70 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=993.06..1719.93 rows=1 width=68) (actual time=6820.956..122055.657 rows=70 loops=1) Join Filter: ((COALESCE("inner".geom, "inner".geom) @ COALESCE("outer".geom, "outer".geom)) AND ("inner".region_id <> "outer".region_id)) -> Nested Loop Left Join (cost=0.00..726.75 rows=1 width=410) (actual time=120.044..815.785 rows=712 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=119.837..592.618 rows=712 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=0.189..0.308 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=0.086..0.120 rows=1 loops=1) Filter: (region_id = 1129::numeric) -> Seq Scan on region_import (cost=0.00..1.04 rows=4 width=19) (actual time=0.015..0.047 rows=4 loops=1) -> Subquery Scan all_imported (cost=0.00..649.48 rows=4940 width=96) (actual time=0.156..521.893 rows=4920 loops=1) -> Append (cost=0.00..600.08 rows=4940 width=720) (actual time=0.138..431.425 rows=4920 loops=1) -> Subquery Scan "*SELECT* 1" (cost=0.00..194.63 rows=1292 width=639) (actual time=0.126..73.643 rows=1294 loops=1) -> Seq Scan on "north_am_v1.0" (cost=0.00..181.71 rows=1292 width=639) (actual time=0.104..49.796 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.140..209.442 rows=1802 loops=1) -> Seq Scan on "north_am_v1.0" (cost=0.00..181.71 rows=1813 width=627) (actual time=0.113..162.602 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.133..96.078 rows=1802 loops=1) -> Seq Scan on "north_am_v1.0" (cost=0.00..181.71 rows=1813 width=621) (actual time=0.109..64.625 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.098..0.113 rows=1 loops=1) -> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.077..0.082 rows=1 loops=1) -> Subquery Scan "*SELECT* 5" (cost=0.00..5.74 rows=21 width=720) (actual time=0.102..1.182 rows=21 loops=1) -> Seq Scan on oncity_2m_prod (cost=0.00..5.53 rows=21 width=720) (actual time=0.075..0.797 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.055..0.066 rows=1 loops=712) -> Merge Left Join (cost=993.06..993.10 rows=5 width=410) (actual time=1.145..24.804 rows=749 loops=712) Merge Cond: ("outer".region_id = "inner".region_id) -> Sort (cost=992.04..992.05 rows=5 width=66) (actual time=1.090..5.550 rows=749 loops=712) Sort Key: bric_extension.region.region_id -> Merge Left Join (cost=954.88..991.98 rows=5 width=66) (actual time=686.311..753.800 rows=749 loops=1) Merge Cond: (("outer"."?column5?" = "inner"."class") AND ("outer".name = "inner"."?column4?")) -> Sort (cost=2.33..2.34 rows=5 width=43) (actual time=0.684..0.713 rows=5 loops=1) Sort Key: (bric_extension.region."class")::text, bric_extension.region_import.name -> Merge Left Join (cost=2.19..2.27 rows=5 width=43) (actual time=0.398..0.599 rows=5 loops=1) Merge Cond: ("outer".region_id = "inner".region_id) -> Sort (cost=1.11..1.12 rows=5 width=34) (actual time=0.191..0.217 rows=5 loops=1) Sort Key: bric_extension.region.region_id -> Seq Scan on region (cost=0.00..1.05 rows=5 width=34) (actual time=0.038..0.094 rows=5 loops=1) -> Sort (cost=1.08..1.09 rows=4 width=19) (actual time=0.116..0.137 rows=4 loops=1) Sort Key: bric_extension.region_import.region_id -> Seq Scan on region_import (cost=0.00..1.04 rows=4 width=19) (actual time=0.021..0.051 rows=4 loops=1) -> Sort (cost=952.55..964.90 rows=4940 width=96) (actual time=662.723..686.980 rows=4920 loops=1) Sort Key: all_imported."class", (all_imported.name)::text -> Subquery Scan all_imported (cost=0.00..649.48 rows=4940 width=96) (actual time=0.211..508.662 rows=4920 loops=1) -> Append (cost=0.00..600.08 rows=4940 width=720) (actual time=0.175..361.006 rows=4920 loops=1) -> Subquery Scan "*SELECT* 1" (cost=0.00..194.63 rows=1292 width=639) (actual time=0.162..80.308 rows=1294 loops=1) -> Seq Scan on "north_am_v1.0" (cost=0.00..181.71 rows=1292 width=639) (actual time=0.124..58.949 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.123..111.029 rows=1802 loops=1) -> Seq Scan on "north_am_v1.0" (cost=0.00..181.71 rows=1813 width=627) (actual time=0.098..77.918 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.169..114.539 rows=1802 loops=1) -> Seq Scan on "north_am_v1.0" (cost=0.00..181.71 rows=1813 width=621) (actual time=0.138..81.395 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.104..0.118 rows=1 loops=1) -> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.082..0.087 rows=1 loops=1) -> Subquery Scan "*SELECT* 5" (cost=0.00..5.74 rows=21 width=720) (actual time=0.108..1.314 rows=21 loops=1) -> Seq Scan on oncity_2m_prod (cost=0.00..5.53 rows=21 width=720) (actual time=0.078..0.926 rows=21 loops=1) Filter: (name IS NOT NULL) -> Sort (cost=1.02..1.02 rows=1 width=354) (actual time=0.006..0.012 rows=1 loops=712) Sort Key: bric_extension.region_lookup.region_id -> Seq Scan on region_lookup (cost=0.00..1.01 rows=1 width=354) (actual time=0.087..0.097 rows=1 loops=1) SubPlan -> Limit (cost=1.65..1.65 rows=1 width=36) (actual time=1.949..1.954 rows=1 loops=3) InitPlan -> Hash Join (cost=1.06..1.32 rows=1 width=4) (actual time=0.922..1.266 rows=1 loops=3) Hash Cond: ("outer".region_class = ("inner"."class")::text) -> Subquery Scan region_classes (cost=0.00..0.21 rows=7 width=36) (actual time=0.109..0.550 rows=7 loops=3) -> Append (cost=0.00..0.14 rows=7 width=0) (actual time=0.091..0.450 rows=7 loops=3) -> Subquery Scan "*SELECT* 1" (cost=0.00..0.02 rows=1 width=0) (actual time=0.080..0.094 rows=1 loops=3) -> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.061..0.065 rows=1 loops=3) -> Subquery Scan "*SELECT* 2" (cost=0.00..0.02 rows=1 width=0) (actual time=0.028..0.042 rows=1 loops=3) -> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.012..0.016 rows=1 loops=3) -> Subquery Scan "*SELECT* 3" (cost=0.00..0.02 rows=1 width=0) (actual time=0.027..0.041 rows=1 loops=3) -> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.012..0.016 rows=1 loops=3) -> Subquery Scan "*SELECT* 4" (cost=0.00..0.02 rows=1 width=0) (actual time=0.028..0.041 rows=1 loops=3) -> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.012..0.017 rows=1 loops=3) -> Subquery Scan "*SELECT* 5" (cost=0.00..0.02 rows=1 width=0) (actual time=0.028..0.042 rows=1 loops=3) -> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.012..0.017 rows=1 loops=3) -> Subquery Scan "*SELECT* 6" (cost=0.00..0.02 rows=1 width=0) (actual time=0.028..0.042 rows=1 loops=3) -> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.012..0.017 rows=1 loops=3) -> Subquery Scan "*SELECT* 7" (cost=0.00..0.02 rows=1 width=0) (actual time=0.028..0.042 rows=1 loops=3) -> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.012..0.017 rows=1 loops=3) -> Hash (cost=1.06..1.06 rows=1 width=14) (actual time=0.412..0.412 rows=0 loops=3) -> Seq Scan on region (cost=0.00..1.06 rows=1 width=14) (actual time=0.079..0.124 rows=1 loops=3) Filter: (region_id = $0) -> Sort (cost=0.33..0.34 rows=7 width=36) (actual time=1.930..1.930 rows=1 loops=3) Sort Key: "order" -> Subquery Scan region_classes (cost=0.00..0.23 rows=7 width=36) (actual time=1.530..1.834 rows=4 loops=3) -> Append (cost=0.00..0.16 rows=7 width=0) (actual time=1.500..1.756 rows=4 loops=3) -> Subquery Scan "*SELECT* 1" (cost=0.00..0.02 rows=1 width=0) (actual time=1.342..1.342 rows=0 loops=3) -> Result (cost=0.00..0.01 rows=1 width=0) (actual time=1.329..1.329 rows=0 loops=3) One-Time Filter: (10 > $1) -> Subquery Scan "*SELECT* 2" (cost=0.00..0.02 rows=1 width=0) (actual time=0.022..0.022 rows=0 loops=3) -> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.009..0.009 rows=0 loops=3) One-Time Filter: (20 > $1) -> Subquery Scan "*SELECT* 3" (cost=0.00..0.02 rows=1 width=0) (actual time=0.022..0.022 rows=0 loops=3) -> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.009..0.009 rows=0 loops=3) One-Time Filter: (30 > $1) -> Subquery Scan "*SELECT* 4" (cost=0.00..0.02 rows=1 width=0) (actual time=0.066..0.081 rows=1 loops=3) -> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.016..0.020 rows=1 loops=3) One-Time Filter: (40 > $1) -> Subquery Scan "*SELECT* 5" (cost=0.00..0.02 rows=1 width=0) (actual time=0.054..0.074 rows=1 loops=3) -> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.035..0.040 rows=1 loops=3) One-Time Filter: (50 > $1) -> Subquery Scan "*SELECT* 6" (cost=0.00..0.02 rows=1 width=0) (actual time=0.034..0.049 rows=1 loops=3) -> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.017..0.021 rows=1 loops=3) One-Time Filter: (60 > $1) -> Subquery Scan "*SELECT* 7" (cost=0.00..0.02 rows=1 width=0) (actual time=0.033..0.048 rows=1 loops=3) -> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.016..0.021 rows=1 loops=3) One-Time Filter: (70 > $1) Total runtime: 122080.977 ms (120 rows)