explain.depesz.com

PostgreSQL's explain analyze made readable

Result: inTe : StandardShareClassPerformance_1s_QA

Settings
# exclusive inclusive rows x rows loops node
1. 0.014 40.825 ↓ 2.0 2 1

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

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

CTE super

3. 0.014 36.914 ↑ 1.0 1 1

Nested Loop (cost=0.71..51,329.03 rows=1 width=169) (actual time=33.517..36.914 rows=1 loops=1)

  • Buffers: shared hit=9,943 read=1
4. 36.875 36.875 ↑ 1.0 1 1

Index Scan using entitysuperset_pkey on entitysuperset es (cost=0.43..51,320.72 rows=1 width=76) (actual time=33.478..36.875 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=9,940 read=1
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.025..0.025 rows=1 loops=1)

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

CTE pref

7. 0.010 36.968 ↑ 3.5 2 1

Nested Loop (cost=4.48..31.13 rows=7 width=1,057) (actual time=33.568..36.968 rows=2 loops=1)

  • Buffers: shared hit=9,956 read=1
8. 36.918 36.918 ↑ 1.0 1 1

CTE Scan on super s_1 (cost=0.00..0.02 rows=1 width=1,032) (actual time=33.520..36.918 rows=1 loops=1)

  • Buffers: shared hit=9,943 read=1
9. 0.015 0.040 ↑ 3.5 2 1

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

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

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

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

CTE sources

12. 0.010 37.007 ↑ 4.0 2 1

HashAggregate (cost=52.98..53.06 rows=8 width=1,592) (actual time=37.005..37.007 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=9,956 read=1
13. 0.002 36.997 ↑ 4.0 2 1

Append (cost=0.02..52.84 rows=8 width=1,592) (actual time=33.573..36.997 rows=2 loops=1)

  • Buffers: shared hit=9,956 read=1
14. 0.004 36.975 ↑ 3.5 2 1

Result (cost=0.02..0.16 rows=7 width=2,076) (actual time=33.573..36.975 rows=2 loops=1)

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

Initplan (for Result)

16. 33.569 33.569 ↑ 7.0 1 1

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

  • Buffers: shared hit=9,024
17. 3.402 3.402 ↑ 3.5 2 1

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

  • Buffers: shared hit=932 read=1
18. 0.000 0.020 ↓ 0.0 0 1

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

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

Initplan (for Group)

20. 0.001 0.001 ↑ 7.0 1 1

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

21. 0.018 0.020 ↓ 0.0 0 1

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

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

Result (cost=5.01..52.55 rows=1 width=30) (actual time=0.002..0.002 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.012 40.707 ↓ 2.0 2 1

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

  • Buffers: shared hit=11,474 read=1
31. 0.009 37.067 ↓ 2.0 2 1

Nested Loop (cost=0.44..8.72 rows=1 width=3,237) (actual time=37.043..37.067 rows=2 loops=1)

  • Join Filter: ((a_1.performancemasterentityid)::text = (abr_1.masterentityid)::text)
  • Buffers: shared hit=9,964 read=1
32. 0.016 37.032 ↓ 2.0 2 1

Hash Join (cost=0.03..0.23 rows=1 width=3,744) (actual time=37.022..37.032 rows=2 loops=1)

  • Hash Cond: ((s_2.performancemasterentityid)::text = (a_1.performancemasterentityid)::text)
  • Buffers: shared hit=9,956 read=1
33. 37.012 37.012 ↑ 4.0 2 1

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

  • Buffers: shared hit=9,956 read=1
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. 0.026 0.026 ↑ 1.0 1 2

Index Scan using accountbenchmarkrelationship_pkey on accountbenchmarkrelationship abr_1 (cost=0.41..8.47 rows=1 width=28) (actual time=0.013..0.013 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=8
37. 3.628 3.628 ↑ 1.0 1 2

Index Scan using index_pkey on index i (cost=0.41..492.14 rows=1 width=61) (actual time=1.798..1.814 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,510
38. 0.012 40.811 ↓ 2.0 2 1

Nested Loop (cost=0.98..17.18 rows=1 width=2,690) (actual time=39.217..40.811 rows=2 loops=1)

  • Buffers: shared hit=11,494 read=1
39. 0.011 40.787 ↓ 2.0 2 1

Nested Loop (cost=0.84..16.93 rows=1 width=2,720) (actual time=39.200..40.787 rows=2 loops=1)

  • Buffers: shared hit=11,490 read=1
40. 0.013 40.760 ↓ 2.0 2 1

Nested Loop (cost=0.56..8.62 rows=1 width=2,709) (actual time=39.182..40.760 rows=2 loops=1)

  • Buffers: shared hit=11,484 read=1
41. 40.713 40.713 ↓ 2.0 2 1

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

  • Buffers: shared hit=11,474 read=1
42. 0.034 0.034 ↑ 1.0 1 2

Index Scan using performanceaccountbenchmark_pkey on performanceaccountbenchmark perf (cost=0.56..8.59 rows=1 width=566) (actual time=0.017..0.017 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=10
43. 0.016 0.016 ↑ 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.008..0.008 rows=1 loops=2)

  • Index Cond: ((masterentityid)::text = (ab.masterentityid)::text)
  • Buffers: shared hit=6
44. 0.012 0.012 ↑ 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.006..0.006 rows=1 loops=2)

  • Index Cond: ((preferenceprofileid)::text = (sv.preferenceprofileid)::text)
  • Buffers: shared hit=4
Planning time : 2.989 ms
Execution time : 41.237 ms