explain.depesz.com

PostgreSQL's explain analyze made readable

Result: O5Ye

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 557.366 25,453.179 ↑ 1.0 1 1

Aggregate (cost=1,462,082.18..1,462,082.19 rows=1 width=8) (actual time=25,453.179..25,453.179 rows=1 loops=1)

2. 802.142 24,895.813 ↑ 4.0 4,663,969 1

Hash Join (cost=717,961.90..1,414,905.27 rows=18,870,766 width=4) (actual time=15,430.508..24,895.813 rows=4,663,969 loops=1)

  • Hash Cond: (s.screening_recommendation_type_id = srt.id)
3. 1,474.428 24,093.661 ↑ 4.0 4,668,336 1

Hash Join (cost=717,960.76..1,330,437.14 rows=18,870,766 width=8) (actual time=15,430.320..24,093.661 rows=4,668,336 loops=1)

  • Hash Cond: (sa.screening_id = s.id)
4. 3,389.363 20,879.735 ↑ 4.0 4,668,336 1

Hash Join (cost=579,073.76..1,142,014.33 rows=18,870,766 width=8) (actual time=13,671.604..20,879.735 rows=4,668,336 loops=1)

  • Hash Cond: (st.screening_applicant_id = sa.id)
5. 3,856.033 3,856.033 ↓ 1.1 20,276,642 1

Index Only Scan using screening_transactions_screening_applicant_id_idx on screening_transactions st (cost=0.56..513,405.33 rows=18,870,766 width=4) (actual time=0.024..3,856.033 rows=20,276,642 loops=1)

  • Heap Fetches: 1,074,501
6. 580.449 13,634.339 ↑ 4.3 1,305,258 1

Hash (cost=508,845.20..508,845.20 rows=5,618,240 width=8) (actual time=13,634.339..13,634.339 rows=1,305,258 loops=1)

  • Buckets: 8,388,608 Batches: 1 Memory Usage: 116,523kB
7. 13,053.890 13,053.890 ↑ 4.3 1,305,258 1

Seq Scan on screening_applicants sa (cost=0.00..508,845.20 rows=5,618,240 width=8) (actual time=0.081..13,053.890 rows=1,305,258 loops=1)

  • Filter: ((lower((name_first)::text) ~~ '%theodore%'::text) OR (lower((name_last)::text) ~~ '%theodore%'::text) OR (lower((name_middle)::text) ~~ '%theodore%'::text) OR (lower((name_first)::text) ~~ '%j%'::text) OR (lower((name_last)::text) ~~ '%j%'::text) OR (lower((name_middle)::text) ~~ '%j%'::text) OR (lower((name_first)::text) ~~ '%thomas%'::text) OR (lower((name_last)::text) ~~ '%thomas%'::text) OR (lower((name_middle)::text) ~~ '%thomas%'::text))
  • Rows Removed by Filter: 4,312,982
8. 945.280 1,739.498 ↑ 1.0 3,436,739 1

Hash (cost=95,922.00..95,922.00 rows=3,437,200 width=8) (actual time=1,739.498..1,739.498 rows=3,436,739 loops=1)

  • Buckets: 4,194,304 Batches: 1 Memory Usage: 166,992kB
9. 794.218 794.218 ↑ 1.0 3,436,739 1

Seq Scan on screenings s (cost=0.00..95,922.00 rows=3,437,200 width=8) (actual time=0.018..794.218 rows=3,436,739 loops=1)

10. 0.003 0.010 ↓ 1.2 7 1

Hash (cost=1.06..1.06 rows=6 width=4) (actual time=0.010..0.010 rows=7 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
11. 0.007 0.007 ↓ 1.2 7 1

Seq Scan on screening_recommendation_types srt (cost=0.00..1.06 rows=6 width=4) (actual time=0.006..0.007 rows=7 loops=1)

Planning time : 1.575 ms
Execution time : 25,460.322 ms