explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Paoc : rep_spread_capture_v_old

Settings
# exclusive inclusive rows x rows loops node
1. 4,860.481 140,579.949 ↑ 6,816.7 11,974,095 1

Append (cost=6,267,206.37..3,697,400,470.41 rows=81,623,346,647 width=536) (actual time=80,743.971..140,579.949 rows=11,974,095 loops=1)

  • Functions: 138
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 28.289 ms, Inlining 161.381 ms, Optimization 3109.133 ms, Emission 2273.317 ms, Total 5572.120 ms
2.          

CTE q1

3. 9,933.507 9,933.507 ↑ 1.0 11,771,378 1

Seq Scan on src_spread_capture_to_dlk (cost=0.00..445,755.24 rows=11,776,847 width=213) (actual time=2.302..9,933.507 rows=11,771,378 loops=1)

  • Filter: (instrument ~ '[0-9]'::text)
  • Rows Removed by Filter: 8,145
4. 6,364.008 123,138.033 ↑ 6,819.3 11,765,967 1

Subquery Scan on *SELECT* 1 (cost=5,821,451.13..3,215,297,929.84 rows=80,235,835,264 width=479) (actual time=80,743.969..123,138.033 rows=11,765,967 loops=1)

5. 19,029.532 116,774.025 ↑ 6,819.3 11,765,967 1

Merge Join (cost=5,821,451.13..2,212,349,989.04 rows=80,235,835,264 width=503) (actual time=80,743.960..116,774.025 rows=11,765,967 loops=1)

  • Merge Cond: (((a.id)::text) = q1.account_id)
6. 7,681.285 11,796.864 ↑ 1.0 1,358,133 1

Sort (cost=231,935.45..235,341.96 rows=1,362,603 width=23) (actual time=9,480.596..11,796.864 rows=1,358,133 loops=1)

  • Sort Key: ((a.id)::text)
  • Sort Method: external merge Disk: 62,168kB
7. 4,115.579 4,115.579 ↑ 1.0 1,358,222 1

Seq Scan on as_account a (cost=0.00..76,731.03 rows=1,362,603 width=23) (actual time=2,886.187..4,115.579 rows=1,358,222 loops=1)

8. 7,774.823 85,947.629 ↑ 1.0 11,765,967 1

Materialize (cost=5,589,515.68..5,648,399.92 rows=11,776,847 width=407) (actual time=71,262.335..85,947.629 rows=11,765,967 loops=1)

9. 33,792.677 78,172.806 ↑ 1.0 11,765,967 1

Sort (cost=5,589,515.68..5,618,957.80 rows=11,776,847 width=407) (actual time=71,262.331..78,172.806 rows=11,765,967 loops=1)

  • Sort Key: q1.account_id
  • Sort Method: external merge Disk: 1,659,440kB
10. 8,577.665 44,380.129 ↑ 1.0 11,765,967 1

Hash Join (cost=96,795.13..1,660,072.89 rows=11,776,847 width=407) (actual time=2,377.820..44,380.129 rows=11,765,967 loops=1)

  • Hash Cond: ((q1.instrument)::bigint = i.id)
11. 11,473.844 35,057.378 ↑ 1.0 11,765,967 1

Hash Join (cost=86,247.29..1,615,655.87 rows=11,776,847 width=380) (actual time=1,632.675..35,057.378 rows=11,765,967 loops=1)

  • Hash Cond: (q1.user_id = u.user_id)
12. 21,954.379 21,954.379 ↑ 1.0 11,771,378 1

CTE Scan on q1 (cost=0.00..235,536.94 rows=11,776,847 width=368) (actual time=2.309..21,954.379 rows=11,771,378 loops=1)

13. 307.871 1,629.155 ↑ 1.0 828,967 1

Hash (cost=71,026.35..71,026.35 rows=829,035 width=20) (actual time=1,629.155..1,629.155 rows=828,967 loops=1)

  • Buckets: 131,072 Batches: 8 Memory Usage: 6,662kB
14. 1,321.284 1,321.284 ↑ 1.0 828,967 1

Seq Scan on etl_tbl_ums_users u (cost=0.00..71,026.35 rows=829,035 width=20) (actual time=0.687..1,321.284 rows=828,967 loops=1)

15. 4.234 745.086 ↑ 1.9 7,557 1

Hash (cost=10,364.53..10,364.53 rows=14,665 width=35) (actual time=745.086..745.086 rows=7,557 loops=1)

  • Buckets: 16,384 Batches: 1 Memory Usage: 654kB
16. 740.852 740.852 ↑ 1.9 7,557 1

Index Scan using ts_instrument__id__idx on ts_instrument i (cost=0.29..10,364.53 rows=14,665 width=35) (actual time=0.014..740.852 rows=7,557 loops=1)

17. 846.251 12,581.435 ↑ 6,666.6 208,128 1

Merge Join (cost=689,243.14..59,664,938.26 rows=1,387,511,383 width=452) (actual time=11,201.817..12,581.435 rows=208,128 loops=1)

  • Merge Cond: (((fct_transactions.user_account_id)::text) = ((a_1.id)::text))
18. 431.352 3,070.106 ↓ 1.0 208,128 1

Sort (cost=457,307.69..457,816.83 rows=203,656 width=84) (actual time=2,930.062..3,070.106 rows=208,128 loops=1)

  • Sort Key: ((fct_transactions.user_account_id)::text)
  • Sort Method: external merge Disk: 27,424kB
19. 110.420 2,638.754 ↓ 1.0 208,128 1

Gather (cost=83,900.66..433,640.28 rows=203,656 width=84) (actual time=2,485.071..2,638.754 rows=208,128 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
20. 116.572 2,528.334 ↑ 1.2 69,376 3 / 3

Parallel Hash Join (cost=82,900.66..412,274.68 rows=84,857 width=84) (actual time=2,463.648..2,528.334 rows=69,376 loops=3)

  • Hash Cond: (fct_transactions.user_id = u_1.user_id)
21. 51.671 1,070.702 ↑ 1.2 69,376 3 / 3

Parallel Hash Join (cost=10,367.46..335,054.70 rows=84,857 width=72) (actual time=810.761..1,070.702 rows=69,376 loops=3)

  • Hash Cond: (fct_transactions.details_instrument_id = i_1.id)
22. 1,004.944 1,004.944 ↑ 1.2 70,077 3 / 3

Parallel Seq Scan on etl_fct_transactions fct_transactions (cost=0.00..324,458.30 rows=87,188 width=73) (actual time=796.591..1,004.944 rows=70,077 loops=3)

  • Filter: ((transaction_type <> 'DEMO_TRANSFER'::text) AND (transaction_type = ANY ('{TRADE_COMMISSION,EXCHANGE_COMMISSION}'::text[])) AND (transaction_status = 'PROCESSED'::text))
  • Rows Removed by Filter: 1,928,035
23. 1.647 14.087 ↑ 2.4 2,519 3 / 3

Parallel Hash (cost=10,291.08..10,291.08 rows=6,110 width=35) (actual time=14.087..14.087 rows=2,519 loops=3)

  • Buckets: 16,384 Batches: 1 Memory Usage: 704kB
24. 12.440 12.440 ↑ 2.4 2,519 3 / 3

Parallel Index Scan using ts_instrument_pkey on ts_instrument i_1 (cost=0.29..10,291.08 rows=6,110 width=35) (actual time=0.671..12.440 rows=2,519 loops=3)

25. 261.444 1,341.060 ↑ 1.3 276,322 3 / 3

Parallel Hash (cost=66,190.31..66,190.31 rows=345,431 width=20) (actual time=1,341.060..1,341.060 rows=276,322 loops=3)

  • Buckets: 131,072 Batches: 8 Memory Usage: 6,784kB
26. 1,079.616 1,079.616 ↑ 1.3 276,322 3 / 3

Parallel Seq Scan on etl_tbl_ums_users u_1 (cost=0.00..66,190.31 rows=345,431 width=20) (actual time=886.905..1,079.616 rows=276,322 loops=3)

27. 563.669 8,665.078 ↓ 1.1 1,454,921 1

Materialize (cost=231,935.45..238,748.46 rows=1,362,603 width=23) (actual time=6,109.705..8,665.078 rows=1,454,921 loops=1)

28. 7,537.559 8,101.409 ↑ 1.1 1,249,730 1

Sort (cost=231,935.45..235,341.96 rows=1,362,603 width=23) (actual time=6,109.700..8,101.409 rows=1,249,730 loops=1)

  • Sort Key: ((a_1.id)::text)
  • Sort Method: external merge Disk: 62,168kB
29. 563.850 563.850 ↑ 1.0 1,358,222 1

Seq Scan on as_account a_1 (cost=0.00..76,731.03 rows=1,362,603 width=23) (actual time=0.017..563.850 rows=1,358,222 loops=1)

Execution time : 143,940.445 ms