explain.depesz.com

PostgreSQL's explain analyze made readable

Result: uYeA

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 275,013.342 ↓ 0.0 0 1

Hash Left Join (cost=35,046,098.17..38,917,883.92 rows=149 width=92) (actual time=275,013.342..275,013.342 rows=0 loops=1)

  • Hash Cond: (("substring"((a.pos_financial_data)::text, 7, 1) = n.code) AND (CASE WHEN (char_length((a.pos_authorisation_data)::text) < 5) THEN 'Unknown'::text WHEN ("substring"((a.pos_authorisation_data)::text, 5, 1) = '0'::text) THEN 'Present'::text ELSE 'Absent'::text END = n.card_present_flag))
2. 0.001 275,013.342 ↓ 0.0 0 1

Hash Left Join (cost=35,046,096.37..38,917,880.87 rows=149 width=131) (actual time=275,013.342..275,013.342 rows=0 loops=1)

  • Hash Cond: (upper((d.payment_token_wallet)::text) = m.enumeration)
3. 0.000 275,013.341 ↓ 0.0 0 1

Hash Left Join (cost=35,046,095.22..38,917,879.29 rows=149 width=133) (actual time=275,013.341..275,013.341 rows=0 loops=1)

  • Hash Cond: (upper((a.transaction_code)::text) = l.enumeration)
4. 0.000 275,013.341 ↓ 0.0 0 1

Hash Left Join (cost=35,046,093.86..38,917,877.07 rows=149 width=155) (actual time=275,013.341..275,013.341 rows=0 loops=1)

  • Hash Cond: (btrim(upper((c.product)::text)) = j.name)
5. 0.001 275,013.341 ↓ 0.0 0 1

Hash Left Join (cost=35,046,092.81..38,917,875.58 rows=149 width=162) (actual time=275,013.341..275,013.341 rows=0 loops=1)

  • Hash Cond: (btrim(upper((a.transaction_currency)::text)) = i.code)
6. 0.000 275,013.340 ↓ 0.0 0 1

Hash Left Join (cost=35,046,086.99..38,917,869.32 rows=149 width=164) (actual time=275,013.340..275,013.340 rows=0 loops=1)

  • Hash Cond: (btrim(upper((a.transaction_country)::text)) = h.code)
7. 0.001 275,013.340 ↓ 0.0 0 1

Nested Loop Left Join (cost=35,046,076.36..38,917,858.25 rows=149 width=165) (actual time=275,013.340..275,013.340 rows=0 loops=1)

8. 0.000 275,013.339 ↓ 0.0 0 1

Nested Loop (cost=35,046,075.94..38,916,814.96 rows=149 width=177) (actual time=275,013.339..275,013.339 rows=0 loops=1)

9. 0.025 275,013.339 ↓ 0.0 0 1

Hash Join (cost=35,046,075.64..38,915,575.66 rows=149 width=173) (actual time=275,013.339..275,013.339 rows=0 loops=1)

  • Hash Cond: ((timezone('Europe/London'::text, timezone('UTC'::text, a.transaction_time)))::date = e.date)
10. 0.001 275,011.665 ↓ 0.0 0 1

Nested Loop (cost=35,046,033.01..38,915,532.58 rows=149 width=169) (actual time=275,011.665..275,011.665 rows=0 loops=1)

11. 0.009 0.009 ↑ 1.0 1 1

Seq Scan on card_transaction_channel (cost=0.00..1.20 rows=1 width=2) (actual time=0.007..0.009 rows=1 loops=1)

  • Filter: (enumeration = 'UNKNOWN'::text)
  • Rows Removed by Filter: 15
12. 0.002 275,011.655 ↓ 0.0 0 1

Nested Loop (cost=35,046,033.01..38,915,529.89 rows=149 width=167) (actual time=275,011.655..275,011.655 rows=0 loops=1)

13. 0.002 0.039 ↑ 1.0 1 1

Nested Loop (cost=0.00..7.67 rows=1 width=14) (actual time=0.016..0.039 rows=1 loops=1)

14. 0.000 0.019 ↑ 1.0 1 1

Nested Loop (cost=0.00..3.53 rows=1 width=12) (actual time=0.012..0.019 rows=1 loops=1)

15. 0.002 0.012 ↑ 1.0 1 1

Nested Loop (cost=0.00..2.12 rows=1 width=8) (actual time=0.009..0.012 rows=1 loops=1)

16. 0.003 0.003 ↑ 1.0 1 1

Seq Scan on card_product (cost=0.00..1.02 rows=1 width=4) (actual time=0.002..0.003 rows=1 loops=1)

  • Filter: (name = 'Unknown'::text)
  • Rows Removed by Filter: 1
17. 0.007 0.007 ↑ 1.0 1 1

Seq Scan on mobile_wallet (cost=0.00..1.09 rows=1 width=4) (actual time=0.006..0.007 rows=1 loops=1)

  • Filter: (enumeration = 'NOT_APPLICABLE'::text)
  • Rows Removed by Filter: 6
18. 0.007 0.007 ↑ 1.0 1 1

Seq Scan on payment_method (cost=0.00..1.40 rows=1 width=4) (actual time=0.002..0.007 rows=1 loops=1)

  • Filter: (code = '??'::text)
  • Rows Removed by Filter: 31
19. 0.018 0.018 ↑ 1.0 1 1

Seq Scan on currency (cost=0.00..4.12 rows=1 width=2) (actual time=0.004..0.018 rows=1 loops=1)

  • Filter: (code = '???'::text)
  • Rows Removed by Filter: 169
20. 0.000 275,011.614 ↓ 0.0 0 1

Nested Loop (cost=35,046,033.01..38,915,520.73 rows=149 width=153) (actual time=275,011.614..275,011.614 rows=0 loops=1)

21. 0.001 275,011.614 ↓ 0.0 0 1

Nested Loop (cost=35,045,033.01..38,899,492.67 rows=1 width=149) (actual time=275,011.614..275,011.614 rows=0 loops=1)

22. 0.002 275,011.613 ↓ 0.0 0 1

Merge Right Join (cost=35,045,033.01..38,899,484.53 rows=1 width=147) (actual time=275,011.613..275,011.613 rows=0 loops=1)

  • Merge Cond: (p.card_transaction_id = a.card_transaction_id)
23. 0.005 275,011.595 ↑ 274,122.0 1 1

Nested Loop (cost=35,045,001.06..38,898,767.26 rows=274,122 width=36) (actual time=275,011.595..275,011.595 rows=1 loops=1)

24. 0.001 275,011.085 ↑ 277,808.0 1 1

Subquery Scan on p (cost=35,045,000.49..36,850,752.10 rows=277,808 width=36) (actual time=275,011.085..275,011.085 rows=1 loops=1)

  • Filter: (p.row_number = 1)
25. 0.005 275,011.084 ↑ 55,561,588.0 1 1

WindowAgg (cost=35,045,000.49..36,156,232.25 rows=55,561,588 width=44) (actual time=275,011.084..275,011.084 rows=1 loops=1)

26. 30,776.781 275,011.079 ↑ 55,561,588.0 1 1

Sort (cost=35,045,000.49..35,183,904.46 rows=55,561,588 width=36) (actual time=275,011.079..275,011.079 rows=1 loops=1)

  • Sort Key: o.card_transaction_id, o.transaction_time
  • Sort Method: external sort Disk: 2909720kB
27. 25,690.235 244,234.298 ↑ 1.0 55,069,027 1

WindowAgg (cost=22,367,557.95..23,339,885.74 rows=55,561,588 width=36) (actual time=212,031.489..244,234.298 rows=55,069,027 loops=1)

28. 46,491.759 218,544.063 ↑ 1.0 55,069,027 1

Sort (cost=22,367,557.95..22,506,461.92 rows=55,561,588 width=28) (actual time=212,031.479..218,544.063 rows=55,069,027 loops=1)

  • Sort Key: o.card_transaction_id
  • Sort Method: external merge Disk: 2263192kB
29. 172,052.304 172,052.304 ↑ 1.0 55,069,027 1

Seq Scan on card_transaction_detail o (cost=0.00..11,232,162.70 rows=55,561,588 width=28) (actual time=1.515..172,052.304 rows=55,069,027 loops=1)

  • Filter: ((transaction_type)::text = 'A'::text)
  • Rows Removed by Filter: 48464415
30. 0.505 0.505 ↑ 1.0 1 1

Index Scan using pk_card_authorisation on card_authorisation q (cost=0.56..7.37 rows=1 width=32) (actual time=0.505..0.505 rows=1 loops=1)

  • Index Cond: (uid = p.card_transaction_detail_uid)
31. 0.007 0.016 ↓ 0.0 0 1

Sort (cost=31.95..31.96 rows=1 width=119) (actual time=0.016..0.016 rows=0 loops=1)

  • Sort Key: a.card_transaction_id
  • Sort Method: quicksort Memory: 25kB
32. 0.001 0.009 ↓ 0.0 0 1

Nested Loop Left Join (cost=1.98..31.94 rows=1 width=119) (actual time=0.009..0.009 rows=0 loops=1)

33. 0.000 0.008 ↓ 0.0 0 1

Nested Loop (cost=1.56..23.50 rows=1 width=119) (actual time=0.008..0.008 rows=0 loops=1)

34. 0.001 0.008 ↓ 0.0 0 1

Nested Loop (cost=1.13..16.50 rows=1 width=92) (actual time=0.008..0.008 rows=0 loops=1)

35. 0.007 0.007 ↓ 0.0 0 1

Index Scan using pk_card_transaction_detail on card_transaction_detail a (cost=0.57..7.92 rows=1 width=88) (actual time=0.007..0.007 rows=0 loops=1)

  • Index Cond: (card_transaction_detail_id < 10000)
  • Filter: ((transaction_type)::text = 'P'::text)
36. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_card_transaction on card_transaction b (cost=0.56..8.58 rows=1 width=8) (never executed)

  • Index Cond: (card_transaction_id = a.card_transaction_id)
37. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_card on card c (cost=0.42..7.00 rows=1 width=31) (never executed)

  • Index Cond: (card_id = b.card_id)
38. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_card_in_wallet_card_id_payment_token_id on card_in_wallet d (cost=0.42..8.44 rows=1 width=16) (never executed)

  • Index Cond: ((b.card_id = card_id) AND ((a.payment_token_id)::text = (payment_token_id)::text))
39. 0.000 0.000 ↓ 0.0 0

Seq Scan on country (cost=0.00..8.12 rows=1 width=2) (never executed)

  • Filter: (code = '???'::text)
40. 0.000 0.000 ↓ 0.0 0

Gather (cost=1,000.00..16,026.57 rows=149 width=4) (never executed)

  • Workers Planned: 2
  • Workers Launched: 0
41. 0.000 0.000 ↓ 0.0 0

Parallel Seq Scan on account (cost=0.00..15,011.67 rows=62 width=4) (never executed)

  • Filter: (type = 'UNKNOWN'::text)
42. 0.158 1.649 ↑ 1.0 1,228 1

Hash (cost=27.28..27.28 rows=1,228 width=8) (actual time=1.649..1.649 rows=1,228 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 64kB
43. 1.491 1.491 ↑ 1.0 1,228 1

Seq Scan on date e (cost=0.00..27.28 rows=1,228 width=8) (actual time=0.694..1.491 rows=1,228 loops=1)

44. 0.000 0.000 ↓ 0.0 0

Index Scan using uk_time on "time" f (cost=0.30..8.32 rows=1 width=12) (never executed)

  • Index Cond: ("time" = (date_trunc('SECOND'::text, timezone('Europe/London'::text, a.transaction_time)))::time without time zone)
45. 0.000 0.000 ↓ 0.0 0

Index Scan using uk_account on account g (cost=0.42..7.00 rows=1 width=20) (never executed)

  • Index Cond: (c.account_uid = uid)
46. 0.000 0.000 ↓ 0.0 0

Hash (cost=7.50..7.50 rows=250 width=6) (never executed)

47. 0.000 0.000 ↓ 0.0 0

Seq Scan on country h (cost=0.00..7.50 rows=250 width=6) (never executed)

48. 0.000 0.000 ↓ 0.0 0

Hash (cost=3.70..3.70 rows=170 width=6) (never executed)

49. 0.000 0.000 ↓ 0.0 0

Seq Scan on currency i (cost=0.00..3.70 rows=170 width=6) (never executed)

50. 0.000 0.000 ↓ 0.0 0

Hash (cost=1.02..1.02 rows=2 width=10) (never executed)

51. 0.000 0.000 ↓ 0.0 0

Seq Scan on card_product j (cost=0.00..1.02 rows=2 width=10) (never executed)

52. 0.000 0.000 ↓ 0.0 0

Hash (cost=1.16..1.16 rows=16 width=18) (never executed)

53. 0.000 0.000 ↓ 0.0 0

Seq Scan on card_transaction_channel l (cost=0.00..1.16 rows=16 width=18) (never executed)

54. 0.000 0.000 ↓ 0.0 0

Hash (cost=1.07..1.07 rows=7 width=13) (never executed)

55. 0.000 0.000 ↓ 0.0 0

Seq Scan on mobile_wallet m (cost=0.00..1.07 rows=7 width=13) (never executed)

56. 0.000 0.000 ↓ 0.0 0

Hash (cost=1.32..1.32 rows=32 width=13) (never executed)

57. 0.000 0.000 ↓ 0.0 0

Seq Scan on payment_method n (cost=0.00..1.32 rows=32 width=13) (never executed)

Planning time : 53.408 ms
Execution time : 275,688.942 ms