explain.depesz.com

PostgreSQL's explain analyze made readable

Result: UTU5 : rep_spread_capture_v_new

Settings
# exclusive inclusive rows x rows loops node
1. 4,689.383 93,371.140 ↑ 1.0 11,974,095 1

Append (cost=673,701.31..4,255,652.43 rows=11,974,903 width=536) (actual time=5,284.019..93,371.140 rows=11,974,095 loops=1)

  • Functions: 150
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 33.796 ms, Inlining 130.559 ms, Optimization 3517.426 ms, Emission 2529.032 ms, Total 6210.813 ms
2.          

CTE q1

3. 14,349.057 14,349.057 ↑ 1.0 11,765,967 1

Seq Scan on src_spread_capture_to_dlk (cost=0.00..475,157.62 rows=11,771,247 width=213) (actual time=0.041..14,349.057 rows=11,765,967 loops=1)

  • Filter: ((instrument ~ '[0-9]'::text) AND (account_id ~ '[0-9]'::text))
  • Rows Removed by Filter: 13,556
4. 6,171.512 85,552.238 ↑ 1.0 11,765,967 1

Subquery Scan on *SELECT* 1 (cost=198,543.70..3,210,860.75 rows=11,771,247 width=479) (actual time=5,284.018..85,552.238 rows=11,765,967 loops=1)

5. 20,061.740 79,380.726 ↑ 1.0 11,765,967 1

Hash Join (cost=198,543.70..3,063,720.17 rows=11,771,247 width=503) (actual time=5,284.013..79,380.726 rows=11,765,967 loops=1)

  • Hash Cond: ((q1.instrument)::bigint = i.id)
6. 15,487.191 56,269.963 ↑ 1.0 11,765,967 1

Hash Join (cost=187,995.86..2,931,034.90 rows=11,771,247 width=395) (actual time=2,234.933..56,269.963 rows=11,765,967 loops=1)

  • Hash Cond: ((q1.account_id)::bigint = a.id)
7. 11,974.367 39,608.352 ↑ 1.0 11,765,967 1

Hash Join (cost=86,247.29..1,614,930.87 rows=11,771,247 width=380) (actual time=1,054.208..39,608.352 rows=11,765,967 loops=1)

  • Hash Cond: (q1.user_id = u.user_id)
8. 26,580.477 26,580.477 ↑ 1.0 11,765,967 1

CTE Scan on q1 (cost=0.00..235,424.94 rows=11,771,247 width=368) (actual time=0.048..26,580.477 rows=11,765,967 loops=1)

9. 316.263 1,053.508 ↑ 1.0 828,967 1

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

  • Buckets: 131,072 Batches: 8 Memory Usage: 6,662kB
10. 737.245 737.245 ↑ 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.016..737.245 rows=828,967 loops=1)

11. 485.011 1,174.420 ↑ 1.0 1,358,224 1

Hash (cost=76,731.03..76,731.03 rows=1,362,603 width=23) (actual time=1,174.420..1,174.420 rows=1,358,224 loops=1)

  • Buckets: 131,072 Batches: 16 Memory Usage: 5,325kB
12. 689.409 689.409 ↑ 1.0 1,358,224 1

Seq Scan on as_account a (cost=0.00..76,731.03 rows=1,362,603 width=23) (actual time=0.036..689.409 rows=1,358,224 loops=1)

13. 3.392 3,049.023 ↑ 1.9 7,557 1

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

  • Buckets: 16,384 Batches: 1 Memory Usage: 654kB
14. 3,045.631 3,045.631 ↑ 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=3,023.603..3,045.631 rows=7,557 loops=1)

15. 28.954 3,129.519 ↓ 1.0 208,128 1

Gather (cost=83,901.09..507,722.99 rows=203,656 width=452) (actual time=2,532.149..3,129.519 rows=208,128 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
16. 183.193 3,100.565 ↑ 1.2 69,376 3 / 3

Nested Loop (cost=82,901.09..486,357.39 rows=84,857 width=452) (actual time=2,509.692..3,100.565 rows=69,376 loops=3)

17. 112.022 2,570.492 ↑ 1.2 69,376 3 / 3

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

  • Hash Cond: (fct_transactions.user_id = u_1.user_id)
18. 49.363 834.602 ↑ 1.2 69,376 3 / 3

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

  • Hash Cond: (fct_transactions.details_instrument_id = i_1.id)
19. 779.627 779.627 ↑ 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=557.246..779.627 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
20. 1.500 5.612 ↑ 2.4 2,519 3 / 3

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

  • Buckets: 16,384 Batches: 1 Memory Usage: 704kB
21. 4.112 4.112 ↑ 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.511..4.112 rows=2,519 loops=3)

22. 379.108 1,623.868 ↑ 1.3 276,322 3 / 3

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

  • Buckets: 131,072 Batches: 8 Memory Usage: 6,784kB
23. 1,244.760 1,244.760 ↑ 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=1,052.537..1,244.760 rows=276,322 loops=3)

24. 346.880 346.880 ↑ 1.0 1 208,128 / 3

Index Scan using as_account_pkey on as_account a_1 (cost=0.43..0.86 rows=1 width=23) (actual time=0.005..0.005 rows=1 loops=208,128)

  • Index Cond: (id = fct_transactions.user_account_id)
Execution time : 95,936.842 ms