explain.depesz.com

PostgreSQL's explain analyze made readable

Result: KBgH

Settings
# exclusive inclusive rows x rows loops node
1. 133,831.585 133,831.585 ↓ 154,433.0 154,433 1

CTE Scan on zones_roads (cost=909,871.34..909,871.36 rows=1 width=56) (actual time=10,717.383..133,831.585 rows=154,433 loops=1)

2.          

CTE address_count

3. 125.952 10,077.407 ↓ 9,366.6 65,566 1

GroupAggregate (cost=849,653.38..849,653.50 rows=7 width=12) (actual time=9,878.890..10,077.407 rows=65,566 loops=1)

  • Group Key: tbl_roads.id
4.          

Initplan (forGroupAggregate)

5. 0.019 0.019 ↑ 1.0 1 1

Seq Scan on tbl_zone_layers zl (cost=0.00..4.17 rows=1 width=32) (actual time=0.009..0.019 rows=1 loops=1)

  • Filter: (id = 11)
  • Rows Removed by Filter: 172
6. 0.094 0.108 ↑ 1.0 1 1

Index Scan using tbl_zones_layer_id_idx on tbl_zones (cost=4.59..1,104.31 rows=1 width=3,266) (actual time=0.074..0.108 rows=1 loops=1)

  • Index Cond: (layer_id = 1)
  • Filter: ((name)::text = ($1)::text)
  • Rows Removed by Filter: 257
7.          

Initplan (forIndex Scan)

8. 0.014 0.014 ↑ 1.0 1 1

Seq Scan on tbl_zone_layers zl_1 (cost=0.00..4.16 rows=1 width=3) (actual time=0.004..0.014 rows=1 loops=1)

  • Filter: (id = 11)
  • Rows Removed by Filter: 172
9. 220.546 9,951.328 ↓ 90,340.0 632,380 1

Sort (cost=848,544.90..848,544.92 rows=7 width=8) (actual time=9,878.871..9,951.328 rows=632,380 loops=1)

  • Sort Key: tbl_roads.id
  • Sort Method: quicksort Memory: 54219kB
10. 206.460 9,730.782 ↓ 90,340.0 632,380 1

Nested Loop (cost=0.99..848,544.80 rows=7 width=8) (actual time=0.225..9,730.782 rows=632,380 loops=1)

11. 35.467 35.467 ↓ 1.9 155,555 1

Index Scan using uq_addresses_datasetid_objectid on tbl_addresses (cost=0.57..152,119.73 rows=81,898 width=36) (actual time=0.015..35.467 rows=155,555 loops=1)

  • Index Cond: (dataset_id = ANY ('{10}'::integer[]))
12. 9,488.855 9,488.855 ↓ 4.0 4 155,555

Index Scan using tbl_roads_geo_linestring_idx on tbl_roads (cost=0.42..8.49 rows=1 width=110) (actual time=0.041..0.061 rows=4 loops=155,555)

  • Index Cond: ((geo_linestring && st_expand(tbl_addresses.geo_location, '0.00050000000000000001'::double precision)) AND (geo_linestring && $2))
  • Filter: ((dataset_id = 223) AND ((country)::text = $0) AND (tbl_addresses.geo_location && st_expand(geo_linestring, '0.00050000000000000001'::double precision)) AND _st_dwithin(geo_linestring, tbl_addresses.geo_location, '0.00050000000000000001'::double precision))
  • Rows Removed by Filter: 15
13.          

CTE roads

14. 154.589 11,280.488 ↓ 557.0 150,398 1

Hash Left Join (cost=1,599.82..57,923.55 rows=270 width=129) (actual time=10,716.733..11,280.488 rows=150,398 loops=1)

  • Hash Cond: (r.id = ac.id)
15.          

Initplan (forHash Left Join)

16. 0.042 0.042 ↑ 1.0 1 1

Seq Scan on tbl_zone_layers zl_2 (cost=0.00..4.17 rows=1 width=32) (actual time=0.030..0.042 rows=1 loops=1)

  • Filter: (id = 11)
  • Rows Removed by Filter: 172
17. 0.239 0.284 ↑ 1.0 1 1

Index Scan using tbl_zones_layer_id_idx on tbl_zones tbl_zones_1 (cost=4.59..1,104.31 rows=1 width=3,266) (actual time=0.203..0.284 rows=1 loops=1)

  • Index Cond: (layer_id = 1)
  • Filter: ((name)::text = ($6)::text)
  • Rows Removed by Filter: 257
18.          

Initplan (forIndex Scan)

19. 0.045 0.045 ↑ 1.0 1 1

Seq Scan on tbl_zone_layers zl_3 (cost=0.00..4.16 rows=1 width=3) (actual time=0.014..0.045 rows=1 loops=1)

  • Filter: (id = 11)
  • Rows Removed by Filter: 172
20. 818.703 1,027.812 ↓ 557.0 150,398 1

Bitmap Heap Scan on tbl_roads r (cost=491.11..56,813.82 rows=270 width=121) (actual time=618.952..1,027.812 rows=150,398 loops=1)

  • Recheck Cond: (geo_linestring && $7)
  • Filter: ((dataset_id = 223) AND ((country)::text = $5))
  • Rows Removed by Filter: 536769
  • Heap Blocks: exact=384711
21. 209.109 209.109 ↓ 46.6 687,167 1

Bitmap Index Scan on tbl_roads_geo_linestring_idx (cost=0.00..491.04 rows=14,749 width=0) (actual time=209.109..209.109 rows=687,167 loops=1)

  • Index Cond: (geo_linestring && $7)
22. 7.467 10,097.761 ↓ 9,366.6 65,566 1

Hash (cost=0.14..0.14 rows=7 width=12) (actual time=10,097.761..10,097.761 rows=65,566 loops=1)

  • Buckets: 131072 (originally 1024) Batches: 1 (originally 1) Memory Usage: 3842kB
23. 10,090.294 10,090.294 ↓ 9,366.6 65,566 1

CTE Scan on address_count ac (cost=0.00..0.14 rows=7 width=12) (actual time=9,878.893..10,090.294 rows=65,566 loops=1)

24.          

CTE zones_roads

25. 274.753 133,662.246 ↓ 154,433.0 154,433 1

Nested Loop (cost=0.29..2,294.29 rows=1 width=56) (actual time=10,717.380..133,662.246 rows=154,433 loops=1)

26. 11,414.715 11,414.715 ↓ 557.0 150,398 1

CTE Scan on roads r_1 (cost=0.00..5.40 rows=270 width=84) (actual time=10,716.734..11,414.715 rows=150,398 loops=1)

27. 121,972.778 121,972.778 ↑ 1.0 1 150,398

Index Scan using tbl_zones_geo_polygon_idx on tbl_zones z (cost=0.29..8.47 rows=1 width=3,270) (actual time=0.601..0.811 rows=1 loops=150,398)

  • Index Cond: ((r_1.geo_linestring && geo_polygon) AND (r_1.geo_linestring && geo_polygon))
  • Filter: ((layer_id = ANY ('{11}'::integer[])) AND _st_intersects(r_1.geo_linestring, geo_polygon))
  • Rows Removed by Filter: 28
Planning time : 1.773 ms
Execution time : 133,863.466 ms