explain.depesz.com

PostgreSQL's explain analyze made readable

Result: jsnS2

Settings
# exclusive inclusive rows x rows loops node
1. 19.301 1,773.236 ↓ 847.3 5,084 1

Sort (cost=2,700.26..2,700.27 rows=6 width=406) (actual time=1,772.714..1,773.236 rows=5,084 loops=1)

  • Sort Key: tblvacancy.lastmodifieddatetime DESC, tblvacancy.vacancyid DESC
  • Sort Method: quicksort Memory: 2922kB
2. 395.224 1,753.935 ↓ 847.3 5,084 1

Hash Left Join (cost=1,990.18..2,700.18 rows=6 width=406) (actual time=48.256..1,753.935 rows=5,084 loops=1)

  • Hash Cond: (tblvacancy.vacancyid = _vacancystats.vacancyid)
3. 6.371 1,218.207 ↓ 847.3 5,084 1

Hash Left Join (cost=1,889.32..2,566.45 rows=6 width=298) (actual time=41.346..1,218.207 rows=5,084 loops=1)

  • Hash Cond: (tblvacancy.vacancyid = _counts.vacancyid)
4. 5.472 1,170.746 ↓ 847.3 5,084 1

Nested Loop (cost=0.98..678.10 rows=6 width=282) (actual time=0.231..1,170.746 rows=5,084 loops=1)

5. 0.733 11.330 ↓ 344.7 1,034 1

Nested Loop (cost=0.56..387.25 rows=3 width=101) (actual time=0.150..11.330 rows=1,034 loops=1)

6. 5.102 5.102 ↓ 31.4 157 1

Index Scan using fk_tblemployer_emptypeid_autoidx on tblemployer (cost=0.28..371.34 rows=5 width=56) (actual time=0.082..5.102 rows=157 loops=1)

  • Index Cond: (emptypeid = 1)
  • Filter: (sendnhsdigitalreports AND (emptype2id <> ALL ('{8,9,10}'::integer[])))
  • Rows Removed by Filter: 510
7. 5.495 5.495 ↓ 7.0 7 157

Index Scan using fk_tbldepartment_employerid_autoidx on tbldepartment (cost=0.28..3.17 rows=1 width=53) (actual time=0.010..0.035 rows=7 loops=157)

  • Index Cond: (employerid = tblemployer.employerid)
  • Filter: sendnhsdigitalreports
  • Rows Removed by Filter: 0
8. 1,153.944 1,153.944 ↑ 1.0 5 1,034

Index Scan using fk_tblvacancy_departmentid_autoidx on tblvacancy (cost=0.42..96.90 rows=5 width=189) (actual time=0.180..1.116 rows=5 loops=1,034)

  • Index Cond: (departmentid = tbldepartment.departmentid)
  • Filter: ((vacstage <> 'Authorisation'::nn_stage) AND (vacstate <> 'AuthRefused'::nn_vacstate) AND (contacttype = 6) AND (workingperiod = 'full'::nn_workingperiod))
  • Rows Removed by Filter: 118
9. 0.118 41.090 ↑ 1.0 256 1

Hash (cost=1,885.06..1,885.06 rows=262 width=20) (actual time=41.090..41.090 rows=256 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 21kB
10. 0.059 40.972 ↑ 1.0 257 1

Subquery Scan on _counts (cost=1,879.83..1,885.06 rows=262 width=20) (actual time=40.803..40.972 rows=257 loops=1)

11. 10.069 40.913 ↑ 1.0 257 1

HashAggregate (cost=1,879.83..1,882.44 rows=262 width=20) (actual time=40.802..40.913 rows=257 loops=1)

  • Group Key: tblapplication.vacancyid
12. 30.844 30.844 ↑ 1.0 21,522 1

Seq Scan on tblapplication (cost=0.00..1,554.13 rows=21,713 width=20) (actual time=0.022..30.844 rows=21,522 loops=1)

13. 0.227 3.236 ↓ 1.0 595 1

Hash (cost=93.52..93.52 rows=588 width=12) (actual time=3.236..3.236 rows=595 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 34kB
14. 0.136 3.009 ↓ 1.0 595 1

Subquery Scan on _vacancystats (cost=81.76..93.52 rows=588 width=12) (actual time=2.679..3.009 rows=595 loops=1)

15. 1.947 2.873 ↓ 1.0 595 1

HashAggregate (cost=81.76..87.64 rows=588 width=12) (actual time=2.678..2.873 rows=595 loops=1)

  • Group Key: tblvacancystats.vacancyid
16. 0.926 0.926 ↑ 1.0 3,274 1

Seq Scan on tblvacancystats (cost=0.00..65.17 rows=3,317 width=8) (actual time=0.023..0.926 rows=3,274 loops=1)

17.          

SubPlan (for Hash Left Join)

18. 32.304 137.268 ↑ 1.0 1 5,084

Nested Loop (cost=0.83..4.95 rows=1 width=32) (actual time=0.023..0.027 rows=1 loops=5,084)

19. 13.768 91.512 ↑ 1.0 1 5,084

Nested Loop (cost=0.69..4.78 rows=1 width=91) (actual time=0.015..0.018 rows=1 loops=5,084)

20. 50.840 50.840 ↑ 1.0 1 5,084

Index Only Scan using tblvacancydiscipline_pkey on tblvacancydiscipline (cost=0.42..2.46 rows=1 width=4) (actual time=0.009..0.010 rows=1 loops=5,084)

  • Index Cond: (vacancyid = tblvacancy.vacancyid)
  • Heap Fetches: 6726
21. 26.904 26.904 ↑ 1.0 1 6,726

Index Scan using tbldiscipline_pkey on tbldiscipline (cost=0.27..2.31 rows=1 width=95) (actual time=0.004..0.004 rows=1 loops=6,726)

  • Index Cond: (disciplineid = tblvacancydiscipline.disciplineid)
22. 13.452 13.452 ↑ 1.0 1 6,726

Index Scan using tblsector_pkey on tblsector (cost=0.14..0.16 rows=1 width=36) (actual time=0.002..0.002 rows=1 loops=6,726)

  • Index Cond: (sectorid = tbldiscipline.sectorid)
Planning time : 28.591 ms
Execution time : 1,776.659 ms