explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9mHR

Settings
# exclusive inclusive rows x rows loops node
1. 1.137 54,318.788 ↓ 24.0 24 1

GroupAggregate (cost=126,237.78..126,237.80 rows=1 width=24) (actual time=54,317.143..54,318.788 rows=24 loops=1)

  • Group Key: sourcing_strategies.id, applications.source_id
  • Buffers: shared hit=80982 read=182278 dirtied=3524
2.          

CTE cte

3. 0.002 0.128 ↑ 1.0 1 1

Nested Loop Left Join (cost=41.00..45.25 rows=1 width=8) (actual time=0.128..0.128 rows=1 loops=1)

  • Filter: ((source_overrides.hide IS NULL) OR (source_overrides.hide IS FALSE))
  • Buffers: shared hit=58
4. 0.004 0.118 ↑ 1.0 1 1

Nested Loop (cost=40.58..42.80 rows=1 width=8) (actual time=0.118..0.118 rows=1 loops=1)

  • Join Filter: (sources_1.sourcing_strategy_id = sourcing_strategies_1.id)
  • Rows Removed by Join Filter: 6
  • Buffers: shared hit=55
5. 0.005 0.107 ↑ 1.0 1 1

Bitmap Heap Scan on sources sources_1 (cost=40.58..41.62 rows=1 width=16) (actual time=0.106..0.107 rows=1 loops=1)

  • Recheck Cond: (((organization_id IS NULL) OR (organization_id = '4002074002'::bigint)) AND (id = ANY ('{4000010002,4000011002,4000015002,4000017002,4000031002,4000032002,4000056002,4000061002,4000096002,4000098002,4000100002,4000110002,4000151002,4000173002,4021046002,4021051002,4021060002,4032176002,4033453002,4033456002,4037705002}'::bigint[])))
  • Filter: display
  • Heap Blocks: exact=1
  • Buffers: shared hit=54
6. 0.008 0.102 ↓ 0.0 0 1

BitmapAnd (cost=40.58..40.58 rows=1 width=0) (actual time=0.102..0.102 rows=0 loops=1)

  • Buffers: shared hit=53
7. 0.002 0.062 ↓ 0.0 0 1

BitmapOr (cost=13.08..13.08 rows=866 width=0) (actual time=0.061..0.062 rows=0 loops=1)

  • Buffers: shared hit=10
8. 0.022 0.022 ↓ 1.0 210 1

Bitmap Index Scan on index_sources_on_organization_id (cost=0.00..3.84 rows=207 width=0) (actual time=0.022..0.022 rows=210 loops=1)

  • Index Cond: (organization_id IS NULL)
  • Buffers: shared hit=4
9. 0.038 0.038 ↑ 1.0 658 1

Bitmap Index Scan on index_sources_on_organization_id (cost=0.00..9.24 rows=660 width=0) (actual time=0.038..0.038 rows=658 loops=1)

  • Index Cond: (organization_id = '4002074002'::bigint)
  • Buffers: shared hit=6
10. 0.032 0.032 ↑ 1.0 21 1

Bitmap Index Scan on sources_pkey (cost=0.00..27.25 rows=21 width=0) (actual time=0.032..0.032 rows=21 loops=1)

  • Index Cond: (id = ANY ('{4000010002,4000011002,4000015002,4000017002,4000031002,4000032002,4000056002,4000061002,4000096002,4000098002,4000100002,4000110002,4000151002,4000173002,4021046002,4021051002,4021060002,4032176002,4033453002,4033456002,4037705002}'::bigint[]))
  • Buffers: shared hit=43
11. 0.007 0.007 ↑ 1.1 7 1

Seq Scan on sourcing_strategies sourcing_strategies_1 (cost=0.00..1.08 rows=8 width=8) (actual time=0.005..0.007 rows=7 loops=1)

  • Filter: (display AND reportable)
  • Buffers: shared hit=1
12. 0.008 0.008 ↓ 0.0 0 1

Index Scan using index_source_overrides_on_source_id_and_organization_id on source_overrides (cost=0.42..2.44 rows=1 width=9) (actual time=0.007..0.008 rows=0 loops=1)

  • Index Cond: ((sources_1.id = source_id) AND (organization_id = '4002074002'::bigint))
  • Buffers: shared hit=3
13. 3.748 54,317.651 ↓ 2,287.0 2,287 1

Sort (cost=126,192.53..126,192.54 rows=1 width=24) (actual time=54,317.117..54,317.651 rows=2,287 loops=1)

  • Sort Key: sourcing_strategies.id, applications.source_id
  • Sort Method: quicksort Memory: 275kB
  • Buffers: shared hit=80982 read=182278 dirtied=3524
14. 4.725 54,313.903 ↓ 2,287.0 2,287 1

Nested Loop (cost=83,088.43..126,192.52 rows=1 width=24) (actual time=36,405.749..54,313.903 rows=2,287 loops=1)

  • Buffers: shared hit=80982 read=182278 dirtied=3524
15. 6.320 54,304.604 ↓ 2,287.0 2,287 1

Nested Loop Left Join (cost=83,088.30..126,192.37 rows=1 width=24) (actual time=36,405.719..54,304.604 rows=2,287 loops=1)

  • Filter: ((prospective_hiring_plans.hiring_plan_id = '4460836002'::bigint) OR (prospective_hiring_plans.hiring_plan_id IS NULL))
  • Rows Removed by Filter: 1457
  • Buffers: shared hit=78695 read=182277 dirtied=3524
16. 13.076 54,268.356 ↓ 2.3 3,741 1

Nested Loop (cost=83,087.87..122,848.89 rows=1,624 width=24) (actual time=36,405.709..54,268.356 rows=3,741 loops=1)

  • Buffers: shared hit=66055 read=182182 dirtied=3524
17. 17,981.053 54,228.932 ↓ 1.2 3,764 1

Bitmap Heap Scan on applications (cost=83,087.58..120,605.99 rows=3,248 width=16) (actual time=36,405.556..54,228.932 rows=3,764 loops=1)

  • Recheck Cond: ((organization_id = '4002074002'::bigint) AND (created_at >= '2020-01-01 06:00:00'::timestamp without time zone) AND (created_at <= '2021-01-01 05:59:59.999999'::timestamp without time zone))
  • Rows Removed by Index Recheck: 173113
  • Filter: prospect
  • Rows Removed by Filter: 60693
  • Heap Blocks: exact=156286
  • Buffers: shared hit=54774 read=182182 dirtied=3524
18. 66.370 36,247.879 ↓ 0.0 0 1

BitmapAnd (cost=83,087.58..83,087.58 rows=37,509 width=0) (actual time=36,247.879..36,247.879 rows=0 loops=1)

  • Buffers: shared hit=2272 read=78398
19. 25,645.364 25,645.364 ↓ 1.0 602,833 1

Bitmap Index Scan on index_applications_on_organization_id (cost=0.00..19,980.53 rows=585,462 width=0) (actual time=25,645.363..25,645.364 rows=602,833 loops=1)

  • Index Cond: (organization_id = '4002074002'::bigint)
  • Buffers: shared hit=7 read=32572
20. 10,536.145 10,536.145 ↓ 1.3 3,129,050 1

Bitmap Index Scan on index_applications_on_created_at (cost=0.00..63,105.18 rows=2,373,961 width=0) (actual time=10,536.144..10,536.145 rows=3,129,050 loops=1)

  • Index Cond: ((created_at >= '2020-01-01 06:00:00'::timestamp without time zone) AND (created_at <= '2021-01-01 05:59:59.999999'::timestamp without time zone))
  • Buffers: shared hit=2265 read=45826
21. 22.607 26.348 ↑ 1.0 1 3,764

Index Scan using sources_pkey on sources (cost=0.29..0.68 rows=1 width=16) (actual time=0.007..0.007 rows=1 loops=3,764)

  • Index Cond: (id = applications.source_id)
  • Filter: (SubPlan 2)
  • Buffers: shared hit=11281
22.          

SubPlan (for Index Scan)

23. 3.741 3.741 ↑ 1.0 1 3,741

CTE Scan on cte (cost=0.00..0.02 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=3,741)

  • Buffers: shared hit=58
24. 29.928 29.928 ↓ 0.0 0 3,741

Index Scan using index_prospective_hiring_plans_on_application_id on prospective_hiring_plans (cost=0.43..2.05 rows=1 width=16) (actual time=0.007..0.008 rows=0 loops=3,741)

  • Index Cond: (application_id = applications.id)
  • Buffers: shared hit=12640 read=95
25. 4.574 4.574 ↑ 1.0 1 2,287

Index Only Scan using sourcing_strategies_pkey on sourcing_strategies (cost=0.13..0.15 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=2,287)

  • Index Cond: (id = sources.sourcing_strategy_id)
  • Heap Fetches: 0
  • Buffers: shared hit=2287 read=1
Planning time : 1.413 ms
Execution time : 54,321.877 ms