explain.depesz.com

PostgreSQL's explain analyze made readable

Result: yTMa

Settings
# exclusive inclusive rows x rows loops node
1. 0.053 1.755 ↑ 2,656.0 2 1

HashAggregate (cost=527.09..580.21 rows=5,312 width=68) (actual time=1.729..1.755 rows=2 loops=1)

  • Group Key: customer.cutoff_date, customer.identifier, account.account_number, COALESCE(customer_relation.target_customer, customer.identifier), COALESCE(customer_relation.is_economic_leader, 0), customer.category, customer.date_of_birth, facility.amount, installment.avg_monthly_repayment
2. 0.004 1.702 ↑ 885.3 6 1

Nested Loop (cost=89.55..394.29 rows=5,312 width=68) (actual time=1.605..1.702 rows=6 loops=1)

3. 0.006 1.464 ↑ 664.0 2 1

Nested Loop Left Join (cost=89.13..286.65 rows=1,328 width=50) (actual time=1.374..1.464 rows=2 loops=1)

4. 0.018 1.160 ↑ 4.0 2 1

Hash Left Join (cost=89.13..137.21 rows=8 width=46) (actual time=1.105..1.160 rows=2 loops=1)

  • Hash Cond: ((customer.identifier = customer_relation.source_customer) AND ((SubPlan 4) = customer_relation.cutoff_date))
5. 0.001 0.087 ↑ 2.0 1 1

Append (cost=0.00..2.79 rows=2 width=34) (actual time=0.085..0.087 rows=1 loops=1)

6. 0.007 0.007 ↓ 0.0 0 1

Seq Scan on customer (cost=0.00..0.00 rows=1 width=48) (actual time=0.007..0.007 rows=0 loops=1)

  • Filter: ((category IS NOT NULL) AND (identifier = 57,292,326) AND (cutoff_date = '2020-06-30'::date))
7. 0.079 0.079 ↑ 1.0 1 1

Index Scan using data_customer_2020_06_cutoff_date_identifier_key on data_customer_2020_06 (cost=0.57..2.79 rows=1 width=19) (actual time=0.077..0.079 rows=1 loops=1)

  • Index Cond: ((cutoff_date = '2020-06-30'::date) AND (identifier = 57,292,326))
  • Filter: (category IS NOT NULL)
8. 0.017 0.806 ↓ 2.3 56 1

Hash (cost=88.77..88.77 rows=24 width=24) (actual time=0.806..0.806 rows=56 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
9. 0.007 0.789 ↓ 2.3 56 1

Append (cost=0.00..88.77 rows=24 width=24) (actual time=0.061..0.789 rows=56 loops=1)

10. 0.002 0.002 ↓ 0.0 0 1

Seq Scan on customer_relation (cost=0.00..0.00 rows=1 width=24) (actual time=0.002..0.002 rows=0 loops=1)

  • Filter: ((source_customer = 57,292,326) AND (relation_type_code = 'GCO'::text))
11. 0.061 0.061 ↓ 2.0 2 1

Index Scan using data_customer_relation_2019_1_source_customer_cutoff_date_c_idx on data_customer_relation_2019_10 (cost=0.56..7.22 rows=1 width=24) (actual time=0.059..0.061 rows=2 loops=1)

  • Index Cond: (source_customer = 57,292,326)
  • Filter: (relation_type_code = 'GCO'::text)
  • Rows Removed by Filter: 2
12. 0.071 0.071 ↓ 4.0 4 1

Index Scan using data_customer_relation_2019_1_source_customer_cutoff_date__idx1 on data_customer_relation_2019_11 (cost=0.56..10.61 rows=1 width=24) (actual time=0.058..0.071 rows=4 loops=1)

  • Index Cond: (source_customer = 57,292,326)
  • Filter: (relation_type_code = 'GCO'::text)
  • Rows Removed by Filter: 4
13. 0.051 0.051 ↓ 2.0 2 1

Index Scan using data_customer_relation_2019_1_source_customer_cutoff_date__idx2 on data_customer_relation_2019_12 (cost=0.43..4.80 rows=1 width=24) (actual time=0.050..0.051 rows=2 loops=1)

  • Index Cond: (source_customer = 57,292,326)
  • Filter: (relation_type_code = 'GCO'::text)
  • Rows Removed by Filter: 2
14. 0.036 0.036 ↓ 0.0 0 1

Index Scan using data_customer_relation_2020_0_source_customer_cutoff_date_c_idx on data_customer_relation_2020_01 (cost=0.43..3.16 rows=1 width=24) (actual time=0.036..0.036 rows=0 loops=1)

  • Index Cond: (source_customer = 57,292,326)
  • Filter: (relation_type_code = 'GCO'::text)
15. 0.075 0.075 ↓ 4.0 4 1

Index Scan using data_customer_relation_2020_0_source_customer_cutoff_date__idx1 on data_customer_relation_2020_02 (cost=0.56..9.35 rows=1 width=24) (actual time=0.059..0.075 rows=4 loops=1)

  • Index Cond: (source_customer = 57,292,326)
  • Filter: (relation_type_code = 'GCO'::text)
  • Rows Removed by Filter: 4
16. 0.074 0.074 ↓ 4.0 4 1

Index Scan using data_customer_relation_2020_0_source_customer_cutoff_date__idx2 on data_customer_relation_2020_03 (cost=0.56..9.36 rows=1 width=24) (actual time=0.060..0.074 rows=4 loops=1)

  • Index Cond: (source_customer = 57,292,326)
  • Filter: (relation_type_code = 'GCO'::text)
  • Rows Removed by Filter: 4
17. 0.063 0.063 ↓ 4.0 4 1

Index Scan using data_customer_relation_2020_0_source_customer_cutoff_date__idx3 on data_customer_relation_2020_04 (cost=0.56..13.92 rows=1 width=24) (actual time=0.050..0.063 rows=4 loops=1)

  • Index Cond: (source_customer = 57,292,326)
  • Filter: (relation_type_code = 'GCO'::text)
  • Rows Removed by Filter: 4
18. 0.118 0.118 ↓ 3.0 12 1

Index Scan using data_customer_relation_2020_0_source_customer_cutoff_date__idx4 on data_customer_relation_2020_05 (cost=0.56..11.44 rows=4 width=24) (actual time=0.060..0.118 rows=12 loops=1)

  • Index Cond: (source_customer = 57,292,326)
  • Filter: (relation_type_code = 'GCO'::text)
  • Rows Removed by Filter: 2
19. 0.117 0.117 ↓ 3.0 12 1

Index Scan using data_customer_relation_2020_0_source_customer_cutoff_date__idx5 on data_customer_relation_2020_06 (cost=0.56..7.23 rows=4 width=24) (actual time=0.061..0.117 rows=12 loops=1)

  • Index Cond: (source_customer = 57,292,326)
  • Filter: (relation_type_code = 'GCO'::text)
20. 0.114 0.114 ↓ 1.5 12 1

Index Scan using data_customer_relation_2020_0_source_customer_cutoff_date__idx6 on data_customer_relation_2020_07 (cost=0.56..11.68 rows=8 width=24) (actual time=0.058..0.114 rows=12 loops=1)

  • Index Cond: (source_customer = 57,292,326)
  • Filter: (relation_type_code = 'GCO'::text)
21.          

SubPlan (for Hash Left Join)

22. 0.009 0.249 ↑ 1.0 1 3

Result (cost=6.44..6.45 rows=1 width=4) (actual time=0.083..0.083 rows=1 loops=3)

23.          

Initplan (for Result)

24. 0.003 0.240 ↑ 1.0 1 3

Limit (cost=5.65..6.44 rows=1 width=4) (actual time=0.080..0.080 rows=1 loops=3)

25. 0.027 0.237 ↑ 69.0 1 3

Merge Append (cost=5.65..60.13 rows=69 width=4) (actual time=0.079..0.079 rows=1 loops=3)

  • Sort Key: customer_relation_1.cutoff_date DESC
26. 0.006 0.006 ↓ 0.0 0 3

Index Only Scan Backward using indx_data_customer_relation_source_customer on customer_relation customer_relation_1 (cost=0.12..2.34 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=3)

  • Index Cond: ((source_customer = customer.identifier) AND (cutoff_date IS NOT NULL))
  • Heap Fetches: 0
27. 0.018 0.018 ↑ 5.0 1 3

Index Only Scan Backward using data_customer_relation_2019_1_source_customer_cutoff_date_c_idx on data_customer_relation_2019_10 data_customer_relation_2019_10_1 (cost=0.56..7.22 rows=5 width=4) (actual time=0.006..0.006 rows=1 loops=3)

  • Index Cond: ((source_customer = customer.identifier) AND (cutoff_date IS NOT NULL))
  • Heap Fetches: 3
28. 0.015 0.015 ↑ 8.0 1 3

Index Only Scan Backward using data_customer_relation_2019_1_source_customer_cutoff_date__idx1 on data_customer_relation_2019_11 data_customer_relation_2019_11_1 (cost=0.56..10.61 rows=8 width=4) (actual time=0.005..0.005 rows=1 loops=3)

  • Index Cond: ((source_customer = customer.identifier) AND (cutoff_date IS NOT NULL))
  • Heap Fetches: 3
29. 0.012 0.012 ↑ 4.0 1 3

Index Only Scan Backward using data_customer_relation_2019_1_source_customer_cutoff_date__idx2 on data_customer_relation_2019_12 data_customer_relation_2019_12_1 (cost=0.43..4.80 rows=4 width=4) (actual time=0.004..0.004 rows=1 loops=3)

  • Index Cond: ((source_customer = customer.identifier) AND (cutoff_date IS NOT NULL))
  • Heap Fetches: 3
30. 0.027 0.027 ↓ 0.0 0 3

Index Only Scan Backward using data_customer_relation_2020_0_source_customer_cutoff_date_c_idx on data_customer_relation_2020_01 data_customer_relation_2020_01_1 (cost=0.43..3.16 rows=2 width=4) (actual time=0.009..0.009 rows=0 loops=3)

  • Index Cond: ((source_customer = customer.identifier) AND (cutoff_date IS NOT NULL))
  • Heap Fetches: 0
31. 0.015 0.015 ↑ 7.0 1 3

Index Only Scan Backward using data_customer_relation_2020_0_source_customer_cutoff_date__idx1 on data_customer_relation_2020_02 data_customer_relation_2020_02_1 (cost=0.56..9.35 rows=7 width=4) (actual time=0.005..0.005 rows=1 loops=3)

  • Index Cond: ((source_customer = customer.identifier) AND (cutoff_date IS NOT NULL))
  • Heap Fetches: 3
32. 0.015 0.015 ↑ 7.0 1 3

Index Only Scan Backward using data_customer_relation_2020_0_source_customer_cutoff_date__idx2 on data_customer_relation_2020_03 data_customer_relation_2020_03_1 (cost=0.56..9.36 rows=7 width=4) (actual time=0.005..0.005 rows=1 loops=3)

  • Index Cond: ((source_customer = customer.identifier) AND (cutoff_date IS NOT NULL))
  • Heap Fetches: 3
33. 0.024 0.024 ↑ 11.0 1 3

Index Only Scan Backward using data_customer_relation_2020_0_source_customer_cutoff_date__idx3 on data_customer_relation_2020_04 data_customer_relation_2020_04_1 (cost=0.56..2.98 rows=11 width=4) (actual time=0.008..0.008 rows=1 loops=3)

  • Index Cond: ((source_customer = customer.identifier) AND (cutoff_date IS NOT NULL))
  • Heap Fetches: 0
34. 0.027 0.027 ↑ 10.0 1 3

Index Only Scan Backward using data_customer_relation_2020_0_source_customer_cutoff_date__idx4 on data_customer_relation_2020_05 data_customer_relation_2020_05_1 (cost=0.56..2.96 rows=10 width=4) (actual time=0.009..0.009 rows=1 loops=3)

  • Index Cond: ((source_customer = customer.identifier) AND (cutoff_date IS NOT NULL))
  • Heap Fetches: 0
35. 0.027 0.027 ↑ 5.0 1 3

Index Only Scan Backward using data_customer_relation_2020_0_source_customer_cutoff_date__idx5 on data_customer_relation_2020_06 data_customer_relation_2020_06_1 (cost=0.56..2.85 rows=5 width=4) (actual time=0.009..0.009 rows=1 loops=3)

  • Index Cond: ((source_customer = customer.identifier) AND (cutoff_date IS NOT NULL))
  • Heap Fetches: 0
36. 0.024 0.024 ↑ 9.0 1 3

Index Only Scan Backward using data_customer_relation_2020_0_source_customer_cutoff_date__idx6 on data_customer_relation_2020_07 data_customer_relation_2020_07_1 (cost=0.56..2.94 rows=9 width=4) (actual time=0.008..0.008 rows=1 loops=3)

  • Index Cond: ((source_customer = customer.identifier) AND (cutoff_date IS NOT NULL))
  • Heap Fetches: 0
37. 0.010 0.298 ↓ 0.0 0 2

Append (cost=0.00..18.60 rows=8 width=12) (actual time=0.149..0.149 rows=0 loops=2)

38. 0.002 0.002 ↓ 0.0 0 2

Seq Scan on customer_default (cost=0.00..0.00 rows=1 width=12) (actual time=0.001..0.001 rows=0 loops=2)

  • Filter: ((cutoff_date >= '2019-06-30'::date) AND (cutoff_date <= '2020-06-30'::date) AND (customer_identifier = 57,292,326) AND (customer.identifier = customer_identifier))
39. 0.046 0.046 ↓ 0.0 0 2

Index Only Scan using data_customer_default_2019_10_customer_identifier_cutoff_da_idx on data_customer_default_2019_10 (cost=0.43..2.66 rows=1 width=12) (actual time=0.023..0.023 rows=0 loops=2)

  • Index Cond: ((customer_identifier = customer.identifier) AND (customer_identifier = 57,292,326) AND (cutoff_date >= '2019-06-30'::date) AND (cutoff_date <= '2020-06-30'::date))
  • Heap Fetches: 0
40. 0.034 0.034 ↓ 0.0 0 2

Index Only Scan using data_customer_default_2019_09_customer_identifier_cutoff_da_idx on data_customer_default_2019_09 (cost=0.43..2.66 rows=1 width=12) (actual time=0.017..0.017 rows=0 loops=2)

  • Index Cond: ((customer_identifier = customer.identifier) AND (customer_identifier = 57,292,326) AND (cutoff_date >= '2019-06-30'::date) AND (cutoff_date <= '2020-06-30'::date))
  • Heap Fetches: 0
41. 0.042 0.042 ↓ 0.0 0 2

Index Only Scan using data_customer_default_2019_08_customer_identifier_cutoff_da_idx on data_customer_default_2019_08 (cost=0.43..2.66 rows=1 width=12) (actual time=0.021..0.021 rows=0 loops=2)

  • Index Cond: ((customer_identifier = customer.identifier) AND (customer_identifier = 57,292,326) AND (cutoff_date >= '2019-06-30'::date) AND (cutoff_date <= '2020-06-30'::date))
  • Heap Fetches: 0
42. 0.038 0.038 ↓ 0.0 0 2

Index Only Scan using data_customer_default_2019_07_customer_identifier_cutoff_da_idx on data_customer_default_2019_07 (cost=0.43..2.66 rows=1 width=12) (actual time=0.019..0.019 rows=0 loops=2)

  • Index Cond: ((customer_identifier = customer.identifier) AND (customer_identifier = 57,292,326) AND (cutoff_date >= '2019-06-30'::date) AND (cutoff_date <= '2020-06-30'::date))
  • Heap Fetches: 0
43. 0.038 0.038 ↓ 0.0 0 2

Index Only Scan using data_customer_default_2019_06_customer_identifier_cutoff_da_idx on data_customer_default_2019_06 (cost=0.43..2.66 rows=1 width=12) (actual time=0.019..0.019 rows=0 loops=2)

  • Index Cond: ((customer_identifier = 57,292,326) AND (cutoff_date >= '2019-06-30'::date) AND (cutoff_date <= '2020-06-30'::date))
  • Filter: (customer.identifier = customer_identifier)
  • Heap Fetches: 0
44. 0.048 0.048 ↓ 0.0 0 2

Index Only Scan using data_customer_default_2019_11_customer_identifier_cutoff_da_idx on data_customer_default_2019_11 (cost=0.43..2.66 rows=1 width=12) (actual time=0.024..0.024 rows=0 loops=2)

  • Index Cond: ((customer_identifier = customer.identifier) AND (customer_identifier = 57,292,326) AND (cutoff_date >= '2019-06-30'::date) AND (cutoff_date <= '2020-06-30'::date))
  • Heap Fetches: 0
45. 0.040 0.040 ↓ 0.0 0 2

Index Only Scan using data_customer_default_2019_12_customer_identifier_cutoff_da_idx on data_customer_default_2019_12 (cost=0.43..2.65 rows=1 width=12) (actual time=0.020..0.020 rows=0 loops=2)

  • Index Cond: ((customer_identifier = customer.identifier) AND (customer_identifier = 57,292,326) AND (cutoff_date >= '2019-06-30'::date) AND (cutoff_date <= '2020-06-30'::date))
  • Heap Fetches: 0
46. 0.007 0.234 ↑ 1.3 3 2

Materialize (cost=0.42..41.25 rows=4 width=30) (actual time=0.114..0.117 rows=3 loops=2)

47. 0.005 0.227 ↑ 1.3 3 1

Nested Loop Left Join (cost=0.42..41.23 rows=4 width=30) (actual time=0.223..0.227 rows=3 loops=1)

48. 0.003 0.162 ↑ 3.0 1 1

Nested Loop Left Join (cost=0.42..31.04 rows=3 width=25) (actual time=0.161..0.162 rows=1 loops=1)

49. 0.002 0.070 ↑ 3.0 1 1

Append (cost=0.00..15.14 rows=3 width=20) (actual time=0.069..0.070 rows=1 loops=1)

50. 0.002 0.002 ↓ 0.0 0 1

Seq Scan on account (cost=0.00..0.00 rows=1 width=20) (actual time=0.002..0.002 rows=0 loops=1)

  • Filter: ((account_type = ANY ('{C1,C2}'::t_account_type[])) AND (owning_customer_identifier = 57,292,326) AND (cutoff_date = '2020-06-30'::date))
51. 0.066 0.066 ↑ 2.0 1 1

Index Scan using data_account_2020_06_owning_customer_identifier_cutoff_date_idx on data_account_2020_06 (cost=0.57..15.14 rows=2 width=20) (actual time=0.066..0.066 rows=1 loops=1)

  • Index Cond: ((owning_customer_identifier = 57,292,326) AND (cutoff_date = '2020-06-30'::date))
  • Filter: (account_type = ANY ('{C1,C2}'::t_account_type[]))
52. 0.047 0.089 ↑ 1.0 1 1

Index Scan using indx_data_installment_account_number on installment (cost=0.42..5.29 rows=1 width=17) (actual time=0.088..0.089 rows=1 loops=1)

  • Index Cond: (account.account_number = account_number)
  • Filter: (cutoff_date = (SubPlan 2))
53.          

SubPlan (for Index Scan)

54. 0.002 0.042 ↑ 1.0 1 1

Result (cost=2.64..2.65 rows=1 width=4) (actual time=0.042..0.042 rows=1 loops=1)

55.          

Initplan (for Result)

56. 0.001 0.040 ↑ 1.0 1 1

Limit (cost=0.42..2.64 rows=1 width=4) (actual time=0.040..0.040 rows=1 loops=1)

57. 0.039 0.039 ↑ 1.0 1 1

Index Only Scan Backward using indx_data_installment_account_number_cutoff_date on installment installment_1 (cost=0.42..2.64 rows=1 width=4) (actual time=0.039..0.039 rows=1 loops=1)

  • Index Cond: ((account_number = account.account_number) AND (cutoff_date IS NOT NULL))
  • Heap Fetches: 1
58. 0.002 0.060 ↑ 1.0 3 1

Append (cost=0.00..3.37 rows=3 width=17) (actual time=0.058..0.060 rows=3 loops=1)

59. 0.002 0.002 ↓ 0.0 0 1

Seq Scan on facility (cost=0.00..0.00 rows=1 width=44) (actual time=0.002..0.002 rows=0 loops=1)

  • Filter: ((cutoff_date = '2020-06-30'::date) AND (account.cutoff_date = cutoff_date) AND (account.account_number = account))
60. 0.056 0.056 ↓ 1.5 3 1

Index Scan using idx_partition_data_facility_2020_06_account on data_facility_2020_06 (cost=0.43..3.37 rows=2 width=17) (actual time=0.055..0.056 rows=3 loops=1)

  • Index Cond: (account.account_number = account)
  • Filter: ((cutoff_date = '2020-06-30'::date) AND (account.cutoff_date = cutoff_date))
Planning time : 15.267 ms
Execution time : 2.134 ms