explain.depesz.com

PostgreSQL's explain analyze made readable

Result: wCWe

Settings
# exclusive inclusive rows x rows loops node
1. 0.441 38.040 ↓ 21.0 21 1

GroupAggregate (cost=18,611.46..18,611.48 rows=1 width=24) (actual time=37.513..38.040 rows=21 loops=1)

  • Group Key: sourcing_strategies.id, applications.source_id
  • Buffers: shared hit=20,631
2.          

CTE cte

3. 0.002 0.105 ↑ 1.0 1 1

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

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

Nested Loop (cost=40.52..42.74 rows=1 width=8) (actual time=0.095..0.095 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.004 0.087 ↑ 1.0 1 1

Bitmap Heap Scan on sources sources_1 (cost=40.52..41.56 rows=1 width=16) (actual time=0.087..0.087 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.083 ↓ 0.0 0 1

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

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

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

  • Buffers: shared hit=10
8. 0.017 0.017 ↑ 1.0 210 1

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

  • Index Cond: (organization_id IS NULL)
  • Buffers: shared hit=4
9. 0.032 0.032 ↓ 1.0 653 1

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

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

Bitmap Index Scan on sources_pkey (cost=0.00..27.25 rows=21 width=0) (actual time=0.024..0.024 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.006 0.006 ↑ 1.1 7 1

Seq Scan on sourcing_strategies sourcing_strategies_1 (cost=0.00..1.08 rows=8 width=8) (actual time=0.004..0.006 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.008..0.008 rows=0 loops=1)

  • Index Cond: ((sources_1.id = source_id) AND (organization_id = '4002074002'::bigint))
  • Buffers: shared hit=3
13. 0.568 37.599 ↓ 2,052.0 2,052 1

Sort (cost=18,566.27..18,566.27 rows=1 width=24) (actual time=37.500..37.599 rows=2,052 loops=1)

  • Sort Key: sourcing_strategies.id, applications.source_id
  • Sort Method: quicksort Memory: 257kB
  • Buffers: shared hit=20,631
14. 1.328 37.031 ↓ 2,052.0 2,052 1

Nested Loop (cost=1,734.04..18,566.26 rows=1 width=24) (actual time=22.692..37.031 rows=2,052 loops=1)

  • Buffers: shared hit=20,631
15. 1.828 35.703 ↓ 2,052.0 2,052 1

Nested Loop Left Join (cost=1,733.91..18,566.10 rows=1 width=24) (actual time=22.687..35.703 rows=2,052 loops=1)

  • Filter: ((prospective_hiring_plans.hiring_plan_id = '4460836002'::bigint) OR (prospective_hiring_plans.hiring_plan_id IS NULL))
  • Rows Removed by Filter: 1,190
  • Buffers: shared hit=18,578
16. 1.643 30.635 ↓ 2.6 3,240 1

Hash Join (cost=1,733.48..15,902.39 rows=1,250 width=24) (actual time=22.612..30.635 rows=3,240 loops=1)

  • Hash Cond: (applications.source_id = sources.id)
  • Join Filter: (SubPlan 2)
  • Buffers: shared hit=7,613
17. 17.537 17.537 ↓ 1.3 3,263 1

Index Scan using pob_applications_idx on applications (cost=0.56..14,162.92 rows=2,499 width=16) (actual time=10.991..17.537 rows=3,263 loops=1)

  • Index Cond: ((organization_id = '4002074002'::bigint) AND (prospect = true) 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))
  • Filter: prospect
  • Buffers: shared hit=6,690
18. 4.969 11.455 ↑ 1.0 38,574 1

Hash (cost=1,250.74..1,250.74 rows=38,574 width=16) (actual time=11.455..11.455 rows=38,574 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 2,321kB
  • Buffers: shared hit=865
19. 6.486 6.486 ↑ 1.0 38,574 1

Seq Scan on sources (cost=0.00..1,250.74 rows=38,574 width=16) (actual time=0.006..6.486 rows=38,574 loops=1)

  • Buffers: shared hit=865
20.          

SubPlan (for Hash Join)

21. 0.000 0.000 ↑ 1.0 1 3,240

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

  • Buffers: shared hit=58
22. 3.240 3.240 ↓ 0.0 0 3,240

Index Scan using index_prospective_hiring_plans_on_application_id on prospective_hiring_plans (cost=0.43..2.12 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=3,240)

  • Index Cond: (application_id = applications.id)
  • Buffers: shared hit=10,965
23. 0.000 0.000 ↑ 1.0 1 2,052

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

  • Index Cond: (id = sources.sourcing_strategy_id)
  • Heap Fetches: 0
  • Buffers: shared hit=2,053
Planning time : 10.370 ms
Execution time : 38.134 ms