explain.depesz.com

PostgreSQL's explain analyze made readable

Result: UhU3v

Settings
# exclusive inclusive rows x rows loops node
1. 85.666 1,442.692 ↓ 4,517.0 4,517 1

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

  • Sort Key: restapi_padded_curves_structured.curve_date
  • Sort Method: external sort Disk: 6328kB
2. 1.531 1,357.026 ↓ 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=1,265.909..1,357.026 rows=4,517 loops=1)

3. 56.676 1,355.495 ↓ 4,517.0 4,517 1

GroupAggregate (cost=1,704.15..1,704.22 rows=1 width=185) (actual time=1,265.908..1,355.495 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
4. 129.230 1,298.819 ↓ 4,517.0 4,517 1

Sort (cost=1,704.15..1,704.16 rows=1 width=157) (actual time=1,265.871..1,298.819 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
5. 10.594 1,169.589 ↓ 4,517.0 4,517 1

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

  • Filter: ((lower((c.client)::text) = 'axioma'::text) AND (lower((c.subscription_name)::text) = 'all'::text))
6. 4.774 1,158.995 ↓ 4,517.0 4,517 1

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

7. 204.140 1,154.221 ↓ 9,034.0 9,034 1

Sort (cost=1,704.11..1,704.12 rows=1 width=183) (actual time=1,094.481..1,154.221 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
8. 428.333 950.081 ↓ 9,034.0 9,034 1

Nested Loop Left Join (cost=6.44..1,704.10 rows=1 width=183) (actual time=38.012..950.081 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
9. 27.375 235.112 ↓ 10,237.0 40,948 1

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

  • Join Filter: (ci.axioma_data_id = "*SELECT* 1".axioma_data_id)
10. 41.120 102.137 ↓ 13,200.0 13,200 1

Nested Loop (cost=5.29..1,673.13 rows=1 width=75) (actual time=2.236..102.137 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
11. 1.942 14.817 ↓ 19.8 6,600 1

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

  • Filter: (cd.curve_date <= '2020-02-05'::date)
12. 3.091 12.875 ↓ 6.6 6,600 1

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

13.          

Initplan (for Result)

14. 0.001 0.023 ↑ 1.0 1 1

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

15. 0.022 0.022 ↑ 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.021..0.022 rows=1 loops=1)

  • Index Cond: (curve_date IS NOT NULL)
  • Heap Fetches: 0
16. 0.000 0.011 ↑ 1.0 1 1

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

17. 0.011 0.011 ↑ 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.011..0.011 rows=1 loops=1)

  • Index Cond: (curve_date IS NOT NULL)
  • Heap Fetches: 1
18. 9.714 9.750 ↓ 6.6 6,600 1

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

19. 0.036 0.036 ↑ 1.0 1 1

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

20. 8.377 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)

21. 0.003 37.823 ↓ 7.5 15 1

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

22. 0.004 37.796 ↓ 15.0 15 1

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

23. 0.015 37.792 ↓ 15.0 15 1

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

24. 0.004 0.097 ↓ 15.0 15 1

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

25. 0.002 0.024 ↑ 1.0 1 1

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

26. 0.006 0.022 ↑ 1.0 1 1

Sort (cost=2.65..2.66 rows=1 width=379) (actual time=0.021..0.022 rows=1 loops=1)

  • Sort Key: t.transaction_time DESC
  • Sort Method: quicksort Memory: 25kB
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))
28. 0.069 0.069 ↓ 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.025..0.069 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)
29. 37.680 37.680 ↑ 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.992..2.512 rows=1 loops=15)

  • Index Cond: (axioma_data_id = 216956352)
  • Heap Fetches: 15
30. 0.000 0.024 ↓ 0.0 0 1

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

31. 0.000 0.024 ↓ 0.0 0 1

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

  • Join Filter: (ccu.cluster_id = t_1.cluster_id)
32. 0.001 0.024 ↓ 0.0 0 1

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

33. 0.023 0.023 ↓ 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.023..0.023 rows=0 loops=1)

  • Index Cond: (axioma_data_id = 216956352)
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. 105.600 105.600 ↑ 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.007..0.008 rows=2 loops=13,200)

  • Index Cond: ((curve_date = cd.curve_date) AND (axioma_data_id = 216956352))
39. 286.636 286.636 ↑ 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.005..0.007 rows=3 loops=40,948)

  • Index Cond: ((curve_date = cd.curve_date) AND (axioma_data_id = "*SELECT* 1".axioma_data_id_cluster))
Planning time : 2.345 ms
Execution time : 1,454.524 ms