explain.depesz.com

PostgreSQL's explain analyze made readable

Result: h2uM

Settings
# exclusive inclusive rows x rows loops node
1. 3,475.368 15,183.303 ↓ 21,857.1 2,644,704 1

Hash Join (cost=1,979.59..1,964,735.63 rows=121 width=132) (actual time=51.911..15,183.303 rows=2,644,704 loops=1)

  • Hash Cond: ((booking.business_id = business.business_id) AND ((SubPlan 1) = business.etl_id))
2. 655.754 1,076.363 ↓ 6.0 2,645,291 1

Hash Join (cost=12.79..113,840.51 rows=443,920 width=68) (actual time=0.151..1,076.363 rows=2,645,291 loops=1)

  • Hash Cond: (booking.stage_load_id = etl_load.id)
3. 420.483 420.483 ↓ 1.0 2,645,291 1

Seq Scan on s_booking_booking booking (cost=0.00..106,780.50 rows=2,644,629 width=68) (actual time=0.013..420.483 rows=2,645,291 loops=1)

  • Filter: (stage_source_id = 3)
  • Rows Removed by Filter: 19949
4. 0.019 0.126 ↑ 1.3 37 1

Hash (cost=12.20..12.20 rows=47 width=4) (actual time=0.126..0.126 rows=37 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
5. 0.107 0.107 ↑ 1.3 37 1

Seq Scan on etl_load (cost=0.00..12.20 rows=47 width=4) (actual time=0.012..0.107 rows=37 loops=1)

  • Filter: ((source_id = 3) AND ((status)::text = 'S'::text))
  • Rows Removed by Filter: 243
6. 22.115 51.582 ↓ 2.0 14,994 1

Hash (cost=1,854.04..1,854.04 rows=7,518 width=412) (actual time=51.582..51.582 rows=14,994 loops=1)

  • Buckets: 16384 (originally 8192) Batches: 2 (originally 1) Memory Usage: 3969kB
7. 29.260 29.467 ↓ 2.0 14,994 1

Seq Scan on s_business_business business (cost=13.35..1,854.04 rows=7,518 width=412) (actual time=0.384..29.467 rows=14,994 loops=1)

  • Filter: ((NOT (hashed SubPlan 2)) AND (stage_source_id = 3))
  • Rows Removed by Filter: 452
8.          

SubPlan (forSeq Scan)

9. 0.207 0.207 ↑ 1.0 343 1

Seq Scan on etl_user_filter_out_lookup (cost=0.00..12.49 rows=343 width=4) (actual time=0.070..0.207 rows=343 loops=1)

  • Filter: (stage_source_id = 3)
  • Rows Removed by Filter: 336
10.          

SubPlan (forHash Join)

11. 0.000 10,579.990 ↑ 1.0 1 5,289,995

Limit (cost=0.29..8.32 rows=1 width=12) (actual time=0.002..0.002 rows=1 loops=5,289,995)

12. 10,579.990 10,579.990 ↑ 1.0 1 5,289,995

Index Scan using s_business_id_source_from_till_idx on s_business_business inner_business (cost=0.29..8.32 rows=1 width=12) (actual time=0.002..0.002 rows=1 loops=5,289,995)

  • Index Cond: ((booking.business_id = business_id) AND (stage_source_id = booking.stage_source_id) AND (effective_from < COALESCE(booking.effective_till, (''infinity''::timestamp without time zone)::timestamp with time zone)))
  • Filter: (booking.effective_from < COALESCE(effective_till, (''infinity''::timestamp without time zone)::timestamp with time zone))
  • Rows Removed by Filter: 1
Planning time : 2.348 ms
Execution time : 15,245.608 ms