explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Z2sJ

Settings
# exclusive inclusive rows x rows loops node
1. 93.660 4,388.110 ↓ 5.7 8,749 1

Sort (cost=43,281.26..43,282.03 rows=1,545 width=141) (actual time=4,387.545..4,388.110 rows=8,749 loops=1)

  • Sort Key: (CASE WHEN (data.score IS NULL) THEN round((data.source_rank)::numeric, 1) ELSE round(((data.source_rank * ((data.score)::double precision ^ '2'::double precision)))::numeric, 5) END) DESC, data.dist, data.creation DESC
  • Sort Method: quicksort Memory: 2092kB
2. 32.375 4,294.450 ↓ 5.7 8,749 1

Subquery Scan on data (cost=43,238.84..43,264.89 rows=1,545 width=141) (actual time=4,115.658..4,294.450 rows=8,749 loops=1)

  • Filter: ((data.dist <= '25000'::double precision) OR (data.dist IS NULL))
  • Rows Removed by Filter: 75495
3. 99.785 4,262.075 ↓ 18.4 84,244 1

Unique (cost=43,238.84..43,243.43 rows=4,588 width=121) (actual time=4,115.641..4,262.075 rows=84,244 loops=1)

4. 190.480 4,162.290 ↓ 19.2 87,888 1

Sort (cost=43,238.84..43,241.13 rows=4,588 width=121) (actual time=4,115.639..4,162.290 rows=87,888 loops=1)

  • Sort Key: j.id
  • Sort Method: quicksort Memory: 22808kB
5. 725.501 3,971.810 ↓ 19.2 87,888 1

Nested Loop Left Join (cost=8,399.93..43,183.03 rows=4,588 width=121) (actual time=203.441..3,971.810 rows=87,888 loops=1)

6. 0.000 2,120.171 ↓ 18.9 86,626 1

Gather (cost=8,399.88..42,633.39 rows=4,588 width=141) (actual time=203.147..2,120.171 rows=86,626 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
7. 49.427 2,504.758 ↓ 16.0 43,313 2 / 2

Hash Join (cost=7,399.88..41,174.59 rows=2,699 width=141) (actual time=247.109..2,504.758 rows=43,313 loops=2)

  • Hash Cond: ((j.source)::text = (so.source)::text)
8. 90.184 2,455.150 ↓ 16.0 43,313 2 / 2

Nested Loop (cost=7,382.52..41,155.77 rows=2,699 width=143) (actual time=246.887..2,455.150 rows=43,313 loops=2)

9. 78.896 2,018.462 ↓ 16.0 43,313 2 / 2

Nested Loop Left Join (cost=7,382.43..39,034.19 rows=2,699 width=120) (actual time=246.861..2,018.462 rows=43,313 loops=2)

10. 54.720 1,593.062 ↓ 16.0 43,313 2 / 2

Nested Loop (cost=7,382.35..37,850.04 rows=2,699 width=81) (actual time=246.835..1,593.062 rows=43,313 loops=2)

  • Join Filter: (svdtqt.id = j.id)
11. 533.213 975.273 ↑ 1.2 43,313 2 / 2

Hash Join (cost=7,382.26..15,320.41 rows=52,168 width=12) (actual time=246.787..975.273 rows=43,313 loops=2)

  • Hash Cond: (jt.job_id = svdtqt.id)
12. 202.320 202.320 ↑ 1.2 547,894 2 / 2

Parallel Seq Scan on jobs_job_types_tb jt (cost=0.00..7,599.74 rows=644,581 width=8) (actual time=0.016..202.320 rows=547,894 loops=2)

13. 107.124 239.740 ↑ 1.0 87,425 2 / 2

Hash (cost=7,076.27..7,076.27 rows=87,425 width=4) (actual time=239.740..239.740 rows=87,425 loops=2)

  • Buckets: 131072 Batches: 1 Memory Usage: 4098kB
14. 132.616 132.616 ↑ 1.0 87,425 2 / 2

Seq Scan on svdtqt (cost=0.00..7,076.27 rows=87,425 width=4) (actual time=0.011..132.616 rows=87,425 loops=2)

15. 563.069 563.069 ↑ 1.0 1 86,626 / 2

Index Scan using job_tb_pkey on job_tb j (cost=0.09..0.43 rows=1 width=77) (actual time=0.013..0.013 rows=1 loops=86,626)

  • Index Cond: (id = jt.job_id)
  • Filter: (NOT finished)
16. 346.504 346.504 ↑ 1.0 1 86,626 / 2

Index Scan using location_tb_pkey on location_tb l (cost=0.08..0.44 rows=1 width=53) (actual time=0.008..0.008 rows=1 loops=86,626)

  • Index Cond: (j.location_id = id)
17. 346.504 346.504 ↑ 1.0 1 86,626 / 2

Index Scan using b2b_tb_pkey on b2b_tb b (cost=0.08..0.79 rows=1 width=31) (actual time=0.008..0.008 rows=1 loops=86,626)

  • Index Cond: (id = j.b2b_id)
18. 0.046 0.181 ↓ 1.0 211 2 / 2

Hash (cost=16.63..16.63 rows=209 width=17) (actual time=0.181..0.181 rows=211 loops=2)

  • Buckets: 1024 Batches: 1 Memory Usage: 19kB
19. 0.135 0.135 ↓ 1.0 211 2 / 2

Seq Scan on source_rank_tb so (cost=0.00..16.63 rows=209 width=17) (actual time=0.016..0.135 rows=211 loops=2)

20. 1,126.138 1,126.138 ↓ 0.0 0 86,626

Index Scan using kj_title_search on known_jobs_tb kj (cost=0.06..0.07 rows=1 width=48) (actual time=0.013..0.013 rows=0 loops=86,626)

  • Index Cond: ((title)::text = (j.title)::text)
Planning time : 3.827 ms
Execution time : 4,389.448 ms