explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9Vw

Settings
# exclusive inclusive rows x rows loops node
1. 161.195 12,565.862 ↓ 46.5 93,778 1

GroupAggregate (cost=1,163,982.85..1,164,043.36 rows=2,017 width=98) (actual time=12,373.276..12,565.862 rows=93,778 loops=1)

  • Group Key: ev.entity_config_uuid, ef.name, ev.company_id, ev.entity_uuid, (CASE WHEN (((COALESCE(ev.number, referenced_value.number))::text) IS NOT NULL) THEN 5 WHEN ((COALESCE(ev.text, referenced_value.text)) IS NOT NULL) THEN 0 WHEN (((COALESCE(ev.date (...)
2. 223.490 12,404.667 ↓ 53.1 107,150 1

Sort (cost=1,163,982.85..1,163,987.89 rows=2,017 width=322) (actual time=12,373.250..12,404.667 rows=107,150 loops=1)

  • Sort Key: ef.name, ev.entity_uuid, (CASE WHEN (((COALESCE(ev.number, referenced_value.number))::text) IS NOT NULL) THEN 5 WHEN ((COALESCE(ev.text, referenced_value.text)) IS NOT NULL) THEN 0 WHEN (((COALESCE(ev.date, referenced_value.date))::text) (...)
  • Sort Method: quicksort Memory: 21,476kB
3. 54.356 12,181.177 ↓ 53.1 107,150 1

Result (cost=1,163,090.58..1,163,872.13 rows=2,017 width=322) (actual time=11,718.792..12,181.177 rows=107,150 loops=1)

  • One-Time Filter: (get_company_id(current_setting('gan.company_token'::text, true)) = 546)
4. 63.816 12,126.821 ↓ 53.1 107,150 1

Hash Join (cost=1,163,090.58..1,163,872.13 rows=2,017 width=318) (actual time=11,718.736..12,126.821 rows=107,150 loops=1)

  • Hash Cond: (ev.entity_field_uuid = ef.uuid)
5. 52.122 12,059.859 ↓ 3.2 107,150 1

Hash Left Join (cost=1,162,231.49..1,162,868.37 rows=33,201 width=308) (actual time=11,715.577..12,059.859 rows=107,150 loops=1)

  • Hash Cond: (referenced_reference_value.workflow_status_uuid = referenced_workflow_status.uuid)
6. 49.588 12,007.541 ↓ 3.2 107,150 1

Hash Left Join (cost=1,162,142.97..1,162,692.68 rows=33,201 width=308) (actual time=11,715.371..12,007.541 rows=107,150 loops=1)

  • Hash Cond: (referenced_reference_value.document_uuid = referenced_document.uuid)
7. 53.914 11,921.917 ↓ 3.2 107,150 1

Hash Left Join (cost=1,160,917.58..1,161,380.15 rows=33,201 width=326) (actual time=11,679.327..11,921.917 rows=107,150 loops=1)

  • Hash Cond: (referenced_reference_value.select_option_uuid = referenced_select_option.uuid)
8. 50.470 11,863.223 ↓ 3.2 107,150 1

Hash Left Join (cost=1,158,432.61..1,158,808.02 rows=33,201 width=329) (actual time=11,674.534..11,863.223 rows=107,150 loops=1)

  • Hash Cond: (erv.document_uuid = doc.uuid)
9. 98.700 11,776.018 ↓ 3.2 107,150 1

Merge Left Join (cost=1,157,207.23..1,157,495.49 rows=33,201 width=311) (actual time=11,637.784..11,776.018 rows=107,150 loops=1)

  • Merge Cond: ((erv.referenced_entity_field_uuid = referenced_value.entity_field_uuid) AND (erv.referenced_entity_uuid = referenced_value.entity_uuid))
10. 109.138 4,877.120 ↓ 3.1 102,765 1

Sort (cost=732,641.20..732,724.20 rows=33,201 width=262) (actual time=4,852.621..4,877.120 rows=102,765 loops=1)

  • Sort Key: erv.referenced_entity_field_uuid, erv.referenced_entity_uuid
  • Sort Method: quicksort Memory: 19,601kB
11. 108.262 4,767.982 ↓ 3.1 102,765 1

Nested Loop Left Join (cost=245,324.20..730,147.98 rows=33,201 width=262) (actual time=1,901.940..4,767.982 rows=102,765 loops=1)

12. 68.064 4,454.190 ↓ 3.1 102,765 1

Hash Left Join (cost=245,312.99..316,947.62 rows=33,201 width=251) (actual time=1,901.930..4,454.190 rows=102,765 loops=1)

  • Hash Cond: (erv.select_option_uuid = so.uuid)
13. 59.151 4,357.852 ↓ 3.1 102,765 1

Hash Left Join (cost=242,828.01..314,375.50 rows=33,201 width=248) (actual time=1,873.648..4,357.852 rows=102,765 loops=1)

  • Hash Cond: (erv.workflow_status_uuid = ws.uuid)
14. 129.394 4,298.321 ↓ 3.1 102,765 1

Merge Left Join (cost=242,739.50..314,199.82 rows=33,201 width=248) (actual time=1,873.257..4,298.321 rows=102,765 loops=1)

  • Merge Cond: ((ev.entity_uuid = ecf.from_entity_uuid) AND (ev.entity_field_uuid = ecf.from_entity_field_uuid))
  • -> Index Scan using ux_entity_connection_from_and_to_entity_field on entity_connection ecf (cost=0.28..1781.19 rows=3,046 width=48) (actual time=0.310..62.302 rows=3,064 loops= (...)
15. 127.667 4,168.927 ↓ 3.1 102,765 1

Nested Loop Left Join (cost=242,739.21..312,237.38 rows=33,201 width=232) (actual time=1,872.942..4,168.927 rows=102,765 loops=1)

  • Filter: (company_id = get_company_id(current_setting('gan.company_token'::text, true)))
  • Rows Removed by Filter: 2,433
16. 57.378 2,191.490 ↓ 3.1 102,765 1

Merge Join (cost=242,738.79..253,721.33 rows=33,201 width=152) (actual time=1,872.931..2,191.490 rows=102,765 loops=1)

  • Merge Cond: (ev.entity_uuid = e.uuid)
17. 72.151 1,373.490 ↓ 2.3 102,765 1

Merge Left Join (cost=242,722.50..243,077.36 rows=44,268 width=152) (actual time=1,267.031..1,373.490 rows=102,765 loops=1)

  • Merge Cond: ((ev.entity_uuid = ect.to_entity_uuid) AND (ev.entity_field_uuid = ect.to_entity_field_uuid))
18. 122.090 1,247.238 ↓ 2.3 102,765 1

Sort (cost=240,915.81..241,026.48 rows=44,268 width=136) (actual time=1,214.098..1,247.238 rows=102,765 loops=1)

  • Sort Key: ev.entity_uuid, ev.entity_field_uuid
  • Sort Method: quicksort Memory: 18,702kB
19. 1,125.148 1,125.148 ↓ 2.3 102,765 1

Bitmap Heap Scan on entity_value ev (cost=7,888.15..237,499.65 rows=44,268 width=136) (actual time=600.456..1,125.148 rows=102,765 loops=1)

  • Recheck Cond: (NOT deleted)
  • Filter: ((company_id = 546) AND (entity_config_uuid = '6051f0e3-9941-463e-bb7b-505892af51ec'::uuid))
  • Rows Removed by Filter: 401,695
  • Heap Blocks: exact=22,388
  • -> Bitmap Index Scan on ux_entity_reference_value_for_field_in_entity (cost=0.00..7877.08 rows=431,112 width=0) (actual time=94.506..94.506 rows= (...)
20. 2.837 54.101 ↓ 1.0 3,064 1

Sort (cost=1,806.69..1,814.31 rows=3,046 width=48) (actual time=52.926..54.101 rows=3,064 loops=1)

  • Sort Key: ect.to_entity_uuid, ect.to_entity_field_uuid
  • Sort Method: quicksort Memory: 336kB
21. 51.264 51.264 ↓ 1.0 3,064 1

Seq Scan on entity_connection ect (cost=0.00..1,630.44 rows=3,046 width=48) (actual time=0.115..51.264 rows=3,064 loops=1)

  • Filter: ((NOT deleted) AND (company_id = get_company_id(current_setting('gan.company_token'::text, true))))
  • Rows Removed by Filter: 2,433
22. 157.234 760.622 ↑ 59.0 3,673 1

Index Only Scan using ux_entity_uuid on entity e (cost=11.21..9,659.17 rows=216,846 width=16) (actual time=605.895..760.622 rows=3,673 loops=1)

  • Filter: ((hashed SubPlan 1) OR (hashed SubPlan 2))
  • Rows Removed by Filter: 286,780
  • Heap Fetches: 41,142
23.          

SubPlan (for Index Only Scan)

24. 603.381 603.382 ↓ 3.7 3,675 1

ProjectSet (cost=0.00..5.27 rows=1,000 width=16) (actual time=602.522..603.382 rows=3,675 loops=1)

25. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=1)

26. 0.000 0.006 ↓ 0.0 0 1

Result (cost=0.00..2.77 rows=100 width=16) (actual time=0.006..0.006 rows=0 loops=1)

27. 0.005 0.006 ↓ 0.0 0 1

ProjectSet (cost=0.00..0.52 rows=100 width=32) (actual time=0.005..0.006 rows=0 loops=1)

28. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=1)

29. 1,849.770 1,849.770 ↑ 1.0 1 102,765

Index Scan using ux_entity_reference_value_uuid on entity_reference_value erv (cost=0.43..1.76 rows=1 width=112) (actual time=0.018..0.018 rows=1 loops=102,765)

  • Index Cond: (ev.entity_reference_value_uuid = uuid)
  • Filter: (company_id = get_company_id(current_setting('gan.company_token'::text, true)))
30. 0.019 0.380 ↑ 1.0 38 1

Hash (cost=88.04..88.04 rows=38 width=32) (actual time=0.380..0.380 rows=38 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
31. 0.361 0.361 ↑ 1.0 38 1

Index Scan using workflow_status_uuid_company_id_unique on workflow_status ws (cost=0.53..88.04 rows=38 width=32) (actual time=0.039..0.361 rows=38 loops=1)

  • Index Cond: (company_id = get_company_id(current_setting('gan.company_token'::text, true)))
32. 1.257 28.274 ↑ 1.0 2,857 1

Hash (cost=2,449.26..2,449.26 rows=2,857 width=35) (actual time=28.274..28.274 rows=2,857 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 400kB
33. 27.017 27.017 ↑ 1.0 2,857 1

Index Scan using c_select_option_position_unique on select_option so (cost=0.67..2,449.26 rows=2,857 width=35) (actual time=0.050..27.017 rows=2,857 loops=1)

  • Index Cond: (company_id = get_company_id(current_setting('gan.company_token'::text, true)))
34. 118.422 205.530 ↓ 0.0 0 102,765

Index Scan using employee_uuid_unique on employee em (cost=11.22..12.45 rows=1 width=43) (actual time=0.002..0.002 rows=0 loops=102,765)

  • Index Cond: (erv.employee_uuid = uuid)
  • Filter: ((hashed SubPlan 3) OR (hashed SubPlan 4))
35.          

SubPlan (for Index Scan)

36. 87.106 87.108 ↑ 1.9 531 1

ProjectSet (cost=0.00..5.27 rows=1,000 width=16) (actual time=86.978..87.108 rows=531 loops=1)

37. 0.002 0.002 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.002 rows=1 loops=1)

38. 0.000 0.000 ↓ 0.0 0

Result (cost=0.00..2.77 rows=100 width=16) (never executed)

39. 0.000 0.000 ↓ 0.0 0

ProjectSet (cost=0.00..0.52 rows=100 width=32) (never executed)

40. 0.000 0.000 ↓ 0.0 0

Result (cost=0.00..0.01 rows=1 width=0) (never executed)

41. 220.072 6,800.198 ↓ 21.2 110,832 1

Sort (cost=424,566.01..424,579.10 rows=5,234 width=175) (actual time=6,769.649..6,800.198 rows=110,832 loops=1)

  • Sort Key: referenced_value.entity_field_uuid, referenced_value.entity_uuid
  • Sort Method: quicksort Memory: 23,853kB
42. 131.337 6,580.126 ↓ 26.2 137,303 1

Nested Loop Left Join (cost=7,890.04..424,242.72 rows=5,234 width=175) (actual time=1,433.064..6,580.126 rows=137,303 loops=1)

43. 215.922 6,311.486 ↓ 26.2 137,303 1

Nested Loop Left Join (cost=7,878.82..359,103.39 rows=5,234 width=164) (actual time=1,384.356..6,311.486 rows=137,303 loops=1)

44. 4,678.569 4,722.534 ↓ 26.2 137,303 1

Bitmap Heap Scan on entity_value referenced_value (cost=7,878.39..345,267.89 rows=5,234 width=116) (actual time=1,384.290..4,722.534 rows=137,303 loops=1)

  • Recheck Cond: (NOT deleted)
  • Filter: (company_id = get_company_id(current_setting('gan.company_token'::text, true)))
  • Rows Removed by Filter: 367,157
  • Heap Blocks: exact=22,388
45. 43.965 43.965 ↓ 1.4 604,780 1

Bitmap Index Scan on ux_entity_reference_value_for_field_in_entity (cost=0.00..7,877.08 rows=431,112 width=0) (actual time=43.965..43.965 rows=604,780 loops=1)

46. 1,373.030 1,373.030 ↑ 1.0 1 137,303

Index Scan using ux_entity_reference_value_uuid on entity_reference_value referenced_reference_value (cost=0.43..2.64 rows=1 width=80) (actual time=0.010..0.010 rows=1 loops=137,303)

  • Index Cond: (uuid = referenced_value.entity_reference_value_uuid)
  • Filter: (company_id = get_company_id(current_setting('gan.company_token'::text, true)))
47. 88.849 137.303 ↓ 0.0 0 137,303

Index Scan using employee_uuid_unique on employee referenced_employee (cost=11.22..12.45 rows=1 width=43) (actual time=0.001..0.001 rows=0 loops=137,303)

  • Index Cond: (uuid = referenced_reference_value.employee_uuid)
  • Filter: ((hashed SubPlan 5) OR (hashed SubPlan 6))
48.          

SubPlan (for Index Scan)

49. 48.453 48.454 ↑ 1.9 531 1

ProjectSet (cost=0.00..5.27 rows=1,000 width=16) (actual time=48.325..48.454 rows=531 loops=1)

50. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=1)

51. 0.000 0.000 ↓ 0.0 0

Result (cost=0.00..2.77 rows=100 width=16) (never executed)

52. 0.000 0.000 ↓ 0.0 0

ProjectSet (cost=0.00..0.52 rows=100 width=32) (never executed)

53. 0.000 0.000 ↓ 0.0 0

Result (cost=0.00..0.01 rows=1 width=0) (never executed)

54. 0.070 36.735 ↑ 1.0 121 1

Hash (cost=1,223.86..1,223.86 rows=122 width=50) (actual time=36.734..36.735 rows=121 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 19kB
55. 36.665 36.665 ↑ 1.0 121 1

Seq Scan on document doc (cost=0.00..1,223.86 rows=122 width=50) (actual time=20.232..36.665 rows=121 loops=1)

  • Filter: (company_id = get_company_id(current_setting('gan.company_token'::text, true)))
  • Rows Removed by Filter: 3,999
56. 1.133 4.780 ↑ 1.0 2,857 1

Hash (cost=2,449.26..2,449.26 rows=2,857 width=35) (actual time=4.780..4.780 rows=2,857 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 400kB
57. 3.647 3.647 ↑ 1.0 2,857 1

Index Scan using c_select_option_position_unique on select_option referenced_select_option (cost=0.67..2,449.26 rows=2,857 width=35) (actual time=0.039..3.647 rows=2,857 loops=1)

  • Index Cond: (company_id = get_company_id(current_setting('gan.company_token'::text, true)))
58. 0.066 36.036 ↑ 1.0 121 1

Hash (cost=1,223.86..1,223.86 rows=122 width=50) (actual time=36.036..36.036 rows=121 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 19kB
59. 35.970 35.970 ↑ 1.0 121 1

Seq Scan on document referenced_document (cost=0.00..1,223.86 rows=122 width=50) (actual time=19.976..35.970 rows=121 loops=1)

  • Filter: (company_id = get_company_id(current_setting('gan.company_token'::text, true)))
  • Rows Removed by Filter: 3,999
60. 0.018 0.196 ↑ 1.0 38 1

Hash (cost=88.04..88.04 rows=38 width=32) (actual time=0.196..0.196 rows=38 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
61. 0.178 0.178 ↑ 1.0 38 1

Index Scan using workflow_status_uuid_company_id_unique on workflow_status referenced_workflow_status (cost=0.53..88.04 rows=38 width=32) (actual time=0.031..0.178 rows=38 loops=1)

  • Index Cond: (company_id = get_company_id(current_setting('gan.company_token'::text, true)))
62. 0.185 3.146 ↓ 1.1 401 1

Hash (cost=854.24..854.24 rows=368 width=42) (actual time=3.146..3.146 rows=401 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 46kB
63. 0.179 2.961 ↓ 1.1 401 1

Subquery Scan on ef (cost=0.54..854.24 rows=368 width=42) (actual time=0.054..2.961 rows=401 loops=1)

64. 2.782 2.782 ↓ 1.1 401 1

Index Scan using entity_field_uuid_company_id_unique on entity_field (cost=0.54..850.56 rows=368 width=320) (actual time=0.053..2.782 rows=401 loops=1)

  • Index Cond: (company_id = get_company_id(current_setting('gan.company_token'::text, true)))
  • Filter: (reportable AND (NOT deleted))
  • Rows Removed by Filter: 82
Planning time : 101.276 ms
Execution time : 12,588.359 ms