explain.depesz.com

PostgreSQL's explain analyze made readable

Result: kGTB

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 29,595.130 ↑ 1.0 1 1

Limit (cost=1,555,815.90..1,555,816.40 rows=1 width=4) (actual time=29,595.111..29,595.130 rows=1 loops=1)

  • Output: (CASE WHEN ((customer_balance.balance_rounded + ops_only.balance_rounded) <> total_issued.balance_rounded) THEN 1 ELSE 0 END)
  • Buffers: shared hit=1036521 dirtied=951
2.          

CTE customer_balance

3. 0.012 17,605.699 ↓ 1.9 13 1

Subquery Scan on a (cost=817,086.00..817,088.04 rows=7 width=64) (actual time=17,605.677..17,605.699 rows=13 loops=1)

  • Output: a.currency, CASE WHEN ((a.currency)::text = ANY ('{BTC,BCH}'::text[])) THEN round(a.balance, 8) WHEN ((a.currency)::text = 'ETH'::text) THEN round(a.balance, 18) WHEN ((a.currency)::text = 'XRP'::text) THEN round(a.balance, 6) ELSE round(a.balance, 2) END
  • Buffers: shared hit=511534 dirtied=841
4. 0.024 17,605.687 ↓ 1.9 13 1

Finalize GroupAggregate (cost=817,086.00..817,087.84 rows=7 width=72) (actual time=17,605.671..17,605.687 rows=13 loops=1)

  • Output: (CASE wallets_crypto_account.currency WHEN 'PBTC'::text THEN 'PHP'::character varying ELSE wallets_crypto_account.currency END), sum((wallets_crypto_account.balance)::numeric(38,18)), NULL::timestamp with time zone
  • Group Key: (CASE wallets_crypto_account.currency WHEN 'PBTC'::text THEN 'PHP'::character varying ELSE wallets_crypto_account.currency END)
  • Buffers: shared hit=511534 dirtied=841
5. 0.005 17,605.663 ↑ 1.1 13 1

Gather Merge (cost=817,086.00..817,087.63 rows=14 width=64) (actual time=17,605.660..17,605.663 rows=13 loops=1)

  • Output: (CASE wallets_crypto_account.currency WHEN 'PBTC'::text THEN 'PHP'::character varying ELSE wallets_crypto_account.currency END), (PARTIAL sum((wallets_crypto_account.balance)::numeric(38,18)))
  • Workers Planned: 2
  • Workers Launched: 0
  • Buffers: shared hit=511534 dirtied=841
6. 0.016 17,605.658 ↓ 1.9 13 1

Sort (cost=816,085.98..816,085.99 rows=7 width=64) (actual time=17,605.657..17,605.658 rows=13 loops=1)

  • Output: (CASE wallets_crypto_account.currency WHEN 'PBTC'::text THEN 'PHP'::character varying ELSE wallets_crypto_account.currency END), (PARTIAL sum((wallets_crypto_account.balance)::numeric(38,18)))
  • Sort Key: (CASE wallets_crypto_account.currency WHEN 'PBTC'::text THEN 'PHP'::character varying ELSE wallets_crypto_account.currency END)
  • Sort Method: quicksort Memory: 26kB
  • Buffers: shared hit=511534 dirtied=841
7. 4,946.475 17,605.642 ↓ 1.9 13 1

Partial HashAggregate (cost=816,085.77..816,085.88 rows=7 width=64) (actual time=17,605.635..17,605.642 rows=13 loops=1)

  • Output: (CASE wallets_crypto_account.currency WHEN 'PBTC'::text THEN 'PHP'::character varying ELSE wallets_crypto_account.currency END), PARTIAL sum((wallets_crypto_account.balance)::numeric(38,18))
  • Group Key: CASE wallets_crypto_account.currency WHEN 'PBTC'::text THEN 'PHP'::character varying ELSE wallets_crypto_account.currency END
  • Buffers: shared hit=511534 dirtied=841
8. 12,659.167 12,659.167 ↓ 2.4 21,896,114 1

Parallel Seq Scan on public.wallets_crypto_account (cost=0.00..748,408.08 rows=9,023,692 width=35) (actual time=0.020..12,659.167 rows=21,896,114 loops=1)

  • Output: CASE wallets_crypto_account.currency WHEN 'PBTC'::text THEN 'PHP'::character varying ELSE wallets_crypto_account.currency END, wallets_crypto_account.balance
  • Filter: ((wallets_crypto_account.owner_id)::text <> ALL ('{03a21b76f2d84d759e9993dd32b14c8c,8258d72ffc564b1890e578da4a8dbfd1,ee4f8c1d071348afb69f8effa79a5d88,e7c416599a0942c4b81ec866225f1f91,afd0f1e6515f4327a5d467473b211ec9,9c6c8e136d064b3a82fe4240feca94f4,cad46ddfe4774f1ca1c4b66982feb48d,f238f9a2c5794810a342fb180182f966,1de62ebe89a2474d8f368825098eb0f8,6f953f691f0442f181835b6f57c02a3b,cae873ba34614dd2b01421fd526d4101}'::text[]))
  • Rows Removed by Filter: 119
  • Buffers: shared hit=511534 dirtied=841
9.          

CTE ops_only

10. 0.011 20.597 ↓ 1.9 13 1

Subquery Scan on a_1 (cost=595.67..597.35 rows=7 width=64) (actual time=20.561..20.597 rows=13 loops=1)

  • Output: a_1.currency, CASE WHEN ((a_1.currency)::text = ANY ('{BTC,BCH}'::text[])) THEN round(a_1.balance, 8) WHEN ((a_1.currency)::text = 'ETH'::text) THEN round(a_1.balance, 18) WHEN ((a_1.currency)::text = 'XRP'::text) THEN round(a_1.balance, 6) ELSE round(a_1.balance, 2) END
  • Buffers: shared hit=13453 dirtied=110
11. 0.034 20.586 ↓ 1.9 13 1

GroupAggregate (cost=595.67..597.16 rows=7 width=72) (actual time=20.557..20.586 rows=13 loops=1)

  • Output: (CASE wallets_crypto_account_1.currency WHEN 'PBTC'::text THEN 'PHP'::character varying ELSE wallets_crypto_account_1.currency END), sum((wallets_crypto_account_1.balance)::numeric(38,18)), NULL::timestamp with time zone
  • Group Key: (CASE wallets_crypto_account_1.currency WHEN 'PBTC'::text THEN 'PHP'::character varying ELSE wallets_crypto_account_1.currency END)
  • Buffers: shared hit=13453 dirtied=110
12. 0.062 20.552 ↑ 1.5 92 1

Sort (cost=595.67..596.01 rows=139 width=35) (actual time=20.547..20.552 rows=92 loops=1)

  • Output: (CASE wallets_crypto_account_1.currency WHEN 'PBTC'::text THEN 'PHP'::character varying ELSE wallets_crypto_account_1.currency END), wallets_crypto_account_1.balance
  • Sort Key: (CASE wallets_crypto_account_1.currency WHEN 'PBTC'::text THEN 'PHP'::character varying ELSE wallets_crypto_account_1.currency END)
  • Sort Method: quicksort Memory: 29kB
  • Buffers: shared hit=13453 dirtied=110
13. 20.490 20.490 ↑ 1.5 92 1

Index Scan using periscope_wallets_crypto_account_owner_id_idx on public.wallets_crypto_account wallets_crypto_account_1 (cost=0.56..590.72 rows=139 width=35) (actual time=0.062..20.490 rows=92 loops=1)

  • Output: CASE wallets_crypto_account_1.currency WHEN 'PBTC'::text THEN 'PHP'::character varying ELSE wallets_crypto_account_1.currency END, wallets_crypto_account_1.balance
  • Index Cond: ((wallets_crypto_account_1.owner_id)::text = ANY ('{03a21b76f2d84d759e9993dd32b14c8c,9c6c8e136d064b3a82fe4240feca94f4,cad46ddfe4774f1ca1c4b66982feb48d,f238f9a2c5794810a342fb180182f966,1de62ebe89a2474d8f368825098eb0f8,6f953f691f0442f181835b6f57c02a3b,cae873ba34614dd2b01421fd526d4101}'::text[]))
  • Buffers: shared hit=13453 dirtied=110
14.          

CTE total_issued

15. 0.009 11,968.799 ↓ 1.9 13 1

Subquery Scan on a_2 (cost=738,128.48..738,130.51 rows=7 width=64) (actual time=11,968.781..11,968.799 rows=13 loops=1)

  • Output: a_2.currency, CASE WHEN ((a_2.currency)::text = ANY ('{BTC,BCH}'::text[])) THEN round(a_2.balance, 8) WHEN ((a_2.currency)::text = 'ETH'::text) THEN round(a_2.balance, 18) WHEN ((a_2.currency)::text = 'XRP'::text) THEN round(a_2.balance, 6) ELSE round(a_2.balance, 2) END
  • Buffers: shared hit=511534
16. 0.020 11,968.790 ↓ 1.9 13 1

Finalize GroupAggregate (cost=738,128.48..738,130.32 rows=7 width=72) (actual time=11,968.777..11,968.790 rows=13 loops=1)

  • Output: (CASE wallets_crypto_account_2.currency WHEN 'PBTC'::text THEN 'PHP'::character varying ELSE wallets_crypto_account_2.currency END), sum((wallets_crypto_account_2.balance)::numeric(38,18)), NULL::timestamp with time zone
  • Group Key: (CASE wallets_crypto_account_2.currency WHEN 'PBTC'::text THEN 'PHP'::character varying ELSE wallets_crypto_account_2.currency END)
  • Buffers: shared hit=511534
17. 0.004 11,968.770 ↑ 1.1 13 1

Gather Merge (cost=738,128.48..738,130.11 rows=14 width=64) (actual time=11,968.767..11,968.770 rows=13 loops=1)

  • Output: (CASE wallets_crypto_account_2.currency WHEN 'PBTC'::text THEN 'PHP'::character varying ELSE wallets_crypto_account_2.currency END), (PARTIAL sum((wallets_crypto_account_2.balance)::numeric(38,18)))
  • Workers Planned: 2
  • Workers Launched: 0
  • Buffers: shared hit=511534
18. 0.019 11,968.766 ↓ 1.9 13 1

Sort (cost=737,128.45..737,128.47 rows=7 width=64) (actual time=11,968.765..11,968.766 rows=13 loops=1)

  • Output: (CASE wallets_crypto_account_2.currency WHEN 'PBTC'::text THEN 'PHP'::character varying ELSE wallets_crypto_account_2.currency END), (PARTIAL sum((wallets_crypto_account_2.balance)::numeric(38,18)))
  • Sort Key: (CASE wallets_crypto_account_2.currency WHEN 'PBTC'::text THEN 'PHP'::character varying ELSE wallets_crypto_account_2.currency END)
  • Sort Method: quicksort Memory: 26kB
  • Buffers: shared hit=511534
19. 5,015.106 11,968.747 ↓ 1.9 13 1

Partial HashAggregate (cost=737,128.25..737,128.35 rows=7 width=64) (actual time=11,968.740..11,968.747 rows=13 loops=1)

  • Output: (CASE wallets_crypto_account_2.currency WHEN 'PBTC'::text THEN 'PHP'::character varying ELSE wallets_crypto_account_2.currency END), PARTIAL sum((wallets_crypto_account_2.balance)::numeric(38,18))
  • Group Key: CASE wallets_crypto_account_2.currency WHEN 'PBTC'::text THEN 'PHP'::character varying ELSE wallets_crypto_account_2.currency END
  • Buffers: shared hit=511534
20. 6,953.641 6,953.641 ↓ 2.4 21,896,206 1

Parallel Seq Scan on public.wallets_crypto_account wallets_crypto_account_2 (cost=0.00..669,450.12 rows=9,023,750 width=35) (actual time=0.009..6,953.641 rows=21,896,206 loops=1)

  • Output: CASE wallets_crypto_account_2.currency WHEN 'PBTC'::text THEN 'PHP'::character varying ELSE wallets_crypto_account_2.currency END, wallets_crypto_account_2.balance
  • Filter: ((wallets_crypto_account_2.owner_id)::text <> ALL ('{8258d72ffc564b1890e578da4a8dbfd1,ee4f8c1d071348afb69f8effa79a5d88,e7c416599a0942c4b81ec866225f1f91,afd0f1e6515f4327a5d467473b211ec9}'::text[]))
  • Rows Removed by Filter: 27
  • Buffers: shared hit=511534
21. 0.009 29,595.128 ↑ 1.0 1 1

Nested Loop (cost=0.00..0.50 rows=1 width=4) (actual time=29,595.110..29,595.128 rows=1 loops=1)

  • Output: CASE WHEN ((customer_balance.balance_rounded + ops_only.balance_rounded) <> total_issued.balance_rounded) THEN 1 ELSE 0 END
  • Buffers: shared hit=1036521 dirtied=951
22. 0.004 17,626.314 ↑ 1.0 1 1

Nested Loop (cost=0.00..0.33 rows=1 width=96) (actual time=17,626.299..17,626.314 rows=1 loops=1)

  • Output: customer_balance.balance_rounded, customer_balance.currency, ops_only.balance_rounded
  • Buffers: shared hit=524987 dirtied=951
23. 17,605.706 17,605.706 ↑ 1.0 1 1

CTE Scan on customer_balance (cost=0.00..0.16 rows=1 width=64) (actual time=17,605.700..17,605.706 rows=1 loops=1)

  • Output: customer_balance.currency, customer_balance.balance_rounded
  • Filter: ((customer_balance.currency)::text = 'VND'::text)
  • Rows Removed by Filter: 12
  • Buffers: shared hit=511534 dirtied=841
24. 20.604 20.604 ↑ 1.0 1 1

CTE Scan on ops_only (cost=0.00..0.16 rows=1 width=64) (actual time=20.595..20.604 rows=1 loops=1)

  • Output: ops_only.currency, ops_only.balance_rounded
  • Filter: ((ops_only.currency)::text = 'VND'::text)
  • Rows Removed by Filter: 12
  • Buffers: shared hit=13453 dirtied=110
25. 11,968.805 11,968.805 ↑ 1.0 1 1

CTE Scan on total_issued (cost=0.00..0.16 rows=1 width=64) (actual time=11,968.803..11,968.805 rows=1 loops=1)

  • Output: total_issued.currency, total_issued.balance_rounded
  • Filter: ((total_issued.currency)::text = 'VND'::text)
  • Rows Removed by Filter: 12
  • Buffers: shared hit=511534