explain.depesz.com

PostgreSQL's explain analyze made readable

Result: BdxB

Settings
# exclusive inclusive rows x rows loops node
1. 488.715 58,379.856 ↓ 133,892.0 133,892 1

Nested Loop Left Join (cost=908,755.45..1,286,341.04 rows=1 width=154) (actual time=12,139.910..58,379.856 rows=133,892 loops=1)

  • Buffers: shared hit=1997794 read=216562 dirtied=51481
2. 458.800 57,623.357 ↓ 133,892.0 133,892 1

Hash Left Join (cost=908,755.17..1,286,335.19 rows=1 width=117) (actual time=12,139.880..57,623.357 rows=133,892 loops=1)

  • Hash Cond: (((e.sales_channel)::text = (l.sales_channel)::text) AND ((e.bundle_add)::text = (l.bundle_add)::text) AND ((COALESCE(e.add_video_desc, 'NULL'::character varying))::text = (l.add_video_desc)::text) AND ((COALESCE(e.add_hsd_speed, 'NULL'::character varying))::text = (l.add_hsd_speed)::text))
  • Join Filter: ((e.wordate >= l.begin_date) AND (e.wordate <= l.end_date))
  • Rows Removed by Join Filter: 83984
  • Buffers: shared hit=1727348 read=216562 dirtied=51481
3. 418.232 57,162.415 ↓ 133,892.0 133,892 1

Nested Loop (cost=908,692.93..1,286,271.89 rows=1 width=93) (actual time=12,137.667..57,162.415 rows=133,892 loops=1)

  • Buffers: shared hit=1727322 read=216562 dirtied=51481
4. 397.461 56,342.507 ↓ 133,892.0 133,892 1

Nested Loop (cost=908,692.79..1,286,269.72 rows=1 width=97) (actual time=12,137.622..56,342.507 rows=133,892 loops=1)

  • Join Filter: ((e.wip_transaction_id = sh.wip_transaction_id) AND (e.sale_date = sh.sale_date))
  • Rows Removed by Join Filter: 23883
  • Buffers: shared hit=1459538 read=216562 dirtied=51481
5. 399.445 19,124.746 ↓ 133,892.0 133,892 1

Nested Loop (cost=908,692.23..1,286,263.12 rows=1 width=99) (actual time=12,137.577..19,124.746 rows=133,892 loops=1)

  • Buffers: shared hit=813966 read=169557 dirtied=51481
6. 5,123.245 17,252.489 ↓ 27.9 133,892 1

Bitmap Heap Scan on f_ibs_entered_sales_wip_dtls e (cost=908,691.79..1,246,902.86 rows=4,792 width=83) (actual time=12,137.530..17,252.489 rows=133,892 loops=1)

  • Recheck Cond: ((wordate >= '2019-05-01 00:00:00'::timestamp without time zone) AND ((sales_wstat)::text = 'A'::text))
  • Filter: (bundle_add IS NOT NULL)
  • Rows Removed by Filter: 24297
  • Heap Blocks: exact=34188
  • Buffers: shared hit=9428 read=169555
7. 44.809 12,129.244 ↓ 0.0 0 1

BitmapAnd (cost=908,691.79..908,691.79 rows=103,636 width=0) (actual time=12,129.244..12,129.244 rows=0 loops=1)

  • Buffers: shared read=144795
8. 4,727.205 4,727.205 ↓ 1.1 2,302,275 1

Bitmap Index Scan on indx_entered_who_wordate (cost=0.00..58,756.96 rows=2,147,786 width=0) (actual time=4,727.205..4,727.205 rows=2,302,275 loops=1)

  • Index Cond: (wordate >= '2019-05-01 00:00:00'::timestamp without time zone)
  • Buffers: shared read=18203
9. 7,357.230 7,357.230 ↓ 1.0 2,213,534 1

Bitmap Index Scan on indx_sls_chnl_wstat (cost=0.00..849,932.18 rows=2,196,433 width=0) (actual time=7,357.230..7,357.230 rows=2,213,534 loops=1)

  • Index Cond: ((sales_wstat)::text = 'A'::text)
  • Buffers: shared read=126592
10. 1,472.812 1,472.812 ↑ 1.0 1 133,892

Index Scan using nfov_install_rate_history_pkey on nfov_install_rate_history r (cost=0.43..8.20 rows=1 width=35) (actual time=0.007..0.011 rows=1 loops=133,892)

  • Index Cond: ((wip_transaction_id = e.wip_transaction_id) AND (wipmaster_id = e.last_wipmaster_id))
  • Filter: (orig_order_dt >= '2019-05-01 00:00:00'::timestamp without time zone)
  • Buffers: shared hit=804538 read=2 dirtied=51481
11. 36,820.300 36,820.300 ↑ 1.0 1 133,892

Index Scan using stg_kom_sales_history_pk on stg_kom_sales_history sh (cost=0.56..6.59 rows=1 width=30) (actual time=0.238..0.275 rows=1 loops=133,892)

  • Index Cond: (wip_transaction_id = r.wip_transaction_id)
  • Buffers: shared hit=645572 read=47005
12. 401.676 401.676 ↑ 1.0 1 133,892

Index Only Scan using stg_d_kom_video_tier_pkey on stg_kom_d_video_tier vt (cost=0.14..2.16 rows=1 width=4) (actual time=0.002..0.003 rows=1 loops=133,892)

  • Index Cond: (video_tier_id = sh.curr_video_tier_id)
  • Heap Fetches: 133892
  • Buffers: shared hit=267784
13. 1.212 2.142 ↑ 1.0 1,308 1

Hash (cost=36.08..36.08 rows=1,308 width=69) (actual time=2.142..2.142 rows=1,308 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 160kB
  • Buffers: shared hit=23
14. 0.930 0.930 ↑ 1.0 1,308 1

Seq Scan on d_opt_install_logic_card l (cost=0.00..36.08 rows=1,308 width=69) (actual time=0.009..0.930 rows=1,308 loops=1)

  • Buffers: shared hit=23
15. 267.784 267.784 ↓ 0.0 0 133,892

Index Scan using indx_nfov_wip_trans on tmp_nfov_install_rate x (cost=0.28..5.83 rows=1 width=45) (actual time=0.002..0.002 rows=0 loops=133,892)

  • Index Cond: (wip_transaction_id = e.wip_transaction_id)
  • Buffers: shared hit=270446
Planning time : 272.298 ms
Execution time : 58,462.402 ms