Skip to content

Instantly share code, notes, and snippets.

@antoniocarlon

antoniocarlon/do_tilesets_1_st_stats.md Secret

Created July 26, 2018 10:52
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save antoniocarlon/61f9b244e2ffb499681d80e9ffbba1f2 to your computer and use it in GitHub Desktop.
Save antoniocarlon/61f9b244e2ffb499681d80e9ffbba1f2 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
do_tilesets_1_st_stats.md

States, zoom level 0

Query
explain analyze
select *
from tiler.xyz_us_do_geoms do_
left outer join "us.mastercard".mc_state mc2012 on do_.geoid = mc2012.region_id and mc2012.month = '02/01/2012'
left outer join "us.mastercard".mc_state mc2013 on do_.geoid = mc2013.region_id and mc2013.month = '02/01/2013'
left outer join "us.mastercard".mc_state mc2014 on do_.geoid = mc2014.region_id and mc2014.month = '02/01/2014'
left outer join "us.mastercard".mc_state mc2015 on do_.geoid = mc2015.region_id and mc2015.month = '02/01/2015'
left outer join "us.mastercard".mc_state mc2016 on do_.geoid = mc2016.region_id and mc2016.month = '02/01/2016'
left outer join "us.mastercard".mc_state mc2017 on do_.geoid = mc2017.region_id and mc2017.month = '02/01/2017'
left outer join "us.mastercard".mc_state mc2018 on do_.geoid = mc2018.region_id and mc2018.month = '02/01/2018'
where x = 0
  and y = 0
  and z = 0;
Feature count
56
Execution plan (removed geometry for legibility)
 Nested Loop Left Join  (cost=2.52..68.77 rows=1 width=13069) (actual time=0.087..2.297 rows=56 loops=1)
   ->  Nested Loop Left Join  (cost=2.24..60.18 rows=1 width=11302) (actual time=0.080..1.942 rows=56 loops=1)
         ->  Nested Loop Left Join  (cost=1.96..51.58 rows=1 width=9535) (actual time=0.073..1.589 rows=56 loops=1)
               ->  Nested Loop Left Join  (cost=1.68..42.98 rows=1 width=7768) (actual time=0.067..1.283 rows=56 loops=1)
                     ->  Nested Loop Left Join  (cost=1.40..34.39 rows=1 width=6001) (actual time=0.058..0.979 rows=56 loops=1)
                           ->  Nested Loop Left Join  (cost=1.12..25.78 rows=1 width=4234) (actual time=0.049..0.691 rows=56 loops=1)
                                 ->  Nested Loop Left Join  (cost=0.84..17.18 rows=1 width=2467) (actual time=0.042..0.425 rows=56 loops=1)
                                       ->  Index Scan using xyz_us_do_geoms_pk on xyz_us_do_geoms do_  (cost=0.56..8.58 rows=1 width=700) (actual time=0.024..0.049 rows=56 loops=1)
                                             Index Cond: ((x = 0) AND (y = 0) AND (z = 0))
                                       ->  Index Scan using mc_state_pkey on mc_state mc2012  (cost=0.28..8.30 rows=1 width=1767) (actual time=0.005..0.005 rows=1 loops=56)
                                             Index Cond: (((do_.geoid)::text = region_id) AND (month = '02/01/2012'::text))
                                 ->  Index Scan using mc_state_pkey on mc_state mc2013  (cost=0.28..8.30 rows=1 width=1767) (actual time=0.003..0.003 rows=1 loops=56)
                                       Index Cond: (((do_.geoid)::text = region_id) AND (month = '02/01/2013'::text))
                           ->  Index Scan using mc_state_pkey on mc_state mc2014  (cost=0.28..8.30 rows=1 width=1767) (actual time=0.003..0.003 rows=1 loops=56)
                                 Index Cond: (((do_.geoid)::text = region_id) AND (month = '02/01/2014'::text))
                     ->  Index Scan using mc_state_pkey on mc_state mc2015  (cost=0.28..8.30 rows=1 width=1767) (actual time=0.003..0.003 rows=1 loops=56)
                           Index Cond: (((do_.geoid)::text = region_id) AND (month = '02/01/2015'::text))
               ->  Index Scan using mc_state_pkey on mc_state mc2016  (cost=0.28..8.30 rows=1 width=1767) (actual time=0.003..0.003 rows=1 loops=56)
                     Index Cond: (((do_.geoid)::text = region_id) AND (month = '02/01/2016'::text))
         ->  Index Scan using mc_state_pkey on mc_state mc2017  (cost=0.28..8.30 rows=1 width=1767) (actual time=0.003..0.003 rows=1 loops=56)
               Index Cond: (((do_.geoid)::text = region_id) AND (month = '02/01/2017'::text))
   ->  Index Scan using mc_state_pkey on mc_state mc2018  (cost=0.28..8.30 rows=1 width=1767) (actual time=0.003..0.003 rows=1 loops=56)
         Index Cond: (((do_.geoid)::text = region_id) AND (month = '02/01/2018'::text))
 Planning time: 8.502 ms
 Execution time: 2.913 ms
(25 rows)

States, zoom level 1

Query
explain analyze
select *
from tiler.xyz_us_do_geoms do_
left outer join "us.mastercard".mc_state mc2012 on do_.geoid = mc2012.region_id and mc2012.month = '02/01/2012'
left outer join "us.mastercard".mc_state mc2013 on do_.geoid = mc2013.region_id and mc2013.month = '02/01/2013'
left outer join "us.mastercard".mc_state mc2014 on do_.geoid = mc2014.region_id and mc2014.month = '02/01/2014'
left outer join "us.mastercard".mc_state mc2015 on do_.geoid = mc2015.region_id and mc2015.month = '02/01/2015'
left outer join "us.mastercard".mc_state mc2016 on do_.geoid = mc2016.region_id and mc2016.month = '02/01/2016'
left outer join "us.mastercard".mc_state mc2017 on do_.geoid = mc2017.region_id and mc2017.month = '02/01/2017'
left outer join "us.mastercard".mc_state mc2018 on do_.geoid = mc2018.region_id and mc2018.month = '02/01/2018'
where x = 0
  and y = 0
  and z = 1;
Feature count
53
Execution plan (removed geometry for legibility)
 Nested Loop Left Join  (cost=2.52..68.77 rows=1 width=13069) (actual time=0.075..2.103 rows=53 loops=1)
   ->  Nested Loop Left Join  (cost=2.24..60.18 rows=1 width=11302) (actual time=0.068..1.790 rows=53 loops=1)
         ->  Nested Loop Left Join  (cost=1.96..51.58 rows=1 width=9535) (actual time=0.062..1.506 rows=53 loops=1)
               ->  Nested Loop Left Join  (cost=1.68..42.98 rows=1 width=7768) (actual time=0.056..1.215 rows=53 loops=1)
                     ->  Nested Loop Left Join  (cost=1.40..34.39 rows=1 width=6001) (actual time=0.049..0.942 rows=53 loops=1)
                           ->  Nested Loop Left Join  (cost=1.12..25.78 rows=1 width=4234) (actual time=0.043..0.677 rows=53 loops=1)
                                 ->  Nested Loop Left Join  (cost=0.84..17.18 rows=1 width=2467) (actual time=0.037..0.434 rows=53 loops=1)
                                       ->  Index Scan using xyz_us_do_geoms_pk on xyz_us_do_geoms do_  (cost=0.56..8.58 rows=1 width=700) (actual time=0.021..0.052 rows=53 loops=1)
                                             Index Cond: ((x = 0) AND (y = 0) AND (z = 1))
                                       ->  Index Scan using mc_state_pkey on mc_state mc2012  (cost=0.28..8.30 rows=1 width=1767) (actual time=0.005..0.005 rows=1 loops=53)
                                             Index Cond: (((do_.geoid)::text = region_id) AND (month = '02/01/2012'::text))
                                 ->  Index Scan using mc_state_pkey on mc_state mc2013  (cost=0.28..8.30 rows=1 width=1767) (actual time=0.003..0.003 rows=1 loops=53)
                                       Index Cond: (((do_.geoid)::text = region_id) AND (month = '02/01/2013'::text))
                           ->  Index Scan using mc_state_pkey on mc_state mc2014  (cost=0.28..8.30 rows=1 width=1767) (actual time=0.003..0.003 rows=1 loops=53)
                                 Index Cond: (((do_.geoid)::text = region_id) AND (month = '02/01/2014'::text))
                     ->  Index Scan using mc_state_pkey on mc_state mc2015  (cost=0.28..8.30 rows=1 width=1767) (actual time=0.003..0.003 rows=1 loops=53)
                           Index Cond: (((do_.geoid)::text = region_id) AND (month = '02/01/2015'::text))
               ->  Index Scan using mc_state_pkey on mc_state mc2016  (cost=0.28..8.30 rows=1 width=1767) (actual time=0.003..0.003 rows=1 loops=53)
                     Index Cond: (((do_.geoid)::text = region_id) AND (month = '02/01/2016'::text))
         ->  Index Scan using mc_state_pkey on mc_state mc2017  (cost=0.28..8.30 rows=1 width=1767) (actual time=0.003..0.003 rows=1 loops=53)
               Index Cond: (((do_.geoid)::text = region_id) AND (month = '02/01/2017'::text))
   ->  Index Scan using mc_state_pkey on mc_state mc2018  (cost=0.28..8.30 rows=1 width=1767) (actual time=0.003..0.003 rows=1 loops=53)
         Index Cond: (((do_.geoid)::text = region_id) AND (month = '02/01/2018'::text))
 Planning time: 8.868 ms
 Execution time: 2.652 ms
(25 rows)

States, zoom level 2

Query
explain analyze
select *
from tiler.xyz_us_do_geoms do_
left outer join "us.mastercard".mc_state mc2012 on do_.geoid = mc2012.region_id and mc2012.month = '02/01/2012'
left outer join "us.mastercard".mc_state mc2013 on do_.geoid = mc2013.region_id and mc2013.month = '02/01/2013'
left outer join "us.mastercard".mc_state mc2014 on do_.geoid = mc2014.region_id and mc2014.month = '02/01/2014'
left outer join "us.mastercard".mc_state mc2015 on do_.geoid = mc2015.region_id and mc2015.month = '02/01/2015'
left outer join "us.mastercard".mc_state mc2016 on do_.geoid = mc2016.region_id and mc2016.month = '02/01/2016'
left outer join "us.mastercard".mc_state mc2017 on do_.geoid = mc2017.region_id and mc2017.month = '02/01/2017'
left outer join "us.mastercard".mc_state mc2018 on do_.geoid = mc2018.region_id and mc2018.month = '02/01/2018'
where x = 0
  and y = 1
  and z = 2;
Feature count
29
Execution plan (removed geometry for legibility)
 Nested Loop Left Join  (cost=2.52..68.77 rows=1 width=13069) (actual time=0.081..1.183 rows=29 loops=1)
   ->  Nested Loop Left Join  (cost=2.24..60.18 rows=1 width=11302) (actual time=0.074..1.018 rows=29 loops=1)
         ->  Nested Loop Left Join  (cost=1.96..51.58 rows=1 width=9535) (actual time=0.068..0.846 rows=29 loops=1)
               ->  Nested Loop Left Join  (cost=1.68..42.98 rows=1 width=7768) (actual time=0.061..0.688 rows=29 loops=1)
                     ->  Nested Loop Left Join  (cost=1.40..34.39 rows=1 width=6001) (actual time=0.054..0.540 rows=29 loops=1)
                           ->  Nested Loop Left Join  (cost=1.12..25.78 rows=1 width=4234) (actual time=0.047..0.399 rows=29 loops=1)
                                 ->  Nested Loop Left Join  (cost=0.84..17.18 rows=1 width=2467) (actual time=0.040..0.248 rows=29 loops=1)
                                       ->  Index Scan using xyz_us_do_geoms_pk on xyz_us_do_geoms do_  (cost=0.56..8.58 rows=1 width=700) (actual time=0.022..0.041 rows=29 loops=1)
                                             Index Cond: ((x = 0) AND (y = 1) AND (z = 2))
                                       ->  Index Scan using mc_state_pkey on mc_state mc2012  (cost=0.28..8.30 rows=1 width=1767) (actual time=0.005..0.005 rows=1 loops=29)
                                             Index Cond: (((do_.geoid)::text = region_id) AND (month = '02/01/2012'::text))
                                 ->  Index Scan using mc_state_pkey on mc_state mc2013  (cost=0.28..8.30 rows=1 width=1767) (actual time=0.003..0.003 rows=1 loops=29)
                                       Index Cond: (((do_.geoid)::text = region_id) AND (month = '02/01/2013'::text))
                           ->  Index Scan using mc_state_pkey on mc_state mc2014  (cost=0.28..8.30 rows=1 width=1767) (actual time=0.003..0.003 rows=1 loops=29)
                                 Index Cond: (((do_.geoid)::text = region_id) AND (month = '02/01/2014'::text))
                     ->  Index Scan using mc_state_pkey on mc_state mc2015  (cost=0.28..8.30 rows=1 width=1767) (actual time=0.003..0.003 rows=1 loops=29)
                           Index Cond: (((do_.geoid)::text = region_id) AND (month = '02/01/2015'::text))
               ->  Index Scan using mc_state_pkey on mc_state mc2016  (cost=0.28..8.30 rows=1 width=1767) (actual time=0.003..0.003 rows=1 loops=29)
                     Index Cond: (((do_.geoid)::text = region_id) AND (month = '02/01/2016'::text))
         ->  Index Scan using mc_state_pkey on mc_state mc2017  (cost=0.28..8.30 rows=1 width=1767) (actual time=0.003..0.003 rows=1 loops=29)
               Index Cond: (((do_.geoid)::text = region_id) AND (month = '02/01/2017'::text))
   ->  Index Scan using mc_state_pkey on mc_state mc2018  (cost=0.28..8.30 rows=1 width=1767) (actual time=0.003..0.003 rows=1 loops=29)
         Index Cond: (((do_.geoid)::text = region_id) AND (month = '02/01/2018'::text))
 Planning time: 9.443 ms
 Execution time: 1.885 ms
(25 rows)

States, zoom level 3

Query
explain analyze
select *
from tiler.xyz_us_do_geoms do_
left outer join "us.mastercard".mc_state mc2012 on do_.geoid = mc2012.region_id and mc2012.month = '02/01/2012'
left outer join "us.mastercard".mc_state mc2013 on do_.geoid = mc2013.region_id and mc2013.month = '02/01/2013'
left outer join "us.mastercard".mc_state mc2014 on do_.geoid = mc2014.region_id and mc2014.month = '02/01/2014'
left outer join "us.mastercard".mc_state mc2015 on do_.geoid = mc2015.region_id and mc2015.month = '02/01/2015'
left outer join "us.mastercard".mc_state mc2016 on do_.geoid = mc2016.region_id and mc2016.month = '02/01/2016'
left outer join "us.mastercard".mc_state mc2017 on do_.geoid = mc2017.region_id and mc2017.month = '02/01/2017'
left outer join "us.mastercard".mc_state mc2018 on do_.geoid = mc2018.region_id and mc2018.month = '02/01/2018'
where x = 2
  and y = 3
  and z = 3;
Feature count
24
Execution plan (removed geometry for legibility)
 Nested Loop Left Join  (cost=2.52..68.77 rows=1 width=13069) (actual time=0.075..0.946 rows=24 loops=1)
   ->  Nested Loop Left Join  (cost=2.24..60.18 rows=1 width=11302) (actual time=0.068..0.812 rows=24 loops=1)
         ->  Nested Loop Left Join  (cost=1.96..51.58 rows=1 width=9535) (actual time=0.062..0.686 rows=24 loops=1)
               ->  Nested Loop Left Join  (cost=1.68..42.98 rows=1 width=7768) (actual time=0.055..0.548 rows=24 loops=1)
                     ->  Nested Loop Left Join  (cost=1.40..34.39 rows=1 width=6001) (actual time=0.049..0.431 rows=24 loops=1)
                           ->  Nested Loop Left Join  (cost=1.12..25.78 rows=1 width=4234) (actual time=0.042..0.318 rows=24 loops=1)
                                 ->  Nested Loop Left Join  (cost=0.84..17.18 rows=1 width=2467) (actual time=0.036..0.207 rows=24 loops=1)
                                       ->  Index Scan using xyz_us_do_geoms_pk on xyz_us_do_geoms do_  (cost=0.56..8.58 rows=1 width=700) (actual time=0.020..0.035 rows=24 loops=1)
                                             Index Cond: ((x = 2) AND (y = 3) AND (z = 3))
                                       ->  Index Scan using mc_state_pkey on mc_state mc2012  (cost=0.28..8.30 rows=1 width=1767) (actual time=0.005..0.005 rows=1 loops=24)
                                             Index Cond: (((do_.geoid)::text = region_id) AND (month = '02/01/2012'::text))
                                 ->  Index Scan using mc_state_pkey on mc_state mc2013  (cost=0.28..8.30 rows=1 width=1767) (actual time=0.003..0.003 rows=1 loops=24)
                                       Index Cond: (((do_.geoid)::text = region_id) AND (month = '02/01/2013'::text))
                           ->  Index Scan using mc_state_pkey on mc_state mc2014  (cost=0.28..8.30 rows=1 width=1767) (actual time=0.003..0.003 rows=1 loops=24)
                                 Index Cond: (((do_.geoid)::text = region_id) AND (month = '02/01/2014'::text))
                     ->  Index Scan using mc_state_pkey on mc_state mc2015  (cost=0.28..8.30 rows=1 width=1767) (actual time=0.003..0.003 rows=1 loops=24)
                           Index Cond: (((do_.geoid)::text = region_id) AND (month = '02/01/2015'::text))
               ->  Index Scan using mc_state_pkey on mc_state mc2016  (cost=0.28..8.30 rows=1 width=1767) (actual time=0.003..0.003 rows=1 loops=24)
                     Index Cond: (((do_.geoid)::text = region_id) AND (month = '02/01/2016'::text))
         ->  Index Scan using mc_state_pkey on mc_state mc2017  (cost=0.28..8.30 rows=1 width=1767) (actual time=0.003..0.003 rows=1 loops=24)
               Index Cond: (((do_.geoid)::text = region_id) AND (month = '02/01/2017'::text))
   ->  Index Scan using mc_state_pkey on mc_state mc2018  (cost=0.28..8.30 rows=1 width=1767) (actual time=0.003..0.003 rows=1 loops=24)
         Index Cond: (((do_.geoid)::text = region_id) AND (month = '02/01/2018'::text))
 Planning time: 9.429 ms
 Execution time: 1.493 ms

States, zoom level 4

Query
explain analyze
select *
from tiler.xyz_us_do_geoms do_
left outer join "us.mastercard".mc_state mc2012 on do_.geoid = mc2012.region_id and mc2012.month = '02/01/2012'
left outer join "us.mastercard".mc_state mc2013 on do_.geoid = mc2013.region_id and mc2013.month = '02/01/2013'
left outer join "us.mastercard".mc_state mc2014 on do_.geoid = mc2014.region_id and mc2014.month = '02/01/2014'
left outer join "us.mastercard".mc_state mc2015 on do_.geoid = mc2015.region_id and mc2015.month = '02/01/2015'
left outer join "us.mastercard".mc_state mc2016 on do_.geoid = mc2016.region_id and mc2016.month = '02/01/2016'
left outer join "us.mastercard".mc_state mc2017 on do_.geoid = mc2017.region_id and mc2017.month = '02/01/2017'
left outer join "us.mastercard".mc_state mc2018 on do_.geoid = mc2018.region_id and mc2018.month = '02/01/2018'
where x = 4
  and y = 6
  and z = 4;
Feature count
22
Execution plan (removed geometry for legibility)
 Nested Loop Left Join  (cost=2.52..68.77 rows=1 width=13069) (actual time=0.079..0.966 rows=22 loops=1)
   ->  Nested Loop Left Join  (cost=2.24..60.18 rows=1 width=11302) (actual time=0.073..0.823 rows=22 loops=1)
         ->  Nested Loop Left Join  (cost=1.96..51.58 rows=1 width=9535) (actual time=0.066..0.698 rows=22 loops=1)
               ->  Nested Loop Left Join  (cost=1.68..42.98 rows=1 width=7768) (actual time=0.059..0.562 rows=22 loops=1)
                     ->  Nested Loop Left Join  (cost=1.40..34.39 rows=1 width=6001) (actual time=0.052..0.446 rows=22 loops=1)
                           ->  Nested Loop Left Join  (cost=1.12..25.78 rows=1 width=4234) (actual time=0.046..0.333 rows=22 loops=1)
                                 ->  Nested Loop Left Join  (cost=0.84..17.18 rows=1 width=2467) (actual time=0.039..0.223 rows=22 loops=1)
                                       ->  Index Scan using xyz_us_do_geoms_pk on xyz_us_do_geoms do_  (cost=0.56..8.58 rows=1 width=700) (actual time=0.023..0.041 rows=22 loops=1)
                                             Index Cond: ((x = 4) AND (y = 6) AND (z = 4))
                                       ->  Index Scan using mc_state_pkey on mc_state mc2012  (cost=0.28..8.30 rows=1 width=1767) (actual time=0.006..0.006 rows=1 loops=22)
                                             Index Cond: (((do_.geoid)::text = region_id) AND (month = '02/01/2012'::text))
                                 ->  Index Scan using mc_state_pkey on mc_state mc2013  (cost=0.28..8.30 rows=1 width=1767) (actual time=0.003..0.003 rows=1 loops=22)
                                       Index Cond: (((do_.geoid)::text = region_id) AND (month = '02/01/2013'::text))
                           ->  Index Scan using mc_state_pkey on mc_state mc2014  (cost=0.28..8.30 rows=1 width=1767) (actual time=0.003..0.003 rows=1 loops=22)
                                 Index Cond: (((do_.geoid)::text = region_id) AND (month = '02/01/2014'::text))
                     ->  Index Scan using mc_state_pkey on mc_state mc2015  (cost=0.28..8.30 rows=1 width=1767) (actual time=0.003..0.003 rows=1 loops=22)
                           Index Cond: (((do_.geoid)::text = region_id) AND (month = '02/01/2015'::text))
               ->  Index Scan using mc_state_pkey on mc_state mc2016  (cost=0.28..8.30 rows=1 width=1767) (actual time=0.004..0.004 rows=1 loops=22)
                     Index Cond: (((do_.geoid)::text = region_id) AND (month = '02/01/2016'::text))
         ->  Index Scan using mc_state_pkey on mc_state mc2017  (cost=0.28..8.30 rows=1 width=1767) (actual time=0.003..0.003 rows=1 loops=22)
               Index Cond: (((do_.geoid)::text = region_id) AND (month = '02/01/2017'::text))
   ->  Index Scan using mc_state_pkey on mc_state mc2018  (cost=0.28..8.30 rows=1 width=1767) (actual time=0.004..0.004 rows=1 loops=22)
         Index Cond: (((do_.geoid)::text = region_id) AND (month = '02/01/2018'::text))
 Planning time: 9.039 ms
 Execution time: 1.541 ms
(25 rows)

Counties, zoom level 5

Query
explain analyze
select *
from tiler.xyz_us_do_geoms do_
left outer join "us.mastercard".mc_county mc2012 on do_.geoid = mc2012.region_id and mc2012.month = '02/01/2012'
left outer join "us.mastercard".mc_county mc2013 on do_.geoid = mc2013.region_id and mc2013.month = '02/01/2013'
left outer join "us.mastercard".mc_county mc2014 on do_.geoid = mc2014.region_id and mc2014.month = '02/01/2014'
left outer join "us.mastercard".mc_county mc2015 on do_.geoid = mc2015.region_id and mc2015.month = '02/01/2015'
left outer join "us.mastercard".mc_county mc2016 on do_.geoid = mc2016.region_id and mc2016.month = '02/01/2016'
left outer join "us.mastercard".mc_county mc2017 on do_.geoid = mc2017.region_id and mc2017.month = '02/01/2017'
left outer join "us.mastercard".mc_county mc2018 on do_.geoid = mc2018.region_id and mc2018.month = '02/01/2018'
where x = 8
  and y = 12
  and z = 5;
Feature count
895
Execution plan (removed geometry for legibility)
 Nested Loop Left Join  (cost=3.50..67.70 rows=1 width=3619) (actual time=0.094..44.638 rows=895 loops=1)
   ->  Nested Loop Left Join  (cost=3.08..59.26 rows=1 width=3202) (actual time=0.086..38.039 rows=895 loops=1)
         ->  Nested Loop Left Join  (cost=2.66..50.81 rows=1 width=2785) (actual time=0.078..31.579 rows=895 loops=1)
               ->  Nested Loop Left Join  (cost=2.24..42.37 rows=1 width=2368) (actual time=0.070..25.355 rows=895 loops=1)
                     ->  Nested Loop Left Join  (cost=1.82..33.92 rows=1 width=1951) (actual time=0.056..19.455 rows=895 loops=1)
                           ->  Nested Loop Left Join  (cost=1.40..25.48 rows=1 width=1534) (actual time=0.048..13.778 rows=895 loops=1)
                                 ->  Nested Loop Left Join  (cost=0.98..17.03 rows=1 width=1117) (actual time=0.040..7.611 rows=895 loops=1)
                                       ->  Index Scan using xyz_us_do_geoms_pk on xyz_us_do_geoms do_  (cost=0.56..8.58 rows=1 width=700) (actual time=0.023..0.485 rows=895 loops=1)
                                             Index Cond: ((x = 8) AND (y = 12) AND (z = 5))
                                       ->  Index Scan using mc_county_pkey on mc_county mc2012  (cost=0.42..8.44 rows=1 width=417) (actual time=0.006..0.006 rows=1 loops=895)
                                             Index Cond: (((do_.geoid)::text = region_id) AND (month = '02/01/2012'::text))
                                 ->  Index Scan using mc_county_pkey on mc_county mc2013  (cost=0.42..8.44 rows=1 width=417) (actual time=0.005..0.005 rows=1 loops=895)
                                       Index Cond: (((do_.geoid)::text = region_id) AND (month = '02/01/2013'::text))
                           ->  Index Scan using mc_county_pkey on mc_county mc2014  (cost=0.42..8.44 rows=1 width=417) (actual time=0.004..0.004 rows=1 loops=895)
                                 Index Cond: (((do_.geoid)::text = region_id) AND (month = '02/01/2014'::text))
                     ->  Index Scan using mc_county_pkey on mc_county mc2015  (cost=0.42..8.44 rows=1 width=417) (actual time=0.004..0.004 rows=1 loops=895)
                           Index Cond: (((do_.geoid)::text = region_id) AND (month = '02/01/2015'::text))
               ->  Index Scan using mc_county_pkey on mc_county mc2016  (cost=0.42..8.44 rows=1 width=417) (actual time=0.004..0.004 rows=1 loops=895)
                     Index Cond: (((do_.geoid)::text = region_id) AND (month = '02/01/2016'::text))
         ->  Index Scan using mc_county_pkey on mc_county mc2017  (cost=0.42..8.44 rows=1 width=417) (actual time=0.004..0.004 rows=1 loops=895)
               Index Cond: (((do_.geoid)::text = region_id) AND (month = '02/01/2017'::text))
   ->  Index Scan using mc_county_pkey on mc_county mc2018  (cost=0.42..8.44 rows=1 width=417) (actual time=0.004..0.004 rows=1 loops=895)
         Index Cond: (((do_.geoid)::text = region_id) AND (month = '02/01/2018'::text))
 Planning time: 10.161 ms
 Execution time: 45.353 ms

Counties, zoom level 6

Query
explain analyze
select *
from tiler.xyz_us_do_geoms do_
left outer join "us.mastercard".mc_county mc2012 on do_.geoid = mc2012.region_id and mc2012.month = '02/01/2012'
left outer join "us.mastercard".mc_county mc2013 on do_.geoid = mc2013.region_id and mc2013.month = '02/01/2013'
left outer join "us.mastercard".mc_county mc2014 on do_.geoid = mc2014.region_id and mc2014.month = '02/01/2014'
left outer join "us.mastercard".mc_county mc2015 on do_.geoid = mc2015.region_id and mc2015.month = '02/01/2015'
left outer join "us.mastercard".mc_county mc2016 on do_.geoid = mc2016.region_id and mc2016.month = '02/01/2016'
left outer join "us.mastercard".mc_county mc2017 on do_.geoid = mc2017.region_id and mc2017.month = '02/01/2017'
left outer join "us.mastercard".mc_county mc2018 on do_.geoid = mc2018.region_id and mc2018.month = '02/01/2018'
where x = 18
  and y = 24
  and z = 6;
Feature count
185
Execution plan (removed geometry for legibility)
 Nested Loop Left Join  (cost=3.50..67.70 rows=1 width=3619) (actual time=0.107..9.825 rows=185 loops=1)
   ->  Nested Loop Left Join  (cost=3.08..59.26 rows=1 width=3202) (actual time=0.100..8.430 rows=185 loops=1)
         ->  Nested Loop Left Join  (cost=2.66..50.81 rows=1 width=2785) (actual time=0.092..7.075 rows=185 loops=1)
               ->  Nested Loop Left Join  (cost=2.24..42.37 rows=1 width=2368) (actual time=0.085..5.744 rows=185 loops=1)
                     ->  Nested Loop Left Join  (cost=1.82..33.92 rows=1 width=1951) (actual time=0.078..4.411 rows=185 loops=1)
                           ->  Nested Loop Left Join  (cost=1.40..25.48 rows=1 width=1534) (actual time=0.069..3.009 rows=185 loops=1)
                                 ->  Nested Loop Left Join  (cost=0.98..17.03 rows=1 width=1117) (actual time=0.062..1.758 rows=185 loops=1)
                                       ->  Index Scan using xyz_us_do_geoms_pk on xyz_us_do_geoms do_  (cost=0.56..8.58 rows=1 width=700) (actual time=0.046..0.166 rows=185 loops=1)
                                             Index Cond: ((x = 18) AND (y = 24) AND (z = 6))
                                       ->  Index Scan using mc_county_pkey on mc_county mc2012  (cost=0.42..8.44 rows=1 width=417) (actual time=0.006..0.006 rows=1 loops=185)
                                             Index Cond: (((do_.geoid)::text = region_id) AND (month = '02/01/2012'::text))
                                 ->  Index Scan using mc_county_pkey on mc_county mc2013  (cost=0.42..8.44 rows=1 width=417) (actual time=0.004..0.004 rows=1 loops=185)
                                       Index Cond: (((do_.geoid)::text = region_id) AND (month = '02/01/2013'::text))
                           ->  Index Scan using mc_county_pkey on mc_county mc2014  (cost=0.42..8.44 rows=1 width=417) (actual time=0.005..0.005 rows=1 loops=185)
                                 Index Cond: (((do_.geoid)::text = region_id) AND (month = '02/01/2014'::text))
                     ->  Index Scan using mc_county_pkey on mc_county mc2015  (cost=0.42..8.44 rows=1 width=417) (actual time=0.005..0.005 rows=1 loops=185)
                           Index Cond: (((do_.geoid)::text = region_id) AND (month = '02/01/2015'::text))
               ->  Index Scan using mc_county_pkey on mc_county mc2016  (cost=0.42..8.44 rows=1 width=417) (actual time=0.005..0.005 rows=1 loops=185)
                     Index Cond: (((do_.geoid)::text = region_id) AND (month = '02/01/2016'::text))
         ->  Index Scan using mc_county_pkey on mc_county mc2017  (cost=0.42..8.44 rows=1 width=417) (actual time=0.005..0.005 rows=1 loops=185)
               Index Cond: (((do_.geoid)::text = region_id) AND (month = '02/01/2017'::text))
   ->  Index Scan using mc_county_pkey on mc_county mc2018  (cost=0.42..8.44 rows=1 width=417) (actual time=0.004..0.004 rows=1 loops=185)
         Index Cond: (((do_.geoid)::text = region_id) AND (month = '02/01/2018'::text))
 Planning time: 10.764 ms
 Execution time: 10.569 ms

Counties, zoom level 7

Query
explain analyze
select *
from tiler.xyz_us_do_geoms do_
left outer join "us.mastercard".mc_county mc2012 on do_.geoid = mc2012.region_id and mc2012.month = '02/01/2012'
left outer join "us.mastercard".mc_county mc2013 on do_.geoid = mc2013.region_id and mc2013.month = '02/01/2013'
left outer join "us.mastercard".mc_county mc2014 on do_.geoid = mc2014.region_id and mc2014.month = '02/01/2014'
left outer join "us.mastercard".mc_county mc2015 on do_.geoid = mc2015.region_id and mc2015.month = '02/01/2015'
left outer join "us.mastercard".mc_county mc2016 on do_.geoid = mc2016.region_id and mc2016.month = '02/01/2016'
left outer join "us.mastercard".mc_county mc2017 on do_.geoid = mc2017.region_id and mc2017.month = '02/01/2017'
left outer join "us.mastercard".mc_county mc2018 on do_.geoid = mc2018.region_id and mc2018.month = '02/01/2018'
where x = 37
  and y = 48
  and z = 7;
Feature count
50
Execution plan (removed geometry for legibility)
 Nested Loop Left Join  (cost=3.50..67.70 rows=1 width=3619) (actual time=0.076..2.631 rows=50 loops=1)
   ->  Nested Loop Left Join  (cost=3.08..59.26 rows=1 width=3202) (actual time=0.068..2.254 rows=50 loops=1)
         ->  Nested Loop Left Join  (cost=2.66..50.81 rows=1 width=2785) (actual time=0.061..1.906 rows=50 loops=1)
               ->  Nested Loop Left Join  (cost=2.24..42.37 rows=1 width=2368) (actual time=0.054..1.549 rows=50 loops=1)
                     ->  Nested Loop Left Join  (cost=1.82..33.92 rows=1 width=1951) (actual time=0.047..1.232 rows=50 loops=1)
                           ->  Nested Loop Left Join  (cost=1.40..25.48 rows=1 width=1534) (actual time=0.039..0.832 rows=50 loops=1)
                                 ->  Nested Loop Left Join  (cost=0.98..17.03 rows=1 width=1117) (actual time=0.032..0.506 rows=50 loops=1)
                                       ->  Index Scan using xyz_us_do_geoms_pk on xyz_us_do_geoms do_  (cost=0.56..8.58 rows=1 width=700) (actual time=0.016..0.051 rows=50 loops=1)
                                             Index Cond: ((x = 37) AND (y = 48) AND (z = 7))
                                       ->  Index Scan using mc_county_pkey on mc_county mc2012  (cost=0.42..8.44 rows=1 width=417) (actual time=0.007..0.007 rows=1 loops=50)
                                             Index Cond: (((do_.geoid)::text = region_id) AND (month = '02/01/2012'::text))
                                 ->  Index Scan using mc_county_pkey on mc_county mc2013  (cost=0.42..8.44 rows=1 width=417) (actual time=0.004..0.004 rows=1 loops=50)
                                       Index Cond: (((do_.geoid)::text = region_id) AND (month = '02/01/2013'::text))
                           ->  Index Scan using mc_county_pkey on mc_county mc2014  (cost=0.42..8.44 rows=1 width=417) (actual time=0.005..0.005 rows=1 loops=50)
                                 Index Cond: (((do_.geoid)::text = region_id) AND (month = '02/01/2014'::text))
                     ->  Index Scan using mc_county_pkey on mc_county mc2015  (cost=0.42..8.44 rows=1 width=417) (actual time=0.004..0.004 rows=1 loops=50)
                           Index Cond: (((do_.geoid)::text = region_id) AND (month = '02/01/2015'::text))
               ->  Index Scan using mc_county_pkey on mc_county mc2016  (cost=0.42..8.44 rows=1 width=417) (actual time=0.004..0.004 rows=1 loops=50)
                     Index Cond: (((do_.geoid)::text = region_id) AND (month = '02/01/2016'::text))
         ->  Index Scan using mc_county_pkey on mc_county mc2017  (cost=0.42..8.44 rows=1 width=417) (actual time=0.004..0.004 rows=1 loops=50)
               Index Cond: (((do_.geoid)::text = region_id) AND (month = '02/01/2017'::text))
   ->  Index Scan using mc_county_pkey on mc_county mc2018  (cost=0.42..8.44 rows=1 width=417) (actual time=0.004..0.004 rows=1 loops=50)
         Index Cond: (((do_.geoid)::text = region_id) AND (month = '02/01/2018'::text))
 Planning time: 10.576 ms
 Execution time: 3.171 ms

Counties, zoom level 8

Query
explain analyze
select *
from tiler.xyz_us_do_geoms do_
left outer join "us.mastercard".mc_county mc2012 on do_.geoid = mc2012.region_id and mc2012.month = '02/01/2012'
left outer join "us.mastercard".mc_county mc2013 on do_.geoid = mc2013.region_id and mc2013.month = '02/01/2013'
left outer join "us.mastercard".mc_county mc2014 on do_.geoid = mc2014.region_id and mc2014.month = '02/01/2014'
left outer join "us.mastercard".mc_county mc2015 on do_.geoid = mc2015.region_id and mc2015.month = '02/01/2015'
left outer join "us.mastercard".mc_county mc2016 on do_.geoid = mc2016.region_id and mc2016.month = '02/01/2016'
left outer join "us.mastercard".mc_county mc2017 on do_.geoid = mc2017.region_id and mc2017.month = '02/01/2017'
left outer join "us.mastercard".mc_county mc2018 on do_.geoid = mc2018.region_id and mc2018.month = '02/01/2018'
where x = 75
  and y = 96
  and z = 8;
Feature count
20
Execution plan (removed geometry for legibility)
Nested Loop Left Join  (cost=3.50..67.70 rows=1 width=3619) (actual time=0.079..1.001 rows=20 loops=1)
  ->  Nested Loop Left Join  (cost=3.08..59.26 rows=1 width=3202) (actual time=0.071..0.866 rows=20 loops=1)
        ->  Nested Loop Left Join  (cost=2.66..50.81 rows=1 width=2785) (actual time=0.063..0.733 rows=20 loops=1)
              ->  Nested Loop Left Join  (cost=2.24..42.37 rows=1 width=2368) (actual time=0.056..0.600 rows=20 loops=1)
                    ->  Nested Loop Left Join  (cost=1.82..33.92 rows=1 width=1951) (actual time=0.048..0.474 rows=20 loops=1)
                          ->  Nested Loop Left Join  (cost=1.40..25.48 rows=1 width=1534) (actual time=0.040..0.355 rows=20 loops=1)
                                ->  Nested Loop Left Join  (cost=0.98..17.03 rows=1 width=1117) (actual time=0.033..0.234 rows=20 loops=1)
                                      ->  Index Scan using xyz_us_do_geoms_pk on xyz_us_do_geoms do_  (cost=0.56..8.58 rows=1 width=700) (actual time=0.015..0.032 rows=20 loops=1)
                                            Index Cond: ((x = 75) AND (y = 96) AND (z = 8))
                                      ->  Index Scan using mc_county_pkey on mc_county mc2012  (cost=0.42..8.44 rows=1 width=417) (actual time=0.007..0.007 rows=1 loops=20)
                                            Index Cond: (((do_.geoid)::text = region_id) AND (month = '02/01/2012'::text))
                                ->  Index Scan using mc_county_pkey on mc_county mc2013  (cost=0.42..8.44 rows=1 width=417) (actual time=0.004..0.004 rows=1 loops=20)
                                      Index Cond: (((do_.geoid)::text = region_id) AND (month = '02/01/2013'::text))
                          ->  Index Scan using mc_county_pkey on mc_county mc2014  (cost=0.42..8.44 rows=1 width=417) (actual time=0.004..0.004 rows=1 loops=20)
                                Index Cond: (((do_.geoid)::text = region_id) AND (month = '02/01/2014'::text))
                    ->  Index Scan using mc_county_pkey on mc_county mc2015  (cost=0.42..8.44 rows=1 width=417) (actual time=0.004..0.004 rows=1 loops=20)
                          Index Cond: (((do_.geoid)::text = region_id) AND (month = '02/01/2015'::text))
              ->  Index Scan using mc_county_pkey on mc_county mc2016  (cost=0.42..8.44 rows=1 width=417) (actual time=0.004..0.004 rows=1 loops=20)
                    Index Cond: (((do_.geoid)::text = region_id) AND (month = '02/01/2016'::text))
        ->  Index Scan using mc_county_pkey on mc_county mc2017  (cost=0.42..8.44 rows=1 width=417) (actual time=0.004..0.004 rows=1 loops=20)
              Index Cond: (((do_.geoid)::text = region_id) AND (month = '02/01/2017'::text))
  ->  Index Scan using mc_county_pkey on mc_county mc2018  (cost=0.42..8.44 rows=1 width=417) (actual time=0.004..0.004 rows=1 loops=20)
        Index Cond: (((do_.geoid)::text = region_id) AND (month = '02/01/2018'::text))
Planning time: 9.540 ms
Execution time: 2.542 ms

Census tracts, zoom level 9

Query
explain analyze
select *
from tiler.xyz_us_do_geoms do_
left outer join "us.mastercard".mc_tract mc2012 on do_.geoid = mc2012.region_id and mc2012.month = '02/01/2012'
left outer join "us.mastercard".mc_tract mc2013 on do_.geoid = mc2013.region_id and mc2013.month = '02/01/2013'
left outer join "us.mastercard".mc_tract mc2014 on do_.geoid = mc2014.region_id and mc2014.month = '02/01/2014'
left outer join "us.mastercard".mc_tract mc2015 on do_.geoid = mc2015.region_id and mc2015.month = '02/01/2015'
left outer join "us.mastercard".mc_tract mc2016 on do_.geoid = mc2016.region_id and mc2016.month = '02/01/2016'
left outer join "us.mastercard".mc_tract mc2017 on do_.geoid = mc2017.region_id and mc2017.month = '02/01/2017'
left outer join "us.mastercard".mc_tract mc2018 on do_.geoid = mc2018.region_id and mc2018.month = '02/01/2018'
where x = 150
  and y = 192
  and z = 9;
Feature count
2864
Execution plan (removed geometry for legibility)
 Nested Loop Left Join  (cost=4.47..44.63 rows=1 width=3661) (actual time=0.089..187.146 rows=2864 loops=1)
   ->  Nested Loop Left Join  (cost=3.91..39.19 rows=1 width=3238) (actual time=0.079..160.076 rows=2864 loops=1)
         ->  Nested Loop Left Join  (cost=3.35..33.74 rows=1 width=2815) (actual time=0.068..133.514 rows=2864 loops=1)
               ->  Nested Loop Left Join  (cost=2.79..28.29 rows=1 width=2392) (actual time=0.060..107.144 rows=2864 loops=1)
                     ->  Nested Loop Left Join  (cost=2.24..22.84 rows=1 width=1969) (actual time=0.052..80.895 rows=2864 loops=1)
                           ->  Nested Loop Left Join  (cost=1.68..18.44 rows=1 width=1546) (actual time=0.044..56.469 rows=2864 loops=1)
                                 ->  Nested Loop Left Join  (cost=1.12..12.99 rows=1 width=1123) (actual time=0.035..32.280 rows=2864 loops=1)
                                       ->  Index Scan using xyz_us_do_geoms_pk on xyz_us_do_geoms do_  (cost=0.56..8.58 rows=1 width=700) (actual time=0.017..1.362 rows=2864 loops=1)
                                             Index Cond: ((x = 150) AND (y = 192) AND (z = 9))
                                       ->  Index Scan using mc_tract_pkey on mc_tract mc2012  (cost=0.56..12.10 rows=3 width=423) (actual time=0.008..0.008 rows=1 loops=2864)
                                             Index Cond: (((do_.geoid)::text = region_id) AND (month = '02/01/2012'::text))
                                 ->  Index Scan using mc_tract_pkey on mc_tract mc2013  (cost=0.56..10.34 rows=2 width=423) (actual time=0.007..0.007 rows=1 loops=2864)
                                       Index Cond: (((do_.geoid)::text = region_id) AND (month = '02/01/2013'::text))
                           ->  Index Scan using mc_tract_pkey on mc_tract mc2014  (cost=0.56..12.10 rows=3 width=423) (actual time=0.006..0.006 rows=1 loops=2864)
                                 Index Cond: (((do_.geoid)::text = region_id) AND (month = '02/01/2014'::text))
                     ->  Index Scan using mc_tract_pkey on mc_tract mc2015  (cost=0.56..10.34 rows=2 width=423) (actual time=0.007..0.007 rows=1 loops=2864)
                           Index Cond: (((do_.geoid)::text = region_id) AND (month = '02/01/2015'::text))
               ->  Index Scan using mc_tract_pkey on mc_tract mc2016  (cost=0.56..10.34 rows=2 width=423) (actual time=0.007..0.007 rows=1 loops=2864)
                     Index Cond: (((do_.geoid)::text = region_id) AND (month = '02/01/2016'::text))
         ->  Index Scan using mc_tract_pkey on mc_tract mc2017  (cost=0.56..10.34 rows=2 width=423) (actual time=0.006..0.006 rows=1 loops=2864)
               Index Cond: (((do_.geoid)::text = region_id) AND (month = '02/01/2017'::text))
   ->  Index Scan using mc_tract_pkey on mc_tract mc2018  (cost=0.56..10.34 rows=2 width=423) (actual time=0.006..0.006 rows=1 loops=2864)
         Index Cond: (((do_.geoid)::text = region_id) AND (month = '02/01/2018'::text))
 Planning time: 10.307 ms
 Execution time: 187.923 ms

Census tracts, zoom level 10

Query
explain analyze
select *
from tiler.xyz_us_do_geoms do_
left outer join "us.mastercard".mc_tract mc2012 on do_.geoid = mc2012.region_id and mc2012.month = '02/01/2012'
left outer join "us.mastercard".mc_tract mc2013 on do_.geoid = mc2013.region_id and mc2013.month = '02/01/2013'
left outer join "us.mastercard".mc_tract mc2014 on do_.geoid = mc2014.region_id and mc2014.month = '02/01/2014'
left outer join "us.mastercard".mc_tract mc2015 on do_.geoid = mc2015.region_id and mc2015.month = '02/01/2015'
left outer join "us.mastercard".mc_tract mc2016 on do_.geoid = mc2016.region_id and mc2016.month = '02/01/2016'
left outer join "us.mastercard".mc_tract mc2017 on do_.geoid = mc2017.region_id and mc2017.month = '02/01/2017'
left outer join "us.mastercard".mc_tract mc2018 on do_.geoid = mc2018.region_id and mc2018.month = '02/01/2018'
where x = 301
  and y = 384
  and z = 10;
Feature count
1364
Execution plan (removed geometry for legibility)
 Nested Loop Left Join  (cost=4.47..44.63 rows=1 width=3661) (actual time=0.092..82.691 rows=1364 loops=1)
   ->  Nested Loop Left Join  (cost=3.91..39.19 rows=1 width=3238) (actual time=0.082..70.728 rows=1364 loops=1)
         ->  Nested Loop Left Join  (cost=3.35..33.74 rows=1 width=2815) (actual time=0.071..59.332 rows=1364 loops=1)
               ->  Nested Loop Left Join  (cost=2.79..28.29 rows=1 width=2392) (actual time=0.062..47.852 rows=1364 loops=1)
                     ->  Nested Loop Left Join  (cost=2.24..22.84 rows=1 width=1969) (actual time=0.053..36.857 rows=1364 loops=1)
                           ->  Nested Loop Left Join  (cost=1.68..18.44 rows=1 width=1546) (actual time=0.045..25.851 rows=1364 loops=1)
                                 ->  Nested Loop Left Join  (cost=1.12..12.99 rows=1 width=1123) (actual time=0.036..13.525 rows=1364 loops=1)
                                       ->  Index Scan using xyz_us_do_geoms_pk on xyz_us_do_geoms do_  (cost=0.56..8.58 rows=1 width=700) (actual time=0.017..0.585 rows=1364 loops=1)
                                             Index Cond: ((x = 301) AND (y = 384) AND (z = 10))
                                       ->  Index Scan using mc_tract_pkey on mc_tract mc2012  (cost=0.56..12.10 rows=3 width=423) (actual time=0.007..0.007 rows=1 loops=1364)
                                             Index Cond: (((do_.geoid)::text = region_id) AND (month = '02/01/2012'::text))
                                 ->  Index Scan using mc_tract_pkey on mc_tract mc2013  (cost=0.56..10.34 rows=2 width=423) (actual time=0.007..0.007 rows=1 loops=1364)
                                       Index Cond: (((do_.geoid)::text = region_id) AND (month = '02/01/2013'::text))
                           ->  Index Scan using mc_tract_pkey on mc_tract mc2014  (cost=0.56..12.10 rows=3 width=423) (actual time=0.006..0.006 rows=1 loops=1364)
                                 Index Cond: (((do_.geoid)::text = region_id) AND (month = '02/01/2014'::text))
                     ->  Index Scan using mc_tract_pkey on mc_tract mc2015  (cost=0.56..10.34 rows=2 width=423) (actual time=0.006..0.006 rows=1 loops=1364)
                           Index Cond: (((do_.geoid)::text = region_id) AND (month = '02/01/2015'::text))
               ->  Index Scan using mc_tract_pkey on mc_tract mc2016  (cost=0.56..10.34 rows=2 width=423) (actual time=0.006..0.006 rows=1 loops=1364)
                     Index Cond: (((do_.geoid)::text = region_id) AND (month = '02/01/2016'::text))
         ->  Index Scan using mc_tract_pkey on mc_tract mc2017  (cost=0.56..10.34 rows=2 width=423) (actual time=0.006..0.006 rows=1 loops=1364)
               Index Cond: (((do_.geoid)::text = region_id) AND (month = '02/01/2017'::text))
   ->  Index Scan using mc_tract_pkey on mc_tract mc2018  (cost=0.56..10.34 rows=2 width=423) (actual time=0.006..0.006 rows=1 loops=1364)
         Index Cond: (((do_.geoid)::text = region_id) AND (month = '02/01/2018'::text))
 Planning time: 11.478 ms
 Execution time: 83.329 ms

Census tracts, zoom level 11

Query
explain analyze
select *
from tiler.xyz_us_do_geoms do_
left outer join "us.mastercard".mc_tract mc2012 on do_.geoid = mc2012.region_id and mc2012.month = '02/01/2012'
left outer join "us.mastercard".mc_tract mc2013 on do_.geoid = mc2013.region_id and mc2013.month = '02/01/2013'
left outer join "us.mastercard".mc_tract mc2014 on do_.geoid = mc2014.region_id and mc2014.month = '02/01/2014'
left outer join "us.mastercard".mc_tract mc2015 on do_.geoid = mc2015.region_id and mc2015.month = '02/01/2015'
left outer join "us.mastercard".mc_tract mc2016 on do_.geoid = mc2016.region_id and mc2016.month = '02/01/2016'
left outer join "us.mastercard".mc_tract mc2017 on do_.geoid = mc2017.region_id and mc2017.month = '02/01/2017'
left outer join "us.mastercard".mc_tract mc2018 on do_.geoid = mc2018.region_id and mc2018.month = '02/01/2018'
where x = 603
  and y = 769
  and z = 11;
Feature count
729
Execution plan (removed geometry for legibility)
 Nested Loop Left Join  (cost=4.47..44.63 rows=1 width=3661) (actual time=0.094..45.224 rows=729 loops=1)
   ->  Nested Loop Left Join  (cost=3.91..39.19 rows=1 width=3238) (actual time=0.084..38.639 rows=729 loops=1)
         ->  Nested Loop Left Join  (cost=3.35..33.74 rows=1 width=2815) (actual time=0.075..32.286 rows=729 loops=1)
               ->  Nested Loop Left Join  (cost=2.79..28.29 rows=1 width=2392) (actual time=0.066..25.958 rows=729 loops=1)
                     ->  Nested Loop Left Join  (cost=2.24..22.84 rows=1 width=1969) (actual time=0.056..19.781 rows=729 loops=1)
                           ->  Nested Loop Left Join  (cost=1.68..18.44 rows=1 width=1546) (actual time=0.048..13.699 rows=729 loops=1)
                                 ->  Nested Loop Left Join  (cost=1.12..12.99 rows=1 width=1123) (actual time=0.038..7.517 rows=729 loops=1)
                                       ->  Index Scan using xyz_us_do_geoms_pk on xyz_us_do_geoms do_  (cost=0.56..8.58 rows=1 width=700) (actual time=0.018..0.317 rows=729 loops=1)
                                             Index Cond: ((x = 603) AND (y = 769) AND (z = 11))
                                       ->  Index Scan using mc_tract_pkey on mc_tract mc2012  (cost=0.56..12.10 rows=3 width=423) (actual time=0.008..0.008 rows=1 loops=729)
                                             Index Cond: (((do_.geoid)::text = region_id) AND (month = '02/01/2012'::text))
                                 ->  Index Scan using mc_tract_pkey on mc_tract mc2013  (cost=0.56..10.34 rows=2 width=423) (actual time=0.007..0.007 rows=1 loops=729)
                                       Index Cond: (((do_.geoid)::text = region_id) AND (month = '02/01/2013'::text))
                           ->  Index Scan using mc_tract_pkey on mc_tract mc2014  (cost=0.56..12.10 rows=3 width=423) (actual time=0.006..0.006 rows=1 loops=729)
                                 Index Cond: (((do_.geoid)::text = region_id) AND (month = '02/01/2014'::text))
                     ->  Index Scan using mc_tract_pkey on mc_tract mc2015  (cost=0.56..10.34 rows=2 width=423) (actual time=0.006..0.006 rows=1 loops=729)
                           Index Cond: (((do_.geoid)::text = region_id) AND (month = '02/01/2015'::text))
               ->  Index Scan using mc_tract_pkey on mc_tract mc2016  (cost=0.56..10.34 rows=2 width=423) (actual time=0.006..0.006 rows=1 loops=729)
                     Index Cond: (((do_.geoid)::text = region_id) AND (month = '02/01/2016'::text))
         ->  Index Scan using mc_tract_pkey on mc_tract mc2017  (cost=0.56..10.34 rows=2 width=423) (actual time=0.006..0.006 rows=1 loops=729)
               Index Cond: (((do_.geoid)::text = region_id) AND (month = '02/01/2017'::text))
   ->  Index Scan using mc_tract_pkey on mc_tract mc2018  (cost=0.56..10.34 rows=2 width=423) (actual time=0.006..0.006 rows=1 loops=729)
         Index Cond: (((do_.geoid)::text = region_id) AND (month = '02/01/2018'::text))
 Planning time: 10.006 ms
 Execution time: 45.841 ms

Block groups, zoom level 12

Query
explain analyze
select *
from tiler.xyz_us_do_geoms do_
left outer join "us.mastercard".mc_block_group mc2012 on do_.geoid = mc2012.region_id and mc2012.month = '02/01/2012'
left outer join "us.mastercard".mc_block_group mc2013 on do_.geoid = mc2013.region_id and mc2013.month = '02/01/2013'
left outer join "us.mastercard".mc_block_group mc2014 on do_.geoid = mc2014.region_id and mc2014.month = '02/01/2014'
left outer join "us.mastercard".mc_block_group mc2015 on do_.geoid = mc2015.region_id and mc2015.month = '02/01/2015'
left outer join "us.mastercard".mc_block_group mc2016 on do_.geoid = mc2016.region_id and mc2016.month = '02/01/2016'
left outer join "us.mastercard".mc_block_group mc2017 on do_.geoid = mc2017.region_id and mc2017.month = '02/01/2017'
left outer join "us.mastercard".mc_block_group mc2018 on do_.geoid = mc2018.region_id and mc2018.month = '02/01/2018'
where x = 1206
  and y = 1539
  and z = 12;
Feature count
792
Execution plan (removed geometry for legibility)
 Nested Loop Left Join  (cost=4.48..35.78 rows=1 width=3668) (actual time=0.110..51.163 rows=792 loops=1)
   ->  Nested Loop Left Join  (cost=3.92..31.90 rows=1 width=3244) (actual time=0.100..43.940 rows=792 loops=1)
         ->  Nested Loop Left Join  (cost=3.36..28.01 rows=1 width=2820) (actual time=0.090..36.497 rows=792 loops=1)
               ->  Nested Loop Left Join  (cost=2.80..24.13 rows=1 width=2396) (actual time=0.081..29.458 rows=792 loops=1)
                     ->  Nested Loop Left Join  (cost=2.24..20.24 rows=1 width=1972) (actual time=0.071..22.040 rows=792 loops=1)
                           ->  Nested Loop Left Join  (cost=1.68..16.36 rows=1 width=1548) (actual time=0.063..15.400 rows=792 loops=1)
                                 ->  Nested Loop Left Join  (cost=1.12..12.47 rows=1 width=1124) (actual time=0.040..8.763 rows=792 loops=1)
                                       ->  Index Scan using xyz_us_do_geoms_pk on xyz_us_do_geoms do_  (cost=0.56..8.58 rows=1 width=700) (actual time=0.019..0.334 rows=792 loops=1)
                                             Index Cond: ((x = 1206) AND (y = 1539) AND (z = 12))
                                       ->  Index Scan using mc_block_group_pkey on mc_block_group mc2012  (cost=0.56..13.86 rows=4 width=424) (actual time=0.008..0.008 rows=1 loops=792)
                                             Index Cond: (((do_.geoid)::text = region_id) AND (month = '02/01/2012'::text))
                                 ->  Index Scan using mc_block_group_pkey on mc_block_group mc2013  (cost=0.56..13.86 rows=4 width=424) (actual time=0.007..0.007 rows=1 loops=792)
                                       Index Cond: (((do_.geoid)::text = region_id) AND (month = '02/01/2013'::text))
                           ->  Index Scan using mc_block_group_pkey on mc_block_group mc2014  (cost=0.56..13.86 rows=4 width=424) (actual time=0.006..0.006 rows=1 loops=792)
                                 Index Cond: (((do_.geoid)::text = region_id) AND (month = '02/01/2014'::text))
                     ->  Index Scan using mc_block_group_pkey on mc_block_group mc2015  (cost=0.56..13.86 rows=4 width=424) (actual time=0.007..0.007 rows=1 loops=792)
                           Index Cond: (((do_.geoid)::text = region_id) AND (month = '02/01/2015'::text))
               ->  Index Scan using mc_block_group_pkey on mc_block_group mc2016  (cost=0.56..13.86 rows=4 width=424) (actual time=0.006..0.006 rows=1 loops=792)
                     Index Cond: (((do_.geoid)::text = region_id) AND (month = '02/01/2016'::text))
         ->  Index Scan using mc_block_group_pkey on mc_block_group mc2017  (cost=0.56..13.86 rows=4 width=424) (actual time=0.007..0.007 rows=1 loops=792)
               Index Cond: (((do_.geoid)::text = region_id) AND (month = '02/01/2017'::text))
   ->  Index Scan using mc_block_group_pkey on mc_block_group mc2018  (cost=0.56..13.86 rows=4 width=424) (actual time=0.006..0.006 rows=1 loops=792)
         Index Cond: (((do_.geoid)::text = region_id) AND (month = '02/01/2018'::text))
 Planning time: 11.572 ms
 Execution time: 51.902 ms

Block groups, zoom level 13

Query
explain analyze
select *
from tiler.xyz_us_do_geoms do_
left outer join "us.mastercard".mc_block_group mc2012 on do_.geoid = mc2012.region_id and mc2012.month = '02/01/2012'
left outer join "us.mastercard".mc_block_group mc2013 on do_.geoid = mc2013.region_id and mc2013.month = '02/01/2013'
left outer join "us.mastercard".mc_block_group mc2014 on do_.geoid = mc2014.region_id and mc2014.month = '02/01/2014'
left outer join "us.mastercard".mc_block_group mc2015 on do_.geoid = mc2015.region_id and mc2015.month = '02/01/2015'
left outer join "us.mastercard".mc_block_group mc2016 on do_.geoid = mc2016.region_id and mc2016.month = '02/01/2016'
left outer join "us.mastercard".mc_block_group mc2017 on do_.geoid = mc2017.region_id and mc2017.month = '02/01/2017'
left outer join "us.mastercard".mc_block_group mc2018 on do_.geoid = mc2018.region_id and mc2018.month = '02/01/2018'
where x = 2413
  and y = 3079
  and z = 13;
Feature count
117
Execution plan (removed geometry for legibility)
 Nested Loop Left Join  (cost=4.48..35.78 rows=1 width=3668) (actual time=0.098..7.641 rows=117 loops=1)
   ->  Nested Loop Left Join  (cost=3.92..31.90 rows=1 width=3244) (actual time=0.088..6.543 rows=117 loops=1)
         ->  Nested Loop Left Join  (cost=3.36..28.01 rows=1 width=2820) (actual time=0.078..5.492 rows=117 loops=1)
               ->  Nested Loop Left Join  (cost=2.80..24.13 rows=1 width=2396) (actual time=0.069..4.275 rows=117 loops=1)
                     ->  Nested Loop Left Join  (cost=2.24..20.24 rows=1 width=1972) (actual time=0.059..3.247 rows=117 loops=1)
                           ->  Nested Loop Left Join  (cost=1.68..16.36 rows=1 width=1548) (actual time=0.050..2.253 rows=117 loops=1)
                                 ->  Nested Loop Left Join  (cost=1.12..12.47 rows=1 width=1124) (actual time=0.041..1.302 rows=117 loops=1)
                                       ->  Index Scan using xyz_us_do_geoms_pk on xyz_us_do_geoms do_  (cost=0.56..8.58 rows=1 width=700) (actual time=0.018..0.068 rows=117 loops=1)
                                             Index Cond: ((x = 2413) AND (y = 3079) AND (z = 13))
                                       ->  Index Scan using mc_block_group_pkey on mc_block_group mc2012  (cost=0.56..13.86 rows=4 width=424) (actual time=0.008..0.008 rows=1 loops=117)
                                             Index Cond: (((do_.geoid)::text = region_id) AND (month = '02/01/2012'::text))
                                 ->  Index Scan using mc_block_group_pkey on mc_block_group mc2013  (cost=0.56..13.86 rows=4 width=424) (actual time=0.006..0.006 rows=1 loops=117)
                                       Index Cond: (((do_.geoid)::text = region_id) AND (month = '02/01/2013'::text))
                           ->  Index Scan using mc_block_group_pkey on mc_block_group mc2014  (cost=0.56..13.86 rows=4 width=424) (actual time=0.006..0.006 rows=1 loops=117)
                                 Index Cond: (((do_.geoid)::text = region_id) AND (month = '02/01/2014'::text))
                     ->  Index Scan using mc_block_group_pkey on mc_block_group mc2015  (cost=0.56..13.86 rows=4 width=424) (actual time=0.007..0.007 rows=1 loops=117)
                           Index Cond: (((do_.geoid)::text = region_id) AND (month = '02/01/2015'::text))
               ->  Index Scan using mc_block_group_pkey on mc_block_group mc2016  (cost=0.56..13.86 rows=4 width=424) (actual time=0.007..0.007 rows=1 loops=117)
                     Index Cond: (((do_.geoid)::text = region_id) AND (month = '02/01/2016'::text))
         ->  Index Scan using mc_block_group_pkey on mc_block_group mc2017  (cost=0.56..13.86 rows=4 width=424) (actual time=0.006..0.006 rows=1 loops=117)
               Index Cond: (((do_.geoid)::text = region_id) AND (month = '02/01/2017'::text))
   ->  Index Scan using mc_block_group_pkey on mc_block_group mc2018  (cost=0.56..13.86 rows=4 width=424) (actual time=0.006..0.006 rows=1 loops=117)
         Index Cond: (((do_.geoid)::text = region_id) AND (month = '02/01/2018'::text))
 Planning time: 11.223 ms
 Execution time: 8.262 ms

Blocks, zoom level 14

Query
explain analyze
select *
from tiler.xyz_us_do_geoms do_
left outer join "us.mastercard".mc_block mc2012 on do_.geoid = mc2012.region_id and mc2012.month = '02/01/2012'
left outer join "us.mastercard".mc_block mc2013 on do_.geoid = mc2013.region_id and mc2013.month = '02/01/2013'
left outer join "us.mastercard".mc_block mc2014 on do_.geoid = mc2014.region_id and mc2014.month = '02/01/2014'
left outer join "us.mastercard".mc_block mc2015 on do_.geoid = mc2015.region_id and mc2015.month = '02/01/2015'
left outer join "us.mastercard".mc_block mc2016 on do_.geoid = mc2016.region_id and mc2016.month = '02/01/2016'
left outer join "us.mastercard".mc_block mc2017 on do_.geoid = mc2017.region_id and mc2017.month = '02/01/2017'
left outer join "us.mastercard".mc_block mc2018 on do_.geoid = mc2018.region_id and mc2018.month = '02/01/2018'
where x = 4826
  and y = 6159
  and z = 14;
Feature count
333
Execution plan (removed geometry for legibility)
 Nested Loop Left Join  (cost=4.48..35.78 rows=1 width=3689) (actual time=0.112..21.245 rows=333 loops=1)
   ->  Nested Loop Left Join  (cost=3.92..31.90 rows=1 width=3262) (actual time=0.101..18.103 rows=333 loops=1)
         ->  Nested Loop Left Join  (cost=3.36..28.01 rows=1 width=2835) (actual time=0.090..14.858 rows=333 loops=1)
               ->  Nested Loop Left Join  (cost=2.80..24.13 rows=1 width=2408) (actual time=0.068..11.811 rows=333 loops=1)
                     ->  Nested Loop Left Join  (cost=2.24..20.24 rows=1 width=1981) (actual time=0.058..8.850 rows=333 loops=1)
                           ->  Nested Loop Left Join  (cost=1.68..16.36 rows=1 width=1554) (actual time=0.049..5.948 rows=333 loops=1)
                                 ->  Nested Loop Left Join  (cost=1.12..12.47 rows=1 width=1127) (actual time=0.039..3.123 rows=333 loops=1)
                                       ->  Index Scan using xyz_us_do_geoms_pk on xyz_us_do_geoms do_  (cost=0.56..8.58 rows=1 width=700) (actual time=0.019..0.149 rows=333 loops=1)
                                             Index Cond: ((x = 4826) AND (y = 6159) AND (z = 14))
                                       ->  Index Scan using mc_block_pkey on mc_block mc2012  (cost=0.56..13.86 rows=4 width=427) (actual time=0.007..0.007 rows=0 loops=333)
                                             Index Cond: (((do_.geoid)::text = region_id) AND (month = '02/01/2012'::text))
                                 ->  Index Scan using mc_block_pkey on mc_block mc2013  (cost=0.56..13.86 rows=4 width=427) (actual time=0.007..0.007 rows=0 loops=333)
                                       Index Cond: (((do_.geoid)::text = region_id) AND (month = '02/01/2013'::text))
                           ->  Index Scan using mc_block_pkey on mc_block mc2014  (cost=0.56..13.86 rows=4 width=427) (actual time=0.007..0.007 rows=0 loops=333)
                                 Index Cond: (((do_.geoid)::text = region_id) AND (month = '02/01/2014'::text))
                     ->  Index Scan using mc_block_pkey on mc_block mc2015  (cost=0.56..13.86 rows=4 width=427) (actual time=0.007..0.007 rows=0 loops=333)
                           Index Cond: (((do_.geoid)::text = region_id) AND (month = '02/01/2015'::text))
               ->  Index Scan using mc_block_pkey on mc_block mc2016  (cost=0.56..13.86 rows=4 width=427) (actual time=0.007..0.007 rows=0 loops=333)
                     Index Cond: (((do_.geoid)::text = region_id) AND (month = '02/01/2016'::text))
         ->  Index Scan using mc_block_pkey on mc_block mc2017  (cost=0.56..13.86 rows=4 width=427) (actual time=0.007..0.007 rows=0 loops=333)
               Index Cond: (((do_.geoid)::text = region_id) AND (month = '02/01/2017'::text))
   ->  Index Scan using mc_block_pkey on mc_block mc2018  (cost=0.56..13.86 rows=4 width=427) (actual time=0.007..0.007 rows=0 loops=333)
         Index Cond: (((do_.geoid)::text = region_id) AND (month = '02/01/2018'::text))
 Planning time: 13.950 ms
 Execution time: 21.886 ms
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment