explain.depesz.com

PostgreSQL's explain analyze made readable

Result: CpJO

Settings
# exclusive inclusive rows x rows loops node
1. 0.033 2,213,343.571 ↓ 4.0 4 1

GroupAggregate (cost=3,844.66..3,844.68 rows=1 width=24) (actual time=2,213,343.553..2,213,343.571 rows=4 loops=1)

  • Group Key: sourcing_strategies.id, applications.source_id
  • Buffers: shared hit=8676384 read=1734367 dirtied=26648
2. 0.101 2,213,343.538 ↓ 56.0 56 1

Sort (cost=3,844.66..3,844.66 rows=1 width=24) (actual time=2,213,343.535..2,213,343.538 rows=56 loops=1)

  • Sort Key: sourcing_strategies.id, applications.source_id
  • Sort Method: quicksort Memory: 29kB
  • Buffers: shared hit=8676384 read=1734367 dirtied=26648
3. 0.165 2,213,343.437 ↓ 56.0 56 1

Nested Loop Left Join (cost=27.42..3,844.65 rows=1 width=24) (actual time=18,062.568..2,213,343.437 rows=56 loops=1)

  • Filter: ((prospective_hiring_plans.hiring_plan_id = '4460836002'::bigint) OR (prospective_hiring_plans.hiring_plan_id IS NULL))
  • Rows Removed by Filter: 33
  • Buffers: shared hit=8676384 read=1734367 dirtied=26648
4. 0.272 2,213,328.854 ↓ 89.0 89 1

Nested Loop (cost=26.99..3,842.19 rows=1 width=24) (actual time=1,137.046..2,213,328.854 rows=89 loops=1)

  • Buffers: shared hit=8676119 read=1734332 dirtied=26648
5. 0.090 2,213,328.315 ↓ 89.0 89 1

Nested Loop (cost=26.86..3,842.04 rows=1 width=24) (actual time=1,137.032..2,213,328.315 rows=89 loops=1)

  • Buffers: shared hit=8676029 read=1734332 dirtied=26648
6. 0.049 773.673 ↓ 8.0 8 1

Nested Loop (cost=26.29..28.32 rows=1 width=24) (actual time=772.084..773.673 rows=8 loops=1)

  • Buffers: shared hit=77 read=17
7. 0.025 772.096 ↓ 8.0 8 1

HashAggregate (cost=26.00..26.01 rows=1 width=8) (actual time=772.078..772.096 rows=8 loops=1)

  • Group Key: sources_1.id
  • Buffers: shared hit=58 read=12
8. 0.020 772.071 ↓ 8.0 8 1

Sort (cost=25.98..25.99 rows=1 width=40) (actual time=772.070..772.071 rows=8 loops=1)

  • Sort Key: (lower((sources_1.public_name)::text))
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=58 read=12
9. 0.047 772.051 ↓ 8.0 8 1

Nested Loop Left Join (cost=0.71..25.97 rows=1 width=40) (actual time=127.175..772.051 rows=8 loops=1)

  • Filter: ((source_overrides.hide IS NULL) OR (source_overrides.hide IS FALSE))
  • Rows Removed by Filter: 2
  • Buffers: shared hit=58 read=12
10. 0.027 79.434 ↓ 10.0 10 1

Nested Loop (cost=0.29..23.52 rows=1 width=23) (actual time=0.026..79.434 rows=10 loops=1)

  • Join Filter: (sources_1.sourcing_strategy_id = sourcing_strategies_1.id)
  • Rows Removed by Join Filter: 27
  • Buffers: shared hit=37 read=1
11. 79.387 79.387 ↓ 10.0 10 1

Index Scan using sources_pkey on sources sources_1 (cost=0.29..22.34 rows=1 width=31) (actual time=0.021..79.387 rows=10 loops=1)

  • Index Cond: (id = ANY ('{4000011002,4000017002,4000031002,4000056002,4000096002,4000098002,4000100002,4000110002,4000151002,4000173002}'::bigint[]))
  • Filter: (display AND ((organization_id IS NULL) OR (organization_id = '4002074002'::bigint)))
  • Buffers: shared hit=27 read=1
12. 0.020 0.020 ↑ 2.0 4 10

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

  • Filter: (display AND reportable)
  • Buffers: shared hit=10
13. 692.570 692.570 ↓ 0.0 0 10

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=69.257..69.257 rows=0 loops=10)

  • Index Cond: ((sources_1.id = source_id) AND (organization_id = '4002074002'::bigint))
  • Buffers: shared hit=21 read=11
14. 1.528 1.528 ↑ 1.0 1 8

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

  • Index Cond: (id = sources_1.id)
  • Buffers: shared hit=19 read=5
15. 2,212,554.552 2,212,554.552 ↓ 11.0 11 8

Index Scan using index_applications_on_source_id on applications (cost=0.56..3,813.71 rows=1 width=16) (actual time=7,101.435..276,569.319 rows=11 loops=8)

  • Index Cond: (source_id = sources.id)
  • Filter: (prospect AND (created_at >= '2020-01-27 00:00:00'::timestamp without time zone) AND (created_at <= '2020-02-02 23:59:59.999999'::timestamp without time zone) AND (organization_id = '4002074002'::bigint) AND (stage_status_id = '4000005002'::bigint))
  • Rows Removed by Filter: 1388672
  • Buffers: shared hit=8675952 read=1734315 dirtied=26648
16. 0.267 0.267 ↑ 1.0 1 89

Index Only Scan using sourcing_strategies_pkey on sourcing_strategies (cost=0.13..0.15 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=89)

  • Index Cond: (id = sources.sourcing_strategy_id)
  • Heap Fetches: 0
  • Buffers: shared hit=90
17. 14.418 14.418 ↓ 0.0 0 89

Index Scan using index_prospective_hiring_plans_on_application_id on prospective_hiring_plans (cost=0.43..2.45 rows=1 width=16) (actual time=0.162..0.162 rows=0 loops=89)

  • Index Cond: (application_id = applications.id)
  • Buffers: shared hit=265 read=35
Planning time : 2,182.179 ms
Execution time : 2,213,343.675 ms