explain.depesz.com

PostgreSQL's explain analyze made readable

Result: oxpF

Settings
# exclusive inclusive rows x rows loops node
1. 121.986 1,899.820 ↓ 4,517.0 4,517 1

Sort (cost=1,725.04..1,725.04 rows=1 width=185) (actual time=1,852.561..1,899.820 rows=4,517 loops=1)

  • Sort Key: restapi_padded_curves_structured.curve_date
  • Sort Method: external sort Disk: 6328kB
2. 1.606 1,777.834 ↓ 4,517.0 4,517 1

Subquery Scan on restapi_padded_curves_structured (cost=1,724.95..1,725.03 rows=1 width=185) (actual time=1,669.953..1,777.834 rows=4,517 loops=1)

3. 67.862 1,776.228 ↓ 4,517.0 4,517 1

GroupAggregate (cost=1,724.95..1,725.02 rows=1 width=185) (actual time=1,669.952..1,776.228 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. 179.182 1,708.366 ↓ 4,517.0 4,517 1

Sort (cost=1,724.95..1,724.96 rows=1 width=157) (actual time=1,669.918..1,708.366 rows=4,517 loops=1)

  • Sort Key: c.curve_short_name, c.curve_date
  • Sort Method: external sort Disk: 6176kB
5. 3.847 1,529.184 ↓ 4,517.0 4,517 1

Subquery Scan on c (cost=1,724.92..1,724.94 rows=1 width=157) (actual time=1,400.359..1,529.184 rows=4,517 loops=1)

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

Unique (cost=1,724.92..1,724.93 rows=1 width=183) (actual time=1,400.355..1,525.337 rows=4,517 loops=1)

7. 323.742 1,517.321 ↓ 9,034.0 9,034 1

Sort (cost=1,724.92..1,724.92 rows=1 width=183) (actual time=1,400.353..1,517.321 rows=9,034 loops=1)

  • Sort Key: (((generate_series((($0))::timestamp with time zone, (($1))::timestamp with time zone, '1 day'::interval)))::date), (COALESCE(ci.category, cc.category)), (COALESCE(ci.version, cc.version)), "*SELECT* 1".transaction_time DESC
  • Sort Method: external sort Disk: 12568kB
8. 541.266 1,193.579 ↓ 9,034.0 9,034 1

Nested Loop Left Join (cost=6.44..1,724.91 rows=1 width=183) (actual time=67.123..1,193.579 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.category, cc.category))::text = 'lc'::text) AND (COALESCE(ci.version, cc.version) = 5))
  • Rows Removed by Filter: 40738
9. 38.897 324.729 ↓ 10,237.0 40,948 1

Nested Loop Left Join (cost=5.86..1,700.08 rows=4 width=788) (actual time=4.115..324.729 rows=40,948 loops=1)

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

Nested Loop (cost=5.29..1,693.97 rows=1 width=75) (actual time=4.089..153.832 rows=13,200 loops=1)

  • Join Filter: (("*SELECT* 1".validity_range @> (((generate_series((($0))::timestamp with time zone, (($1))::timestamp with time zone, '1 day'::interval)))::date)) AND ("*SELECT* 1".validityrange @> (((generate_series((($0))::timestamp with time zone, (($1))::timestamp with time zone, '1 day'::interval)))::date)))
  • Rows Removed by Join Filter: 85800
11. 5.042 17.269 ↓ 6.6 6,600 1

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

12.          

Initplan (for Result)

13. 0.002 0.044 ↑ 1.0 1 1

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

14. 0.042 0.042 ↑ 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.042..0.042 rows=1 loops=1)

  • Index Cond: (curve_date IS NOT NULL)
  • Heap Fetches: 0
15. 0.001 0.024 ↑ 1.0 1 1

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

16. 0.023 0.023 ↑ 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.023..0.023 rows=1 loops=1)

  • Index Cond: (curve_date IS NOT NULL)
  • Heap Fetches: 1
17. 12.088 12.159 ↓ 6.6 6,600 1

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

18. 0.071 0.071 ↑ 1.0 1 1

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

19. 12.415 79.200 ↓ 7.5 15 6,600

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

20. 0.006 66.785 ↓ 7.5 15 1

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

21. 0.009 66.756 ↓ 15.0 15 1

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

22. 0.042 66.747 ↓ 15.0 15 1

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

23. 0.017 0.255 ↓ 15.0 15 1

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

24. 0.001 0.115 ↑ 1.0 1 1

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

25. 0.093 0.114 ↑ 1.0 1 1

Sort (cost=2.65..2.66 rows=1 width=379) (actual time=0.113..0.114 rows=1 loops=1)

  • Sort Key: t.transaction_time DESC
  • Sort Method: quicksort Memory: 25kB
26. 0.021 0.021 ↑ 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.019..0.021 rows=1 loops=1)

  • Index Cond: (axioma_data_id = 216956352)
  • Filter: ((validityrange @> CURRENT_DATE) AND (transaction_time @> CURRENT_TIMESTAMP))
27. 0.123 0.123 ↓ 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.037..0.123 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)
28. 66.450 66.450 ↑ 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=3.519..4.430 rows=1 loops=15)

  • Index Cond: (axioma_data_id = 216956352)
  • Heap Fetches: 15
29. 0.002 0.023 ↓ 0.0 0 1

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

30. 0.000 0.021 ↓ 0.0 0 1

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

  • Join Filter: (ccu.cluster_id = t_1.cluster_id)
31. 0.000 0.021 ↓ 0.0 0 1

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

32. 0.021 0.021 ↓ 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.021..0.021 rows=0 loops=1)

  • Index Cond: (axioma_data_id = 216956352)
33. 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
34. 0.000 0.000 ↓ 0.0 0

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

35. 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
36. 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))
37. 132.000 132.000 ↑ 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.009..0.010 rows=2 loops=13,200)

  • Index Cond: ((curve_date = (((generate_series((($0))::timestamp with time zone, (($1))::timestamp with time zone, '1 day'::interval)))::date)) AND (axioma_data_id = 216956352))
38. 327.584 327.584 ↑ 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.007..0.008 rows=3 loops=40,948)

  • Index Cond: ((curve_date = (((generate_series((($0))::timestamp with time zone, (($1))::timestamp with time zone, '1 day'::interval)))::date)) AND (axioma_data_id = "*SELECT* 1".axioma_data_id_cluster))