explain.depesz.com

PostgreSQL's explain analyze made readable

Result: cZi7 : Optimization for: Optimization for: plan #x8Np; plan #EBRJ

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.993 1,317.587 ↓ 12.0 12 1

Nested Loop (cost=126,507.35..126,534.10 rows=1 width=32) (actual time=1,258.622..1,317.587 rows=12 loops=1)

2. 13.584 1,311.110 ↓ 1,371.0 1,371 1

Subquery Scan on a (cost=126,505.81..126,531.41 rows=1 width=24) (actual time=1,257.305..1,311.110 rows=1,371 loops=1)

  • Filter: ((a.orden = '1'::bigint) AND (a.purchase_date = (CURRENT_DATE - '32 days'::interval)))
  • Rows Removed by Filter: 65,589
3. 33.840 1,297.526 ↓ 104.6 66,960 1

WindowAgg (cost=126,505.81..126,518.61 rows=640 width=1,652) (actual time=1,257.275..1,297.526 rows=66,960 loops=1)

4. 358.904 1,263.686 ↓ 104.6 66,960 1

Sort (cost=126,505.81..126,507.41 rows=640 width=28) (actual time=1,257.268..1,263.686 rows=66,960 loops=1)

  • Sort Key: mkt_product_sales_history_2019.msisdn, mkt_product_sales_history_2019.purchase_date DESC
  • Sort Method: quicksort Memory: 8,304kB
5. 745.625 904.782 ↓ 104.6 66,960 1

Bitmap Heap Scan on mkt_product_sales_history_2019 (cost=1,452.31..126,475.98 rows=640 width=28) (actual time=189.943..904.782 rows=66,960 loops=1)

  • Recheck Cond: (purchase_date >= (CURRENT_DATE - '32 days'::interval))
  • Filter: ((product_code = '101103'::bpchar) AND (tipo_producto = 'Antiplan'::bpchar))
  • Rows Removed by Filter: 2,844,485
  • Heap Blocks: exact=171,813
6. 159.157 159.157 ↓ 24.9 2,911,528 1

Bitmap Index Scan on mkt_product_sales_history_2019_purchase_date_idx (cost=0.00..1,452.14 rows=116,837 width=0) (actual time=159.157..159.157 rows=2,911,528 loops=1)

  • Index Cond: (purchase_date >= (CURRENT_DATE - '32 days'::interval))
7. 1.371 5.484 ↓ 0.0 0 1,371

Bitmap Heap Scan on sales_activations (cost=1.54..2.68 rows=1 width=8) (actual time=0.004..0.004 rows=0 loops=1,371)

  • Recheck Cond: (subscriberid = a.subscriberid)
  • Filter: (((canal_venta)::text = 'CANAL FDV'::text) AND ((fecha)::date >= '2019-04-01'::date) AND ((fecha)::date >= '2019-04-01'::date) AND ((fecha)::date >= (CURRENT_DATE - '60 days'::interval)))
  • Rows Removed by Filter: 1
  • Heap Blocks: exact=1,371
8. 4.113 4.113 ↑ 1.0 1 1,371

Bitmap Index Scan on idx_sales_activations_subscriberid (cost=0.00..1.54 rows=1 width=0) (actual time=0.003..0.003 rows=1 loops=1,371)

  • Index Cond: (subscriberid = a.subscriberid)
Planning time : 0.299 ms
Execution time : 1,317.640 ms