explain.depesz.com

PostgreSQL's explain analyze made readable

Result: q3gN

Settings
# exclusive inclusive rows x rows loops node
1. 0.418 265,523.931 ↓ 19.0 19 1

GroupAggregate (cost=3,819.74..3,819.76 rows=1 width=24) (actual time=265,523.443..265,523.931 rows=19 loops=1)

  • Group Key: sourcing_strategies.id, applications.source_id
  • Buffers: shared hit=18965910 read=2085640 dirtied=36842 written=71
2. 0.653 265,523.513 ↓ 1,825.0 1,825 1

Sort (cost=3,819.74..3,819.74 rows=1 width=24) (actual time=265,523.424..265,523.513 rows=1,825 loops=1)

  • Sort Key: sourcing_strategies.id, applications.source_id
  • Sort Method: quicksort Memory: 191kB
  • Buffers: shared hit=18965910 read=2085640 dirtied=36842 written=71
3. 1.815 265,522.860 ↓ 1,825.0 1,825 1

Nested Loop Left Join (cost=46.61..3,819.73 rows=1 width=24) (actual time=395.998..265,522.860 rows=1,825 loops=1)

  • Filter: ((prospective_hiring_plans.hiring_plan_id = '4460836002'::bigint) OR (prospective_hiring_plans.hiring_plan_id IS NULL))
  • Rows Removed by Filter: 243
  • Buffers: shared hit=18965910 read=2085640 dirtied=36842 written=71
4. 0.461 264,888.237 ↓ 1,034.0 2,068 1

Nested Loop (cost=46.18..3,815.48 rows=2 width=24) (actual time=395.464..264,888.237 rows=2,068 loops=1)

  • Buffers: shared hit=18959942 read=2085146 dirtied=36842 written=71
5. 0.036 390.391 ↓ 19.0 19 1

Nested Loop (cost=45.61..47.66 rows=1 width=24) (actual time=390.012..390.391 rows=19 loops=1)

  • Buffers: shared hit=174 read=51
6. 0.075 390.317 ↓ 19.0 19 1

Nested Loop (cost=45.48..47.51 rows=1 width=24) (actual time=390.008..390.317 rows=19 loops=1)

  • Buffers: shared hit=154 read=51
7. 0.044 390.033 ↓ 19.0 19 1

HashAggregate (cost=45.19..45.20 rows=1 width=8) (actual time=390.001..390.033 rows=19 loops=1)

  • Group Key: sources_1.id
  • Buffers: shared hit=115 read=33
8. 0.007 389.989 ↓ 19.0 19 1

Nested Loop Left Join (cost=40.94..45.19 rows=1 width=8) (actual time=241.993..389.989 rows=19 loops=1)

  • Filter: ((source_overrides.hide IS NULL) OR (source_overrides.hide IS FALSE))
  • Rows Removed by Filter: 2
  • Buffers: shared hit=115 read=33
9. 0.022 113.958 ↓ 21.0 21 1

Nested Loop (cost=40.52..42.74 rows=1 width=8) (actual time=112.912..113.958 rows=21 loops=1)

  • Join Filter: (sources_1.sourcing_strategy_id = sourcing_strategies_1.id)
  • Rows Removed by Join Filter: 65
  • Buffers: shared hit=64 read=19
10. 1.464 113.915 ↓ 21.0 21 1

Bitmap Heap Scan on sources sources_1 (cost=40.52..41.56 rows=1 width=16) (actual time=112.903..113.915 rows=21 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=9
  • Buffers: shared hit=43 read=19
11. 0.010 112.451 ↓ 0.0 0 1

BitmapAnd (cost=40.52..40.52 rows=1 width=0) (actual time=112.451..112.451 rows=0 loops=1)

  • Buffers: shared hit=40 read=13
12. 0.001 4.745 ↓ 0.0 0 1

BitmapOr (cost=13.02..13.02 rows=859 width=0) (actual time=4.745..4.745 rows=0 loops=1)

  • Buffers: shared hit=1 read=9
13. 2.113 2.113 ↑ 1.0 210 1

Bitmap Index Scan on index_sources_on_organization_id (cost=0.00..3.90 rows=215 width=0) (actual time=2.113..2.113 rows=210 loops=1)

  • Index Cond: (organization_id IS NULL)
  • Buffers: shared read=4
14. 2.631 2.631 ↓ 1.0 653 1

Bitmap Index Scan on index_sources_on_organization_id (cost=0.00..9.12 rows=644 width=0) (actual time=2.631..2.631 rows=653 loops=1)

  • Index Cond: (organization_id = '4002074002'::bigint)
  • Buffers: shared hit=1 read=5
15. 107.696 107.696 ↑ 1.0 21 1

Bitmap Index Scan on sources_pkey (cost=0.00..27.25 rows=21 width=0) (actual time=107.696..107.696 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=39 read=4
16. 0.021 0.021 ↑ 2.0 4 21

Seq Scan on sourcing_strategies sourcing_strategies_1 (cost=0.00..1.08 rows=8 width=8) (actual time=0.001..0.001 rows=4 loops=21)

  • Filter: (display AND reportable)
  • Buffers: shared hit=21
17. 276.024 276.024 ↓ 0.0 0 21

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=13.144..13.144 rows=0 loops=21)

  • Index Cond: ((sources_1.id = source_id) AND (organization_id = '4002074002'::bigint))
  • Buffers: shared hit=51 read=14
18. 0.209 0.209 ↑ 1.0 1 19

Index Scan using sources_pkey on sources (cost=0.29..2.31 rows=1 width=16) (actual time=0.011..0.011 rows=1 loops=19)

  • Index Cond: (id = sources_1.id)
  • Buffers: shared hit=39 read=18
19. 0.038 0.038 ↑ 1.0 1 19

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=19)

  • Index Cond: (id = sources.sourcing_strategy_id)
  • Heap Fetches: 0
  • Buffers: shared hit=20
20. 264,497.385 264,497.385 ↓ 54.5 109 19

Index Scan using index_applications_on_source_id on applications (cost=0.56..3,767.79 rows=2 width=16) (actual time=357.753..13,920.915 rows=109 loops=19)

  • Index Cond: (source_id = sources.id)
  • Filter: (prospect 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) AND (organization_id = '4002074002'::bigint))
  • Rows Removed by Filter: 1205971
  • Buffers: shared hit=18959768 read=2085095 dirtied=36842 written=71
21. 632.808 632.808 ↓ 0.0 0 2,068

Index Scan using index_prospective_hiring_plans_on_application_id on prospective_hiring_plans (cost=0.43..2.11 rows=1 width=16) (actual time=0.306..0.306 rows=0 loops=2,068)

  • Index Cond: (application_id = applications.id)
  • Buffers: shared hit=5968 read=494
Planning time : 1.065 ms
Execution time : 265,524.022 ms