explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Bxpw

Settings
# exclusive inclusive rows x rows loops node
1. 7.161 510.450 ↓ 4,517.0 4,517 1

Sort (cost=1,704.24..1,704.24 rows=1 width=185) (actual time=510.357..510.450 rows=4,517 loops=1)

  • Sort Key: restapi_padded_curves_structured.curve_date
  • Sort Method: quicksort Memory: 9297kB
  • Buffers: shared hit=307521
2. 0.410 503.289 ↓ 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=477.899..503.289 rows=4,517 loops=1)

  • Buffers: shared hit=307521
3. 24.875 502.879 ↓ 4,517.0 4,517 1

GroupAggregate (cost=1,704.15..1,704.22 rows=1 width=185) (actual time=477.898..502.879 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=307521
4. 7.571 478.004 ↓ 4,517.0 4,517 1

Sort (cost=1,704.15..1,704.16 rows=1 width=157) (actual time=477.872..478.004 rows=4,517 loops=1)

  • Sort Key: c.client, c.subscription_name, c.curve_short_name, c.curve_date, c.category
  • Sort Method: quicksort Memory: 9297kB
  • Buffers: shared hit=307521
5. 3.169 470.433 ↓ 4,517.0 4,517 1

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

  • Filter: ((lower((c.client)::text) = 'axioma'::text) AND (lower((c.subscription_name)::text) = 'all'::text))
  • Buffers: shared hit=307521
6. 2.307 467.264 ↓ 4,517.0 4,517 1

Unique (cost=1,704.11..1,704.12 rows=1 width=183) (actual time=464.704..467.264 rows=4,517 loops=1)

  • Buffers: shared hit=307521
7. 26.875 464.957 ↓ 9,034.0 9,034 1

Sort (cost=1,704.11..1,704.12 rows=1 width=183) (actual time=464.704..464.957 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: quicksort Memory: 18594kB
  • Buffers: shared hit=307521
8. 189.472 438.082 ↓ 9,034.0 9,034 1

Nested Loop Left Join (cost=6.44..1,704.10 rows=1 width=183) (actual time=27.851..438.082 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=307521
9. 16.998 125.766 ↓ 10,237.0 40,948 1

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

  • Join Filter: (ci.axioma_data_id = "*SELECT* 1".axioma_data_id)
  • Buffers: shared hit=68255
10. 17.773 55.968 ↓ 13,200.0 13,200 1

Nested Loop (cost=5.29..1,673.13 rows=1 width=75) (actual time=1.988..55.968 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=4537
11. 0.718 5.195 ↓ 19.8 6,600 1

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

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

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

  • Buffers: shared hit=11
13.          

Initplan (for Result)

14. 0.001 0.030 ↑ 1.0 1 1

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

  • Buffers: shared hit=5
15. 0.029 0.029 ↑ 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.029..0.029 rows=1 loops=1)

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

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

  • Buffers: shared hit=6
17. 0.021 0.021 ↑ 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.021..0.021 rows=1 loops=1)

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

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

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

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

  • Buffers: shared hit=11
20. 5.317 33.000 ↓ 7.5 15 6,600

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

  • Buffers: shared hit=4526
21. 0.003 27.683 ↓ 7.5 15 1

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

  • Buffers: shared hit=4526
22. 0.003 27.674 ↓ 15.0 15 1

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

  • Buffers: shared hit=4524
23. 0.010 27.671 ↓ 15.0 15 1

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

  • Buffers: shared hit=4524
24. 0.006 0.091 ↓ 15.0 15 1

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

  • Buffers: shared hit=24
25. 0.001 0.024 ↑ 1.0 1 1

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

  • Buffers: shared hit=4
26. 0.007 0.023 ↑ 1.0 1 1

Sort (cost=2.65..2.66 rows=1 width=379) (actual time=0.022..0.023 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. 0.061 0.061 ↓ 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=0.026..0.061 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=20
29. 27.570 27.570 ↑ 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.466..1.838 rows=1 loops=15)

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

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

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

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

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

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

  • Buffers: shared hit=2
33. 0.007 0.007 ↓ 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.006..0.007 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. 52.800 52.800 ↑ 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.004..0.004 rows=2 loops=13,200)

  • Index Cond: ((curve_date = cd.curve_date) AND (axioma_data_id = 216956352))
  • Buffers: shared hit=63718
39. 122.844 122.844 ↑ 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.002..0.003 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.037 ms
Execution time : 516.109 ms