explain.depesz.com

PostgreSQL's explain analyze made readable

Result: pfMF

Settings
# exclusive inclusive rows x rows loops node
1. 40.066 2,383.342 ↓ 4,517.0 4,517 1

Sort (cost=1,704.24..1,704.24 rows=1 width=185) (actual time=2,372.000..2,383.342 rows=4,517 loops=1)

  • Sort Key: restapi_padded_curves_structured.curve_date
  • Sort Method: external sort Disk: 6328kB
  • Buffers: shared hit=302166 read=5355, temp read=3134 written=3134
  • I/O Timings: read=1470.225
2. 0.704 2,343.276 ↓ 4,517.0 4,517 1

Subquery Scan on restapi_padded_curves_structured (cost=1,704.15..1,704.23 rows=1 width=185) (actual time=2,297.473..2,343.276 rows=4,517 loops=1)

  • Buffers: shared hit=302166 read=5355, temp read=2343 written=2343
  • I/O Timings: read=1470.225
3. 29.523 2,342.572 ↓ 4,517.0 4,517 1

GroupAggregate (cost=1,704.15..1,704.22 rows=1 width=185) (actual time=2,297.472..2,342.572 rows=4,517 loops=1)

  • Group Key: c.client, c.subscription_name, c.curve_short_name, c.axioma_data_id, c.curve_date, c.version, c.category
  • Buffers: shared hit=302166 read=5355, temp read=2343 written=2343
  • I/O Timings: read=1470.225
4. 59.943 2,313.049 ↓ 4,517.0 4,517 1

Sort (cost=1,704.15..1,704.16 rows=1 width=157) (actual time=2,297.451..2,313.049 rows=4,517 loops=1)

  • Sort Key: c.client, c.subscription_name, c.curve_short_name, c.curve_date, c.category
  • Sort Method: external sort Disk: 6176kB
  • Buffers: shared hit=302166 read=5355, temp read=2343 written=2343
  • I/O Timings: read=1470.225
5. 4.684 2,253.106 ↓ 4,517.0 4,517 1

Subquery Scan on c (cost=1,704.11..1,704.14 rows=1 width=157) (actual time=2,217.829..2,253.106 rows=4,517 loops=1)

  • Filter: ((lower((c.client)::text) = 'axioma'::text) AND (lower((c.subscription_name)::text) = 'all'::text))
  • Buffers: shared hit=302166 read=5355, temp read=1571 written=1571
  • I/O Timings: read=1470.225
6. 2.198 2,248.422 ↓ 4,517.0 4,517 1

Unique (cost=1,704.11..1,704.12 rows=1 width=183) (actual time=2,217.824..2,248.422 rows=4,517 loops=1)

  • Buffers: shared hit=302166 read=5355, temp read=1571 written=1571
  • I/O Timings: read=1470.225
7. 119.578 2,246.224 ↓ 9,034.0 9,034 1

Sort (cost=1,704.11..1,704.12 rows=1 width=183) (actual time=2,217.822..2,246.224 rows=9,034 loops=1)

  • Sort Key: cd.curve_date, (COALESCE(ci.category, cc.category)), (COALESCE(ci.version, cc.version)), "*SELECT* 1".transaction_time DESC
  • Sort Method: external sort Disk: 12568kB
  • Buffers: shared hit=302166 read=5355, temp read=1571 written=1571
  • I/O Timings: read=1470.225
8. 267.701 2,126.646 ↓ 9,034.0 9,034 1

Nested Loop Left Join (cost=6.44..1,704.10 rows=1 width=183) (actual time=39.030..2,126.646 rows=9,034 loops=1)

  • Join Filter: ((cc.category)::text = (COALESCE(ci.category, cc.category))::text)
  • Rows Removed by Join Filter: 83248
  • Filter: ((COALESCE(ci.version, cc.version) = 5) AND (lower((COALESCE(ci.category, cc.category))::text) = 'lc'::text))
  • Rows Removed by Filter: 40738
  • Buffers: shared hit=302166 read=5355
  • I/O Timings: read=1470.225
9. 19.355 1,695.153 ↓ 10,237.0 40,948 1

Nested Loop Left Join (cost=5.86..1,679.24 rows=4 width=788) (actual time=2.991..1,695.153 rows=40,948 loops=1)

  • Join Filter: (ci.axioma_data_id = "*SELECT* 1".axioma_data_id)
  • Buffers: shared hit=62900 read=5355
  • I/O Timings: read=1470.225
10. 21.670 78.598 ↓ 13,200.0 13,200 1

Nested Loop (cost=5.29..1,673.13 rows=1 width=75) (actual time=2.973..78.598 rows=13,200 loops=1)

  • Join Filter: (("*SELECT* 1".validity_range @> cd.curve_date) AND ("*SELECT* 1".validityrange @> cd.curve_date))
  • Rows Removed by Join Filter: 85800
  • Buffers: shared hit=4520 read=17
  • I/O Timings: read=3.513
11. 1.565 10.728 ↓ 19.8 6,600 1

Subquery Scan on cd (cost=1.67..39.20 rows=333 width=4) (actual time=0.052..10.728 rows=6,600 loops=1)

  • Filter: (cd.curve_date <= '2020-02-05'::date)
  • Buffers: shared hit=11
12. 2.316 9.163 ↓ 6.6 6,600 1

Result (cost=1.67..26.70 rows=1,000 width=4) (actual time=0.052..9.163 rows=6,600 loops=1)

  • Buffers: shared hit=11
13.          

Initplan (for Result)

14. 0.001 0.026 ↑ 1.0 1 1

Limit (cost=0.57..0.84 rows=1 width=4) (actual time=0.025..0.026 rows=1 loops=1)

  • Buffers: shared hit=5
15. 0.025 0.025 ↑ 374,727,232.0 1 1

Index Only Scan using xc_curve_ie1 on xc_curve (cost=0.57..99,170,129.15 rows=374,727,232 width=4) (actual time=0.025..0.025 rows=1 loops=1)

  • Index Cond: (curve_date IS NOT NULL)
  • Heap Fetches: 0
  • Buffers: shared hit=5
16. 0.001 0.019 ↑ 1.0 1 1

Limit (cost=0.57..0.84 rows=1 width=4) (actual time=0.019..0.019 rows=1 loops=1)

  • Buffers: shared hit=6
17. 0.018 0.018 ↑ 374,727,232.0 1 1

Index Only Scan Backward using xc_curve_ie1 on xc_curve xc_curve_1 (cost=0.57..99,170,129.15 rows=374,727,232 width=4) (actual time=0.018..0.018 rows=1 loops=1)

  • Index Cond: (curve_date IS NOT NULL)
  • Heap Fetches: 1
  • Buffers: shared hit=6
18. 6.756 6.802 ↓ 6.6 6,600 1

ProjectSet (cost=1.67..6.70 rows=1,000 width=8) (actual time=0.052..6.802 rows=6,600 loops=1)

  • Buffers: shared hit=11
19. 0.046 0.046 ↑ 1.0 1 1

Result (cost=1.67..1.68 rows=1 width=8) (actual time=0.046..0.046 rows=1 loops=1)

  • Buffers: shared hit=11
20. 7.384 46.200 ↓ 7.5 15 6,600

Materialize (cost=3.62..1,622.28 rows=2 width=118) (actual time=0.000..0.007 rows=15 loops=6,600)

  • Buffers: shared hit=4509 read=17
  • I/O Timings: read=3.513
21. 0.005 38.816 ↓ 7.5 15 1

Append (cost=3.62..1,622.27 rows=2 width=118) (actual time=2.914..38.816 rows=15 loops=1)

  • Buffers: shared hit=4509 read=17
  • I/O Timings: read=3.513
22. 0.008 38.799 ↓ 15.0 15 1

Subquery Scan on *SELECT* 1 (cost=3.62..794.49 rows=1 width=91) (actual time=2.914..38.799 rows=15 loops=1)

  • Buffers: shared hit=4507 read=17
  • I/O Timings: read=3.513
23. 0.021 38.791 ↓ 15.0 15 1

Nested Loop (cost=3.62..794.48 rows=1 width=91) (actual time=2.913..38.791 rows=15 loops=1)

  • Buffers: shared hit=4507 read=17
  • I/O Timings: read=3.513
24. 0.008 3.820 ↓ 15.0 15 1

Nested Loop (cost=3.20..5.46 rows=1 width=75) (actual time=1.156..3.820 rows=15 loops=1)

  • Buffers: shared hit=7 read=17
  • I/O Timings: read=3.513
25. 0.001 0.025 ↑ 1.0 1 1

Unique (cost=2.65..2.66 rows=1 width=379) (actual time=0.024..0.025 rows=1 loops=1)

  • Buffers: shared hit=4
26. 0.008 0.024 ↑ 1.0 1 1

Sort (cost=2.65..2.66 rows=1 width=379) (actual time=0.023..0.024 rows=1 loops=1)

  • Sort Key: t.transaction_time DESC
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=4
27. 0.016 0.016 ↑ 1.0 1 1

Index Scan using xc_issuer_curve_universe_base_pk on xc_issuer_curve_universe_base t (cost=0.41..2.64 rows=1 width=379) (actual time=0.016..0.016 rows=1 loops=1)

  • Index Cond: (axioma_data_id = 216956352)
  • Filter: ((validityrange @> CURRENT_DATE) AND (transaction_time @> CURRENT_TIMESTAMP))
  • Buffers: shared hit=4
28. 3.787 3.787 ↓ 15.0 15 1

Index Scan using risk_entity_to_axcurve_base_pk on risk_entity_to_axcurve_base reta (cost=0.55..2.78 rows=1 width=51) (actual time=1.129..3.787 rows=15 loops=1)

  • Index Cond: ((risk_entity_id = t.risk_entity_id) AND (currency = t.currency) AND ((pricing_tier)::text = (t.pricing_tier)::text) AND ((peer_type)::text = 'Cluster'::text))
  • Filter: (upper(transaction_time) = '9999-12-31 00:00:00+00'::timestamp with time zone)
  • Buffers: shared hit=3 read=17
  • I/O Timings: read=3.513
29. 34.950 34.950 ↑ 1.0 1 15

Index Only Scan using curve_subscription_pk on curve_subscription cs (cost=0.41..789.01 rows=1 width=20) (actual time=1.839..2.330 rows=1 loops=15)

  • Index Cond: (axioma_data_id = 216956352)
  • Heap Fetches: 15
  • Buffers: shared hit=4500
30. 0.000 0.012 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2 (cost=27.65..827.79 rows=1 width=144) (actual time=0.012..0.012 rows=0 loops=1)

  • Buffers: shared hit=2
31. 0.000 0.012 ↓ 0.0 0 1

Nested Loop (cost=27.65..827.78 rows=1 width=144) (actual time=0.012..0.012 rows=0 loops=1)

  • Join Filter: (ccu.cluster_id = t_1.cluster_id)
  • Buffers: shared hit=2
32. 0.000 0.012 ↓ 0.0 0 1

Nested Loop (cost=0.70..791.52 rows=1 width=44) (actual time=0.012..0.012 rows=0 loops=1)

  • Buffers: shared hit=2
33. 0.012 0.012 ↓ 0.0 0 1

Index Scan using xc_cluster_curve_universe_pk on xc_cluster_curve_universe ccu (cost=0.28..2.50 rows=1 width=28) (actual time=0.012..0.012 rows=0 loops=1)

  • Index Cond: (axioma_data_id = 216956352)
  • Buffers: shared hit=2
34. 0.000 0.000 ↓ 0.0 0

Index Only Scan using curve_subscription_pk on curve_subscription cs_1 (cost=0.41..789.01 rows=1 width=20) (never executed)

  • Index Cond: (axioma_data_id = 216956352)
  • Heap Fetches: 0
35. 0.000 0.000 ↓ 0.0 0

Unique (cost=26.96..28.65 rows=338 width=976) (never executed)

36. 0.000 0.000 ↓ 0.0 0

Sort (cost=26.96..27.80 rows=338 width=976) (never executed)

  • Sort Key: t_1.cluster_id, t_1.transaction_time DESC
37. 0.000 0.000 ↓ 0.0 0

Seq Scan on xc_cluster_base t_1 (cost=0.00..12.76 rows=338 width=976) (never executed)

  • Filter: ((validityrange @> CURRENT_DATE) AND (transaction_time @> CURRENT_TIMESTAMP))
38. 1,597.200 1,597.200 ↑ 2.0 2 13,200

Index Scan using xc_curve_ie1 on xc_curve ci (cost=0.57..6.06 rows=4 width=721) (actual time=0.051..0.121 rows=2 loops=13,200)

  • Index Cond: ((curve_date = cd.curve_date) AND (axioma_data_id = 216956352))
  • Buffers: shared hit=58380 read=5338
  • I/O Timings: read=1466.712
39. 163.792 163.792 ↑ 1.3 3 40,948

Index Scan using xc_curve_ie1 on xc_curve cc (cost=0.57..6.06 rows=4 width=721) (actual time=0.003..0.004 rows=3 loops=40,948)

  • Index Cond: ((curve_date = cd.curve_date) AND (axioma_data_id = "*SELECT* 1".axioma_data_id_cluster))
  • Buffers: shared hit=239266
Planning time : 2.167 ms
Execution time : 2,390.259 ms