explain.depesz.com

PostgreSQL's explain analyze made readable

Result: mXat : Optimization for: plan #feQV

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 13.315 285,578.666 ↑ 2.3 10,736 1

GroupAggregate (cost=1,436,572.10..1,437,640.72 rows=25,144 width=96) (actual time=285,563.971..285,578.666 rows=10,736 loops=1)

  • Group Key: ddata_day.yearmonth, db1.partner_id, db1.first_purchase_confirmation_delay, db1.customer_id
2.          

CTE db1

3. 0.000 261,008.198 ↓ 4.2 34,098 1

Gather (cost=1,126.00..1,338,642.08 rows=8,215 width=24) (actual time=740.267..261,008.198 rows=34,098 loops=1)

  • Workers Planned: 4
  • Workers Launched: 4
4. 34.335 280,559.498 ↓ 3.3 6,820 5 / 5

Hash Join (cost=126.00..1,336,805.18 rows=2,054 width=24) (actual time=3,056.444..280,559.498 rows=6,820 loops=5)

  • Hash Cond: (first_purchase_info.affiliate_created_data_day_id = dt_first_purchase.id)
5. 93.136 280,524.135 ↑ 1.9 124,048 5 / 5

Nested Loop (cost=0.57..1,336,061.75 rows=233,386 width=24) (actual time=16.097..280,524.135 rows=124,048 loops=5)

6. 189.719 189.719 ↑ 1.2 973,060 5 / 5

Parallel Seq Scan on first_purchase (cost=0.00..52,708.25 rows=1,216,325 width=16) (actual time=1.839..189.719 rows=973,060 loops=5)

7. 280,241.280 280,241.280 ↓ 0.0 0 4,865,300 / 5

Index Scan using ftransaction_pk on ftransaction first_purchase_info (cost=0.57..1.06 rows=1 width=12) (actual time=0.288..0.288 rows=0 loops=4,865,300)

  • Index Cond: (id = first_purchase.transaction_id)
  • Filter: ((unit_economics_id = 1) AND (status_id = 1))
  • Rows Removed by Filter: 1
8. 0.013 1.028 ↑ 1.1 88 5 / 5

Hash (cost=124.17..124.17 rows=101 width=4) (actual time=1.028..1.028 rows=88 loops=5)

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
9. 0.019 1.015 ↑ 1.1 88 5 / 5

Bitmap Heap Scan on ddata_day dt_first_purchase (cost=40.54..124.17 rows=101 width=4) (actual time=1.001..1.015 rows=88 loops=5)

  • Recheck Cond: ((year = 2,020) AND (data <= (CURRENT_DATE - '90 days'::interval day)))
  • Heap Blocks: exact=4
10. 0.002 0.996 ↓ 0.0 0 5 / 5

BitmapAnd (cost=40.54..40.54 rows=101 width=0) (actual time=0.996..0.996 rows=0 loops=5)

11. 0.364 0.364 ↑ 1.0 366 5 / 5

Bitmap Index Scan on ddata_day_year_idx (cost=0.00..5.23 rows=366 width=0) (actual time=0.364..0.364 rows=366 loops=5)

  • Index Cond: (year = 2,020)
12. 0.630 0.630 ↑ 1.0 3,163 5 / 5

Bitmap Index Scan on ddata_day_data_idx (cost=0.00..35.01 rows=3,163 width=0) (actual time=0.629..0.630 rows=3,163 loops=5)

  • Index Cond: (data <= (CURRENT_DATE - '90 days'::interval day))
13. 31.094 285,565.351 ↑ 1.1 22,561 1

Sort (cost=97,930.02..97,992.88 rows=25,144 width=38) (actual time=285,563.955..285,565.351 rows=22,561 loops=1)

  • Sort Key: ddata_day.yearmonth, db1.partner_id, db1.first_purchase_confirmation_delay, db1.customer_id
  • Sort Method: quicksort Memory: 2,531kB
14. 31.025 285,534.257 ↑ 1.1 22,561 1

Nested Loop (cost=434.80..96,092.25 rows=25,144 width=38) (actual time=754.282..285,534.257 rows=22,561 loops=1)

15. 22.941 261,054.966 ↓ 4.2 34,098 1

Hash Join (cost=434.23..620.11 rows=8,215 width=32) (actual time=746.317..261,054.966 rows=34,098 loops=1)

  • Hash Cond: (db1.first_purchase_date_id = ddata_day.id)
16. 261,026.010 261,026.010 ↓ 4.2 34,098 1

CTE Scan on db1 (cost=0.00..164.30 rows=8,215 width=24) (actual time=740.270..261,026.010 rows=34,098 loops=1)

17. 2.609 6.015 ↑ 1.0 11,477 1

Hash (cost=290.77..290.77 rows=11,477 width=12) (actual time=6.015..6.015 rows=11,477 loops=1)

  • Buckets: 16,384 Batches: 1 Memory Usage: 622kB
18. 3.406 3.406 ↑ 1.0 11,477 1

Seq Scan on ddata_day (cost=0.00..290.77 rows=11,477 width=12) (actual time=0.007..3.406 rows=11,477 loops=1)

19. 24,448.266 24,448.266 ↑ 3.0 1 34,098

Index Scan using ftransaction_customer_id_transaction_type_id_unit_economics_idx on ftransaction next_purchases (cost=0.57..11.59 rows=3 width=26) (actual time=0.576..0.717 rows=1 loops=34,098)

  • Index Cond: ((customer_id = db1.customer_id) AND (transaction_type_id = 1) AND (unit_economics_id = 1) AND (affiliate_created_data_day_id <= (db1.first_purchase_date_id + 30)))
  • Filter: (id > db1.first_purchase_id)
  • Rows Removed by Filter: 1
Planning time : 1.188 ms
Execution time : 285,579.577 ms