explain.depesz.com

PostgreSQL's explain analyze made readable

Result: AvAf

Settings
# exclusive inclusive rows x rows loops node
1. 0.202 42,307.787 ↑ 1,158.7 863 1

Hash Right Join (cost=128,726,536.07..128,784,036.07 rows=1,000,000 width=96) (actual time=42,307.511..42,307.787 rows=863 loops=1)

  • Hash Cond: (grid.id = g.gid)
2.          

CTE bbox

3. 0.002 0.002 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=1)

4.          

CTE locations

5. 612.673 652.154 ↓ 397.7 187,720 1

Bitmap Heap Scan on vw_pop_per_address (cost=51.20..5,646.56 rows=472 width=51) (actual time=40.130..652.154 rows=187,720 loops=1)

  • Recheck Cond: ($2 && geom)
  • Filter: st_intersects($1, geom)
  • Rows Removed by Filter: 10806
  • Heap Blocks: exact=2513
6.          

Initplan (forBitmap Heap Scan)

7. 0.001 0.001 ↑ 1.0 1 1

Limit (cost=0.00..0.02 rows=1 width=32) (actual time=0.001..0.001 rows=1 loops=1)

8. 0.000 0.000 ↑ 1.0 1 1

CTE Scan on bbox (cost=0.00..0.02 rows=1 width=32) (actual time=0.000..0.000 rows=1 loops=1)

9. 0.002 0.006 ↑ 1.0 1 1

Limit (cost=0.00..0.02 rows=1 width=32) (actual time=0.005..0.006 rows=1 loops=1)

10. 0.004 0.004 ↑ 1.0 1 1

CTE Scan on bbox bbox_1 (cost=0.00..0.02 rows=1 width=32) (actual time=0.004..0.004 rows=1 loops=1)

11. 39.474 39.474 ↓ 140.2 198,526 1

Bitmap Index Scan on vw_pop_per_address_geom_idx (cost=0.00..51.04 rows=1,416 width=0) (actual time=39.474..39.474 rows=198,526 loops=1)

  • Index Cond: ($2 && geom)
12.          

CTE grid

13. 14.224 14.325 ↑ 1,040.6 961 1

Nested Loop (cost=0.01..75,010.01 rows=1,000,000 width=104) (actual time=0.455..14.325 rows=961 loops=1)

14. 0.008 0.008 ↑ 32.3 31 1

Function Scan on generate_series x (cost=0.00..10.00 rows=1,000 width=4) (actual time=0.006..0.008 rows=31 loops=1)

15. 0.093 0.093 ↑ 32.3 31 31

Function Scan on generate_series y (cost=0.00..10.00 rows=1,000 width=4) (actual time=0.000..0.003 rows=31 loops=31)

16.          

CTE j

17. 41.540 42,218.061 ↓ 4.7 187,599 1

Unique (cost=128,643,590.00..128,644,770.00 rows=40,000 width=108) (actual time=42,157.012..42,218.061 rows=187,599 loops=1)

18. 116.516 42,176.521 ↓ 1.2 187,599 1

Sort (cost=128,643,590.00..128,643,983.33 rows=157,333 width=108) (actual time=42,157.012..42,176.521 rows=187,599 loops=1)

  • Sort Key: locations.id, grid_1.cell2, grid_1.x, grid_1.y
  • Sort Method: quicksort Memory: 55975kB
19. 28,783.873 42,060.005 ↓ 1.2 187,599 1

Nested Loop (cost=0.00..128,630,009.44 rows=157,333 width=108) (actual time=51.507..42,060.005 rows=187,599 loops=1)

  • Join Filter: ((grid_1.cell2 && locations.geom) AND _st_intersects(grid_1.cell2, locations.geom))
  • Rows Removed by Join Filter: 180211321
20. 698.892 698.892 ↓ 397.7 187,720 1

CTE Scan on locations (cost=0.00..9.44 rows=472 width=68) (actual time=40.131..698.892 rows=187,720 loops=1)

21. 12,577.240 12,577.240 ↑ 1,040.6 961 187,720

CTE Scan on grid grid_1 (cost=0.00..20,000.00 rows=1,000,000 width=72) (actual time=0.000..0.067 rows=961 loops=187,720)

22.          

CTE g

23. 45.530 42,307.236 ↓ 4.3 863 1

HashAggregate (cost=1,100.00..1,103.00 rows=200 width=64) (actual time=42,307.102..42,307.236 rows=863 loops=1)

  • Group Key: j.gid
  • Filter: (sum(j.value_numeric) > '0'::numeric)
  • Rows Removed by Filter: 4
24. 42,261.706 42,261.706 ↓ 4.7 187,599 1

CTE Scan on j (cost=0.00..800.00 rows=40,000 width=64) (actual time=42,157.014..42,261.706 rows=187,599 loops=1)

25. 0.084 0.084 ↑ 1,040.6 961 1

CTE Scan on grid (cost=0.00..20,000.00 rows=1,000,000 width=64) (actual time=0.001..0.084 rows=961 loops=1)

26. 0.082 42,307.501 ↓ 4.3 863 1

Hash (cost=4.00..4.00 rows=200 width=64) (actual time=42,307.501..42,307.501 rows=863 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 58kB
27. 42,307.419 42,307.419 ↓ 4.3 863 1

CTE Scan on g (cost=0.00..4.00 rows=200 width=64) (actual time=42,307.105..42,307.419 rows=863 loops=1)

Planning time : 1.937 ms
Execution time : 42,324.135 ms