explain.depesz.com

PostgreSQL's explain analyze made readable

Result: z895

Settings
# exclusive inclusive rows x rows loops node
1. 1.864 54,474.190 ↓ 23.0 23 1

GroupAggregate (cost=116,808.91..116,808.93 rows=1 width=24) (actual time=54,471.682..54,474.190 rows=23 loops=1)

  • Group Key: sourcing_strategies.id, applications.source_id
  • Buffers: shared hit=84907 read=170724 dirtied=981
2.          

CTE cte

3. 0.003 0.131 ↑ 1.0 1 1

Nested Loop Left Join (cost=40.91..45.16 rows=1 width=8) (actual time=0.130..0.131 rows=1 loops=1)

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

Nested Loop (cost=40.49..42.71 rows=1 width=8) (actual time=0.121..0.122 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.007 0.111 ↑ 1.0 1 1

Bitmap Heap Scan on sources sources_1 (cost=40.49..41.53 rows=1 width=16) (actual time=0.110..0.111 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.104 ↓ 0.0 0 1

BitmapAnd (cost=40.49..40.49 rows=1 width=0) (actual time=0.104..0.104 rows=0 loops=1)

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

BitmapOr (cost=12.99..12.99 rows=854 width=0) (actual time=0.063..0.064 rows=0 loops=1)

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

Bitmap Index Scan on index_sources_on_organization_id (cost=0.00..3.81 rows=203 width=0) (actual time=0.023..0.023 rows=210 loops=1)

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

Bitmap Index Scan on index_sources_on_organization_id (cost=0.00..9.18 rows=652 width=0) (actual time=0.039..0.039 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.031..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.004..0.007 rows=7 loops=1)

  • Filter: (display AND reportable)
  • Buffers: shared hit=1
12. 0.006 0.006 ↓ 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.006..0.006 rows=0 loops=1)

  • Index Cond: ((sources_1.id = source_id) AND (organization_id = '4002074002'::bigint))
  • Buffers: shared hit=3
13. 3.489 54,472.326 ↓ 2,249.0 2,249 1

Sort (cost=116,763.75..116,763.76 rows=1 width=24) (actual time=54,471.665..54,472.326 rows=2,249 loops=1)

  • Sort Key: sourcing_strategies.id, applications.source_id
  • Sort Method: quicksort Memory: 272kB
  • Buffers: shared hit=84907 read=170724 dirtied=981
14. 3.452 54,468.837 ↓ 2,249.0 2,249 1

Nested Loop (cost=78,901.19..116,763.74 rows=1 width=24) (actual time=35,859.838..54,468.837 rows=2,249 loops=1)

  • Buffers: shared hit=84904 read=170724 dirtied=981
15. 7.221 54,460.887 ↓ 2,249.0 2,249 1

Nested Loop Left Join (cost=78,901.06..116,763.59 rows=1 width=24) (actual time=35,859.808..54,460.887 rows=2,249 loops=1)

  • Filter: ((prospective_hiring_plans.hiring_plan_id = '4460836002'::bigint) OR (prospective_hiring_plans.hiring_plan_id IS NULL))
  • Rows Removed by Filter: 1334
  • Buffers: shared hit=82655 read=170723 dirtied=981
16. 11.534 54,392.806 ↓ 2.5 3,580 1

Nested Loop (cost=78,900.63..113,780.84 rows=1,422 width=24) (actual time=35,859.789..54,392.806 rows=3,580 loops=1)

  • Buffers: shared hit=70909 read=170341 dirtied=981
17. 18,596.533 54,359.654 ↓ 1.3 3,603 1

Bitmap Heap Scan on applications (cost=78,900.34..111,671.43 rows=2,845 width=16) (actual time=35,859.631..54,359.654 rows=3,603 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: 173856
  • Filter: prospect
  • Rows Removed by Filter: 58717
  • Heap Blocks: exact=152866
  • Buffers: shared hit=60112 read=170340 dirtied=981
18. 52.722 35,763.121 ↓ 0.0 0 1

BitmapAnd (cost=78,900.34..78,900.34 rows=32,691 width=0) (actual time=35,763.121..35,763.121 rows=0 loops=1)

  • Buffers: shared hit=7573 read=70013
19. 26,750.838 26,750.838 ↓ 1.1 598,169 1

Bitmap Index Scan on index_applications_on_organization_id (cost=0.00..17,877.26 rows=525,960 width=0) (actual time=26,750.838..26,750.838 rows=598,169 loops=1)

  • Index Cond: (organization_id = '4002074002'::bigint)
  • Buffers: shared hit=5 read=32435
20. 8,959.561 8,959.561 ↓ 1.3 2,916,896 1

Bitmap Index Scan on index_applications_on_created_at (cost=0.00..61,021.40 rows=2,302,184 width=0) (actual time=8,959.560..8,959.561 rows=2,916,896 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=7568 read=37578
21. 18.038 21.618 ↑ 1.0 1 3,603

Index Scan using sources_pkey on sources (cost=0.29..0.73 rows=1 width=16) (actual time=0.006..0.006 rows=1 loops=3,603)

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

SubPlan (for Index Scan)

23. 3.580 3.580 ↑ 1.0 1 3,580

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

  • Buffers: shared hit=58
24. 60.860 60.860 ↓ 0.0 0 3,580

Index Scan using index_prospective_hiring_plans_on_application_id on prospective_hiring_plans (cost=0.43..2.09 rows=1 width=16) (actual time=0.017..0.017 rows=0 loops=3,580)

  • Index Cond: (application_id = applications.id)
  • Buffers: shared hit=11746 read=382
25. 4.498 4.498 ↑ 1.0 1 2,249

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,249)

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