explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 1.379 23,734.743 ↓ 6.0 6 1

Nested Loop (cost=135,756.64..141,015.45 rows=1 width=32) (actual time=21,821.512..23,734.743 rows=6 loops=1)

2. 212.453 23,729.647 ↓ 531.0 531 1

Subquery Scan on a (cost=135,755.10..141,012.76 rows=1 width=24) (actual time=21,731.410..23,729.647 rows=531 loops=1)

  • Filter: ((a.orden = '1'::bigint) AND (a.product_code = '101103'::bpchar) AND (a.tipo_producto = 'Antiplan'::bpchar) AND (a.purchase_date = (CURRENT_DATE - '32 days'::interval)))
  • Rows Removed by Filter: 2,910,914
3. 1,328.487 23,517.194 ↓ 24.9 2,911,445 1

WindowAgg (cost=135,755.10..138,091.84 rows=116,837 width=1,556) (actual time=21,730.733..23,517.194 rows=2,911,445 loops=1)

4. 21,254.564 22,188.707 ↓ 24.9 2,911,445 1

Sort (cost=135,755.10..136,047.19 rows=116,837 width=60) (actual time=21,730.726..22,188.707 rows=2,911,445 loops=1)

  • Sort Key: mkt_product_sales_history_2019.msisdn, mkt_product_sales_history_2019.purchase_date DESC
  • Sort Method: quicksort Memory: 507,726kB
5. 804.031 934.143 ↓ 24.9 2,911,445 1

Bitmap Heap Scan on mkt_product_sales_history_2019 (cost=1,481.35..125,920.85 rows=116,837 width=60) (actual time=159.121..934.143 rows=2,911,445 loops=1)

  • Recheck Cond: (purchase_date >= (CURRENT_DATE - '32 days'::interval))
  • Heap Blocks: exact=171,813
6. 130.112 130.112 ↓ 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=130.112..130.112 rows=2,911,528 loops=1)

  • Index Cond: (purchase_date >= (CURRENT_DATE - '32 days'::interval))
7. 1.062 3.717 ↓ 0.0 0 531

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

  • 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=531
8. 2.655 2.655 ↑ 1.0 1 531

Bitmap Index Scan on idx_sales_activations_subscriberid (cost=0.00..1.54 rows=1 width=0) (actual time=0.005..0.005 rows=1 loops=531)

  • Index Cond: (subscriberid = a.subscriberid)
Planning time : 0.275 ms
Execution time : 23,764.680 ms