explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Rjqp

Settings
# exclusive inclusive rows x rows loops node
1. 335.426 1,214.842 ↓ 847.3 5,084 1

Hash Left Join (cost=1,990.18..2,700.18 rows=6 width=398) (actual time=20.752..1,214.842 rows=5,084 loops=1)

  • Hash Cond: (tblvacancy.vacancyid = _vacancystats.vacancyid)
2. 5.667 765.809 ↓ 847.3 5,084 1

Hash Left Join (cost=1,889.32..2,566.45 rows=6 width=290) (actual time=17.108..765.809 rows=5,084 loops=1)

  • Hash Cond: (tblvacancy.vacancyid = _counts.vacancyid)
3. 4.637 743.098 ↓ 847.3 5,084 1

Nested Loop (cost=0.98..678.10 rows=6 width=274) (actual time=0.056..743.098 rows=5,084 loops=1)

4. 0.618 6.389 ↓ 344.7 1,034 1

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

5. 2.160 2.160 ↓ 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.024..2.160 rows=157 loops=1)

  • Index Cond: (emptypeid = 1)
  • Filter: (sendnhsdigitalreports AND (emptype2id <> ALL ('{8,9,10}'::integer[])))
  • Rows Removed by Filter: 510
6. 3.611 3.611 ↓ 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.007..0.023 rows=7 loops=157)

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

Index Scan using fk_tblvacancy_departmentid_autoidx on tblvacancy (cost=0.42..96.90 rows=5 width=181) (actual time=0.112..0.708 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
8. 0.050 17.044 ↑ 1.0 256 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 21kB
9. 0.035 16.994 ↑ 1.0 257 1

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

10. 4.826 16.959 ↑ 1.0 257 1

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

  • Group Key: tblapplication.vacancyid
11. 12.133 12.133 ↑ 1.0 21,522 1

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

12. 0.115 1.759 ↓ 1.0 595 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 34kB
13. 0.094 1.644 ↓ 1.0 595 1

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

14. 1.131 1.550 ↓ 1.0 595 1

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

  • Group Key: tblvacancystats.vacancyid
15. 0.419 0.419 ↑ 1.0 3,274 1

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

16.          

SubPlan (for Hash Left Join)

17. 27.220 111.848 ↑ 1.0 1 5,084

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

18. 10.326 71.176 ↑ 1.0 1 5,084

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

19. 40.672 40.672 ↑ 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.007..0.008 rows=1 loops=5,084)

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

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

  • Index Cond: (disciplineid = tblvacancydiscipline.disciplineid)
21. 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 : 2.552 ms
Execution time : 1,216.294 ms