explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2coN

Settings
# exclusive inclusive rows x rows loops node
1. 37.041 660.197 ↓ 4,517.0 4,517 1

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

  • Sort Key: restapi_padded_curves_structured.curve_date
  • Sort Method: external sort Disk: 6328kB
  • Buffers: shared hit=307521, temp read=3134 written=3134
2. 0.610 623.156 ↓ 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=581.140..623.156 rows=4,517 loops=1)

  • Buffers: shared hit=307521, temp read=2343 written=2343
3. 27.363 622.546 ↓ 4,517.0 4,517 1

GroupAggregate (cost=1,704.15..1,704.22 rows=1 width=185) (actual time=581.138..622.546 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, temp read=2343 written=2343
4. 59.170 595.183 ↓ 4,517.0 4,517 1

Sort (cost=1,704.15..1,704.16 rows=1 width=157) (actual time=581.114..595.183 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=307521, temp read=2343 written=2343
5. 4.687 536.013 ↓ 4,517.0 4,517 1

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

  • Filter: ((lower((c.client)::text) = 'axioma'::text) AND (lower((c.subscription_name)::text) = 'all'::text))
  • Buffers: shared hit=307521, temp read=1571 written=1571
6. 2.192 531.326 ↓ 4,517.0 4,517 1

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

  • Buffers: shared hit=307521, temp read=1571 written=1571
7. 90.915 529.134 ↓ 9,034.0 9,034 1

Sort (cost=1,704.11..1,704.12 rows=1 width=183) (actual time=501.667..529.134 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=307521, temp read=1571 written=1571
8. 188.900 438.219 ↓ 9,034.0 9,034 1

Nested Loop Left Join (cost=6.44..1,704.10 rows=1 width=183) (actual time=28.455..438.219 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.797 126.475 ↓ 10,237.0 40,948 1

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

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

Nested Loop (cost=5.29..1,673.13 rows=1 width=75) (actual time=2.027..56.878 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.807 5.684 ↓ 19.8 6,600 1

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

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

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

  • Buffers: shared hit=11
13.          

Initplan (for Result)

14. 0.001 0.031 ↑ 1.0 1 1

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

  • Buffers: shared hit=5
15. 0.030 0.030 ↑ 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.030..0.030 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. 3.428 3.481 ↓ 6.6 6,600 1

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

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

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

  • Buffers: shared hit=11
20. 4.723 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 28.277 ↓ 7.5 15 1

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

  • Buffers: shared hit=4526
22. 0.004 28.262 ↓ 15.0 15 1

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

  • Buffers: shared hit=4524
23. 0.009 28.258 ↓ 15.0 15 1

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

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

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

  • Buffers: shared hit=24
25. 0.000 0.025 ↑ 1.0 1 1

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

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

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

  • Sort Key: t.transaction_time DESC
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=4
27. 0.018 0.018 ↑ 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.017..0.018 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.063 0.063 ↓ 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.028..0.063 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. 28.155 28.155 ↑ 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.490..1.877 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.001 0.012 ↓ 0.0 0 1

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

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

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

  • Buffers: shared hit=2
33. 0.011 0.011 ↓ 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.011..0.011 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.338 ms
Execution time : 667.363 ms