explain.depesz.com

PostgreSQL's explain analyze made readable

Result: nEP1

Settings
# exclusive inclusive rows x rows loops node
1. 55.470 2,821.496 ↑ 1.0 1 1

Aggregate (cost=894.89..894.90 rows=1 width=64) (actual time=2,821.489..2,821.496 rows=1 loops=1)

2.          

CTE required_attributes

3. 16.090 16.090 ↓ 44.0 2,069 1

Seq Scan on t_attribute a (cost=0.00..75.25 rows=47 width=59) (actual time=0.066..16.090 rows=2,069 loops=1)

  • Filter: ((c_attribute_value_jsonb ->> 'type'::text) = ANY ('{partNumber,quantity,descriptionEn,descriptionDe}'::text[]))
  • Rows Removed by Filter: 177
4.          

CTE attributes

5. 54.846 429.081 ↓ 133.6 1,870 1

HashAggregate (cost=213.76..213.97 rows=14 width=131) (actual time=411.716..429.081 rows=1,870 loops=1)

  • Group Key: e.c_entity_key
6.          

Initplan (for HashAggregate)

7. 0.029 0.100 ↑ 2.0 1 1

Bitmap Heap Scan on t_entity (cost=18.02..20.81 rows=2 width=8) (actual time=0.092..0.100 rows=1 loops=1)

  • Recheck Cond: (c_entity_key @> '{"key": {"Vin": "6BV4200512WSB1212"}, "type": "vehicle"}'::jsonb)
  • Heap Blocks: exact=1
8. 0.071 0.071 ↑ 2.0 1 1

Bitmap Index Scan on idx_entity_key_gin (cost=0.00..18.01 rows=2 width=0) (actual time=0.071..0.071 rows=1 loops=1)

  • Index Cond: (c_entity_key @> '{"key": {"Vin": "6BV4200512WSB1212"}, "type": "vehicle"}'::jsonb)
9. 69.829 374.135 ↓ 267.1 3,740 1

Hash Join (cost=23.39..192.81 rows=14 width=131) (actual time=92.642..374.135 rows=3,740 loops=1)

  • Hash Cond: (ea.c_linked_attrib_id = a_1.c_attribute_id)
10. 111.474 241.574 ↓ 8.1 5,610 1

Nested Loop (cost=21.86..188.55 rows=692 width=107) (actual time=29.852..241.574 rows=5,610 loops=1)

11. 28.981 72.130 ↓ 8.0 1,870 1

Hash Join (cost=21.58..88.19 rows=235 width=115) (actual time=29.815..72.130 rows=1,870 loops=1)

  • Hash Cond: (e.c_entity_id = t_entity_to_entity_link.c_linked_entity_id)
12. 14.951 14.951 ↓ 1.0 1,994 1

Seq Scan on t_entity e (cost=0.00..56.83 rows=1,983 width=107) (actual time=0.011..14.951 rows=1,994 loops=1)

13. 13.994 28.198 ↓ 8.0 1,870 1

Hash (cost=18.64..18.64 rows=235 width=8) (actual time=28.198..28.198 rows=1,870 loops=1)

  • Buckets: 2,048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 104kB
14. 14.204 14.204 ↓ 8.0 1,870 1

Index Only Scan using idx_entity_link_pk on t_entity_to_entity_link (cost=0.28..18.64 rows=235 width=8) (actual time=0.140..14.204 rows=1,870 loops=1)

  • Index Cond: (c_entity_id = $1)
  • Heap Fetches: 1,870
15. 57.970 57.970 ↑ 1.0 3 1,870

Index Only Scan using t_entity_to_attrib_link_c_entity_id_idx on t_entity_to_attrib_link ea (cost=0.28..0.40 rows=3 width=16) (actual time=0.009..0.031 rows=3 loops=1,870)

  • Index Cond: (c_entity_id = e.c_entity_id)
  • Heap Fetches: 5,610
16. 15.450 62.732 ↓ 44.0 2,069 1

Hash (cost=0.94..0.94 rows=47 width=40) (actual time=62.732..62.732 rows=2,069 loops=1)

  • Buckets: 4,096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 229kB
17. 47.282 47.282 ↓ 44.0 2,069 1

CTE Scan on required_attributes a_1 (cost=0.00..0.94 rows=47 width=40) (actual time=0.082..47.282 rows=2,069 loops=1)

18.          

CTE required_history

19. 1.026 1.026 ↓ 4.2 50 1

Seq Scan on t_attribute a_2 (cost=0.00..69.35 rows=12 width=59) (actual time=0.108..1.026 rows=50 loops=1)

  • Filter: ((c_attribute_value_jsonb ->> 'type'::text) = 'quantity'::text)
  • Rows Removed by Filter: 2,196
20.          

CTE attributes_with_history

21. 48.198 348.648 ↓ 467.5 1,870 1

HashAggregate (cost=212.46..212.53 rows=4 width=147) (actual time=331.012..348.648 rows=1,870 loops=1)

  • Group Key: e_1.c_entity_key, (a_3.c_attribute_value_jsonb ->> 'type'::text)
22.          

Initplan (for HashAggregate)

23. 0.028 0.092 ↑ 2.0 1 1

Bitmap Heap Scan on t_entity t_entity_1 (cost=18.02..20.81 rows=2 width=8) (actual time=0.084..0.092 rows=1 loops=1)

  • Recheck Cond: (c_entity_key @> '{"key": {"Vin": "6BV4200512WSB1212"}, "type": "vehicle"}'::jsonb)
  • Heap Blocks: exact=1
24. 0.064 0.064 ↑ 2.0 1 1

Bitmap Index Scan on idx_entity_key_gin (cost=0.00..18.01 rows=2 width=0) (actual time=0.064..0.064 rows=1 loops=1)

  • Index Cond: (c_entity_key @> '{"key": {"Vin": "6BV4200512WSB1212"}, "type": "vehicle"}'::jsonb)
25. 56.279 300.358 ↓ 467.5 1,870 1

Hash Join (cost=22.25..191.58 rows=4 width=147) (actual time=32.066..300.358 rows=1,870 loops=1)

  • Hash Cond: (ea_1.c_linked_attrib_id = a_3.c_attribute_id)
26. 111.727 241.900 ↓ 8.1 5,610 1

Nested Loop (cost=21.86..188.55 rows=692 width=123) (actual time=29.826..241.900 rows=5,610 loops=1)

27. 29.148 72.203 ↓ 8.0 1,870 1

Hash Join (cost=21.58..88.19 rows=235 width=115) (actual time=29.789..72.203 rows=1,870 loops=1)

  • Hash Cond: (e_1.c_entity_id = t_entity_to_entity_link_1.c_linked_entity_id)
28. 14.880 14.880 ↓ 1.0 1,994 1

Seq Scan on t_entity e_1 (cost=0.00..56.83 rows=1,983 width=107) (actual time=0.010..14.880 rows=1,994 loops=1)

29. 13.996 28.175 ↓ 8.0 1,870 1

Hash (cost=18.64..18.64 rows=235 width=8) (actual time=28.175..28.175 rows=1,870 loops=1)

  • Buckets: 2,048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 104kB
30. 14.179 14.179 ↓ 8.0 1,870 1

Index Only Scan using idx_entity_link_pk on t_entity_to_entity_link t_entity_to_entity_link_1 (cost=0.28..18.64 rows=235 width=8) (actual time=0.129..14.179 rows=1,870 loops=1)

  • Index Cond: (c_entity_id = $5)
  • Heap Fetches: 1,870
31. 57.970 57.970 ↑ 1.0 3 1,870

Index Scan using t_entity_to_attrib_link_c_entity_id_idx on t_entity_to_attrib_link ea_1 (cost=0.28..0.40 rows=3 width=32) (actual time=0.009..0.031 rows=3 loops=1,870)

  • Index Cond: (c_entity_id = e_1.c_entity_id)
32. 0.392 2.179 ↓ 4.2 50 1

Hash (cost=0.24..0.24 rows=12 width=40) (actual time=2.179..2.179 rows=50 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 13kB
33. 1.787 1.787 ↓ 4.2 50 1

CTE Scan on required_history a_3 (cost=0.00..0.24 rows=12 width=40) (actual time=0.124..1.787 rows=50 loops=1)

34.          

CTE attributes_history

35. 42.142 419.037 ↓ 467.5 1,870 1

HashAggregate (cost=0.10..0.15 rows=4 width=96) (actual time=403.752..419.037 rows=1,870 loops=1)

  • Group Key: attributes_with_history.c_entity_key
36. 376.895 376.895 ↓ 467.5 1,870 1

CTE Scan on attributes_with_history (cost=0.00..0.08 rows=4 width=96) (actual time=331.030..376.895 rows=1,870 loops=1)

37.          

CTE final_attributes_with_history

38. 35.154 956.954 ↓ 467.5 1,870 1

Hash Join (cost=0.13..0.53 rows=4 width=96) (actual time=875.238..956.954 rows=1,870 loops=1)

  • Hash Cond: (a_4.c_entity_key = ah.c_entity_key)
39. 458.361 458.361 ↓ 133.6 1,870 1

CTE Scan on attributes a_4 (cost=0.00..0.28 rows=14 width=64) (actual time=411.733..458.361 rows=1,870 loops=1)

40. 15.698 463.439 ↓ 467.5 1,870 1

Hash (cost=0.08..0.08 rows=4 width=64) (actual time=463.439..463.439 rows=1,870 loops=1)

  • Buckets: 2,048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 557kB
41. 447.741 447.741 ↓ 467.5 1,870 1

CTE Scan on attributes_history ah (cost=0.00..0.08 rows=4 width=64) (actual time=403.769..447.741 rows=1,870 loops=1)

42.          

CTE required_inherited_attributes

43. 15.871 15.871 ↓ 57.7 2,019 1

Seq Scan on t_attribute a_5 (cost=0.00..72.30 rows=35 width=59) (actual time=0.104..15.871 rows=2,019 loops=1)

  • Filter: ((c_attribute_value_jsonb ->> 'type'::text) = ANY ('{partNumber,descriptionEn,descriptionDe}'::text[]))
  • Rows Removed by Filter: 227
44.          

CTE link_attributes

45. 55.722 358.488 ↓ 170.0 1,870 1

Hash Join (cost=43.81..213.20 rows=11 width=107) (actual time=91.502..358.488 rows=1,870 loops=1)

  • Hash Cond: (ea_2.c_linked_attrib_id = a_6.c_attribute_id)
46.          

Initplan (for Hash Join)

47. 0.029 0.097 ↑ 2.0 1 1

Bitmap Heap Scan on t_entity t_entity_2 (cost=18.02..20.81 rows=2 width=8) (actual time=0.090..0.097 rows=1 loops=1)

  • Recheck Cond: (c_entity_key @> '{"key": {"Vin": "6BV4200512WSB1212"}, "type": "vehicle"}'::jsonb)
  • Heap Blocks: exact=1
48. 0.068 0.068 ↑ 2.0 1 1

Bitmap Index Scan on idx_entity_key_gin (cost=0.00..18.01 rows=2 width=0) (actual time=0.068..0.068 rows=1 loops=1)

  • Index Cond: (c_entity_key @> '{"key": {"Vin": "6BV4200512WSB1212"}, "type": "vehicle"}'::jsonb)
49. 111.051 241.128 ↓ 8.1 5,610 1

Nested Loop (cost=21.86..188.55 rows=692 width=107) (actual time=29.869..241.128 rows=5,610 loops=1)

50. 29.056 72.107 ↓ 8.0 1,870 1

Hash Join (cost=21.58..88.19 rows=235 width=115) (actual time=29.832..72.107 rows=1,870 loops=1)

  • Hash Cond: (e_2.c_entity_id = t_entity_to_entity_link_2.c_linked_entity_id)
51. 14.894 14.894 ↓ 1.0 1,994 1

Seq Scan on t_entity e_2 (cost=0.00..56.83 rows=1,983 width=107) (actual time=0.011..14.894 rows=1,994 loops=1)

52. 14.022 28.157 ↓ 8.0 1,870 1

Hash (cost=18.64..18.64 rows=235 width=8) (actual time=28.157..28.157 rows=1,870 loops=1)

  • Buckets: 2,048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 104kB
53. 14.135 14.135 ↓ 8.0 1,870 1

Index Only Scan using idx_entity_link_pk on t_entity_to_entity_link t_entity_to_entity_link_2 (cost=0.28..18.64 rows=235 width=8) (actual time=0.137..14.135 rows=1,870 loops=1)

  • Index Cond: (c_entity_id = $11)
  • Heap Fetches: 1,870
54. 57.970 57.970 ↑ 1.0 3 1,870

Index Only Scan using t_entity_to_attrib_link_c_entity_id_idx on t_entity_to_attrib_link ea_2 (cost=0.28..0.40 rows=3 width=16) (actual time=0.009..0.031 rows=3 loops=1,870)

  • Index Cond: (c_entity_id = e_2.c_entity_id)
  • Heap Fetches: 5,610
55. 15.210 61.541 ↓ 57.7 2,019 1

Hash (cost=0.70..0.70 rows=35 width=8) (actual time=61.541..61.541 rows=2,019 loops=1)

  • Buckets: 2,048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 95kB
56. 46.331 46.331 ↓ 57.7 2,019 1

CTE Scan on required_inherited_attributes a_6 (cost=0.00..0.70 rows=35 width=8) (actual time=0.127..46.331 rows=2,019 loops=1)

57.          

CTE inherited_entity

58. 0.489 0.525 ↓ 30.5 61 1

Bitmap Heap Scan on t_entity t_entity_3 (cost=9.02..11.81 rows=2 width=8) (actual time=0.056..0.525 rows=61 loops=1)

  • Recheck Cond: (c_entity_key @> '{"type": "partMaster"}'::jsonb)
  • Heap Blocks: exact=2
59. 0.036 0.036 ↓ 30.5 61 1

Bitmap Index Scan on idx_entity_key_gin (cost=0.00..9.01 rows=2 width=0) (actual time=0.036..0.036 rows=61 loops=1)

  • Index Cond: (c_entity_key @> '{"type": "partMaster"}'::jsonb)
60.          

CTE inherited_entity_id

61. 3.632 8.804 ↓ 30.5 183 1

Nested Loop (cost=0.33..9.53 rows=6 width=16) (actual time=1.968..8.804 rows=183 loops=1)

62. 0.915 3.281 ↓ 30.5 61 1

Unique (cost=0.05..0.06 rows=2 width=8) (actual time=1.933..3.281 rows=61 loops=1)

63. 0.919 2.366 ↓ 30.5 61 1

Sort (cost=0.05..0.06 rows=2 width=8) (actual time=1.917..2.366 rows=61 loops=1)

  • Sort Key: inherited_entity.c_entity_id
  • Sort Method: quicksort Memory: 27kB
64. 1.447 1.447 ↓ 30.5 61 1

CTE Scan on inherited_entity (cost=0.00..0.04 rows=2 width=8) (actual time=0.071..1.447 rows=61 loops=1)

65. 1.891 1.891 ↑ 1.0 3 61

Index Only Scan using t_entity_to_attrib_link_c_entity_id_idx on t_entity_to_attrib_link ea_3 (cost=0.28..4.70 rows=3 width=16) (actual time=0.009..0.031 rows=3 loops=61)

  • Index Cond: (c_entity_id = inherited_entity.c_entity_id)
  • Heap Fetches: 183
66.          

CTE distinct_entity_key

67. 30.229 457.786 ↓ 170.0 1,870 1

HashAggregate (cost=0.60..0.71 rows=11 width=40) (actual time=443.210..457.786 rows=1,870 loops=1)

  • Group Key: iei.c_entity_id, la.c_entity_key, la.c_entity_key
68. 27.928 427.557 ↓ 170.0 1,870 1

Hash Left Join (cost=0.20..0.52 rows=11 width=40) (actual time=104.477..427.557 rows=1,870 loops=1)

  • Hash Cond: (la.link_attribute_id = iei.c_linked_attrib_id)
69. 386.701 386.701 ↓ 170.0 1,870 1

CTE Scan on link_attributes la (cost=0.00..0.22 rows=11 width=40) (actual time=91.520..386.701 rows=1,870 loops=1)

70. 1.383 12.928 ↓ 30.5 183 1

Hash (cost=0.12..0.12 rows=6 width=16) (actual time=12.928..12.928 rows=183 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 17kB
71. 11.545 11.545 ↓ 30.5 183 1

CTE Scan on inherited_entity_id iei (cost=0.00..0.12 rows=6 width=16) (actual time=1.983..11.545 rows=183 loops=1)

72.          

CTE final_required_inherited_attributes

73. 37.390 1,669.197 ↓ 170.0 1,870 1

HashAggregate (cost=14.80..14.97 rows=11 width=64) (actual time=1,652.065..1,669.197 rows=1,870 loops=1)

  • Group Key: dek."mainEntityKey
74. 43.699 1,631.807 ↓ 57.0 1,938 1

Nested Loop Left Join (cost=4.09..14.38 rows=34 width=64) (actual time=443.305..1,631.807 rows=1,938 loops=1)

75. 486.678 486.678 ↓ 170.0 1,870 1

CTE Scan on distinct_entity_key dek (cost=0.00..0.22 rows=11 width=40) (actual time=443.228..486.678 rows=1,870 loops=1)

76. 544.714 1,101.430 ↓ 0.0 0 1,870

Hash Right Join (cost=4.09..4.98 rows=3 width=40) (actual time=0.065..0.589 rows=0 loops=1,870)

  • Hash Cond: (ra.c_attribute_id = ea_4.c_linked_attrib_id)
77. 511.836 511.836 ↓ 57.7 2,019 34

CTE Scan on required_inherited_attributes ra (cost=0.00..0.70 rows=35 width=40) (actual time=0.008..15.054 rows=2,019 loops=34)

78. 29.920 44.880 ↓ 0.0 0 1,870

Hash (cost=4.06..4.06 rows=3 width=16) (actual time=0.024..0.024 rows=0 loops=1,870)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
79. 14.960 14.960 ↓ 0.0 0 1,870

Index Only Scan using t_entity_to_attrib_link_c_entity_id_idx on t_entity_to_attrib_link ea_4 (cost=0.28..4.06 rows=3 width=16) (actual time=0.008..0.008 rows=0 loops=1,870)

  • Index Cond: (c_entity_id = dek.c_entity_id)
  • Heap Fetches: 102
80.          

CTE final_result

81. 35.679 2,736.422 ↓ 467.5 1,870 1

Hash Join (cost=0.13..0.47 rows=4 width=96) (actual time=2,654.447..2,736.422 rows=1,870 loops=1)

  • Hash Cond: (ria."mainEntityKey" = ah_1.c_entity_key)
82. 1,698.435 1,698.435 ↓ 170.0 1,870 1

CTE Scan on final_required_inherited_attributes ria (cost=0.00..0.22 rows=11 width=64) (actual time=1,652.083..1,698.435 rows=1,870 loops=1)

83. 15.891 1,002.308 ↓ 467.5 1,870 1

Hash (cost=0.08..0.08 rows=4 width=64) (actual time=1,002.308..1,002.308 rows=1,870 loops=1)

  • Buckets: 2,048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 656kB
84. 986.417 986.417 ↓ 467.5 1,870 1

CTE Scan on final_attributes_with_history ah_1 (cost=0.00..0.08 rows=4 width=64) (actual time=875.255..986.417 rows=1,870 loops=1)

85. 2,766.026 2,766.026 ↓ 467.5 1,870 1

CTE Scan on final_result fr (cost=0.00..0.08 rows=4 width=64) (actual time=2,654.467..2,766.026 rows=1,870 loops=1)

Planning time : 4.026 ms
Execution time : 2,822.937 ms