explain.depesz.com

PostgreSQL's explain analyze made readable

Result: RLyj

Settings
# exclusive inclusive rows x rows loops node
1. 0.954 32,234.611 ↑ 60.8 6 1

Hash Join (cost=1,428,627.31..1,430,694.26 rows=365 width=426) (actual time=32,019.086..32,234.611 rows=6 loops=1)

  • Output: perf.base_date, perf_m1.base_date, perf_ytd.base_date, perf_y1.base_date, perf_y3.base_date, perf_y5.base_date, ((date_part('year'::text, (perf.base_date)::timestamp without time zone))::integer), ((date_part('month'::text, (perf.base_date)::timestamp without time zone))::integer), perf.base_instrument, perf.base_price, perf_m1.base_price, perf_ytd.base_price, perf_y1.base_price, perf_y3.base_price, perf_y5.base_price, (CASE WHEN (perf_m1.base_price = '0'::numeric) THEN '0'::numeric ELSE ((perf.base_price / perf_m1.base_price) - '1'::numeric) END), (CASE WHEN (perf_ytd.base_price = '0'::numeric) THEN '0'::numeric ELSE ((perf.base_price / perf_ytd.base_price) - '1'::numeric) END), (CASE WHEN (perf_y1.base_price = '0'::numeric) THEN '0'::numeric ELSE ((perf.base_price / perf_y1.base_price) - '1'::numeric) END), (CASE WHEN (perf_y3.base_price = '0'::numeric) THEN '0'::numeric ELSE ((perf.base_price / perf_y3.base_price) - '1'::numeric) END), (CASE WHEN (perf_y5.base_price = '0'::numeric) THEN '0'::numeric ELSE ((perf.base_price / perf_y5.base_price) - '1'::numeric) END)
  • Inner Unique: true
  • Hash Cond: ((perf.base_instrument)::text = (factsheet_positions_funds.fundclassid)::text)
  • Buffers: shared hit=219767 read=234606, temp read=30934 written=30938
2. 54.985 32,220.277 ↓ 13.0 4,729 1

Merge Left Join (cost=1,428,608.06..1,430,670.38 rows=365 width=426) (actual time=31,973.980..32,220.277 rows=4,729 loops=1)

  • Output: perf.base_date, perf_m1.base_date, perf_ytd.base_date, perf_y1.base_date, perf_y3.base_date, perf_y5.base_date, (date_part('year'::text, (perf.base_date)::timestamp without time zone))::integer, (date_part('month'::text, (perf.base_date)::timestamp without time zone))::integer, perf.base_instrument, perf.base_price, perf_m1.base_price, perf_ytd.base_price, perf_y1.base_price, perf_y3.base_price, perf_y5.base_price, CASE WHEN (perf_m1.base_price = '0'::numeric) THEN '0'::numeric ELSE ((perf.base_price / perf_m1.base_price) - '1'::numeric) END, CASE WHEN (perf_ytd.base_price = '0'::numeric) THEN '0'::numeric ELSE ((perf.base_price / perf_ytd.base_price) - '1'::numeric) END, CASE WHEN (perf_y1.base_price = '0'::numeric) THEN '0'::numeric ELSE ((perf.base_price / perf_y1.base_price) - '1'::numeric) END, CASE WHEN (perf_y3.base_price = '0'::numeric) THEN '0'::numeric ELSE ((perf.base_price / perf_y3.base_price) - '1'::numeric) END, CASE WHEN (perf_y5.base_price = '0'::numeric) THEN '0'::numeric ELSE ((perf.base_price / perf_y5.base_price) - '1'::numeric) END
  • Merge Cond: (((perf.base_instrument)::text = (perf_y5.base_instrument)::text) AND (((date_trunc('MONTH'::text, (perf.base_date)::timestamp with time zone) + '1 year -1 days'::interval))::date = (((date_trunc('MONTH'::text, (perf_y5.base_date)::timestamp with time zone) + '6 years -1 days'::interval))::date)))
  • Buffers: shared hit=215888 read=234606, temp read=30934 written=30938
3.          

CTE perfeom

4. 1,387.429 27,460.171 ↓ 2.9 210,533 1

Hash Join (cost=1,122,964.13..1,406,646.69 rows=73,026 width=47) (actual time=25,093.982..27,460.171 rows=210,533 loops=1)

  • Output: perf_1.base_date, perf_1.base_instrument, perf_1.base_price
  • Inner Unique: true
  • Hash Cond: ((perf_1.base_date = eomtable.eomdate) AND ((perf_1.base_instrument)::text = (eomtable.base_instrument)::text))
  • Buffers: shared hit=215888 read=234606, temp read=30934 written=30938
5. 979.377 979.377 ↑ 1.0 3,831,840 1

Seq Scan on folio.base_performance perf_1 (cost=0.00..263,565.40 rows=3,831,840 width=47) (actual time=0.007..979.377 rows=3,831,840 loops=1)

  • Output: perf_1.dl_dte_load, perf_1.deactivated, perf_1.createdby, perf_1.createddate, perf_1.modifiedby, perf_1.modifieddate, perf_1.deactivatedby, perf_1.deactivateddate, perf_1.base_performanceid, perf_1.base_referenceid, perf_1.base_instrument, perf_1.base_date, perf_1.base_return, perf_1.base_price, perf_1.base_performancestatus, perf_1.base_description, perf_1.client_source, perf_1.client_valuationdate
  • Buffers: shared hit=107960 read=117287
6. 56.454 25,093.365 ↑ 1.8 210,533 1

Hash (cost=1,117,216.37..1,117,216.37 rows=383,184 width=41) (actual time=25,093.365..25,093.365 rows=210,533 loops=1)

  • Output: eomtable.eomdate, eomtable.base_instrument
  • Buckets: 524288 Batches: 1 Memory Usage: 19105kB
  • Buffers: shared hit=107928 read=117319, temp read=30934 written=30938
7. 21.865 25,036.911 ↑ 1.8 210,533 1

Subquery Scan on eomtable (cost=1,100,931.05..1,117,216.37 rows=383,184 width=41) (actual time=24,956.198..25,036.911 rows=210,533 loops=1)

  • Output: eomtable.eomdate, eomtable.base_instrument
  • Buffers: shared hit=107928 read=117319, temp read=30934 written=30938
8. 936.564 25,015.046 ↑ 1.8 210,533 1

HashAggregate (cost=1,100,931.05..1,113,384.53 rows=383,184 width=53) (actual time=24,956.197..25,015.046 rows=210,533 loops=1)

  • Output: (max(perf_2.base_date) OVER (?)), perf_2.base_instrument, ((date_part('day'::text, (perf_2.base_date)::timestamp without time zone))::integer), ((date_part('year'::text, (perf_2.base_date)::timestamp without time zone))::integer), ((date_part('month'::text, (perf_2.base_date)::timestamp without time zone))::integer)
  • Group Key: max(perf_2.base_date) OVER (?), perf_2.base_instrument
  • Buffers: shared hit=107928 read=117319, temp read=30934 written=30938
9. 2,464.101 24,078.482 ↑ 1.0 3,831,840 1

WindowAgg (cost=899,759.45..1,081,771.85 rows=3,831,840 width=53) (actual time=19,507.745..24,078.482 rows=3,831,840 loops=1)

  • Output: max(perf_2.base_date) OVER (?), perf_2.base_instrument, ((date_part('day'::text, (perf_2.base_date)::timestamp without time zone))::integer), ((date_part('year'::text, (perf_2.base_date)::timestamp without time zone))::integer), ((date_part('month'::text, (perf_2.base_date)::timestamp without time zone))::integer)
  • Buffers: shared hit=107928 read=117319, temp read=30934 written=30938
10. 19,178.676 21,614.381 ↑ 1.0 3,831,840 1

Sort (cost=899,759.45..909,339.05 rows=3,831,840 width=53) (actual time=19,507.721..21,614.381 rows=3,831,840 loops=1)

  • Output: perf_2.base_instrument, ((date_part('day'::text, (perf_2.base_date)::timestamp without time zone))::integer), ((date_part('year'::text, (perf_2.base_date)::timestamp without time zone))::integer), ((date_part('month'::text, (perf_2.base_date)::timestamp without time zone))::integer), perf_2.base_date
  • Sort Key: perf_2.base_instrument, ((date_part('year'::text, (perf_2.base_date)::timestamp without time zone))::integer), ((date_part('month'::text, (perf_2.base_date)::timestamp without time zone))::integer), ((date_part('day'::text, (perf_2.base_date)::timestamp without time zone))::integer) DESC
  • Sort Method: external merge Disk: 247472kB
  • Buffers: shared hit=107928 read=117319, temp read=30934 written=30938
11. 2,435.705 2,435.705 ↑ 1.0 3,831,840 1

Seq Scan on folio.base_performance perf_2 (cost=0.00..349,781.80 rows=3,831,840 width=53) (actual time=0.013..2,435.705 rows=3,831,840 loops=1)

  • Output: perf_2.base_instrument, (date_part('day'::text, (perf_2.base_date)::timestamp without time zone))::integer, (date_part('year'::text, (perf_2.base_date)::timestamp without time zone))::integer, (date_part('month'::text, (perf_2.base_date)::timestamp without time zone))::integer, perf_2.base_date
  • Buffers: shared hit=107928 read=117319
12. 47.173 31,223.766 ↓ 13.0 4,729 1

Merge Left Join (cost=18,179.70..19,542.11 rows=365 width=230) (actual time=31,065.914..31,223.766 rows=4,729 loops=1)

  • Output: perf.base_date, perf.base_instrument, perf.base_price, perf_m1.base_date, perf_m1.base_price, perf_ytd.base_date, perf_ytd.base_price, perf_y1.base_date, perf_y1.base_price, perf_y3.base_date, perf_y3.base_price
  • Merge Cond: (((perf.base_instrument)::text = (perf_y3.base_instrument)::text) AND (((date_trunc('MONTH'::text, (perf.base_date)::timestamp with time zone) + '1 year -1 days'::interval))::date = (((date_trunc('MONTH'::text, (perf_y3.base_date)::timestamp with time zone) + '4 years -1 days'::interval))::date)))
  • Buffers: shared hit=215888 read=234606, temp read=30934 written=30938
13. 43.520 30,308.107 ↓ 13.0 4,729 1

Merge Left Join (cost=14,398.02..15,079.69 rows=365 width=202) (actual time=30,231.060..30,308.107 rows=4,729 loops=1)

  • Output: perf.base_date, perf.base_instrument, perf.base_price, perf_m1.base_date, perf_m1.base_price, perf_ytd.base_date, perf_ytd.base_price, perf_y1.base_date, perf_y1.base_price
  • Merge Cond: (((perf.base_instrument)::text = (perf_y1.base_instrument)::text) AND ((((date_trunc('MONTH'::text, (perf.base_date)::timestamp with time zone) + '1 year -1 days'::interval))::date) = (((date_trunc('MONTH'::text, (perf_y1.base_date)::timestamp with time zone) + '2 years -1 days'::interval))::date)))
  • Buffers: shared hit=215888 read=234606, temp read=30934 written=30938
14. 2.816 29,303.554 ↓ 13.0 4,729 1

Sort (cost=10,616.35..10,617.27 rows=365 width=174) (actual time=29,303.059..29,303.554 rows=4,729 loops=1)

  • Output: perf.base_date, perf.base_instrument, perf.base_price, perf_m1.base_date, perf_m1.base_price, perf_ytd.base_date, perf_ytd.base_price, (((date_trunc('MONTH'::text, (perf.base_date)::timestamp with time zone) + '1 year -1 days'::interval))::date)
  • Sort Key: perf.base_instrument, (((date_trunc('MONTH'::text, (perf.base_date)::timestamp with time zone) + '1 year -1 days'::interval))::date)
  • Sort Method: quicksort Memory: 858kB
  • Buffers: shared hit=215888 read=234606, temp read=30934 written=30938
15. 45.840 29,300.738 ↓ 13.0 4,729 1

Merge Left Join (cost=9,919.16..10,600.82 rows=365 width=174) (actual time=29,224.570..29,300.738 rows=4,729 loops=1)

  • Output: perf.base_date, perf.base_instrument, perf.base_price, perf_m1.base_date, perf_m1.base_price, perf_ytd.base_date, perf_ytd.base_price, ((date_trunc('MONTH'::text, (perf.base_date)::timestamp with time zone) + '1 year -1 days'::interval))::date
  • Merge Cond: (((perf.base_instrument)::text = (perf_ytd.base_instrument)::text) AND ((((date_trunc('YEAR'::text, (perf.base_date)::timestamp with time zone) + '-1 days'::interval))::date) = (((date_trunc('MONTH'::text, (perf_ytd.base_date)::timestamp with time zone) + '1 mon -1 days'::interval))::date)))
  • Buffers: shared hit=215888 read=234606, temp read=30934 written=30938
16. 2.649 28,427.856 ↓ 13.0 4,729 1

Sort (cost=6,137.49..6,138.40 rows=365 width=146) (actual time=28,427.393..28,427.856 rows=4,729 loops=1)

  • Output: perf.base_date, perf.base_instrument, perf.base_price, perf_m1.base_date, perf_m1.base_price, (((date_trunc('YEAR'::text, (perf.base_date)::timestamp with time zone) + '-1 days'::interval))::date)
  • Sort Key: perf.base_instrument, (((date_trunc('YEAR'::text, (perf.base_date)::timestamp with time zone) + '-1 days'::interval))::date)
  • Sort Method: quicksort Memory: 858kB
  • Buffers: shared hit=215888 read=234606, temp read=30934 written=30938
17. 45.248 28,425.207 ↓ 13.0 4,729 1

Merge Left Join (cost=5,440.29..6,121.95 rows=365 width=146) (actual time=28,349.244..28,425.207 rows=4,729 loops=1)

  • Output: perf.base_date, perf.base_instrument, perf.base_price, perf_m1.base_date, perf_m1.base_price, ((date_trunc('YEAR'::text, (perf.base_date)::timestamp with time zone) + '-1 days'::interval))::date
  • Merge Cond: (((perf.base_instrument)::text = (perf_m1.base_instrument)::text) AND ((((date_trunc('MONTH'::text, (perf.base_date)::timestamp with time zone) + '1 mon -1 days'::interval))::date) = (((date_trunc('MONTH'::text, (perf_m1.base_date)::timestamp with time zone) + '2 mons -1 days'::interval))::date)))
  • Buffers: shared hit=215888 read=234606, temp read=30934 written=30938
18. 11.485 27,550.454 ↓ 13.0 4,729 1

Sort (cost=1,658.62..1,659.53 rows=365 width=118) (actual time=27,549.605..27,550.454 rows=4,729 loops=1)

  • Output: perf.base_date, perf.base_instrument, perf.base_price, (((date_trunc('MONTH'::text, (perf.base_date)::timestamp with time zone) + '1 mon -1 days'::interval))::date)
  • Sort Key: perf.base_instrument, (((date_trunc('MONTH'::text, (perf.base_date)::timestamp with time zone) + '1 mon -1 days'::interval))::date)
  • Sort Method: quicksort Memory: 857kB
  • Buffers: shared hit=215888 read=234606, temp read=30934 written=30938
19. 27,538.969 27,538.969 ↓ 13.0 4,729 1

CTE Scan on perfeom perf (cost=0.00..1,643.09 rows=365 width=118) (actual time=25,100.935..27,538.969 rows=4,729 loops=1)

  • Output: perf.base_date, perf.base_instrument, perf.base_price, ((date_trunc('MONTH'::text, (perf.base_date)::timestamp with time zone) + '1 mon -1 days'::interval))::date
  • Filter: (perf.base_date = '2020-04-30'::date)
  • Rows Removed by Filter: 205804
  • Buffers: shared hit=215888 read=234606, temp read=30934 written=30938
20. 638.840 829.505 ↓ 8.1 198,296 1

Sort (cost=3,781.67..3,842.53 rows=24,342 width=118) (actual time=799.540..829.505 rows=198,296 loops=1)

  • Output: perf_m1.base_date, perf_m1.base_price, perf_m1.base_instrument, (((date_trunc('MONTH'::text, (perf_m1.base_date)::timestamp with time zone) + '2 mons -1 days'::interval))::date)
  • Sort Key: perf_m1.base_instrument, (((date_trunc('MONTH'::text, (perf_m1.base_date)::timestamp with time zone) + '2 mons -1 days'::interval))::date)
  • Sort Method: quicksort Memory: 33895kB
21. 190.665 190.665 ↓ 8.1 198,345 1

CTE Scan on perfeom perf_m1 (cost=0.00..2,008.22 rows=24,342 width=118) (actual time=0.022..190.665 rows=198,345 loops=1)

  • Output: perf_m1.base_date, perf_m1.base_price, perf_m1.base_instrument, ((date_trunc('MONTH'::text, (perf_m1.base_date)::timestamp with time zone) + '2 mons -1 days'::interval))::date
  • Filter: (date_part('day'::text, (perf_m1.base_date)::timestamp without time zone) > '25'::double precision)
  • Rows Removed by Filter: 12188
22. 638.634 827.042 ↓ 8.1 198,293 1

Sort (cost=3,781.67..3,842.53 rows=24,342 width=118) (actual time=797.090..827.042 rows=198,293 loops=1)

  • Output: perf_ytd.base_date, perf_ytd.base_price, perf_ytd.base_instrument, (((date_trunc('MONTH'::text, (perf_ytd.base_date)::timestamp with time zone) + '1 mon -1 days'::interval))::date)
  • Sort Key: perf_ytd.base_instrument, (((date_trunc('MONTH'::text, (perf_ytd.base_date)::timestamp with time zone) + '1 mon -1 days'::interval))::date)
  • Sort Method: quicksort Memory: 33895kB
23. 188.408 188.408 ↓ 8.1 198,345 1

CTE Scan on perfeom perf_ytd (cost=0.00..2,008.22 rows=24,342 width=118) (actual time=0.020..188.408 rows=198,345 loops=1)

  • Output: perf_ytd.base_date, perf_ytd.base_price, perf_ytd.base_instrument, ((date_trunc('MONTH'::text, (perf_ytd.base_date)::timestamp with time zone) + '1 mon -1 days'::interval))::date
  • Filter: (date_part('day'::text, (perf_ytd.base_date)::timestamp without time zone) > '25'::double precision)
  • Rows Removed by Filter: 12188
24. 762.787 961.033 ↓ 8.1 198,285 1

Sort (cost=3,781.67..3,842.53 rows=24,342 width=118) (actual time=927.916..961.033 rows=198,285 loops=1)

  • Output: perf_y1.base_date, perf_y1.base_price, perf_y1.base_instrument, (((date_trunc('MONTH'::text, (perf_y1.base_date)::timestamp with time zone) + '2 years -1 days'::interval))::date)
  • Sort Key: perf_y1.base_instrument, (((date_trunc('MONTH'::text, (perf_y1.base_date)::timestamp with time zone) + '2 years -1 days'::interval))::date)
  • Sort Method: quicksort Memory: 33895kB
25. 198.246 198.246 ↓ 8.1 198,345 1

CTE Scan on perfeom perf_y1 (cost=0.00..2,008.22 rows=24,342 width=118) (actual time=0.019..198.246 rows=198,345 loops=1)

  • Output: perf_y1.base_date, perf_y1.base_price, perf_y1.base_instrument, ((date_trunc('MONTH'::text, (perf_y1.base_date)::timestamp with time zone) + '2 years -1 days'::interval))::date
  • Filter: (date_part('day'::text, (perf_y1.base_date)::timestamp without time zone) > '25'::double precision)
  • Rows Removed by Filter: 12188
26. 670.310 868.486 ↓ 8.1 198,284 1

Sort (cost=3,781.67..3,842.53 rows=24,342 width=118) (actual time=834.756..868.486 rows=198,284 loops=1)

  • Output: perf_y3.base_date, perf_y3.base_price, perf_y3.base_instrument, (((date_trunc('MONTH'::text, (perf_y3.base_date)::timestamp with time zone) + '4 years -1 days'::interval))::date)
  • Sort Key: perf_y3.base_instrument, (((date_trunc('MONTH'::text, (perf_y3.base_date)::timestamp with time zone) + '4 years -1 days'::interval))::date)
  • Sort Method: quicksort Memory: 33895kB
27. 198.176 198.176 ↓ 8.1 198,345 1

CTE Scan on perfeom perf_y3 (cost=0.00..2,008.22 rows=24,342 width=118) (actual time=0.010..198.176 rows=198,345 loops=1)

  • Output: perf_y3.base_date, perf_y3.base_price, perf_y3.base_instrument, ((date_trunc('MONTH'::text, (perf_y3.base_date)::timestamp with time zone) + '4 years -1 days'::interval))::date
  • Filter: (date_part('day'::text, (perf_y3.base_date)::timestamp without time zone) > '25'::double precision)
  • Rows Removed by Filter: 12188
28. 744.441 941.526 ↓ 8.1 198,284 1

Sort (cost=3,781.67..3,842.53 rows=24,342 width=118) (actual time=907.965..941.526 rows=198,284 loops=1)

  • Output: perf_y5.base_date, perf_y5.base_price, perf_y5.base_instrument, (((date_trunc('MONTH'::text, (perf_y5.base_date)::timestamp with time zone) + '6 years -1 days'::interval))::date)
  • Sort Key: perf_y5.base_instrument, (((date_trunc('MONTH'::text, (perf_y5.base_date)::timestamp with time zone) + '6 years -1 days'::interval))::date)
  • Sort Method: quicksort Memory: 33895kB
29. 197.085 197.085 ↓ 8.1 198,345 1

CTE Scan on perfeom perf_y5 (cost=0.00..2,008.22 rows=24,342 width=118) (actual time=0.016..197.085 rows=198,345 loops=1)

  • Output: perf_y5.base_date, perf_y5.base_price, perf_y5.base_instrument, ((date_trunc('MONTH'::text, (perf_y5.base_date)::timestamp with time zone) + '6 years -1 days'::interval))::date
  • Filter: (date_part('day'::text, (perf_y5.base_date)::timestamp without time zone) > '25'::double precision)
  • Rows Removed by Filter: 12188
30. 0.003 13.380 ↑ 33.3 6 1

Hash (cost=16.75..16.75 rows=200 width=32) (actual time=13.380..13.380 rows=6 loops=1)

  • Output: factsheet_positions_funds.fundclassid
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=3879
31. 0.010 13.377 ↑ 33.3 6 1

HashAggregate (cost=12.75..14.75 rows=200 width=32) (actual time=13.374..13.377 rows=6 loops=1)

  • Output: factsheet_positions_funds.fundclassid
  • Group Key: factsheet_positions_funds.fundclassid
  • Buffers: shared hit=3879
32. 13.367 13.367 ↑ 166.7 6 1

Function Scan on reporting.factsheet_positions_funds (cost=0.25..10.25 rows=1,000 width=32) (actual time=13.366..13.367 rows=6 loops=1)

  • Output: factsheet_positions_funds.fundid, factsheet_positions_funds.fundname, factsheet_positions_funds.benchmarkid, factsheet_positions_funds.fundclassid
  • Function Call: reporting.factsheet_positions_funds('2F9C2D04-9C03-4A7F-B720-4476B590DB06'::character varying, '2020-04-30'::date)
  • Buffers: shared hit=3879
Planning time : 1.301 ms
Execution time : 32,288.617 ms