explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Zy6e

Settings
# exclusive inclusive rows x rows loops node
1. 403,557.616 12,507,729.618 ↑ 6.7 143,101,652 1

GroupAggregate (cost=503,245,097.11..570,433,972.71 rows=959,841,080 width=390) (actual time=11,902,648.908..12,507,729.618 rows=143,101,652 loops=1)

  • Group Key: t.id, m.sign_up_time, (max(t_1.server_time_created_at)), m.country_id, m.primary_user_id, (CASE WHEN (((t.tx_result)::text = '20'::text) AND (t.current_status_id = 8)) THEN 1 ELSE 0 END), (CASE WHEN ((t.tx_result)::text = '11'::text) THEN 1 ELSE 0 END), (CASE WHEN (((t.tx_result)::text = '20'::text) AND (t.current_status_id = 9)) THEN 1 ELSE 0 END), (CASE WHEN ((t.tx_result)::text = '20'::text) THEN 1 ELSE 0 END), m.merchant_category_id
  • = '11'::text) THEN 1 ELSE 0 END), (CASE WHEN (((t.tx_result)::text = '20'::text) AND (t.current_status_id = 9)) THEN 1 ELSE 0 END), (CASE WHEN ((t.tx_result)::text = '20'::text) THEN 1 ELSE 0 END), m.merchant_category_id
2. 994,402.510 12,104,172.002 ↑ 1.3 757,146,132 1

Sort (cost=503,245,097.11..505,644,699.81 rows=959,841,080 width=365) (actual time=11,902,648.865..12,104,172.002 rows=757,146,132 loops=1)

  • Sort Key: t.id, m.sign_up_time, (max(t_1.server_time_created_at)), m.country_id, m.primary_user_id, (CASE WHEN (((t.tx_result)::text = '20'::text) AND (t.current_status_id = 8)) THEN 1 ELSE 0 END), (CASE WHEN ((t.tx_result)::text
  • Sort Method: external merge Disk: 93,510,528kB
3. 364,237.251 11,109,769.492 ↑ 1.3 757,146,132 1

Hash Join (cost=105,654,882.64..199,290,713.03 rows=959,841,080 width=365) (actual time=10,378,807.292..11,109,769.492 rows=757,146,132 loops=1)

  • Hash Cond: (m.id = uam.id)
4. 0.000 10,745,504.227 ↓ 23.8 1,346,222,521 1

Gather (cost=105,651,228.48..184,740,853.62 rows=56,605,323 width=360) (actual time=10,378,779.057..10,745,504.227 rows=1,346,222,521 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
5. 505,518.896 10,789,847.040 ↓ 19.0 448,740,840 3 / 3

Parallel Hash Left Join (cost=105,650,228.48..179,079,321.32 rows=23,585,551 width=360) (actual time=10,377,885.527..10,789,847.040 rows=448,740,840 loops=3)

  • Hash Cond: (tw.id = tr.transaction_event_id)
6. 64,363.490 9,587,802.263 ↓ 20.8 96,559,957 3 / 3

Hash Left Join (cost=574,705.56..58,215,890.83 rows=4,632,233 width=360) (actual time=17,324.869..9,587,802.263 rows=96,559,957 loops=3)

  • Hash Cond: (((date_trunc('day'::text, t.server_time_created_at))::date = fx_rates.date) AND ((t.currency)::text = (fx_rates.currency)::text))
7. 47,849.438 9,523,429.528 ↓ 20.8 96,559,957 3 / 3

Nested Loop Left Join (cost=573,847.99..58,189,554.51 rows=4,632,233 width=354) (actual time=17,315.408..9,523,429.528 rows=96,559,957 loops=3)

8. 44,742.708 6,339,835.654 ↓ 20.5 95,022,559 3 / 3

Nested Loop (cost=573,847.41..46,572,481.83 rows=4,632,233 width=350) (actual time=17,314.495..6,339,835.654 rows=95,022,559 loops=3)

9. 595.976 20,253.613 ↓ 2.5 115,135 3 / 3

Merge Join (cost=573,846.83..590,740.55 rows=46,137 width=36) (actual time=17,313.902..20,253.613 rows=115,135 loops=3)

  • Merge Cond: (m.id = t_1.merchant_id)
10. 2,810.992 3,315.291 ↑ 1.3 2,535,149 3 / 3

Sort (cost=515,977.19..523,916.54 rows=3,175,741 width=24) (actual time=1,444.754..3,315.291 rows=2,535,149 loops=3)

  • Sort Key: m.id
  • Sort Method: quicksort Memory: 296,538kB
  • Worker 0: Sort Method: quicksort Memory: 298,791kB
  • Worker 1: Sort Method: quicksort Memory: 294,523kB
11. 504.299 504.299 ↑ 1.3 2,538,399 3 / 3

Parallel Seq Scan on merchants m (cost=0.00..173,018.41 rows=3,175,741 width=24) (actual time=0.022..504.299 rows=2,538,399 loops=3)

12. 583.904 16,342.346 ↓ 3.1 345,404 3 / 3

Sort (cost=57,869.64..58,146.46 rows=110,728 width=12) (actual time=15,869.052..16,342.346 rows=345,404 loops=3)

  • Sort Key: t_1.merchant_id
  • Sort Method: quicksort Memory: 28,479kB
  • Worker 0: Sort Method: quicksort Memory: 28,479kB
  • Worker 1: Sort Method: quicksort Memory: 28,479kB
13. 860.166 15,758.442 ↓ 3.1 345,404 3 / 3

HashAggregate (cost=46,377.93..47,485.21 rows=110,728 width=12) (actual time=15,667.045..15,758.442 rows=345,404 loops=3)

  • Group Key: t_1.merchant_id
14. 14,898.276 14,898.276 ↓ 10.2 1,291,950 3 / 3

Index Scan using transactions_server_time_created_at_idx on transactions t_1 (cost=0.58..45,742.37 rows=127,112 width=12) (actual time=1.935..14,898.276 rows=1,291,950 loops=3)

  • Index Cond: ((server_time_created_at >= (CURRENT_DATE - '2 days'::interval)) AND (server_time_created_at < (CURRENT_DATE - '1 day'::interval)))
15. 6,274,839.333 6,274,839.333 ↑ 2.1 825 345,404 / 3

Index Scan using transactions_merchant_id_server_time_created_at_idx on transactions t (cost=0.58..979.61 rows=1,702 width=314) (actual time=0.177..54.500 rows=825 loops=345,404)

  • Index Cond: ((merchant_id = m.id) AND (server_time_created_at < (CURRENT_DATE - '1 day'::interval)))
16. 3,135,744.436 3,135,744.436 ↑ 11.0 1 285,067,676 / 3

Index Scan using transaction_events_transaction_id_idx on transaction_events tw (cost=0.57..2.40 rows=11 width=8) (actual time=0.032..0.033 rows=1 loops=285,067,676)

  • Index Cond: (transaction_id = t.id)
  • Filter: (created_at < (CURRENT_DATE - '1 day'::interval))
  • Rows Removed by Filter: 0
17. 4.778 9.245 ↓ 1.2 31,411 3 / 3

Hash (cost=466.63..466.63 rows=26,063 width=14) (actual time=9.243..9.245 rows=31,411 loops=3)

  • Buckets: 32,768 Batches: 1 Memory Usage: 1,687kB
18. 4.467 4.467 ↓ 1.2 31,411 3 / 3

Seq Scan on currency_exchange_rates fx_rates (cost=0.00..466.63 rows=26,063 width=14) (actual time=0.081..4.467 rows=31,411 loops=3)

19. 205,829.420 696,525.881 ↑ 1.2 1,258,419,211 3 / 3

Parallel Hash (cost=79,281,458.97..79,281,458.97 rows=1,572,209,596 width=8) (actual time=696,525.880..696,525.881 rows=1,258,419,211 loops=3)

  • Buckets: 33,554,432 Batches: 256 Memory Usage: 840,000kB
20. 490,696.461 490,696.461 ↑ 1.2 1,258,419,211 3 / 3

Parallel Seq Scan on transaction_event_states tr (cost=0.00..79,281,458.97 rows=1,572,209,596 width=8) (actual time=0.853..490,696.461 rows=1,258,419,211 loops=3)

  • Filter: (created_at < (CURRENT_DATE - '1 day'::interval))
  • Rows Removed by Filter: 5,689,127
21. 16.788 28.014 ↑ 1.0 135,696 1

Hash (cost=1,957.96..1,957.96 rows=135,696 width=4) (actual time=28.013..28.014 rows=135,696 loops=1)

  • Buckets: 262,144 Batches: 1 Memory Usage: 6,819kB
22. 11.226 11.226 ↑ 1.0 135,696 1

Seq Scan on temp_frd_prediction_day_automatic uam (cost=0.00..1,957.96 rows=135,696 width=4) (actual time=0.015..11.226 rows=135,696 loops=1)

Planning time : 15.688 ms
Execution time : 12,613,481.872 ms