explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1DQE : StandardShareClassPerformance_10s_QA

Settings
# exclusive inclusive rows x rows loops node
1. 0.017 10,320.028 ↓ 2.0 2 1

Sort (cost=51,931.32..51,931.32 rows=1 width=2,690) (actual time=10,320.028..10,320.028 rows=2 loops=1)

  • Sort Key: ab.entity_sequence, ab.benchmark_sequence
  • Sort Method: quicksort Memory: 26kB
  • Buffers: shared hit=1,573 read=9,923
2.          

CTE super

3. 0.014 10,306.826 ↑ 1.0 1 1

Nested Loop (cost=0.71..51,329.03 rows=1 width=169) (actual time=9,351.393..10,306.826 rows=1 loops=1)

  • Buffers: shared hit=31 read=9,914
4. 10,306.787 10,306.787 ↑ 1.0 1 1

Index Scan using entitysuperset_pkey on entitysuperset es (cost=0.43..51,320.72 rows=1 width=76) (actual time=9,351.357..10,306.787 rows=1 loops=1)

  • Index Cond: (((masterentityid)::text = '09TB0TLVXXSCETF'::text) AND ((entitytype)::text = 'SHARECLASS'::text))
  • Filter: (('2020-06-30'::date >= _fromdate) AND ('2020-06-30'::date <= _todate))
  • Rows Removed by Filter: 6
  • Buffers: shared hit=28 read=9,914
5. 0.025 0.025 ↑ 1.0 1 1

Index Scan using ix_122966_prf_sourceview on prf_sourceview s (cost=0.28..8.30 rows=1 width=26) (actual time=0.024..0.025 rows=1 loops=1)

  • Index Cond: ((masterentityid)::text = '09TB0TLVXXSCETF'::text)
  • Buffers: shared hit=3
6.          

CTE pref

7. 0.010 10,309.423 ↑ 3.5 2 1

Nested Loop (cost=4.48..31.13 rows=7 width=1,057) (actual time=9,353.987..10,309.423 rows=2 loops=1)

  • Buffers: shared hit=41 read=9,917
8. 10,306.829 10,306.829 ↑ 1.0 1 1

CTE Scan on super s_1 (cost=0.00..0.02 rows=1 width=1,032) (actual time=9,351.396..10,306.829 rows=1 loops=1)

  • Buffers: shared hit=31 read=9,914
9. 0.015 2.584 ↑ 3.5 2 1

Bitmap Heap Scan on prf_performanceview p (cost=4.48..30.94 rows=7 width=32) (actual time=2.582..2.584 rows=2 loops=1)

  • Recheck Cond: (((masterentityid)::text = (s_1.masterentityid)::text) AND ((context)::text = 'STANDARD'::text))
  • Heap Blocks: exact=10
  • Buffers: shared hit=10 read=3
10. 2.569 2.569 ↓ 1.4 10 1

Bitmap Index Scan on prf_performanceview_pkey (cost=0.00..4.48 rows=7 width=0) (actual time=2.569..2.569 rows=10 loops=1)

  • Index Cond: (((masterentityid)::text = (s_1.masterentityid)::text) AND ((context)::text = 'STANDARD'::text))
  • Buffers: shared read=3
11.          

CTE sources

12. 0.010 10,309.474 ↑ 4.0 2 1

HashAggregate (cost=52.98..53.06 rows=8 width=1,592) (actual time=10,309.471..10,309.474 rows=2 loops=1)

  • Group Key: pref_2.performancemasterentityid, pref_2.entity_sequence, pref_2.currency, pref_2.performancetype, pref_2.grossnet, pref_2.benchmark_sequence, pref_2.benchmarkordinal
  • Buffers: shared hit=41 read=9,917
13. 0.003 10,309.464 ↑ 4.0 2 1

Append (cost=0.02..52.84 rows=8 width=1,592) (actual time=9,353.993..10,309.464 rows=2 loops=1)

  • Buffers: shared hit=41 read=9,917
14. 0.004 10,309.431 ↑ 3.5 2 1

Result (cost=0.02..0.16 rows=7 width=2,076) (actual time=9,353.992..10,309.431 rows=2 loops=1)

  • One-Time Filter: $4
  • Buffers: shared hit=41 read=9,917
15.          

Initplan (for Result)

16. 9,353.989 9,353.989 ↑ 7.0 1 1

CTE Scan on pref pref_1 (cost=0.00..0.14 rows=7 width=0) (actual time=9,353.989..9,353.989 rows=1 loops=1)

  • Buffers: shared hit=36 read=8,988
17. 955.438 955.438 ↑ 3.5 2 1

CTE Scan on pref pref_2 (cost=0.02..0.16 rows=7 width=2,076) (actual time=0.001..955.438 rows=2 loops=1)

  • Buffers: shared hit=5 read=929
18. 0.000 0.030 ↓ 0.0 0 1

Group (cost=52.58..52.60 rows=1 width=70) (actual time=0.030..0.030 rows=0 loops=1)

  • Group Key: abr.masterentityid, perf_1.currencycode, perf_1.performancetype, abr.benchmarkordinalnumber
19.          

Initplan (for Group)

20. 0.002 0.002 ↑ 7.0 1 1

CTE Scan on pref (cost=0.00..0.14 rows=7 width=0) (actual time=0.002..0.002 rows=1 loops=1)

21. 0.025 0.029 ↓ 0.0 0 1

Sort (cost=52.56..52.57 rows=1 width=30) (actual time=0.029..0.029 rows=0 loops=1)

  • Sort Key: abr.masterentityid, perf_1.currencycode, perf_1.performancetype, abr.benchmarkordinalnumber
  • Sort Method: quicksort Memory: 25kB
22. 0.004 0.004 ↓ 0.0 0 1

Result (cost=5.01..52.55 rows=1 width=30) (actual time=0.004..0.004 rows=0 loops=1)

  • One-Time Filter: (NOT $3)
23. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=5.01..52.55 rows=1 width=30) (never executed)

  • Join Filter: ((a.performancemasterentityid)::text = (perf_1.masterentityid)::text)
24. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=4.45..19.75 rows=4 width=544) (never executed)

25. 0.000 0.000 ↓ 0.0 0

CTE Scan on super a (cost=0.00..0.02 rows=1 width=516) (never executed)

26. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on accountbenchmarkrelationship abr (cost=4.45..19.69 rows=4 width=28) (never executed)

  • Recheck Cond: (((masterentityid)::text = (a.performancemasterentityid)::text) AND ((segmentcode)::text = 'TF'::text))
27. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on accountbenchmarkrelationship_pkey (cost=0.00..4.45 rows=4 width=0) (never executed)

  • Index Cond: (((masterentityid)::text = (a.performancemasterentityid)::text) AND ((segmentcode)::text = 'TF'::text))
28. 0.000 0.000 ↓ 0.0 0

Index Scan using performanceaccountbenchmark_pkey on performanceaccountbenchmark perf_1 (cost=0.56..8.19 rows=1 width=37) (never executed)

  • Index Cond: ((effectivedate = '2020-06-30'::date) AND ((masterentityid)::text = (abr.masterentityid)::text) AND ((benchmarkcode)::text = (abr.benchmarkid)::text))
  • Filter: (effectivedate >= COALESCE(performancerestructuredate, performancestartdate, '1900-01-01 00:00:00'::timestamp without time zone))
29.          

CTE accountbenchmark

30. 0.021 10,317.402 ↓ 2.0 2 1

Nested Loop Left Join (cost=0.86..500.90 rows=1 width=3,243) (actual time=10,313.332..10,317.402 rows=2 loops=1)

  • Buffers: shared hit=1,555 read=9,921
31. 0.014 10,312.089 ↓ 2.0 2 1

Nested Loop (cost=0.44..8.72 rows=1 width=3,237) (actual time=10,310.769..10,312.089 rows=2 loops=1)

  • Join Filter: ((a_1.performancemasterentityid)::text = (abr_1.masterentityid)::text)
  • Buffers: shared hit=47 read=9,919
32. 0.021 10,309.505 ↓ 2.0 2 1

Hash Join (cost=0.03..0.23 rows=1 width=3,744) (actual time=10,309.494..10,309.505 rows=2 loops=1)

  • Hash Cond: ((s_2.performancemasterentityid)::text = (a_1.performancemasterentityid)::text)
  • Buffers: shared hit=41 read=9,917
33. 10,309.480 10,309.480 ↑ 4.0 2 1

CTE Scan on sources s_2 (cost=0.00..0.16 rows=8 width=1,592) (actual time=10,309.473..10,309.480 rows=2 loops=1)

  • Buffers: shared hit=41 read=9,917
34. 0.003 0.004 ↑ 1.0 1 1

Hash (cost=0.02..0.02 rows=1 width=2,152) (actual time=0.004..0.004 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
35. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on super a_1 (cost=0.00..0.02 rows=1 width=2,152) (actual time=0.001..0.001 rows=1 loops=1)

36. 2.570 2.570 ↑ 1.0 1 2

Index Scan using accountbenchmarkrelationship_pkey on accountbenchmarkrelationship abr_1 (cost=0.41..8.47 rows=1 width=28) (actual time=1.284..1.285 rows=1 loops=2)

  • Index Cond: (((masterentityid)::text = (s_2.performancemasterentityid)::text) AND ((segmentcode)::text = 'TF'::text) AND (benchmarkordinalnumber = s_2.benchmarkordinal))
  • Buffers: shared hit=6 read=2
37. 5.292 5.292 ↑ 1.0 1 2

Index Scan using index_pkey on index i (cost=0.41..492.14 rows=1 width=61) (actual time=2.630..2.646 rows=1 loops=2)

  • Index Cond: (((abr_1.benchmarkid)::text = (indexcode)::text) AND (_seq = 1))
  • Filter: (_todate = '9999-12-31'::date)
  • Rows Removed by Filter: 6
  • Buffers: shared hit=1,508 read=2
38. 0.014 10,320.011 ↓ 2.0 2 1

Nested Loop (cost=0.98..17.18 rows=1 width=2,690) (actual time=10,314.645..10,320.011 rows=2 loops=1)

  • Buffers: shared hit=1,573 read=9,923
39. 0.009 10,319.981 ↓ 2.0 2 1

Nested Loop (cost=0.84..16.93 rows=1 width=2,720) (actual time=10,314.628..10,319.981 rows=2 loops=1)

  • Buffers: shared hit=1,569 read=9,923
40. 0.021 10,319.950 ↓ 2.0 2 1

Nested Loop (cost=0.56..8.62 rows=1 width=2,709) (actual time=10,314.609..10,319.950 rows=2 loops=1)

  • Buffers: shared hit=1,563 read=9,923
41. 10,317.409 10,317.409 ↓ 2.0 2 1

CTE Scan on accountbenchmark ab (cost=0.00..0.02 rows=1 width=4,224) (actual time=10,313.336..10,317.409 rows=2 loops=1)

  • Buffers: shared hit=1,555 read=9,921
42. 2.520 2.520 ↑ 1.0 1 2

Index Scan using performanceaccountbenchmark_pkey on performanceaccountbenchmark perf (cost=0.56..8.59 rows=1 width=566) (actual time=1.260..1.260 rows=1 loops=2)

  • Index Cond: ((effectivedate = '2020-06-30'::date) AND ((masterentityid)::text = (ab.performancemasterentityid)::text) AND ((currencycode)::text = (ab.currencycode_ab)::text) AND ((performancetype)::text = (ab.performancetype_ab)::text) AND ((benchmarkcode)::text = (ab.benchmarkid)::text))
  • Filter: (effectivedate >= COALESCE(performancerestructuredate, performancestartdate, '1900-01-01 00:00:00'::timestamp without time zone))
  • Buffers: shared hit=8 read=2
43. 0.022 0.022 ↑ 1.0 1 2

Index Scan using ix_122966_prf_sourceview on prf_sourceview sv (cost=0.28..8.30 rows=1 width=24) (actual time=0.011..0.011 rows=1 loops=2)

  • Index Cond: ((masterentityid)::text = (ab.masterentityid)::text)
  • Buffers: shared hit=6
44. 0.016 0.016 ↑ 1.0 1 2

Index Scan using prf_preferenceprofile_pkey on prf_preferenceprofile pp (cost=0.14..0.16 rows=1 width=30) (actual time=0.008..0.008 rows=1 loops=2)

  • Index Cond: ((preferenceprofileid)::text = (sv.preferenceprofileid)::text)
  • Buffers: shared hit=4
Planning time : 12.575 ms
Execution time : 10,320.448 ms