explain.depesz.com

PostgreSQL's explain analyze made readable

Result: feQV

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 56.413 167,910.699 ↓ 46.5 34,098 1

Sort (cost=1,429,472.64..1,429,474.48 rows=734 width=184) (actual time=167,907.660..167,910.699 rows=34,098 loops=1)

  • Sort Key: db1.first_purchase_yearmonth, db1.first_purchase_partner_name, db1.first_purchase_confirmation_delay
  • Sort Method: quicksort Memory: 6,326kB
2.          

CTE db1

3. 29.270 154,086.497 ↓ 4.6 34,098 1

Nested Loop (cost=1,126.57..1,343,325.76 rows=7,343 width=44) (actual time=193.606..154,086.497 rows=34,098 loops=1)

4. 27.698 153,886.737 ↓ 4.6 34,098 1

Nested Loop (cost=1,126.29..1,341,104.39 rows=7,343 width=32) (actual time=192.589..153,886.737 rows=34,098 loops=1)

5. 0.000 153,824.941 ↓ 4.2 34,098 1

Gather (cost=1,126.00..1,338,619.34 rows=8,215 width=32) (actual time=191.498..153,824.941 rows=34,098 loops=1)

  • Workers Planned: 4
  • Workers Launched: 4
6. 32.479 164,996.715 ↓ 3.3 6,820 5 / 5

Hash Join (cost=126.00..1,336,797.84 rows=2,054 width=32) (actual time=500.097..164,996.715 rows=6,820 loops=5)

  • Hash Cond: (first_purchase_info.affiliate_created_data_day_id = dt_first_purchase.id)
7. 366.866 164,962.448 ↑ 1.9 124,044 5 / 5

Nested Loop (cost=0.57..1,336,059.55 rows=233,386 width=24) (actual time=3.863..164,962.448 rows=124,044 loops=5)

8. 148.442 148.442 ↑ 1.2 973,060 5 / 5

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

9. 164,447.140 164,447.140 ↓ 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=16) (actual time=0.169..0.169 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
10. 0.024 1.788 ↑ 1.1 88 5 / 5

Hash (cost=124.17..124.17 rows=101 width=12) (actual time=1.788..1.788 rows=88 loops=5)

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
11. 0.791 1.764 ↑ 1.1 88 5 / 5

Bitmap Heap Scan on ddata_day dt_first_purchase (cost=40.54..124.17 rows=101 width=12) (actual time=1.343..1.764 rows=88 loops=5)

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

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

13. 0.288 0.288 ↑ 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.288..0.288 rows=366 loops=5)

  • Index Cond: (year = 2,020)
14. 0.683 0.683 ↑ 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.683..0.683 rows=3,163 loops=5)

  • Index Cond: (data <= (CURRENT_DATE - '90 days'::interval day))
15. 34.098 34.098 ↑ 1.0 1 34,098

Index Only Scan using ddata_day_pkey on ddata_day dt_first_purchase_confirmation (cost=0.29..0.30 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=34,098)

  • Index Cond: (id = first_purchase_info.confirmed_data_day_id)
  • Heap Fetches: 0
16. 170.490 170.490 ↑ 1.0 1 34,098

Index Scan using dpartner_pkey on dpartner first_purchase_partner (cost=0.28..0.30 rows=1 width=20) (actual time=0.005..0.005 rows=1 loops=34,098)

  • Index Cond: (id = first_purchase_info.partner_id)
17. 86.603 167,854.286 ↓ 46.5 34,098 1

HashAggregate (cost=86,097.26..86,111.94 rows=734 width=184) (actual time=167,829.874..167,854.286 rows=34,098 loops=1)

  • Group Key: db1.customer_id, db1.first_purchase_partner_name, db1.first_purchase_yearmonth, db1.first_purchase_confirmation_delay
18. 20.615 167,767.683 ↓ 2.0 45,923 1

Nested Loop Left Join (cost=0.57..85,647.76 rows=22,475 width=92) (actual time=197.460..167,767.683 rows=45,923 loops=1)

19. 154,107.868 154,107.868 ↓ 4.6 34,098 1

CTE Scan on db1 (cost=0.00..146.86 rows=7,343 width=86) (actual time=193.610..154,107.868 rows=34,098 loops=1)

20. 13,639.200 13,639.200 ↑ 3.0 1 34,098

Index Scan using ftransaction_customer_id_unit_economics_id_transaction_type_idx on ftransaction next_purchases (cost=0.57..11.61 rows=3 width=26) (actual time=0.359..0.400 rows=1 loops=34,098)

  • Index Cond: ((customer_id = db1.customer_id) AND (unit_economics_id = 1) AND (transaction_type_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 : 122.506 ms
Execution time : 167,918.325 ms