explain.depesz.com

PostgreSQL's explain analyze made readable

Result: f1i2

Settings
# exclusive inclusive rows x rows loops node
1. 0.058 2.008 ↑ 1.0 1 1

GroupAggregate (cost=298.65..307.07 rows=1 width=136) (actual time=2.008..2.008 rows=1 loops=1)

  • Group Key: ((SubPlan 2))
2.          

CTE param

3. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=56) (actual time=0.001..0.001 rows=1 loops=1)

4. 0.036 1.950 ↓ 7.0 7 1

Sort (cost=298.64..298.65 rows=1 width=48) (actual time=1.949..1.950 rows=7 loops=1)

  • Sort Key: ((SubPlan 2))
  • Sort Method: quicksort Memory: 25kB
5. 0.046 1.914 ↓ 7.0 7 1

Nested Loop (cost=6.58..298.63 rows=1 width=48) (actual time=0.588..1.914 rows=7 loops=1)

  • Join Filter: (q.req_mo_id = zo.mo_id)
  • Rows Removed by Join Filter: 105
6. 0.011 1.756 ↓ 7.0 7 1

Nested Loop (cost=6.58..288.81 rows=1 width=40) (actual time=0.535..1.756 rows=7 loops=1)

  • Join Filter: ((o.region_id = param.region_id) OR (param.region_id IS NULL))
7. 0.040 1.703 ↓ 7.0 7 1

Nested Loop (cost=6.30..287.11 rows=1 width=36) (actual time=0.511..1.703 rows=7 loops=1)

  • Join Filter: (sh.consultant_mo_id = mo.mo_id)
  • Rows Removed by Join Filter: 140
8. 0.049 1.600 ↓ 7.0 7 1

Nested Loop (cost=6.30..285.53 rows=1 width=36) (actual time=0.493..1.600 rows=7 loops=1)

  • Join Filter: (((sh.consultant_mo_id = param.kmo_id) OR (param.kmo_id IS NULL)) AND (param.nyear = to_char(sh.begin_date, 'yyyy'::text)))
  • Rows Removed by Join Filter: 1
9. 0.046 1.503 ↓ 8.0 8 1

Nested Loop (cost=6.02..284.51 rows=1 width=64) (actual time=0.201..1.503 rows=8 loops=1)

  • Join Filter: ((c.status_id = param.status_id) OR (param.status_id IS NULL))
  • Rows Removed by Join Filter: 37
10. 0.277 1.286 ↓ 57.0 57 1

Hash Join (cost=5.74..283.89 rows=1 width=68) (actual time=0.128..1.286 rows=57 loops=1)

  • Hash Cond: (q.theme_id = t.id)
  • Join Filter: (((q.purpose_id = param.purpose_id) OR (param.purpose_id IS NULL)) AND ((q.req_mo_id = param.zmo_id) OR (param.zmo_id IS NULL)))
  • Rows Removed by Join Filter: 308
11. 0.932 0.932 ↓ 1.0 1,466 1

Seq Scan on tmc_request q (cost=0.00..272.24 rows=1,465 width=34) (actual time=0.012..0.932 rows=1,466 loops=1)

  • Filter: is_done
  • Rows Removed by Filter: 59
12. 0.014 0.077 ↓ 1.8 11 1

Hash (cost=5.67..5.67 rows=6 width=56) (actual time=0.077..0.077 rows=11 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
13. 0.028 0.063 ↓ 1.8 11 1

Nested Loop (cost=0.00..5.67 rows=6 width=56) (actual time=0.027..0.063 rows=11 loops=1)

  • Join Filter: ((t.consultation_type_id = param.profile_id) OR (param.profile_id IS NULL))
  • Rows Removed by Join Filter: 167
14. 0.005 0.005 ↑ 1.0 1 1

CTE Scan on param (cost=0.00..0.02 rows=1 width=56) (actual time=0.005..0.005 rows=1 loops=1)

15. 0.030 0.030 ↓ 1.1 178 1

Seq Scan on tmc_consult_theme_mo t (cost=0.00..3.62 rows=162 width=8) (actual time=0.011..0.030 rows=178 loops=1)

16. 0.171 0.171 ↑ 1.0 1 57

Index Scan using tmc_consultation_idx_request_id on tmc_consultation c (cost=0.28..0.60 rows=1 width=18) (actual time=0.003..0.003 rows=1 loops=57)

  • Index Cond: (request_id = q.id)
17. 0.048 0.048 ↑ 1.0 1 8

Index Scan using tmc_consultation_schedule_pkey on tmc_consultation_schedule sh (cost=0.28..1.00 rows=1 width=24) (actual time=0.006..0.006 rows=1 loops=8)

  • Index Cond: (id = q.schedule_id)
18. 0.063 0.063 ↑ 1.0 21 7

Seq Scan on tmc_mo mo (cost=0.00..1.31 rows=22 width=8) (actual time=0.006..0.009 rows=21 loops=7)

  • Filter: is_consultant
  • Rows Removed by Filter: 10
19. 0.042 0.042 ↑ 1.0 1 7

Index Scan using ref_mo_pkey on ref_mo o (cost=0.29..1.68 rows=1 width=10) (actual time=0.006..0.006 rows=1 loops=7)

  • Index Cond: (id = q.req_mo_id)
20. 0.056 0.056 ↑ 1.1 16 7

Seq Scan on tmc_mo zo (cost=0.00..1.31 rows=17 width=8) (actual time=0.005..0.008 rows=16 loops=7)

  • Filter: is_requester
  • Rows Removed by Filter: 13
21.          

SubPlan (forNested Loop)

22. 0.007 0.056 ↑ 1.0 1 7

Limit (cost=0.29..8.30 rows=1 width=55) (actual time=0.008..0.008 rows=1 loops=7)

23. 0.049 0.049 ↑ 1.0 1 7

Index Scan using ref_mo_pkey on ref_mo y (cost=0.29..8.30 rows=1 width=55) (actual time=0.007..0.007 rows=1 loops=7)

  • Index Cond: (sh.consultant_mo_id = id)