explain.depesz.com

PostgreSQL's explain analyze made readable

Result: yk6LB

Settings
# exclusive inclusive rows x rows loops node
1. 68.438 45,872.152 ↓ 9.8 429,245 1

Unique (cost=6,788,638.25..6,789,294.67 rows=43,761 width=60) (actual time=45,683.516..45,872.152 rows=429,245 loops=1)

2. 374.465 45,803.714 ↓ 9.8 429,245 1

Sort (cost=6,788,638.25..6,788,747.66 rows=43,761 width=60) (actual time=45,683.514..45,803.714 rows=429,245 loops=1)

  • Sort Key: iwg.game_id, iwg.retailer_id, iwg.retail_iwg_pack_serial_number, ((dim_game.game_price * (dim_game.game_pack_size)::numeric)), iwg.transaction_date
  • Sort Method: external merge Disk: 19,240kB
3. 175.196 45,429.249 ↓ 9.8 429,245 1

Hash Left Join (cost=6,737,676.26..6,785,264.86 rows=43,761 width=60) (actual time=44,301.693..45,429.249 rows=429,245 loops=1)

  • Hash Cond: (iwg.game_id = dim_game.game_id)
4. 359.002 45,253.935 ↓ 9.8 429,245 1

Merge Join (cost=6,737,648.03..6,784,416.10 rows=43,761 width=28) (actual time=44,301.545..45,253.935 rows=429,245 loops=1)

  • Merge Cond: (((max(in_iwg.transaction_timestamp)) = iwg.transaction_timestamp) AND ((((in_iwg.game_id * in_iwg.retailer_id) * in_iwg.retail_iwg_pack_serial_number)) = (((iwg.game_id * iwg.retailer_id) * iwg.retail_iwg_pack_serial_number))))
5. 2,395.222 16,182.412 ↓ 1.3 3,073,865 1

Sort (cost=2,512,366.49..2,518,254.55 rows=2,355,224 width=16) (actual time=15,539.464..16,182.412 rows=3,073,865 loops=1)

  • Sort Key: (max(in_iwg.transaction_timestamp)), (((in_iwg.game_id * in_iwg.retailer_id) * in_iwg.retail_iwg_pack_serial_number))
  • Sort Method: external merge Disk: 78,104kB
6. 1,533.632 13,787.190 ↓ 1.3 3,073,865 1

GroupAggregate (cost=2,048,719.06..2,159,037.02 rows=2,355,224 width=16) (actual time=10,289.594..13,787.190 rows=3,073,865 loops=1)

  • Group Key: (((in_iwg.game_id * in_iwg.retailer_id) * in_iwg.retail_iwg_pack_serial_number))
7. 9,001.018 12,253.558 ↓ 1.1 10,615,341 1

Sort (cost=2,048,719.06..2,073,715.59 rows=9,998,614 width=16) (actual time=10,289.586..12,253.558 rows=10,615,341 loops=1)

  • Sort Key: (((in_iwg.game_id * in_iwg.retailer_id) * in_iwg.retail_iwg_pack_serial_number))
  • Sort Method: external merge Disk: 268,800kB
8. 3,252.540 3,252.540 ↓ 1.1 10,615,341 1

Index Scan using fact_transitions_iwg_pack_retail_idx1 on fact_transitions_iwg_pack_retail in_iwg (cost=0.44..544,461.37 rows=9,998,614 width=16) (actual time=0.023..3,252.540 rows=10,615,341 loops=1)

  • Index Cond: ((transaction_date >= '2019-11-01'::date) AND (transaction_date <= '2020-06-28'::date))
9. 42.542 28,712.521 ↑ 2.5 448,301 1

Materialize (cost=4,225,281.54..4,230,911.59 rows=1,126,011 width=36) (actual time=28,618.865..28,712.521 rows=448,301 loops=1)

10. 334.498 28,669.979 ↑ 2.5 448,301 1

Sort (cost=4,225,281.54..4,228,096.57 rows=1,126,011 width=36) (actual time=28,618.860..28,669.979 rows=448,301 loops=1)

  • Sort Key: iwg.transaction_timestamp, (((iwg.game_id * iwg.retailer_id) * iwg.retail_iwg_pack_serial_number))
  • Sort Method: external sort Disk: 25,432kB
11. 5,873.436 28,335.481 ↑ 2.5 448,301 1

Index Scan using fact_transitions_iwg_pack_retail_idx1 on fact_transitions_iwg_pack_retail iwg (cost=3,456,068.73..4,050,522.73 rows=1,126,011 width=36) (actual time=23,619.428..28,335.481 rows=448,301 loops=1)

  • Index Cond: ((transaction_date >= '2019-11-01'::date) AND (transaction_date <= '2020-06-28'::date))
  • Filter: (((retail_iwg_pack_status_to)::text = 'Activated'::text) AND (NOT (hashed SubPlan 1)))
  • Rows Removed by Filter: 10,167,040
12.          

SubPlan (for Index Scan)

13. 409.011 22,462.045 ↓ 26.8 2,349,503 1

Group (cost=3,390,574.43..3,455,848.97 rows=87,730 width=8) (actual time=20,058.610..22,462.045 rows=2,349,503 loops=1)

  • Group Key: (((iwg_win.game_id * iwg_win.retailer_id) * iwg_win.retail_iwg_pack_serial_number))
14. 854.737 22,053.034 ↓ 26.8 2,349,508 1

Merge Join (cost=3,390,574.43..3,455,190.99 rows=87,730 width=8) (actual time=20,058.609..22,053.034 rows=2,349,508 loops=1)

  • Merge Cond: (((((iwg_win2.game_id * iwg_win2.retailer_id) * iwg_win2.retail_iwg_pack_serial_number)) = (((iwg_win.game_id * iwg_win.retailer_id) * iwg_win.retail_iwg_pack_serial_number))) AND ((max(iwg_win2.transaction_timestamp)) = iwg_win.transaction_timestamp))
15. 1,713.787 15,660.756 ↓ 1.3 3,073,859 1

Sort (cost=2,512,366.49..2,518,254.55 rows=2,355,224 width=16) (actual time=15,300.952..15,660.756 rows=3,073,859 loops=1)

  • Sort Key: (((iwg_win2.game_id * iwg_win2.retailer_id) * iwg_win2.retail_iwg_pack_serial_number)), (max(iwg_win2.transaction_timestamp))
  • Sort Method: external sort Disk: 78,128kB
16. 1,503.205 13,946.969 ↓ 1.3 3,073,865 1

GroupAggregate (cost=2,048,719.06..2,159,037.02 rows=2,355,224 width=16) (actual time=10,448.351..13,946.969 rows=3,073,865 loops=1)

  • Group Key: (((iwg_win2.game_id * iwg_win2.retailer_id) * iwg_win2.retail_iwg_pack_serial_number))
17. 9,191.400 12,443.764 ↓ 1.1 10,615,341 1

Sort (cost=2,048,719.06..2,073,715.59 rows=9,998,614 width=16) (actual time=10,448.342..12,443.764 rows=10,615,341 loops=1)

  • Sort Key: (((iwg_win2.game_id * iwg_win2.retailer_id) * iwg_win2.retail_iwg_pack_serial_number))
  • Sort Method: external merge Disk: 268,800kB
18. 3,252.364 3,252.364 ↓ 1.1 10,615,341 1

Index Scan using fact_transitions_iwg_pack_retail_idx1 on fact_transitions_iwg_pack_retail iwg_win2 (cost=0.44..544,461.37 rows=9,998,614 width=16) (actual time=0.025..3,252.364 rows=10,615,341 loops=1)

  • Index Cond: ((transaction_date >= '2019-11-01'::date) AND (transaction_date <= '2020-06-28'::date))
19. 223.266 5,537.541 ↓ 1.1 2,397,299 1

Materialize (cost=878,207.94..889,494.71 rows=2,257,354 width=32) (actual time=4,757.645..5,537.541 rows=2,397,299 loops=1)

20. 2,111.824 5,314.275 ↓ 1.1 2,397,299 1

Sort (cost=878,207.94..883,851.32 rows=2,257,354 width=32) (actual time=4,757.635..5,314.275 rows=2,397,299 loops=1)

  • Sort Key: (((iwg_win.game_id * iwg_win.retailer_id) * iwg_win.retail_iwg_pack_serial_number)), iwg_win.transaction_timestamp
  • Sort Method: external merge Disk: 117,216kB
21. 3,202.451 3,202.451 ↓ 1.1 2,397,299 1

Index Scan using fact_transitions_iwg_pack_retail_idx1 on fact_transitions_iwg_pack_retail iwg_win (cost=0.44..531,963.10 rows=2,257,354 width=32) (actual time=0.052..3,202.451 rows=2,397,299 loops=1)

  • Index Cond: ((transaction_date >= '2019-11-01'::date) AND (transaction_date <= '2020-06-28'::date))
  • Filter: ((retail_iwg_pack_status_to)::text = ANY ('{Settled,"Retailer Stolen Settled","Returned - F"}'::text[]))
  • Rows Removed by Filter: 8,218,042
22. 0.026 0.118 ↑ 1.6 223 1

Hash (cost=23.66..23.66 rows=366 width=17) (actual time=0.118..0.118 rows=223 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 21kB
23. 0.092 0.092 ↑ 1.6 223 1

Seq Scan on dim_game (cost=0.00..23.66 rows=366 width=17) (actual time=0.009..0.092 rows=223 loops=1)

Planning time : 0.665 ms
Execution time : 46,016.069 ms