explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Gwiv

Settings
# exclusive inclusive rows x rows loops node
1. 0.065 83,725.629 ↑ 1.0 100 1

Sort (cost=7,132,204,251.83..7,132,204,252.08 rows=100 width=20) (actual time=83,725.620..83,725.629 rows=100 loops=1)

  • Sort Key: (count(DISTINCT tr.id))
  • Sort Method: quicksort Memory: 32kB
2. 2,805.473 83,725.564 ↑ 1.0 100 1

GroupAggregate (cost=7,073,729,938.64..7,132,204,248.51 rows=100 width=20) (actual time=80,287.126..83,725.564 rows=100 loops=1)

  • Group Key: tbl_zones.id
3. 2,872.163 80,920.091 ↑ 458.1 12,764,345 1

Sort (cost=7,073,729,938.64..7,088,348,515.86 rows=5,847,430,887 width=12) (actual time=80,286.591..80,920.091 rows=12,764,345 loops=1)

  • Sort Key: tbl_zones.id
  • Sort Method: quicksort Memory: 991545kB
4. 1,444.435 78,047.928 ↑ 458.1 12,764,345 1

Nested Loop Left Join (cost=1,478.18..6,025,194,227.55 rows=5,847,430,887 width=12) (actual time=4.574..78,047.928 rows=12,764,345 loops=1)

5. 4.516 354.845 ↑ 31.6 22,788 1

Nested Loop Left Join (cost=1,395.60..4,465,382.11 rows=719,982 width=2,468) (actual time=3.688..354.845 rows=22,788 loops=1)

6. 0.033 0.229 ↑ 1.0 100 1

Limit (cost=0.42..156.66 rows=100 width=2,464) (actual time=0.018..0.229 rows=100 loops=1)

7. 0.196 0.196 ↑ 1,077.3 100 1

Index Scan using uq_zones on tbl_zones (cost=0.42..168,309.01 rows=107,731 width=2,464) (actual time=0.017..0.196 rows=100 loops=1)

  • Index Cond: (layer_id = 90)
8. 335.700 350.100 ↑ 31.6 228 100

Bitmap Heap Scan on tbl_addresses (cost=1,395.18..44,580.24 rows=7,200 width=36) (actual time=0.824..3.501 rows=228 loops=100)

  • Recheck Cond: (tbl_zones.geo_polygon ~ geo_location)
  • Filter: ((dataset_id = 52) AND _st_contains(tbl_zones.geo_polygon, geo_location))
  • Rows Removed by Filter: 686
  • Heap Blocks: exact=91250
9. 14.400 14.400 ↑ 47.1 914 100

Bitmap Index Scan on tbl_addresses_geo_location_idx (cost=0.00..1,393.38 rows=43,013 width=0) (actual time=0.144..0.144 rows=914 loops=100)

  • Index Cond: (tbl_zones.geo_polygon ~ geo_location)
10. 73,286.208 76,248.648 ↑ 1.4 560 22,788

Bitmap Heap Scan on tbl_roads tr (cost=82.57..8,354.21 rows=812 width=94) (actual time=0.202..3.346 rows=560 loops=22,788)

  • Recheck Cond: (geo_linestring && tbl_zones.geo_polygon)
  • Filter: _st_intersects(geo_linestring, tbl_zones.geo_polygon)
  • Rows Removed by Filter: 417
  • Heap Blocks: exact=16156009
11. 2,962.440 2,962.440 ↑ 2.5 977 22,788

Bitmap Index Scan on tbl_roads_geo_linestring_idx (cost=0.00..82.37 rows=2,436 width=0) (actual time=0.130..0.130 rows=977 loops=22,788)

  • Index Cond: (geo_linestring && tbl_zones.geo_polygon)