explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ReWB

Settings
# exclusive inclusive rows x rows loops node
1. 0.085 85,691.906 ↑ 1.0 100 1

Sort (cost=7,132,204,251.83..7,132,204,252.08 rows=100 width=20) (actual time=85,691.899..85,691.906 rows=100 loops=1)

  • Sort Key: (count(DISTINCT tr.id))
  • Sort Method: quicksort Memory: 32kB
2. 2,863.248 85,691.821 ↑ 1.0 100 1

GroupAggregate (cost=7,073,729,938.64..7,132,204,248.51 rows=100 width=20) (actual time=82,175.656..85,691.821 rows=100 loops=1)

  • Group Key: tbl_zones.id
3. 2,970.015 82,828.573 ↑ 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=82,175.105..82,828.573 rows=12,764,345 loops=1)

  • Sort Key: tbl_zones.id
  • Sort Method: quicksort Memory: 991545kB
4. 1,501.744 79,858.558 ↑ 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.866..79,858.558 rows=12,764,345 loops=1)

5. 5.147 376.278 ↑ 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.457..376.278 rows=22,788 loops=1)

6. 0.039 0.331 ↑ 1.0 100 1

Limit (cost=0.42..156.66 rows=100 width=2,660) (actual time=0.023..0.331 rows=100 loops=1)

7. 0.292 0.292 ↑ 1,077.3 100 1

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

  • Index Cond: (layer_id = 90)
8. 356.300 370.800 ↑ 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.855..3.708 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.500 14.500 ↑ 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.145..0.145 rows=914 loops=100)

  • Index Cond: (tbl_zones.geo_polygon ~ geo_location)
10. 74,926.944 77,980.536 ↑ 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.207..3.422 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. 3,053.592 3,053.592 ↑ 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.134..0.134 rows=977 loops=22,788)

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