explain.depesz.com

PostgreSQL's explain analyze made readable

Result: pR1h

Settings
# exclusive inclusive rows x rows loops node
1. 1.032 31,014.135 ↑ 60.8 6 1

Hash Join (cost=1,428,627.31..1,430,694.26 rows=365 width=426) (actual time=30,786.315..31,014.135 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=219125 read=235374, temp read=30934 written=30938
  • Execution Time: 31068.445 ms(130 lignes)
2. 58.632 30,992.966 ↓ 13.0 4,729 1

Merge Left Join (cost=1,428,608.06..1,430,670.38 rows=365 width=426) (actual time=30,734.916..30,992.966 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=215120 read=235374, temp read=30934 written=30938
3.          

CTE perfeom

4. 1,389.792 26,197.589 ↓ 2.9 210,533 1

Hash Join (cost=1,122,964.13..1,406,646.69 rows=73,026 width=47) (actual time=23,888.773..26,197.589 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=215120 read=235374, temp read=30934 written=30938
5. 920.851 920.851 ↑ 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.009..920.851 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=107576 read=117671
6. 56.265 23,886.946 ↑ 1.8 210,533 1

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

  • Output: eomtable.eomdate, eomtable.base_instrument
  • Buckets: 524288 Batches: 1 Memory Usage: 19105kB
  • Buffers: shared hit=107544 read=117703, temp read=30934 written=30938
7. 22.048 23,830.681 ↑ 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=23,750.521..23,830.681 rows=210,533 loops=1)

  • Output: eomtable.eomdate, eomtable.base_instrument
  • Buffers: shared hit=107544 read=117703, temp read=30934 written=30938
8. 943.198 23,808.633 ↑ 1.8 210,533 1

HashAggregate (cost=1,100,931.05..1,113,384.53 rows=383,184 width=53) (actual time=23,750.518..23,808.633 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=107544 read=117703, temp read=30934 written=30938
9. 2,449.006 22,865.435 ↑ 1.0 3,831,840 1

WindowAgg (cost=899,759.45..1,081,771.85 rows=3,831,840 width=53) (actual time=18,326.176..22,865.435 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=107544 read=117703, temp read=30934 written=30938
10. 17,922.393 20,416.429 ↑ 1.0 3,831,840 1

Sort (cost=899,759.45..909,339.05 rows=3,831,840 width=53) (actual time=18,326.158..20,416.429 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=107544 read=117703, temp read=30934 written=30938
11. 2,494.036 2,494.036 ↑ 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.021..2,494.036 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=107544 read=117703
12. 49.836 30,089.144 ↓ 13.0 4,729 1

Merge Left Join (cost=18,179.70..19,542.11 rows=365 width=230) (actual time=29,923.563..30,089.144 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=215120 read=235374, temp read=30934 written=30938
13. 46.114 29,183.007 ↓ 13.0 4,729 1

Merge Left Join (cost=14,398.02..15,079.69 rows=365 width=202) (actual time=29,101.575..29,183.007 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=215120 read=235374, temp read=30934 written=30938
14. 2.832 28,284.936 ↓ 13.0 4,729 1

Sort (cost=10,616.35..10,617.27 rows=365 width=174) (actual time=28,284.433..28,284.936 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=215120 read=235374, temp read=30934 written=30938
15. 46.177 28,282.104 ↓ 13.0 4,729 1

Merge Left Join (cost=9,919.16..10,600.82 rows=365 width=174) (actual time=28,204.685..28,282.104 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=215120 read=235374, temp read=30934 written=30938
16. 2.714 27,246.339 ↓ 13.0 4,729 1

Sort (cost=6,137.49..6,138.40 rows=365 width=146) (actual time=27,245.883..27,246.339 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=215120 read=235374, temp read=30934 written=30938
17. 45.516 27,243.625 ↓ 13.0 4,729 1

Merge Left Join (cost=5,440.29..6,121.95 rows=365 width=146) (actual time=27,166.600..27,243.625 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=215120 read=235374, temp read=30934 written=30938
18. 11.633 26,288.215 ↓ 13.0 4,729 1

Sort (cost=1,658.62..1,659.53 rows=365 width=118) (actual time=26,287.338..26,288.215 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=215120 read=235374, temp read=30934 written=30938
19. 26,276.582 26,276.582 ↓ 13.0 4,729 1

CTE Scan on perfeom perf (cost=0.00..1,643.09 rows=365 width=118) (actual time=23,896.491..26,276.582 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=215120 read=235374, temp read=30934 written=30938
20. 660.965 909.894 ↓ 8.1 198,296 1

Sort (cost=3,781.67..3,842.53 rows=24,342 width=118) (actual time=879.175..909.894 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. 248.929 248.929 ↓ 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.016..248.929 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. 659.787 989.588 ↓ 8.1 198,293 1

Sort (cost=3,781.67..3,842.53 rows=24,342 width=118) (actual time=958.717..989.588 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. 329.801 329.801 ↓ 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..329.801 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. 656.229 851.957 ↓ 8.1 198,285 1

Sort (cost=3,781.67..3,842.53 rows=24,342 width=118) (actual time=817.067..851.957 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. 195.728 195.728 ↓ 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.010..195.728 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. 666.668 856.301 ↓ 8.1 198,284 1

Sort (cost=3,781.67..3,842.53 rows=24,342 width=118) (actual time=821.909..856.301 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. 189.633 189.633 ↓ 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..189.633 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. 657.164 845.190 ↓ 8.1 198,284 1

Sort (cost=3,781.67..3,842.53 rows=24,342 width=118) (actual time=811.258..845.190 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. 188.026 188.026 ↓ 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.008..188.026 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.005 20.137 ↑ 33.3 6 1

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

  • Output: factsheet_positions_funds.fundclassid
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=4005
31. 0.013 20.132 ↑ 33.3 6 1

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

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

Function Scan on reporting.factsheet_positions_funds (cost=0.25..10.25 rows=1,000 width=32) (actual time=20.119..20.119 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=4005
Planning time : 1.159 ms