explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Nh34

Settings
# exclusive inclusive rows x rows loops node
1. 0.031 83,783.742 ↑ 1.0 1 1

Aggregate (cost=901,845.74..901,845.75 rows=1 width=40) (actual time=83,783.741..83,783.742 rows=1 loops=1)

2.          

CTE hash_list

3. 0.015 83,782.590 ↑ 1.0 100 1

Limit (cost=900,985.74..900,997.74 rows=100 width=7) (actual time=83,782.376..83,782.590 rows=100 loops=1)

4. 0.000 83,782.575 ↑ 127,552.1 100 1

Unique (cost=900,985.74..2,431,715.46 rows=12,755,215 width=7) (actual time=83,782.375..83,782.575 rows=100 loops=1)

5. 469.347 83,863.200 ↑ 127,552.1 100 1

Gather Merge (cost=900,985.74..2,399,827.42 rows=12,755,215 width=7) (actual time=83,782.373..83,863.200 rows=100 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
6. 1.515 83,393.853 ↑ 6,403.2 830 3 / 3

Merge Anti Join (cost=899,985.71..926,559.10 rows=5,314,673 width=7) (actual time=83,390.812..83,393.853 rows=830 loops=3)

  • Merge Cond: ((include.hash)::text = (exclude.hash)::text)
7. 79,815.765 83,392.277 ↑ 6,403.2 830 3 / 3

Sort (cost=899,981.25..913,267.93 rows=5,314,673 width=7) (actual time=83,390.745..83,392.277 rows=830 loops=3)

  • Sort Key: include.hash
  • Sort Method: external merge Disk: 73,824kB
  • Worker 0: Sort Method: external merge Disk: 76,112kB
  • Worker 1: Sort Method: external merge Disk: 73,160kB
8. 3,576.512 3,576.512 ↑ 1.2 4,251,758 3 / 3

Parallel Seq Scan on include_test include (cost=0.00..160,964.10 rows=5,314,673 width=7) (actual time=0.017..3,576.512 rows=4,251,758 loops=3)

  • Filter: ((segment IS NULL) AND (location_group IS NULL) AND ((include_exclude)::text = 'include'::text) AND ((segment_type)::text = 'foot_gh'::text))
9. 0.009 0.061 ↓ 0.0 0 3 / 3

Sort (cost=4.46..4.47 rows=1 width=7) (actual time=0.061..0.061 rows=0 loops=3)

  • Sort Key: exclude.hash
  • Sort Method: quicksort Memory: 25kB
  • Worker 0: Sort Method: quicksort Memory: 25kB
  • Worker 1: Sort Method: quicksort Memory: 25kB
10. 0.052 0.052 ↓ 0.0 0 3 / 3

Index Scan using idx_include_index on include_test exclude (cost=0.43..4.46 rows=1 width=7) (actual time=0.052..0.052 rows=0 loops=3)

  • Index Cond: ((include_exclude)::text = 'exclude'::text)
  • Filter: ((segment IS NULL) AND (location_group IS NULL) AND ((segment_type)::text = 'foot_gh'::text))
11. 0.087 83,783.711 ↑ 1.0 100 1

Nested Loop (cost=0.43..847.25 rows=100 width=4) (actual time=83,782.425..83,783.711 rows=100 loops=1)

12. 83,782.624 83,782.624 ↑ 1.0 100 1

CTE Scan on hash_list h (cost=0.00..2.00 rows=100 width=50) (actual time=83,782.379..83,782.624 rows=100 loops=1)

13. 1.000 1.000 ↑ 1.0 1 100

Index Scan using gis_public_nt_geohash_uu_pk on nt_geohash_uu idx (cost=0.43..8.45 rows=1 width=11) (actual time=0.010..0.010 rows=1 loops=100)

  • Index Cond: ((geohash)::text = (h.hash)::text)
Planning time : 0.783 ms
Execution time : 83,883.149 ms