explain.depesz.com

PostgreSQL's explain analyze made readable

Result: elEY

Settings
# exclusive inclusive rows x rows loops node
1. 0.914 31,492.742 ↑ 60.8 6 1

Hash Join (cost=1,428,627.31..1,430,694.26 rows=365 width=426) (actual time=31,278.880..31,492.742 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=219447 read=234926, temp read=30934 written=30938
2. 54.760 31,474.875 ↓ 13.0 4,729 1

Merge Left Join (cost=1,428,608.06..1,430,670.38 rows=365 width=426) (actual time=31,231.539..31,474.875 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=215568 read=234926, temp read=30934 written=30938
3.          

CTE perfeom

4. 1,399.305 26,846.151 ↓ 2.9 210,533 1

Hash Join (cost=1,122,964.13..1,406,646.69 rows=73,026 width=47) (actual time=24,487.721..26,846.151 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=215568 read=234926, temp read=30934 written=30938
5. 961.994 961.994 ↑ 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.018..961.994 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=107800 read=117447
6. 56.184 24,484.852 ↑ 1.8 210,533 1

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

  • Output: eomtable.eomdate, eomtable.base_instrument
  • Buckets: 524288 Batches: 1 Memory Usage: 19105kB
  • Buffers: shared hit=107768 read=117479, temp read=30934 written=30938
7. 22.477 24,428.668 ↑ 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,348.373..24,428.668 rows=210,533 loops=1)

  • Output: eomtable.eomdate, eomtable.base_instrument
  • Buffers: shared hit=107768 read=117479, temp read=30934 written=30938
8. 959.737 24,406.191 ↑ 1.8 210,533 1

HashAggregate (cost=1,100,931.05..1,113,384.53 rows=383,184 width=53) (actual time=24,348.371..24,406.191 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=107768 read=117479, temp read=30934 written=30938
9. 2,510.532 23,446.454 ↑ 1.0 3,831,840 1

WindowAgg (cost=899,759.45..1,081,771.85 rows=3,831,840 width=53) (actual time=18,780.681..23,446.454 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=107768 read=117479, temp read=30934 written=30938
10. 18,429.610 20,935.922 ↑ 1.0 3,831,840 1

Sort (cost=899,759.45..909,339.05 rows=3,831,840 width=53) (actual time=18,780.662..20,935.922 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=107768 read=117479, temp read=30934 written=30938
11. 2,506.312 2,506.312 ↑ 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.032..2,506.312 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=107768 read=117479
12. 46.564 30,552.190 ↓ 13.0 4,729 1

Merge Left Join (cost=18,179.70..19,542.11 rows=365 width=230) (actual time=30,396.167..30,552.190 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=215568 read=234926, temp read=30934 written=30938
13. 43.951 29,652.910 ↓ 13.0 4,729 1

Merge Left Join (cost=14,398.02..15,079.69 rows=365 width=202) (actual time=29,575.707..29,652.910 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=215568 read=234926, temp read=30934 written=30938
14. 3.181 28,766.139 ↓ 13.0 4,729 1

Sort (cost=10,616.35..10,617.27 rows=365 width=174) (actual time=28,765.635..28,766.139 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=215568 read=234926, temp read=30934 written=30938
15. 52.263 28,762.958 ↓ 13.0 4,729 1

Merge Left Join (cost=9,919.16..10,600.82 rows=365 width=174) (actual time=28,677.724..28,762.958 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=215568 read=234926, temp read=30934 written=30938
16. 2.781 27,823.248 ↓ 13.0 4,729 1

Sort (cost=6,137.49..6,138.40 rows=365 width=146) (actual time=27,822.709..27,823.248 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=215568 read=234926, temp read=30934 written=30938
17. 45.638 27,820.467 ↓ 13.0 4,729 1

Merge Left Join (cost=5,440.29..6,121.95 rows=365 width=146) (actual time=27,743.879..27,820.467 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=215568 read=234926, temp read=30934 written=30938
18. 11.617 26,936.286 ↓ 13.0 4,729 1

Sort (cost=1,658.62..1,659.53 rows=365 width=118) (actual time=26,935.440..26,936.286 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=215568 read=234926, temp read=30934 written=30938
19. 26,924.669 26,924.669 ↓ 13.0 4,729 1

CTE Scan on perfeom perf (cost=0.00..1,643.09 rows=365 width=118) (actual time=24,498.001..26,924.669 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=215568 read=234926, temp read=30934 written=30938
20. 649.715 838.543 ↓ 8.1 198,296 1

Sort (cost=3,781.67..3,842.53 rows=24,342 width=118) (actual time=808.352..838.543 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. 188.828 188.828 ↓ 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.011..188.828 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. 692.966 887.447 ↓ 8.1 198,293 1

Sort (cost=3,781.67..3,842.53 rows=24,342 width=118) (actual time=854.921..887.447 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. 194.481 194.481 ↓ 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.010..194.481 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. 653.270 842.820 ↓ 8.1 198,285 1

Sort (cost=3,781.67..3,842.53 rows=24,342 width=118) (actual time=809.995..842.820 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. 189.550 189.550 ↓ 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.011..189.550 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. 660.366 852.716 ↓ 8.1 198,284 1

Sort (cost=3,781.67..3,842.53 rows=24,342 width=118) (actual time=820.382..852.716 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. 192.350 192.350 ↓ 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.008..192.350 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. 674.805 867.925 ↓ 8.1 198,284 1

Sort (cost=3,781.67..3,842.53 rows=24,342 width=118) (actual time=835.274..867.925 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. 193.120 193.120 ↓ 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.007..193.120 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.007 16.953 ↑ 33.3 6 1

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

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

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

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

Function Scan on reporting.factsheet_positions_funds (cost=0.25..10.25 rows=1,000 width=32) (actual time=16.930..16.931 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.277 ms
Execution time : 31,546.684 ms