explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Jbjt

Settings
# exclusive inclusive rows x rows loops node
1. 0.496 1,157.817 ↓ 73.1 585 1

Nested Loop (cost=223,152.39..223,219.74 rows=8 width=132) (actual time=1,151.417..1,157.817 rows=585 loops=1)

2.          

CTE geo_match_closest

3. 0.564 1,155.930 ↓ 73.1 585 1

Nested Loop (cost=210,466.45..223,151.96 rows=8 width=107) (actual time=1,151.402..1,155.930 rows=585 loops=1)

4.          

CTE geo_match_min_dist

5. 0.665 1,152.112 ↑ 1.0 1,483 1

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

6.          

CTE geo_match_id_joined

7. 279.726 1,150.213 ↑ 1.0 1,483 1

Hash Join (cost=188,806.68..210,322.28 rows=1,546 width=36) (actual time=871.170..1,150.213 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.817 ↓ 1.4 585 1

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

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

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

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

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

12. 488.030 869.609 ↓ 1.0 2,731,468 1

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

  • Buckets: 65,536 Batches: 64 Memory Usage: 3,356kB
13. 381.579 381.579 ↓ 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.009..381.579 rows=2,731,468 loops=1)

14. 0.600 1,151.447 ↑ 1.0 1,483 1

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

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

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

16. 1,152.400 1,152.400 ↑ 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,151.382..1,152.400 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: (geo_match_min_dist.min_dist = abs(ang_dist_to_strk))
  • Rows Removed by Filter: 1
18. 1,156.151 1,156.151 ↓ 73.1 585 1

CTE Scan on geo_match_closest (cost=0.00..0.16 rows=8 width=72) (actual time=1,151.405..1,156.151 rows=585 loops=1)

19. 1.170 1.170 ↑ 1.0 1 585

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

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