explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2tHW

Settings
# exclusive inclusive rows x rows loops node
1. 1,485.503 5,303,262.080 ↓ 3.5 100,772 1

Hash Join (cost=46,240.64..177,566,891.86 rows=29,052 width=51) (actual time=499.437..5,303,262.080 rows=100,772 loops=1)

  • Hash Cond: (opps.line_number = os.line_number)
2. 637.395 637.395 ↑ 1.0 358,984 1

Seq Scan on offer_ppoint_staging_3022814 opps (cost=0.00..6,428.30 rows=358,984 width=10) (actual time=0.010..637.395 rows=358,984 loops=1)

  • Filter: ((channel_code IS NULL) AND (quantity = 1))
3. 120.844 431.210 ↓ 3.4 100,772 1

Hash (cost=45,873.70..45,873.70 rows=29,355 width=45) (actual time=431.210..431.210 rows=100,772 loops=1)

  • Buckets: 131,072 (originally 32768) Batches: 1 (originally 1) Memory Usage: 8,897kB
4. 310.366 310.366 ↓ 3.4 100,772 1

Index Scan using offer_staging_3022814_flag_action_idx on offer_staging_3022814 os (cost=0.42..45,873.70 rows=29,355 width=45) (actual time=0.286..310.366 rows=100,772 loops=1)

  • Index Cond: (flag_action = 'ERROR'::text)
  • Filter: (error_code = 3)
  • Rows Removed by Filter: 798
5.          

SubPlan (for Hash Join)

6. 7,557.900 5,300,707.972 ↑ 1.0 1 100,772

Nested Loop (cost=0.84..6,110.16 rows=1 width=1,688) (actual time=52.594..52.601 rows=1 loops=100,772)

7. 13,301.904 13,301.904 ↑ 1.0 1 100,772

Index Scan using offer_staging_3022814_line_number_idx on offer_staging_3022814 os_2 (cost=0.42..8.44 rows=1 width=1,682) (actual time=0.130..0.132 rows=1 loops=100,772)

  • Index Cond: (line_number = os.line_number)
8. 1,410.808 1,410.808 ↑ 1.0 1 100,772

Index Scan using offer_ppoint_staging_3022814_line_number_quantity_idx on offer_ppoint_staging_3022814 opps_2 (cost=0.42..8.44 rows=1 width=10) (actual time=0.011..0.014 rows=1 loops=100,772)

  • Index Cond: ((line_number = os.line_number) AND (quantity = 1))
9.          

SubPlan (for Nested Loop)

10. 1,713.124 5,275,313.428 ↑ 1.0 1 100,772

Aggregate (cost=6,084.80..6,084.82 rows=1 width=56) (actual time=52.348..52.349 rows=1 loops=100,772)

11. 1,007.720 5,273,600.304 ↑ 1.0 1 100,772

Subquery Scan on all_prices (cost=6,084.77..6,084.80 rows=1 width=56) (actual time=52.329..52.332 rows=1 loops=100,772)

12. 2,922.388 5,272,592.584 ↑ 1.0 1 100,772

HashAggregate (cost=6,084.77..6,084.79 rows=1 width=86) (actual time=52.321..52.322 rows=1 loops=100,772)

  • Group Key: ops.line_number, ops.channel_code, ops.discount_start, ops.discount_end
13. 1,511.580 5,269,670.196 ↑ 1.0 1 100,772

Nested Loop (cost=0.42..6,084.75 rows=1 width=86) (actual time=39.801..52.293 rows=1 loops=100,772)

  • Join Filter: (NOT (ops.channel_code IS DISTINCT FROM opps_1.channel_code))
14. 5,266,647.036 5,266,647.036 ↑ 1.0 1 100,772

Seq Scan on offer_pricing_staging_3022814 ops (cost=0.00..6,076.30 rows=1 width=52) (actual time=39.777..52.263 rows=1 loops=100,772)

  • Filter: (line_number = os_2.line_number)
  • Rows Removed by Filter: 358,983
15. 1,511.580 1,511.580 ↑ 1.0 1 100,772

Index Scan using offer_ppoint_staging_3022814_line_number_channel_code_quant_idx on offer_ppoint_staging_3022814 opps_1 (cost=0.42..8.44 rows=1 width=70) (actual time=0.014..0.015 rows=1 loops=100,772)

  • Index Cond: (line_number = os_2.line_number)
16. 3,123.932 3,123.932 ↑ 1.0 1 100,772

Index Scan using offer_staging_3022814_line_number_idx on offer_staging_3022814 os_1 (cost=0.42..8.44 rows=1 width=70) (actual time=0.021..0.031 rows=1 loops=100,772)

  • Index Cond: (line_number = os_2.line_number)