explain.depesz.com

PostgreSQL's explain analyze made readable

Result: cpua

Settings
# exclusive inclusive rows x rows loops node
1. 0.027 30,071.969 ↓ 109.0 109 1

Sort (cost=35,647,904.95..35,647,904.96 rows=1 width=20) (actual time=30,071.961..30,071.969 rows=109 loops=1)

  • Sort Key: ads.count DESC
  • Sort Method: quicksort Memory: 33kB
2.          

CTE tz

3. 0.518 490.211 ↑ 1.0 2,000 1

Limit (cost=177,447.72..177,452.72 rows=2,000 width=2,472) (actual time=486.265..490.211 rows=2,000 loops=1)

4. 49.917 489.693 ↑ 53.9 2,000 1

Sort (cost=177,447.72..177,717.05 rows=107,731 width=2,472) (actual time=486.264..489.693 rows=2,000 loops=1)

  • Sort Key: (st_area(tbl_zones.geo_polygon))
  • Sort Method: top-N heapsort Memory: 7527kB
5. 439.776 439.776 ↑ 1.1 102,581 1

Index Scan using uq_zones on tbl_zones (cost=0.42..171,002.29 rows=107,731 width=2,472) (actual time=0.029..439.776 rows=102,581 loops=1)

  • Index Cond: (layer_id = 90)
6.          

CTE ads

7. 0.788 20,000.802 ↓ 10.0 2,000 1

Sort (cost=29,749,115.46..29,749,115.96 rows=200 width=12) (actual time=20,000.567..20,000.802 rows=2,000 loops=1)

  • Sort Key: (count(DISTINCT tbl_addresses.id))
  • Sort Method: quicksort Memory: 142kB
8. 24.088 20,000.014 ↓ 10.0 2,000 1

GroupAggregate (cost=29,641,108.45..29,749,107.82 rows=200 width=12) (actual time=19,967.811..20,000.014 rows=2,000 loops=1)

  • Group Key: tz.id
9. 35.925 19,975.926 ↑ 229.4 62,780 1

Sort (cost=29,641,108.45..29,677,107.57 rows=14,399,649 width=8) (actual time=19,967.779..19,975.926 rows=62,780 loops=1)

  • Sort Key: tz.id
  • Sort Method: quicksort Memory: 4479kB
10. 18.174 19,940.001 ↑ 229.4 62,780 1

Nested Loop Left Join (cost=0.42..27,929,024.00 rows=14,399,649 width=8) (actual time=486.288..19,940.001 rows=62,780 loops=1)

11. 495.827 495.827 ↑ 1.0 2,000 1

CTE Scan on tz (cost=0.00..40.00 rows=2,000 width=36) (actual time=486.267..495.827 rows=2,000 loops=1)

12. 19,426.000 19,426.000 ↑ 232.3 31 2,000

Index Scan using tbl_addresses_geo_location_idx on tbl_addresses (cost=0.42..13,892.49 rows=7,200 width=36) (actual time=3.655..9.713 rows=31 loops=2,000)

  • Index Cond: (tz.geo_polygon ~ geo_location)
  • Filter: ((dataset_id = 52) AND _st_contains(tz.geo_polygon, geo_location))
  • Rows Removed by Filter: 85
13.          

CTE rds

14. 0.616 10,070.278 ↓ 10.0 2,000 1

Sort (cost=5,721,326.49..5,721,326.99 rows=200 width=12) (actual time=10,070.044..10,070.278 rows=2,000 loops=1)

  • Sort Key: (count(DISTINCT tbl_roads.id))
  • Sort Method: quicksort Memory: 142kB
15. 14.784 10,069.662 ↓ 10.0 2,000 1

GroupAggregate (cost=5,599,492.46..5,721,318.85 rows=200 width=12) (actual time=10,049.896..10,069.662 rows=2,000 loops=1)

  • Group Key: tz_1.id
16. 29.952 10,054.878 ↑ 291.6 55,702 1

Sort (cost=5,599,492.46..5,640,100.59 rows=16,243,252 width=8) (actual time=10,049.863..10,054.878 rows=55,702 loops=1)

  • Sort Key: tz_1.id
  • Sort Method: quicksort Memory: 4148kB
17. 15.753 10,024.926 ↑ 291.6 55,702 1

Nested Loop Left Join (cost=0.42..3,654,092.00 rows=16,243,252 width=8) (actual time=0.202..10,024.926 rows=55,702 loops=1)

18. 3.173 3.173 ↑ 1.0 2,000 1

CTE Scan on tz tz_1 (cost=0.00..40.00 rows=2,000 width=36) (actual time=0.002..3.173 rows=2,000 loops=1)

19. 10,006.000 10,006.000 ↑ 29.0 28 2,000

Index Scan using tbl_roads_geo_linestring_idx on tbl_roads (cost=0.42..1,818.91 rows=812 width=94) (actual time=1.956..5.003 rows=28 loops=2,000)

  • Index Cond: (geo_linestring && tz_1.geo_polygon)
  • Filter: _st_intersects(geo_linestring, tz_1.geo_polygon)
  • Rows Removed by Filter: 23
20. 0.220 30,071.942 ↓ 109.0 109 1

Hash Join (cost=4.51..9.27 rows=1 width=20) (actual time=30,071.212..30,071.942 rows=109 loops=1)

  • Hash Cond: (ads.id = rds.id)
21. 20,001.089 20,001.089 ↓ 10.0 2,000 1

CTE Scan on ads (cost=0.00..4.00 rows=200 width=12) (actual time=20,000.569..20,001.089 rows=2,000 loops=1)

22. 0.020 10,070.633 ↓ 109.0 109 1

Hash (cost=4.50..4.50 rows=1 width=12) (actual time=10,070.633..10,070.633 rows=109 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
23. 10,070.613 10,070.613 ↓ 109.0 109 1

CTE Scan on rds (cost=0.00..4.50 rows=1 width=12) (actual time=10,070.049..10,070.613 rows=109 loops=1)

  • Filter: (count = 0)
  • Rows Removed by Filter: 1891
Planning time : 0.688 ms
Execution time : 30,072.379 ms