explain.depesz.com

PostgreSQL's explain analyze made readable

Result: wCZk

Settings
# exclusive inclusive rows x rows loops node
1. 2.703 647.485 ↑ 1.0 20 1

Limit (cost=240,302.60..240,302.65 rows=20 width=64) (actual time=644.779..647.485 rows=20 loops=1)

2. 1.097 644.782 ↑ 10.0 20 1

Sort (cost=240,302.60..240,303.10 rows=200 width=64) (actual time=644.777..644.782 rows=20 loops=1)

  • Sort Key: (max(filter_query.row_number))
  • Sort Method: top-N heapsort Memory: 408kB
3. 338.272 643.685 ↓ 2.3 455 1

GroupAggregate (cost=240,167.08..240,297.28 rows=200 width=64) (actual time=303.224..643.685 rows=455 loops=1)

  • Group Key: filter_query.uuid
4. 9.757 305.413 ↓ 1.1 11,508 1

Sort (cost=240,167.08..240,192.52 rows=10,176 width=6,370) (actual time=302.431..305.413 rows=11,508 loops=1)

  • Sort Key: filter_query.uuid
  • Sort Method: quicksort Memory: 3695kB
5. 11.964 295.656 ↓ 1.1 11,508 1

Subquery Scan on filter_query (cost=222,912.38..223,192.22 rows=10,176 width=6,370) (actual time=267.988..295.656 rows=11,508 loops=1)

6. 13.339 283.692 ↓ 1.1 11,508 1

WindowAgg (cost=222,912.38..223,090.46 rows=10,176 width=6,330) (actual time=267.973..283.692 rows=11,508 loops=1)

7.          

CTE entity

8. 0.435 0.435 ↓ 1.1 455 1

Index Scan using ix_entity_config on entity (cost=0.41..17.96 rows=414 width=16) (actual time=0.018..0.435 rows=455 loops=1)

  • Index Cond: (entity_config_uuid = '5bbba791-1638-4557-acdd-a812d38bded9'::uuid)
  • Filter: (company_id = 389)
9.          

CTE full

10. 17.058 237.613 ↓ 1.1 11,508 1

Sort (cost=205,466.96..205,492.40 rows=10,176 width=1,309) (actual time=234.965..237.613 rows=11,508 loops=1)

  • Sort Key: entity_value.created_at
  • Sort Method: quicksort Memory: 2764kB
11. 12.788 220.555 ↓ 1.1 11,508 1

Hash Join (cost=13,549.79..204,789.60 rows=10,176 width=1,309) (actual time=78.779..220.555 rows=11,508 loops=1)

  • Hash Cond: (entity_value.entity_uuid = entity_1.uuid)
12. 0.000 206.603 ↑ 1.8 11,508 1

Gather (cost=13,535.97..204,558.27 rows=20,352 width=1,245) (actual time=77.582..206.603 rows=11,508 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
13. 3.628 210.671 ↑ 2.2 3,836 3 / 3

Nested Loop Left Join (cost=12,535.97..201,523.07 rows=8,480 width=1,245) (actual time=70.827..210.671 rows=3,836 loops=3)

14. 3.918 203.207 ↑ 2.2 3,836 3 / 3

Parallel Hash Left Join (cost=12,535.55..196,666.63 rows=8,480 width=1,273) (actual time=70.812..203.207 rows=3,836 loops=3)

  • Hash Cond: (workflow_status_value.uuid = referenced_workflow_status_translation_unit.item_uuid)
15. 3.346 175.892 ↑ 2.2 3,836 3 / 3

Hash Left Join (cost=7,981.05..192,040.14 rows=8,480 width=1,208) (actual time=47.201..175.892 rows=3,836 loops=3)

  • Hash Cond: (referenced_reference_value.workflow_status_uuid = referenced_workflow_status.uuid)
16. 3.370 170.075 ↑ 2.2 3,836 3 / 3

Hash Left Join (cost=7,852.97..191,889.79 rows=8,480 width=1,054) (actual time=44.706..170.075 rows=3,836 loops=3)

  • Hash Cond: (referenced_reference_value.document_uuid = referenced_document.uuid)
17. 6.151 164.669 ↑ 2.2 3,836 3 / 3

Nested Loop Left Join (cost=7,739.07..191,753.63 rows=8,480 width=928) (actual time=42.653..164.669 rows=3,836 loops=3)

18. 6.070 158.518 ↑ 2.2 3,836 3 / 3

Nested Loop Left Join (cost=7,738.64..186,353.90 rows=8,480 width=885) (actual time=42.643..158.518 rows=3,836 loops=3)

  • -> Index Scan using ux_translation_key_locale on translation referenced_select_option_translation (cost=0.42..0.50 rows=1 width=102) (actual time=0.000..0.000 rows=0 (...)
19. 5.855 152.448 ↑ 2.2 3,836 3 / 3

Nested Loop Left Join (cost=7,738.22..182,050.86 rows=8,480 width=913) (actual time=42.633..152.448 rows=3,836 loops=3)

  • -> Index Scan using ux_translation_unit_item_uuid_item_type_item_property_type on translation_unit referenced_select_option_translation_unit (cost=0.42..0.61 ro (...)
  • Index Cond: (referenced_select_option_translation_unit.translation_key = translation_key)
20. 5.956 146.593 ↑ 1.3 3,836 3 / 3

Nested Loop Left Join (cost=7,737.80..179,016.92 rows=4,847 width=848) (actual time=42.624..146.593 rows=3,836 loops=3)

  • -> Index Scan using select_option_uuid_unique on select_option referenced_select_option (cost=0.29..0.31 rows=1 width=34) (actual time=0.000..0.000 rows=0 (...)
  • Index Cond: (item_uuid = referenced_select_option.uuid)
21. 5.853 140.637 ↑ 1.3 3,836 3 / 3

Nested Loop Left Join (cost=7,737.51..177,523.09 rows=4,847 width=830) (actual time=42.616..140.637 rows=3,836 loops=3)

  • -> Index Scan using ux_entity_reference_value_uuid on entity_reference_value referenced_reference_value (cost=0.43..0.82 rows=1 width=88) (actual ti (...)
  • Index Cond: (uuid = referenced_reference_value.select_option_uuid)
22. 6.307 134.784 ↑ 1.3 3,836 3 / 3

Nested Loop Left Join (cost=7,737.08..173,528.49 rows=4,847 width=758) (actual time=42.605..134.784 rows=3,836 loops=3)

  • -> Index Scan using ux_entity_reference_value_for_field_in_entity on entity_value referenced_value (cost=0.42..0.58 rows=1 width=132) (actual (...)
  • Index Cond: (uuid = referenced_value.entity_reference_value_uuid)
23. 7.205 128.477 ↑ 1.3 3,836 3 / 3

Nested Loop Left Join (cost=7,736.66..170,665.55 rows=4,847 width=674) (actual time=42.596..128.477 rows=3,836 loops=3)

  • Filter: ((workflow_status_translation.locale = 'en-US'::text) OR (workflow_status_translation.locale IS NULL))
  • -> Index Scan using ux_translation_key_locale on translation workflow_status_translation (cost=0.42..0.56 rows=1 width=102) (actual time (...)
  • Index Cond: ((entity_field_uuid = entity_reference_value.referenced_entity_field_uuid) AND (entity_uuid = entity_reference_value.reference (...)
24. 3.501 121.272 ↑ 1.6 3,836 3 / 3

Parallel Hash Left Join (cost=7,736.24..167,132.72 rows=6,142 width=702) (actual time=42.580..121.272 rows=3,836 loops=3)

  • Hash Cond: (workflow_status_value.uuid = workflow_status_translation_unit.item_uuid)
  • Index Cond: (workflow_status_translation_unit.translation_key = translation_key)
25. 12.547 93.899 ↑ 1.6 3,836 3 / 3

Nested Loop Left Join (cost=3,181.74..162,526.07 rows=6,142 width=637) (actual time=18.595..93.899 rows=3,836 loops=3)

  • Filter: ((select_option_translation.locale = 'en-US'::text) OR (select_option_translation.locale IS NULL))
  • -> Index Scan using ux_translation_key_locale on translation select_option_translation (cost=0.42..0.50 rows=1 width=102) (a (...)
26. 8.623 81.352 ↑ 2.0 3,836 3 / 3

Nested Loop Left Join (cost=3,181.32..158,557.26 rows=7,783 width=665) (actual time=18.584..81.352 rows=3,836 loops=3)

  • -> Index Scan using ux_translation_unit_item_uuid_item_type_item_property_type on translation_unit select_option_transl (...)
  • Index Cond: (select_option_translation_unit.translation_key = translation_key)
27. 3.085 72.729 ↑ 1.2 3,836 3 / 3

Hash Left Join (cost=3,180.90..155,772.44 rows=4,449 width=600) (actual time=18.574..72.729 rows=3,836 loops=3)

  • Hash Cond: (entity_reference_value.workflow_status_uuid = workflow_status_value.uuid)
  • Index Cond: (item_uuid = select_option.uuid)
28. 64.990 67.069 ↑ 1.2 3,836 3 / 3

Hash Left Join (cost=3,052.82..155,632.68 rows=4,449 width=446) (actual time=15.974..67.069 rows=3,836 loops=3)

  • Hash Cond: (entity_reference_value.document_uuid = document.uuid)
  • -> Nested Loop Left Join (cost=2938.92..155507.10 rows=4449 width=320) (actual time=13.876..61.861 rows=38 (...)
  • -> Nested Loop Left Join (cost=2938.49..152674.16 rows=4449 width=277) (actual time=13.868..54.274 r (...)
  • -> Nested Loop Left Join (cost=2938.20..151302.99 rows=4449 width=259) (actual time=13.859..46 (...)
  • -> Parallel Bitmap Heap Scan on entity_value (cost=2937.77..141541.62 rows=4449 width=15 (...)
  • Recheck Cond: (NOT deleted)
  • Filter: ((entity_config_uuid = '5bbba791-1638-4557-acdd-a812d38bded9'::uuid) AND (co (...)
  • Rows Removed by Filter: 58936
  • Heap Blocks: exact=2834
  • -> Bitmap Index Scan on ux_entity_reference_value_for_field_in_entity (cost=0.00.. (...)
  • -> Index Scan using ux_entity_reference_value_uuid on entity_reference_value (cost=0.43. (...)
  • Index Cond: (uuid = entity_value.entity_reference_value_uuid)
  • -> Index Scan using select_option_uuid_unique on select_option (cost=0.29..0.31 rows=1 width=3 (...)
  • Index Cond: (uuid = entity_reference_value.select_option_uuid)
  • -> Index Scan using employee_uuid_unique on employee user_picker_employee (cost=0.43..0.64 rows=1 wi (...)
  • Index Cond: (uuid = entity_reference_value.employee_uuid)
29. 2.079 2.079 ↓ 1.0 2,044 3 / 3

Hash (cost=88.40..88.40 rows=2,040 width=142) (actual time=2.079..2.079 rows=2,044 loops=3)

  • Buckets: 2048 Batches: 1 Memory Usage: 371kB
  • -> Seq Scan on document (cost=0.00..88.40 rows=2040 width=142) (actual time=0.012..0.947 rows=2044 l (...)
30. 2.575 2.575 ↓ 1.0 2,542 3 / 3

Hash (cost=96.37..96.37 rows=2,537 width=170) (actual time=2.575..2.575 rows=2,542 loops=3)

  • Buckets: 4096 Batches: 1 Memory Usage: 392kB
  • -> Seq Scan on workflow_status workflow_status_value (cost=0.00..96.37 rows=2537 width=170) (actual time=0 (...)
31. 23.872 23.872 ↑ 1.2 21,454 3 / 3

Parallel Hash (cost=4,221.53..4,221.53 rows=26,638 width=81) (actual time=23.872..23.872 rows=21,454 loops=3)

  • Buckets: 65536 Batches: 1 Memory Usage: 8160kB
  • -> Parallel Seq Scan on translation_unit workflow_status_translation_unit (cost=0.00..4221.53 rows=26638 width=81) (actual t (...)
  • Filter: (item_property_type = 'name'::text)
  • Rows Removed by Filter: 23453
32. 0.000 0.000 ↓ 0.0 0 11,508 / 3

Index Scan using employee_uuid_unique on employee referenced_user (cost=0.43..0.64 rows=1 width=59) (actual time=0.000..0.000 rows=0 loops=11,508)

  • Index Cond: (uuid = referenced_reference_value.employee_uuid)
33. 1.144 2.036 ↓ 1.0 2,044 3 / 3

Hash (cost=88.40..88.40 rows=2,040 width=142) (actual time=2.035..2.036 rows=2,044 loops=3)

  • Buckets: 2048 Batches: 1 Memory Usage: 371kB
34. 0.892 0.892 ↓ 1.0 2,044 3 / 3

Seq Scan on document referenced_document (cost=0.00..88.40 rows=2,040 width=142) (actual time=0.004..0.892 rows=2,044 loops=3)

35. 1.355 2.471 ↓ 1.0 2,542 3 / 3

Hash (cost=96.37..96.37 rows=2,537 width=170) (actual time=2.471..2.471 rows=2,542 loops=3)

  • Buckets: 4096 Batches: 1 Memory Usage: 392kB
36. 1.116 1.116 ↓ 1.0 2,542 3 / 3

Seq Scan on workflow_status referenced_workflow_status (cost=0.00..96.37 rows=2,537 width=170) (actual time=0.004..1.116 rows=2,542 loops=3)

37. 10.623 23.397 ↑ 1.2 21,454 3 / 3

Parallel Hash (cost=4,221.53..4,221.53 rows=26,638 width=81) (actual time=23.397..23.397 rows=21,454 loops=3)

  • Buckets: 65536 Batches: 1 Memory Usage: 8128kB
38. 12.774 12.774 ↑ 1.2 21,454 3 / 3

Parallel Seq Scan on translation_unit referenced_workflow_status_translation_unit (cost=0.00..4,221.53 rows=26,638 width=81) (actual time=0.005..12.774 rows=21,454 loops=3)

  • Filter: (item_property_type = 'name'::text)
  • Rows Removed by Filter: 23453
39. 3.836 3.836 ↓ 0.0 0 11,508 / 3

Index Scan using ux_translation_key_locale on translation referenced_workflow_status_translation (cost=0.42..0.56 rows=1 width=102) (actual time=0.001..0.001 rows=0 loops=11,508)

  • Index Cond: (referenced_workflow_status_translation_unit.translation_key = translation_key)
40. 0.140 1.164 ↓ 2.3 455 1

Hash (cost=11.31..11.31 rows=200 width=16) (actual time=1.164..1.164 rows=455 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 30kB
41. 0.333 1.024 ↓ 2.3 455 1

HashAggregate (cost=9.31..11.31 rows=200 width=16) (actual time=0.903..1.024 rows=455 loops=1)

  • Group Key: entity_1.uuid
42. 0.691 0.691 ↓ 1.1 455 1

CTE Scan on entity entity_1 (cost=0.00..8.28 rows=414 width=16) (actual time=0.020..0.691 rows=455 loops=1)

43. 11.339 270.353 ↓ 1.1 11,508 1

Sort (cost=17,402.02..17,427.46 rows=10,176 width=6,322) (actual time=267.931..270.353 rows=11,508 loops=1)

  • Sort Key: full_1."entityValueDate" DESC NULLS LAST
  • Sort Method: quicksort Memory: 2765kB
44. 8.267 259.014 ↓ 1.1 11,508 1

Hash Left Join (cost=229.60..548.11 rows=10,176 width=6,322) (actual time=242.852..259.014 rows=11,508 loops=1)

  • Hash Cond: ("full".uuid = full_1.uuid)
45. 242.882 242.882 ↓ 1.1 11,508 1

CTE Scan on "full" (cost=0.00..203.52 rows=10,176 width=6,314) (actual time=234.972..242.882 rows=11,508 loops=1)

46. 0.159 7.865 ↓ 8.9 455 1

Hash (cost=228.96..228.96 rows=51 width=24) (actual time=7.865..7.865 rows=455 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 33kB
47. 7.706 7.706 ↓ 8.9 455 1

CTE Scan on "full" full_1 (cost=0.00..228.96 rows=51 width=24) (actual time=0.014..7.706 rows=455 loops=1)

  • Filter: ("entityFieldUuid" = 'f9e62210-c5ae-4dd7-9336-86e96974143e'::uuid)
  • Rows Removed by Filter: 11053
Planning time : 11.818 ms
Execution time : 648.286 ms