explain.depesz.com

PostgreSQL's explain analyze made readable

Result: nfky

Settings
# exclusive inclusive rows x rows loops node
1. 166.923 45,498.450 ↓ 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,397.471..45,498.450 rows=429,245 loops=1)

  • Hash Cond: (iwg.game_id = dim_game.game_id)
2. 350.076 45,331.398 ↓ 9.8 429,245 1

Merge Join (cost=6,737,648.03..6,784,416.10 rows=43,761 width=28) (actual time=44,397.307..45,331.398 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))))
3. 2,424.145 16,320.073 ↓ 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,690.767..16,320.073 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
4. 1,529.823 13,895.928 ↓ 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,444.255..13,895.928 rows=3,073,865 loops=1)

  • Group Key: (((in_iwg.game_id * in_iwg.retailer_id) * in_iwg.retail_iwg_pack_serial_number))
5. 9,123.612 12,366.105 ↓ 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,444.247..12,366.105 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
6. 3,242.493 3,242.493 ↓ 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.026..3,242.493 rows=10,615,341 loops=1)

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

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

8. 308.281 28,619.173 ↑ 2.5 448,301 1

Sort (cost=4,225,281.54..4,228,096.57 rows=1,126,011 width=36) (actual time=28,569.370..28,619.173 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
9. 5,575.738 28,310.892 ↑ 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,904.917..28,310.892 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
10.          

SubPlan (for Index Scan)

11. 383.494 22,735.154 ↓ 26.8 2,349,503 1

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

  • Group Key: (((iwg_win.game_id * iwg_win.retailer_id) * iwg_win.retail_iwg_pack_serial_number))
12. 866.620 22,351.660 ↓ 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,321.492..22,351.660 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))
13. 1,771.069 15,884.307 ↓ 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,521.826..15,884.307 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
14. 1,553.381 14,113.238 ↓ 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,575.487..14,113.238 rows=3,073,865 loops=1)

  • Group Key: (((iwg_win2.game_id * iwg_win2.retailer_id) * iwg_win2.retail_iwg_pack_serial_number))
15. 9,291.645 12,559.857 ↓ 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,575.478..12,559.857 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
16. 3,268.212 3,268.212 ↓ 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.028..3,268.212 rows=10,615,341 loops=1)

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

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

18. 2,130.422 5,374.979 ↓ 1.1 2,397,299 1

Sort (cost=878,207.94..883,851.32 rows=2,257,354 width=32) (actual time=4,799.648..5,374.979 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
19. 3,244.557 3,244.557 ↓ 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.050..3,244.557 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
20. 0.043 0.129 ↑ 1.6 223 1

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

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

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

Planning time : 0.623 ms
Execution time : 45,636.631 ms