explain.depesz.com

PostgreSQL's explain analyze made readable

Result: g86m

Settings
# exclusive inclusive rows x rows loops node
1. 0.045 1,198.721 ↓ 55.0 55 1

Nested Loop (cost=223,156.26..223,164.30 rows=1 width=132) (actual time=1,194.049..1,198.721 rows=55 loops=1)

2.          

CTE geo_match_closest

3. 0.749 1,198.485 ↓ 55.0 55 1

Nested Loop (cost=210,466.45..223,155.83 rows=1 width=107) (actual time=1,194.037..1,198.485 rows=55 loops=1)

4.          

CTE geo_match_min_dist

5. 0.666 1,194.482 ↑ 1.0 1,483 1

WindowAgg (cost=210,435.10..210,466.02 rows=1,546 width=36) (actual time=1,193.735..1,194.482 rows=1,483 loops=1)

6.          

CTE geo_match_id_joined

7. 275.759 1,192.942 ↑ 1.0 1,483 1

Hash Join (cost=188,806.68..210,322.28 rows=1,546 width=36) (actual time=917.690..1,192.942 rows=1,483 loops=1)

  • Hash Cond: (geo_match_id_filtered.obj_id = geo_match_1.obj_id)
8.          

CTE geo_match_id_filtered

9. 0.000 0.922 ↓ 1.4 585 1

Gather (cost=1,000.00..60,644.35 rows=432 width=16) (actual time=0.803..0.922 rows=585 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
10. 117.082 117.082 ↓ 1.1 195 3 / 3

Parallel Seq Scan on geo_match (cost=0.00..59,601.15 rows=180 width=16) (actual time=0.221..117.082 rows=195 loops=3)

  • Filter: (ssn_id = 2,207)
  • Rows Removed by Filter: 910,294
11. 0.979 0.979 ↓ 1.4 585 1

CTE Scan on geo_match_id_filtered (cost=0.00..8.64 rows=432 width=16) (actual time=0.806..0.979 rows=585 loops=1)

12. 512.884 916.204 ↓ 1.0 2,731,468 1

Hash (cost=72,687.37..72,687.37 rows=2,731,037 width=36) (actual time=916.204..916.204 rows=2,731,468 loops=1)

  • Buckets: 65,536 Batches: 64 Memory Usage: 3,356kB
13. 403.320 403.320 ↓ 1.0 2,731,468 1

Seq Scan on geo_match geo_match_1 (cost=0.00..72,687.37 rows=2,731,037 width=36) (actual time=0.019..403.320 rows=2,731,468 loops=1)

14. 0.500 1,193.816 ↑ 1.0 1,483 1

Sort (cost=112.81..116.68 rows=1,546 width=36) (actual time=1,193.729..1,193.816 rows=1,483 loops=1)

  • Sort Key: geo_match_id_joined.obj_id
  • Sort Method: quicksort Memory: 164kB
15. 1,193.316 1,193.316 ↑ 1.0 1,483 1

CTE Scan on geo_match_id_joined (cost=0.00..30.92 rows=1,546 width=36) (actual time=917.692..1,193.316 rows=1,483 loops=1)

16. 1,194.770 1,194.770 ↑ 1.0 1,483 1

CTE Scan on geo_match_min_dist (cost=0.00..30.92 rows=1,546 width=20) (actual time=1,193.736..1,194.770 rows=1,483 loops=1)

17. 2.966 2.966 ↓ 0.0 0 1,483

Index Scan using geo_match_pkey on geo_match geo_match_2 (cost=0.43..8.18 rows=1 width=107) (actual time=0.002..0.002 rows=0 loops=1,483)

  • Index Cond: (id = geo_match_min_dist.id)
  • Filter: ((ssn_id = 2,207) AND (geo_match_min_dist.min_dist = abs(ang_dist_to_strk)))
  • Rows Removed by Filter: 1
18. 1,198.511 1,198.511 ↓ 55.0 55 1

CTE Scan on geo_match_closest (cost=0.00..0.02 rows=1 width=72) (actual time=1,194.039..1,198.511 rows=55 loops=1)

19. 0.165 0.165 ↑ 1.0 1 55

Index Scan using geo_objs_pkey on geo_objs (cost=0.43..8.45 rows=1 width=76) (actual time=0.003..0.003 rows=1 loops=55)

  • Index Cond: (obj_id = geo_match_closest.obj_id)
Planning time : 0.803 ms