explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Aska : Jira reporting sort

Settings
# exclusive inclusive rows x rows loops node
1. 543.449 3,866.605 ↑ 1,035,637.7 65,247 1

Sort (cost=63,409,116,240.17..63,578,046,875.80 rows=67,572,254,252 width=828) (actual time=3,849.460..3,866.605 rows=65,247 loops=1)

  • Output: jiraissue.id, ((((project.pkey)::text || '-'::text) || (jiraissue.issuenum)::text)), issuetype.pname, jiraissue.summary, priority.pname, users_creator.display_name, users_assignee.display_name, (to_char(jiraissue.created, 'yyyy-mm-dd'::text)), (to_char(jiraissue.updated, 'yyyy-mm-dd'::text)), project.pname, jiraissue.duedate, issuestatus.pname, jiraissue.environment, jiraissue.timespent, jiraissue.watches, (string_agg(((userhistoryitem.lastviewed)::character varying)::text, ', '::text)), (string_agg(((remotelink.resolved)::character varying)::text, ', '::text)), custom.cfname, custom.cfvalue, project.pkey, jiraissue.issuenum
  • Sort Key: custom.cfname, project.pkey, jiraissue.issuenum
  • Sort Method: quicksort Memory: 32483kB
  • Buffers: shared hit=30844, temp read=4014 written=2219
2. 1,533.142 3,323.156 ↑ 1,035,637.7 65,247 1

Hash Right Join (cost=35,805,605.64..1,828,907,837.67 rows=67,572,254,252 width=828) (actual time=1,817.880..3,323.156 rows=65,247 loops=1)

  • Output: jiraissue.id, (((project.pkey)::text || '-'::text) || (jiraissue.issuenum)::text), issuetype.pname, jiraissue.summary, priority.pname, users_creator.display_name, users_assignee.display_name, to_char(jiraissue.created, 'yyyy-mm-dd'::text), to_char(jiraissue.updated, 'yyyy-mm-dd'::text), project.pname, jiraissue.duedate, issuestatus.pname, jiraissue.environment, jiraissue.timespent, jiraissue.watches, (string_agg(((userhistoryitem.lastviewed)::character varying)::text, ', '::text)), (string_agg(((remotelink.resolved)::character varying)::text, ', '::text)), custom.cfname, custom.cfvalue, project.pkey, jiraissue.issuenum
  • Hash Cond: (custom.issue = jiraissue.id)
  • Buffers: shared hit=30844, temp read=4014 written=2219
3. 8.939 1,260.301 ↑ 13.2 65,701 1

Subquery Scan on custom (cost=11,609,468.61..13,479,974.13 rows=866,077 width=84) (actual time=1,060.336..1,260.301 rows=65,701 loops=1)

  • Output: custom.cfname, custom.cfvalue, custom.issue
  • Buffers: shared hit=19265
4. 4.857 1,251.362 ↑ 13.2 65,701 1

Append (cost=11,609,468.61..13,471,313.36 rows=866,077 width=84) (actual time=1,060.335..1,251.362 rows=65,701 loops=1)

  • Buffers: shared hit=19265
5. 163.808 1,232.022 ↑ 13.7 63,208 1

GroupAggregate (cost=11,609,468.61..13,461,449.79 rows=864,880 width=61) (actual time=1,060.334..1,232.022 rows=63,208 loops=1)

  • Output: customfieldvalue.issue, customfield.cfname, string_agg(COALESCE(CASE WHEN (hashed SubPlan 1) THEN (customfieldoption.customvalue)::text WHEN (hashed SubPlan 2) THEN "AO_8542F1_IFJ_OBJ_ATTR_VAL_1"."TEXT_VALUE" WHEN (hashed SubPlan 3) THEN "AO_8542F1_IFJ_OBJ"."NAME" WHEN (hashed SubPlan 4) THEN (users.display_name)::text ELSE upper((customfieldvalue.stringvalue)::text) END, ((customfieldvalue.numbervalue)::character varying)::text, to_char(customfieldvalue.datevalue, 'yyyy-mm-dd'::text), CASE WHEN (("substring"(customfieldvalue.textvalue, 1, 1) = '<'::text) AND ("substring"(customfieldvalue.textvalue, 20, 5) ~ '^[0-9\.]+$'::text)) THEN (jiraissue_1.summary)::text ELSE customfieldvalue.textvalue END), ', '::text)
  • Group Key: customfieldvalue.issue, customfield.cfname
  • Buffers: shared hit=18681
6. 148.056 974.175 ↑ 397.5 108,436 1

Sort (cost=11,609,468.61..11,717,217.51 rows=43,099,558 width=247) (actual time=945.929..974.175 rows=108,436 loops=1)

  • Output: customfieldvalue.issue, customfield.cfname, customfield.id, customfieldoption.customvalue, customfieldvalue.stringvalue, "AO_8542F1_IFJ_OBJ_ATTR_VAL_1"."TEXT_VALUE", "AO_8542F1_IFJ_OBJ"."NAME", users.display_name, customfieldvalue.numbervalue, customfieldvalue.datevalue, customfieldvalue.textvalue, jiraissue_1.summary
  • Sort Key: customfieldvalue.issue, customfield.cfname
  • Sort Method: quicksort Memory: 19381kB
  • Buffers: shared hit=12977
7. 30.222 826.119 ↑ 397.5 108,436 1

Merge Right Join (cost=56,875.30..1,135,549.08 rows=43,099,558 width=247) (actual time=770.250..826.119 rows=108,436 loops=1)

  • Output: customfieldvalue.issue, customfield.cfname, customfield.id, customfieldoption.customvalue, customfieldvalue.stringvalue, "AO_8542F1_IFJ_OBJ_ATTR_VAL_1"."TEXT_VALUE", "AO_8542F1_IFJ_OBJ"."NAME", users.display_name, customfieldvalue.numbervalue, customfieldvalue.datevalue, customfieldvalue.textvalue, jiraissue_1.summary
  • Merge Cond: ((upper((users.user_name)::text)) = (CASE WHEN (hashed SubPlan 6) THEN upper((customfieldvalue.stringvalue)::text) ELSE ''::text END))
  • Buffers: shared hit=12977
8. 66.733 211.133 ↑ 1.1 75,138 1

Sort (cost=16,170.08..16,367.55 rows=78,989 width=36) (actual time=194.710..211.133 rows=75,138 loops=1)

  • Output: users.display_name, users.user_name, (upper((users.user_name)::text))
  • Sort Key: (upper((users.user_name)::text))
  • Sort Method: quicksort Memory: 11219kB
  • Buffers: shared hit=5667
9. 19.526 144.400 ↑ 1.0 78,744 1

Subquery Scan on users (cost=8,164.80..9,744.57 rows=78,989 width=36) (actual time=94.618..144.400 rows=78,744 loops=1)

  • Output: users.display_name, users.user_name, upper((users.user_name)::text)
  • Buffers: shared hit=5667
10. 76.044 124.874 ↑ 1.0 78,744 1

HashAggregate (cost=8,164.80..8,954.68 rows=78,989 width=36) (actual time=94.606..124.874 rows=78,744 loops=1)

  • Output: cwd_user.user_name, cwd_user.display_name
  • Group Key: cwd_user.user_name, cwd_user.display_name
  • Buffers: shared hit=5667
11. 48.830 48.830 ↑ 1.0 78,744 1

Seq Scan on jira.cwd_user (cost=0.00..7,769.85 rows=78,989 width=36) (actual time=0.021..48.830 rows=78,744 loops=1)

  • Output: cwd_user.id, cwd_user.directory_id, cwd_user.user_name, cwd_user.lower_user_name, cwd_user.active, cwd_user.created_date, cwd_user.updated_date, cwd_user.first_name, cwd_user.lower_first_name, cwd_user.last_name, cwd_user.lower_last_name, cwd_user.display_name, cwd_user.lower_display_name, cwd_user.email_address, cwd_user.lower_email_address, cwd_user.credential, cwd_user.deleted_externally, cwd_user.external_id
  • Filter: (cwd_user.active = '1'::numeric)
  • Rows Removed by Filter: 89628
  • Buffers: shared hit=5667
12. 69.597 584.764 ↑ 1.0 108,436 1

Sort (cost=31,355.59..31,628.41 rows=109,128 width=219) (actual time=575.532..584.764 rows=108,436 loops=1)

  • Output: customfieldvalue.issue, customfieldvalue.stringvalue, customfieldvalue.numbervalue, customfieldvalue.datevalue, customfieldvalue.textvalue, customfield.cfname, customfield.id, jiraissue_1.summary, customfieldoption.customvalue, "AO_8542F1_IFJ_OBJ_ATTR_VAL_1"."TEXT_VALUE", "AO_8542F1_IFJ_OBJ"."NAME", (CASE WHEN (hashed SubPlan 6) THEN upper((customfieldvalue.stringvalue)::text) ELSE ''::text END)
  • Sort Key: (CASE WHEN (hashed SubPlan 6) THEN upper((customfieldvalue.stringvalue)::text) ELSE ''::text END)
  • Sort Method: quicksort Memory: 19335kB
  • Buffers: shared hit=7310
13. 74.673 515.167 ↑ 1.0 108,436 1

Hash Left Join (cost=12,691.62..22,223.94 rows=109,128 width=219) (actual time=221.615..515.167 rows=108,436 loops=1)

  • Output: customfieldvalue.issue, customfieldvalue.stringvalue, customfieldvalue.numbervalue, customfieldvalue.datevalue, customfieldvalue.textvalue, customfield.cfname, customfield.id, jiraissue_1.summary, customfieldoption.customvalue, "AO_8542F1_IFJ_OBJ_ATTR_VAL_1"."TEXT_VALUE", "AO_8542F1_IFJ_OBJ"."NAME", CASE WHEN (hashed SubPlan 6) THEN upper((customfieldvalue.stringvalue)::text) ELSE ''::text END
  • Hash Cond: ((customfieldvalue.stringvalue)::text = ("AO_8542F1_IFJ_OBJ"."OBJECT_KEY")::text)
  • Buffers: shared hit=7310
14. 19.683 335.997 ↑ 1.0 108,436 1

Hash Left Join (cost=12,670.71..21,512.66 rows=109,128 width=208) (actual time=96.597..335.997 rows=108,436 loops=1)

  • Output: customfieldvalue.issue, customfieldvalue.stringvalue, customfieldvalue.numbervalue, customfieldvalue.datevalue, customfieldvalue.textvalue, customfield.cfname, customfield.id, jiraissue_1.summary, customfieldoption.customvalue, "AO_8542F1_IFJ_OBJ_ATTR_VAL_1"."TEXT_VALUE
  • Hash Cond: ("AO_8542F1_IFJ_OBJ_ATTR"."OBJECT_ID" = "AO_8542F1_IFJ_OBJ_ATTR_1"."OBJECT_ID")
  • Buffers: shared hit=1635
15. 57.948 314.932 ↑ 1.0 108,436 1

Hash Left Join (cost=12,500.55..19,872.26 rows=109,128 width=203) (actual time=95.208..314.932 rows=108,436 loops=1)

  • Output: customfieldvalue.issue, customfieldvalue.stringvalue, customfieldvalue.numbervalue, customfieldvalue.datevalue, customfieldvalue.textvalue, customfield.cfname, customfield.id, jiraissue_1.summary, customfieldoption.customvalue, "AO_8542F1_IFJ_OBJ_ATTR"."OBJECT_ID
  • Hash Cond: ((CASE WHEN (hashed SubPlan 5) THEN (customfieldvalue.stringvalue)::integer ELSE 0 END)::numeric = customfieldoption.id)
  • Buffers: shared hit=1586
16. 75.120 256.512 ↑ 1.0 108,436 1

Hash Left Join (cost=12,462.85..17,788.41 rows=109,128 width=191) (actual time=94.696..256.512 rows=108,436 loops=1)

  • Output: customfieldvalue.issue, customfieldvalue.stringvalue, customfieldvalue.numbervalue, customfieldvalue.datevalue, customfieldvalue.textvalue, customfield.cfname, customfield.id, jiraissue_1.summary, "AO_8542F1_IFJ_OBJ_ATTR"."OBJECT_ID
  • Hash Cond: (((CASE WHEN (("substring"(customfieldvalue.textvalue, 1, 1) = '<'::text) AND ("substring"(customfieldvalue.textvalue, 20, 5) ~ '^[0-9\.]+$'::text)) THEN COALESCE("substring"(customfieldvalue.textvalue, 20, 5), '0'::text) ELSE '0'::text END)::integer)::numeric = jiraissue_1.id)
  • Buffers: shared hit=1572
17. 43.041 180.127 ↑ 1.0 108,436 1

Hash Join (cost=12,280.00..15,013.77 rows=109,128 width=149) (actual time=93.414..180.127 rows=108,436 loops=1)

  • Output: customfieldvalue.issue, customfieldvalue.stringvalue, customfieldvalue.numbervalue, customfieldvalue.datevalue, customfieldvalue.textvalue, customfield.cfname, customfield.id, "AO_8542F1_IFJ_OBJ_ATTR"."OBJECT_ID
  • Hash Cond: (customfieldvalue.customfield = customfield.id)
  • Buffers: shared hit=1447
18. 22.419 136.892 ↑ 1.0 108,436 1

Merge Left Join (cost=12,260.34..13,493.60 rows=109,128 width=127) (actual time=93.206..136.892 rows=108,436 loops=1)

  • Output: customfieldvalue.issue, customfieldvalue.stringvalue, customfieldvalue.numbervalue, customfieldvalue.datevalue, customfieldvalue.textvalue, customfieldvalue.customfield, "AO_8542F1_IFJ_OBJ_ATTR"."OBJECT_ID
  • Merge Cond: ((customfieldvalue.stringvalue)::text = "AO_8542F1_IFJ_OBJ_ATTR_VAL"."TEXT_VALUE")
  • Buffers: shared hit=1436
19. 70.461 111.640 ↑ 1.0 108,436 1

Sort (cost=11,910.47..12,183.29 rows=109,128 width=123) (actual time=90.630..111.640 rows=108,436 loops=1)

  • Output: customfieldvalue.issue, customfieldvalue.stringvalue, customfieldvalue.numbervalue, customfieldvalue.datevalue, customfieldvalue.textvalue, customfieldvalue.customfield
  • Sort Key: customfieldvalue.stringvalue
  • Sort Method: quicksort Memory: 13295kB
  • Buffers: shared hit=1390
20. 41.179 41.179 ↑ 1.0 108,436 1

Seq Scan on jira.customfieldvalue (cost=0.00..2,778.82 rows=109,128 width=123) (actual time=0.011..41.179 rows=108,436 loops=1)

  • Output: customfieldvalue.issue, customfieldvalue.stringvalue, customfieldvalue.numbervalue, customfieldvalue.datevalue, customfieldvalue.textvalue, customfieldvalue.customfield
  • Filter: (customfieldvalue.customfield <> ALL ('{11254,11255}'::numeric[]))
  • Rows Removed by Filter: 1938
  • Buffers: shared hit=1390
21. 1.032 2.833 ↑ 1.4 2,088 1

Sort (cost=349.87..357.04 rows=2,869 width=13) (actual time=2.570..2.833 rows=2,088 loops=1)

  • Output: "AO_8542F1_IFJ_OBJ_ATTR_VAL"."TEXT_VALUE", "AO_8542F1_IFJ_OBJ_ATTR"."OBJECT_ID
  • Sort Key: "AO_8542F1_IFJ_OBJ_ATTR_VAL"."TEXT_VALUE
  • Sort Method: quicksort Memory: 232kB
  • Buffers: shared hit=46
22. 0.764 1.801 ↑ 1.0 2,771 1

Hash Right Join (cost=86.55..185.10 rows=2,869 width=13) (actual time=0.853..1.801 rows=2,771 loops=1)

  • Output: "AO_8542F1_IFJ_OBJ_ATTR_VAL"."TEXT_VALUE", "AO_8542F1_IFJ_OBJ_ATTR"."OBJECT_ID
  • Hash Cond: ("AO_8542F1_IFJ_OBJ_ATTR"."ID" = "AO_8542F1_IFJ_OBJ_ATTR_VAL"."OBJECT_ATTRIBUTE_ID")
  • Buffers: shared hit=46
23. 0.211 0.211 ↑ 1.2 2,771 1

Seq Scan on jira."AO_8542F1_IFJ_OBJ_ATTR" (cost=0.00..57.35 rows=3,335 width=8) (actual time=0.006..0.211 rows=2,771 loops=1)

  • Output: "AO_8542F1_IFJ_OBJ_ATTR"."ID", "AO_8542F1_IFJ_OBJ_ATTR"."OBJECT_ID", "AO_8542F1_IFJ_OBJ_ATTR"."OBJECT_TYPE_ATTRIBUTE_ID
  • Buffers: shared hit=24
24. 0.384 0.826 ↑ 1.0 2,771 1

Hash (cost=50.69..50.69 rows=2,869 width=13) (actual time=0.826..0.826 rows=2,771 loops=1)

  • Output: "AO_8542F1_IFJ_OBJ_ATTR_VAL"."TEXT_VALUE", "AO_8542F1_IFJ_OBJ_ATTR_VAL"."OBJECT_ATTRIBUTE_ID
  • Buckets: 4096 Batches: 1 Memory Usage: 140kB
  • Buffers: shared hit=22
25. 0.442 0.442 ↑ 1.0 2,771 1

Seq Scan on jira."AO_8542F1_IFJ_OBJ_ATTR_VAL" (cost=0.00..50.69 rows=2,869 width=13) (actual time=0.016..0.442 rows=2,771 loops=1)

  • Output: "AO_8542F1_IFJ_OBJ_ATTR_VAL"."TEXT_VALUE", "AO_8542F1_IFJ_OBJ_ATTR_VAL"."OBJECT_ATTRIBUTE_ID
  • Buffers: shared hit=22
26. 0.087 0.194 ↑ 1.0 384 1

Hash (cost=14.85..14.85 rows=385 width=29) (actual time=0.194..0.194 rows=384 loops=1)

  • Output: customfield.cfname, customfield.id
  • Buckets: 1024 Batches: 1 Memory Usage: 32kB
  • Buffers: shared hit=11
27. 0.107 0.107 ↑ 1.0 384 1

Seq Scan on jira.customfield (cost=0.00..14.85 rows=385 width=29) (actual time=0.014..0.107 rows=384 loops=1)

  • Output: customfield.cfname, customfield.id
  • Buffers: shared hit=11
28. 0.548 1.265 ↑ 1.0 2,571 1

Hash (cost=150.71..150.71 rows=2,571 width=48) (actual time=1.265..1.265 rows=2,571 loops=1)

  • Output: jiraissue_1.summary, jiraissue_1.id
  • Buckets: 4096 Batches: 1 Memory Usage: 238kB
  • Buffers: shared hit=125
29. 0.717 0.717 ↑ 1.0 2,571 1

Seq Scan on jira.jiraissue jiraissue_1 (cost=0.00..150.71 rows=2,571 width=48) (actual time=0.044..0.717 rows=2,571 loops=1)

  • Output: jiraissue_1.summary, jiraissue_1.id
  • Buffers: shared hit=125
30. 0.120 0.240 ↓ 1.0 642 1

Hash (cost=13.40..13.40 rows=640 width=18) (actual time=0.240..0.240 rows=642 loops=1)

  • Output: customfieldoption.customvalue, customfieldoption.id
  • Buckets: 1024 Batches: 1 Memory Usage: 41kB
  • Buffers: shared hit=7
31. 0.120 0.120 ↓ 1.0 642 1

Seq Scan on jira.customfieldoption (cost=0.00..13.40 rows=640 width=18) (actual time=0.008..0.120 rows=642 loops=1)

  • Output: customfieldoption.customvalue, customfieldoption.id
  • Buffers: shared hit=7
32.          

SubPlan (forHash Left Join)

33. 0.186 0.232 ↑ 1.0 104 1

HashAggregate (cost=15.00..16.04 rows=104 width=7) (actual time=0.224..0.232 rows=104 loops=1)

  • Output: customfieldoption_2.customfield
  • Group Key: customfieldoption_2.customfield
  • Buffers: shared hit=7
34. 0.046 0.046 ↓ 1.0 642 1

Seq Scan on jira.customfieldoption customfieldoption_2 (cost=0.00..13.40 rows=640 width=7) (actual time=0.004..0.046 rows=642 loops=1)

  • Output: customfieldoption_2.id, customfieldoption_2.customfield, customfieldoption_2.customfieldconfig, customfieldoption_2.parentoptionid, customfieldoption_2.sequence, customfieldoption_2.customvalue, customfieldoption_2.optiontype, customfieldoption_2.disabled
  • Buffers: shared hit=7
35. 0.073 1.382 ↑ 1.2 574 1

Hash (cost=161.83..161.83 rows=666 width=13) (actual time=1.382..1.382 rows=574 loops=1)

  • Output: "AO_8542F1_IFJ_OBJ_ATTR_1"."OBJECT_ID", "AO_8542F1_IFJ_OBJ_ATTR_VAL_1"."TEXT_VALUE
  • Buckets: 1024 Batches: 1 Memory Usage: 35kB
  • Buffers: shared hit=49
36. 0.436 1.309 ↑ 1.2 574 1

Hash Join (cost=93.72..161.83 rows=666 width=13) (actual time=0.677..1.309 rows=574 loops=1)

  • Output: "AO_8542F1_IFJ_OBJ_ATTR_1"."OBJECT_ID", "AO_8542F1_IFJ_OBJ_ATTR_VAL_1"."TEXT_VALUE
  • Hash Cond: ("AO_8542F1_IFJ_OBJ_ATTR_VAL_1"."OBJECT_ATTRIBUTE_ID" = "AO_8542F1_IFJ_OBJ_ATTR_1"."ID")
  • Buffers: shared hit=49
37. 0.208 0.208 ↑ 1.0 2,771 1

Seq Scan on jira."AO_8542F1_IFJ_OBJ_ATTR_VAL" "AO_8542F1_IFJ_OBJ_ATTR_VAL_1" (cost=0.00..50.69 rows=2,869 width=13) (actual time=0.004..0.208 rows=2,771 loops=1)

  • Output: "AO_8542F1_IFJ_OBJ_ATTR_VAL_1"."ADDITIONAL_VALUE", "AO_8542F1_IFJ_OBJ_ATTR_VAL_1"."BOOLEAN_VALUE", "AO_8542F1_IFJ_OBJ_ATTR_VAL_1"."DATE_VALUE", "AO_8542F1_IFJ_OBJ_ATTR_VAL_1"."DOUBLE_VALUE", "AO_8542F1_IFJ_OBJ_ATTR_VAL_1"."ID", "AO_8542F1_IFJ_OBJ_ATTR_VAL_1"."INTEGER_VALUE", "AO_8542F1_IFJ_OBJ_ATTR_VAL_1"."OBJECT_ATTRIBUTE_ID", "AO_8542F1_IFJ_OBJ_ATTR_VAL_1"."REFERENCED_OBJECT_ID", "AO_8542F1_IFJ_OBJ_ATTR_VAL_1"."TEXT_VALUE", "AO_8542F1_IFJ_OBJ_ATTR_VAL_1"."TEXT_VALUE_SHORT", "AO_8542F1_IFJ_OBJ_ATTR_VAL_1"."TEXT_VALUE_SHORT_LOWER
  • Buffers: shared hit=22
38. 0.077 0.665 ↑ 1.3 574 1

Hash (cost=84.05..84.05 rows=774 width=8) (actual time=0.665..0.665 rows=574 loops=1)

  • Output: "AO_8542F1_IFJ_OBJ_ATTR_1"."ID", "AO_8542F1_IFJ_OBJ_ATTR_1"."OBJECT_ID
  • Buckets: 1024 Batches: 1 Memory Usage: 31kB
  • Buffers: shared hit=27
39. 0.334 0.588 ↑ 1.3 574 1

Hash Join (cost=6.45..84.05 rows=774 width=8) (actual time=0.065..0.588 rows=574 loops=1)

  • Output: "AO_8542F1_IFJ_OBJ_ATTR_1"."ID", "AO_8542F1_IFJ_OBJ_ATTR_1"."OBJECT_ID
  • Hash Cond: ("AO_8542F1_IFJ_OBJ_ATTR_1"."OBJECT_TYPE_ATTRIBUTE_ID" = "AO_8542F1_IFJ_OBJ_TYPE_ATTR"."ID")
  • Buffers: shared hit=27
40. 0.200 0.200 ↑ 1.2 2,771 1

Seq Scan on jira."AO_8542F1_IFJ_OBJ_ATTR" "AO_8542F1_IFJ_OBJ_ATTR_1" (cost=0.00..57.35 rows=3,335 width=12) (actual time=0.005..0.200 rows=2,771 loops=1)

  • Output: "AO_8542F1_IFJ_OBJ_ATTR_1"."ID", "AO_8542F1_IFJ_OBJ_ATTR_1"."OBJECT_ID", "AO_8542F1_IFJ_OBJ_ATTR_1"."OBJECT_TYPE_ATTRIBUTE_ID
  • Buffers: shared hit=24
41. 0.012 0.054 ↓ 1.0 54 1

Hash (cost=5.80..5.80 rows=52 width=4) (actual time=0.054..0.054 rows=54 loops=1)

  • Output: "AO_8542F1_IFJ_OBJ_TYPE_ATTR"."ID
  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
  • Buffers: shared hit=3
42. 0.042 0.042 ↓ 1.0 54 1

Seq Scan on jira."AO_8542F1_IFJ_OBJ_TYPE_ATTR" (cost=0.00..5.80 rows=52 width=4) (actual time=0.008..0.042 rows=54 loops=1)

  • Output: "AO_8542F1_IFJ_OBJ_TYPE_ATTR"."ID
  • Filter: (("AO_8542F1_IFJ_OBJ_TYPE_ATTR"."NAME")::text = 'Name'::text)
  • Rows Removed by Filter: 174
  • Buffers: shared hit=3
43. 0.077 0.183 ↑ 1.2 461 1

Hash (cost=13.74..13.74 rows=574 width=17) (actual time=0.182..0.183 rows=461 loops=1)

  • Output: "AO_8542F1_IFJ_OBJ"."NAME", "AO_8542F1_IFJ_OBJ"."OBJECT_KEY
  • Buckets: 1024 Batches: 1 Memory Usage: 31kB
  • Buffers: shared hit=8
44. 0.106 0.106 ↑ 1.0 574 1

Seq Scan on jira."AO_8542F1_IFJ_OBJ" (cost=0.00..13.74 rows=574 width=17) (actual time=0.006..0.106 rows=574 loops=1)

  • Output: "AO_8542F1_IFJ_OBJ"."NAME", "AO_8542F1_IFJ_OBJ"."OBJECT_KEY
  • Buffers: shared hit=8
45.          

SubPlan (forHash Left Join)

46. 46.920 104.314 ↑ 1.0 78,744 1

HashAggregate (cost=8,164.80..9,152.16 rows=78,989 width=32) (actual time=83.605..104.314 rows=78,744 loops=1)

  • Output: (upper((cwd_user_4.user_name)::text))
  • Group Key: upper((cwd_user_4.user_name)::text)
  • Buffers: shared hit=5667
47. 57.394 57.394 ↑ 1.0 78,744 1

Seq Scan on jira.cwd_user cwd_user_4 (cost=0.00..7,967.32 rows=78,989 width=32) (actual time=0.011..57.394 rows=78,744 loops=1)

  • Output: upper((cwd_user_4.user_name)::text)
  • Filter: (cwd_user_4.active = '1'::numeric)
  • Rows Removed by Filter: 89628
  • Buffers: shared hit=5667
48.          

SubPlan (forGroupAggregate)

49. 0.193 0.266 ↑ 1.0 104 1

HashAggregate (cost=15.00..16.04 rows=104 width=7) (actual time=0.254..0.266 rows=104 loops=1)

  • Output: customfieldoption_1.customfield
  • Group Key: customfieldoption_1.customfield
  • Buffers: shared hit=7
50. 0.073 0.073 ↓ 1.0 642 1

Seq Scan on jira.customfieldoption customfieldoption_1 (cost=0.00..13.40 rows=640 width=7) (actual time=0.018..0.073 rows=642 loops=1)

  • Output: customfieldoption_1.id, customfieldoption_1.customfield, customfieldoption_1.customfieldconfig, customfieldoption_1.parentoptionid, customfieldoption_1.sequence, customfieldoption_1.customvalue, customfieldoption_1.optiontype, customfieldoption_1.disabled
  • Buffers: shared hit=7
51. 0.366 0.366 ↑ 1.0 2,771 1

Seq Scan on jira."AO_8542F1_IFJ_OBJ_ATTR_VAL" "AO_8542F1_IFJ_OBJ_ATTR_VAL_2" (cost=0.00..50.69 rows=2,869 width=9) (actual time=0.008..0.366 rows=2,771 loops=1)

  • Output: "AO_8542F1_IFJ_OBJ_ATTR_VAL_2"."TEXT_VALUE
  • Buffers: shared hit=22
52. 0.098 0.098 ↑ 1.0 574 1

Seq Scan on jira."AO_8542F1_IFJ_OBJ" "AO_8542F1_IFJ_OBJ_1" (cost=0.00..13.74 rows=574 width=6) (actual time=0.007..0.098 rows=574 loops=1)

  • Output: "AO_8542F1_IFJ_OBJ_1"."OBJECT_KEY
  • Buffers: shared hit=8
53. 41.274 93.309 ↑ 1.0 78,744 1

HashAggregate (cost=8,164.80..9,152.16 rows=78,989 width=32) (actual time=74.972..93.309 rows=78,744 loops=1)

  • Output: (upper((cwd_user_3.user_name)::text))
  • Group Key: upper((cwd_user_3.user_name)::text)
  • Buffers: shared hit=5667
54. 52.035 52.035 ↑ 1.0 78,744 1

Seq Scan on jira.cwd_user cwd_user_3 (cost=0.00..7,967.32 rows=78,989 width=32) (actual time=0.012..52.035 rows=78,744 loops=1)

  • Output: upper((cwd_user_3.user_name)::text)
  • Filter: (cwd_user_3.active = '1'::numeric)
  • Rows Removed by Filter: 89628
  • Buffers: shared hit=5667
55. 0.160 9.551 ↓ 4.7 1,517 1

Subquery Scan on *SELECT* 2 (cost=685.54..692.88 rows=326 width=70) (actual time=8.990..9.551 rows=1,517 loops=1)

  • Output: "*SELECT* 2".issue, "*SELECT* 2".cfname, "*SELECT* 2".cfvalue
  • Buffers: shared hit=292
56. 2.402 9.391 ↓ 4.7 1,517 1

HashAggregate (cost=685.54..689.62 rows=326 width=70) (actual time=8.990..9.391 rows=1,517 loops=1)

  • Output: issuelink.source, ('Issue Link'::text), string_agg(((((((issuelinktype.outward)::text || ' '::text) || (proj_destination.pkey)::text) || '-'::text) || (issue_destination.issuenum)::text)), ', '::text)
  • Group Key: issuelink.source, ('Issue Link'::text)
  • Buffers: shared hit=292
57. 0.227 6.989 ↑ 1.2 2,720 1

Append (cost=185.22..661.09 rows=3,260 width=70) (actual time=1.269..6.989 rows=2,720 loops=1)

  • Buffers: shared hit=292
58. 0.920 3.497 ↑ 1.2 1,360 1

Hash Join (cost=185.22..314.25 rows=1,630 width=70) (actual time=1.268..3.497 rows=1,360 loops=1)

  • Output: issuelink.source, 'Issue Link'::text, (((((issuelinktype.outward)::text || ' '::text) || (proj_destination.pkey)::text) || '-'::text) || (issue_destination.issuenum)::text)
  • Hash Cond: (issue_destination.project = proj_destination.id)
  • Buffers: shared hit=146
59. 0.518 2.550 ↑ 1.2 1,360 1

Hash Join (cost=184.13..266.29 rows=1,630 width=533) (actual time=1.216..2.550 rows=1,360 loops=1)

  • Output: issuelink.source, issuelinktype.outward, issue_destination.issuenum, issue_destination.project
  • Hash Cond: (issuelink.destination = issue_destination.id)
  • Buffers: shared hit=145
60. 0.643 0.899 ↑ 1.2 1,360 1

Hash Join (cost=1.29..61.04 rows=1,630 width=529) (actual time=0.054..0.899 rows=1,360 loops=1)

  • Output: issuelink.source, issuelink.destination, issuelinktype.outward
  • Hash Cond: (issuelink.linktype = issuelinktype.id)
  • Buffers: shared hit=20
61. 0.230 0.230 ↓ 1.1 1,921 1

Seq Scan on jira.issuelink (cost=0.00..36.78 rows=1,778 width=20) (actual time=0.008..0.230 rows=1,921 loops=1)

  • Output: issuelink.id, issuelink.linktype, issuelink.source, issuelink.destination, issuelink.sequence
  • Buffers: shared hit=19
62. 0.009 0.026 ↓ 1.1 12 1

Hash (cost=1.15..1.15 rows=11 width=536) (actual time=0.026..0.026 rows=12 loops=1)

  • Output: issuelinktype.outward, issuelinktype.id
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
63. 0.017 0.017 ↓ 1.1 12 1

Seq Scan on jira.issuelinktype (cost=0.00..1.15 rows=11 width=536) (actual time=0.014..0.017 rows=12 loops=1)

  • Output: issuelinktype.outward, issuelinktype.id
  • Filter: (issuelinktype.id <> '10100'::numeric)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=1
64. 0.557 1.133 ↑ 1.0 2,571 1

Hash (cost=150.71..150.71 rows=2,571 width=17) (actual time=1.133..1.133 rows=2,571 loops=1)

  • Output: issue_destination.issuenum, issue_destination.id, issue_destination.project
  • Buckets: 4096 Batches: 1 Memory Usage: 158kB
  • Buffers: shared hit=125
65. 0.576 0.576 ↑ 1.0 2,571 1

Seq Scan on jira.jiraissue issue_destination (cost=0.00..150.71 rows=2,571 width=17) (actual time=0.005..0.576 rows=2,571 loops=1)

  • Output: issue_destination.issuenum, issue_destination.id, issue_destination.project
  • Buffers: shared hit=125
66. 0.008 0.027 ↑ 1.0 4 1

Hash (cost=1.04..1.04 rows=4 width=10) (actual time=0.027..0.027 rows=4 loops=1)

  • Output: proj_destination.pkey, proj_destination.id
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
67. 0.019 0.019 ↑ 1.0 4 1

Seq Scan on jira.project proj_destination (cost=0.00..1.04 rows=4 width=10) (actual time=0.019..0.019 rows=4 loops=1)

  • Output: proj_destination.pkey, proj_destination.id
  • Buffers: shared hit=1
68. 0.884 3.265 ↑ 1.2 1,360 1

Hash Join (cost=185.22..314.25 rows=1,630 width=71) (actual time=1.093..3.265 rows=1,360 loops=1)

  • Output: issuelink_1.destination, 'Issue Link'::text, (((((issuelinktype_1.inward)::text || ' '::text) || (proj_source.pkey)::text) || '-'::text) || (issue_source.issuenum)::text)
  • Hash Cond: (issue_source.project = proj_source.id)
  • Buffers: shared hit=146
69. 0.542 2.368 ↑ 1.2 1,360 1

Hash Join (cost=184.13..266.29 rows=1,630 width=534) (actual time=1.061..2.368 rows=1,360 loops=1)

  • Output: issuelink_1.destination, issuelinktype_1.inward, issue_source.issuenum, issue_source.project
  • Hash Cond: (issuelink_1.source = issue_source.id)
  • Buffers: shared hit=145
70. 0.599 0.820 ↑ 1.2 1,360 1

Hash Join (cost=1.29..61.04 rows=1,630 width=529) (actual time=0.034..0.820 rows=1,360 loops=1)

  • Output: issuelink_1.destination, issuelink_1.source, issuelinktype_1.inward
  • Hash Cond: (issuelink_1.linktype = issuelinktype_1.id)
  • Buffers: shared hit=20
71. 0.203 0.203 ↓ 1.1 1,921 1

Seq Scan on jira.issuelink issuelink_1 (cost=0.00..36.78 rows=1,778 width=20) (actual time=0.003..0.203 rows=1,921 loops=1)

  • Output: issuelink_1.id, issuelink_1.linktype, issuelink_1.source, issuelink_1.destination, issuelink_1.sequence
  • Buffers: shared hit=19
72. 0.007 0.018 ↓ 1.1 12 1

Hash (cost=1.15..1.15 rows=11 width=536) (actual time=0.018..0.018 rows=12 loops=1)

  • Output: issuelinktype_1.inward, issuelinktype_1.id
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
73. 0.011 0.011 ↓ 1.1 12 1

Seq Scan on jira.issuelinktype issuelinktype_1 (cost=0.00..1.15 rows=11 width=536) (actual time=0.007..0.011 rows=12 loops=1)

  • Output: issuelinktype_1.inward, issuelinktype_1.id
  • Filter: (issuelinktype_1.id <> '10100'::numeric)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=1
74. 0.554 1.006 ↑ 1.0 2,571 1

Hash (cost=150.71..150.71 rows=2,571 width=17) (actual time=1.006..1.006 rows=2,571 loops=1)

  • Output: issue_source.issuenum, issue_source.id, issue_source.project
  • Buckets: 4096 Batches: 1 Memory Usage: 158kB
  • Buffers: shared hit=125
75. 0.452 0.452 ↑ 1.0 2,571 1

Seq Scan on jira.jiraissue issue_source (cost=0.00..150.71 rows=2,571 width=17) (actual time=0.003..0.452 rows=2,571 loops=1)

  • Output: issue_source.issuenum, issue_source.id, issue_source.project
  • Buffers: shared hit=125
76. 0.007 0.013 ↑ 1.0 4 1

Hash (cost=1.04..1.04 rows=4 width=10) (actual time=0.012..0.013 rows=4 loops=1)

  • Output: proj_source.pkey, proj_source.id
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
77. 0.006 0.006 ↑ 1.0 4 1

Seq Scan on jira.project proj_source (cost=0.00..1.04 rows=4 width=10) (actual time=0.006..0.006 rows=4 loops=1)

  • Output: proj_source.pkey, proj_source.id
  • Buffers: shared hit=1
78. 0.649 2.470 ↓ 1.0 428 1

HashAggregate (cost=251.15..256.41 rows=421 width=70) (actual time=2.382..2.470 rows=428 loops=1)

  • Output: issuelink_2.source, 'Products'::character varying, string_agg((((proj_destination_1.pkey)::text || '-'::text) || (issue_destination_1.issuenum)::text), ', '::text)
  • Group Key: issuelink_2.source
  • Buffers: shared hit=146
79. 0.201 1.821 ↓ 1.2 561 1

Hash Join (cost=183.94..244.05 rows=473 width=15) (actual time=1.099..1.821 rows=561 loops=1)

  • Output: issuelink_2.source, proj_destination_1.pkey, issue_destination_1.issuenum
  • Hash Cond: (issue_destination_1.project = proj_destination_1.id)
  • Buffers: shared hit=146
80. 0.232 1.607 ↓ 1.2 561 1

Hash Join (cost=182.85..236.46 rows=473 width=17) (actual time=1.071..1.607 rows=561 loops=1)

  • Output: issuelink_2.source, issue_destination_1.issuenum, issue_destination_1.project
  • Hash Cond: (issuelink_2.destination = issue_destination_1.id)
  • Buffers: shared hit=145
81. 0.064 0.379 ↓ 1.2 561 1

Nested Loop (cost=0.00..47.11 rows=473 width=13) (actual time=0.050..0.379 rows=561 loops=1)

  • Output: issuelink_2.source, issuelink_2.destination
  • Buffers: shared hit=20
82. 0.011 0.011 ↑ 1.0 1 1

Seq Scan on jira.issuelinktype issuelinktype_2 (cost=0.00..1.15 rows=1 width=20) (actual time=0.009..0.011 rows=1 loops=1)

  • Output: issuelinktype_2.id, issuelinktype_2.linkname, issuelinktype_2.inward, issuelinktype_2.outward, issuelinktype_2.pstyle
  • Filter: (issuelinktype_2.id = '10100'::numeric)
  • Rows Removed by Filter: 12
  • Buffers: shared hit=1
83. 0.304 0.304 ↓ 1.2 561 1

Seq Scan on jira.issuelink issuelink_2 (cost=0.00..41.23 rows=473 width=20) (actual time=0.038..0.304 rows=561 loops=1)

  • Output: issuelink_2.id, issuelink_2.linktype, issuelink_2.source, issuelink_2.destination, issuelink_2.sequence
  • Filter: (issuelink_2.linktype = '10100'::numeric)
  • Rows Removed by Filter: 1360
  • Buffers: shared hit=19
84. 0.550 0.996 ↑ 1.0 2,571 1

Hash (cost=150.71..150.71 rows=2,571 width=17) (actual time=0.996..0.996 rows=2,571 loops=1)

  • Output: issue_destination_1.issuenum, issue_destination_1.id, issue_destination_1.project
  • Buckets: 4096 Batches: 1 Memory Usage: 158kB
  • Buffers: shared hit=125
85. 0.446 0.446 ↑ 1.0 2,571 1

Seq Scan on jira.jiraissue issue_destination_1 (cost=0.00..150.71 rows=2,571 width=17) (actual time=0.003..0.446 rows=2,571 loops=1)

  • Output: issue_destination_1.issuenum, issue_destination_1.id, issue_destination_1.project
  • Buffers: shared hit=125
86. 0.006 0.013 ↑ 1.0 4 1

Hash (cost=1.04..1.04 rows=4 width=10) (actual time=0.013..0.013 rows=4 loops=1)

  • Output: proj_destination_1.pkey, proj_destination_1.id
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
87. 0.007 0.007 ↑ 1.0 4 1

Seq Scan on jira.project proj_destination_1 (cost=0.00..1.04 rows=4 width=10) (actual time=0.006..0.007 rows=4 loops=1)

  • Output: proj_destination_1.pkey, proj_destination_1.id
  • Buffers: shared hit=1
88. 0.699 2.462 ↓ 1.2 548 1

HashAggregate (cost=251.15..256.77 rows=450 width=71) (actual time=2.366..2.462 rows=548 loops=1)

  • Output: issuelink_3.destination, 'Referencia'::character varying, string_agg((((proj_source_1.pkey)::text || '-'::text) || (issue_source_1.issuenum)::text), ', '::text)
  • Group Key: issuelink_3.destination
  • Buffers: shared hit=146
89. 0.194 1.763 ↓ 1.2 561 1

Hash Join (cost=183.94..244.05 rows=473 width=16) (actual time=1.019..1.763 rows=561 loops=1)

  • Output: issuelink_3.destination, proj_source_1.pkey, issue_source_1.issuenum
  • Hash Cond: (issue_source_1.project = proj_source_1.id)
  • Buffers: shared hit=146
90. 0.236 1.557 ↓ 1.2 561 1

Hash Join (cost=182.85..236.46 rows=473 width=18) (actual time=0.992..1.557 rows=561 loops=1)

  • Output: issuelink_3.destination, issue_source_1.issuenum, issue_source_1.project
  • Hash Cond: (issuelink_3.source = issue_source_1.id)
  • Buffers: shared hit=145
91. 0.060 0.396 ↓ 1.2 561 1

Nested Loop (cost=0.00..47.11 rows=473 width=13) (actual time=0.045..0.396 rows=561 loops=1)

  • Output: issuelink_3.destination, issuelink_3.source
  • Buffers: shared hit=20
92. 0.009 0.009 ↑ 1.0 1 1

Seq Scan on jira.issuelinktype issuelinktype_3 (cost=0.00..1.15 rows=1 width=20) (actual time=0.007..0.009 rows=1 loops=1)

  • Output: issuelinktype_3.id, issuelinktype_3.linkname, issuelinktype_3.inward, issuelinktype_3.outward, issuelinktype_3.pstyle
  • Filter: (issuelinktype_3.id = '10100'::numeric)
  • Rows Removed by Filter: 12
  • Buffers: shared hit=1
93. 0.327 0.327 ↓ 1.2 561 1

Seq Scan on jira.issuelink issuelink_3 (cost=0.00..41.23 rows=473 width=20) (actual time=0.037..0.327 rows=561 loops=1)

  • Output: issuelink_3.id, issuelink_3.linktype, issuelink_3.source, issuelink_3.destination, issuelink_3.sequence
  • Filter: (issuelink_3.linktype = '10100'::numeric)
  • Rows Removed by Filter: 1360
  • Buffers: shared hit=19
94. 0.544 0.925 ↑ 1.0 2,571 1

Hash (cost=150.71..150.71 rows=2,571 width=17) (actual time=0.925..0.925 rows=2,571 loops=1)

  • Output: issue_source_1.issuenum, issue_source_1.id, issue_source_1.project
  • Buckets: 4096 Batches: 1 Memory Usage: 158kB
  • Buffers: shared hit=125
95. 0.381 0.381 ↑ 1.0 2,571 1

Seq Scan on jira.jiraissue issue_source_1 (cost=0.00..150.71 rows=2,571 width=17) (actual time=0.003..0.381 rows=2,571 loops=1)

  • Output: issue_source_1.issuenum, issue_source_1.id, issue_source_1.project
  • Buffers: shared hit=125
96. 0.006 0.012 ↑ 1.0 4 1

Hash (cost=1.04..1.04 rows=4 width=10) (actual time=0.012..0.012 rows=4 loops=1)

  • Output: proj_source_1.pkey, proj_source_1.id
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
97. 0.006 0.006 ↑ 1.0 4 1

Seq Scan on jira.project proj_source_1 (cost=0.00..1.04 rows=4 width=10) (actual time=0.006..0.006 rows=4 loops=1)

  • Output: proj_source_1.pkey, proj_source_1.id
  • Buffers: shared hit=1
98. 20.996 529.713 ↑ 87,901.9 2,282 1

Hash (cost=4,254,453.89..4,254,453.89 rows=200,592,171 width=684) (actual time=529.713..529.713 rows=2,282 loops=1)

  • Output: jiraissue.id, jiraissue.issuenum, jiraissue.summary, jiraissue.created, jiraissue.updated, jiraissue.duedate, jiraissue.environment, jiraissue.timespent, jiraissue.watches, project.pkey, project.pname, issuetype.pname, issuestatus.pname, priority.pname, (string_agg(((userhistoryitem.lastviewed)::character varying)::text, ', '::text)), (string_agg(((remotelink.resolved)::character varying)::text, ', '::text)), users_creator.display_name, users_assignee.display_name
  • Buckets: 524288 Batches: 1024 Memory Usage: 4097kB
  • Buffers: shared hit=11579
99. 10.332 508.717 ↑ 87,901.9 2,282 1

Merge Right Join (cost=240,550.83..4,254,453.89 rows=200,592,171 width=684) (actual time=480.336..508.717 rows=2,282 loops=1)

  • Output: jiraissue.id, jiraissue.issuenum, jiraissue.summary, jiraissue.created, jiraissue.updated, jiraissue.duedate, jiraissue.environment, jiraissue.timespent, jiraissue.watches, project.pkey, project.pname, issuetype.pname, issuestatus.pname, priority.pname, (string_agg(((userhistoryitem.lastviewed)::character varying)::text, ', '::text)), (string_agg(((remotelink.resolved)::character varying)::text, ', '::text)), users_creator.display_name, users_assignee.display_name
  • Merge Cond: ((upper((users_assignee.user_name)::text)) = (upper((jiraissue.assignee)::text)))
  • Buffers: shared hit=11579
100. 67.536 260.597 ↑ 1.1 75,138 1

Sort (cost=16,170.08..16,367.55 rows=78,989 width=36) (actual time=242.002..260.597 rows=75,138 loops=1)

  • Output: users_assignee.display_name, users_assignee.user_name, (upper((users_assignee.user_name)::text))
  • Sort Key: (upper((users_assignee.user_name)::text))
  • Sort Method: quicksort Memory: 11219kB
  • Buffers: shared hit=5667
101. 20.779 193.061 ↑ 1.0 78,744 1

Subquery Scan on users_assignee (cost=8,164.80..9,744.57 rows=78,989 width=36) (actual time=140.780..193.061 rows=78,744 loops=1)

  • Output: users_assignee.display_name, users_assignee.user_name, upper((users_assignee.user_name)::text)
  • Buffers: shared hit=5667
102. 97.463 172.282 ↑ 1.0 78,744 1

HashAggregate (cost=8,164.80..8,954.68 rows=78,989 width=36) (actual time=140.770..172.282 rows=78,744 loops=1)

  • Output: cwd_user_1.user_name, cwd_user_1.display_name
  • Group Key: cwd_user_1.user_name, cwd_user_1.display_name
  • Buffers: shared hit=5667
103. 74.819 74.819 ↑ 1.0 78,744 1

Seq Scan on jira.cwd_user cwd_user_1 (cost=0.00..7,769.85 rows=78,989 width=36) (actual time=0.025..74.819 rows=78,744 loops=1)

  • Output: cwd_user_1.id, cwd_user_1.directory_id, cwd_user_1.user_name, cwd_user_1.lower_user_name, cwd_user_1.active, cwd_user_1.created_date, cwd_user_1.updated_date, cwd_user_1.first_name, cwd_user_1.lower_first_name, cwd_user_1.last_name, cwd_user_1.lower_last_name, cwd_user_1.display_name, cwd_user_1.lower_display_name, cwd_user_1.email_address, cwd_user_1.lower_email_address, cwd_user_1.credential, cwd_user_1.deleted_externally, cwd_user_1.external_id
  • Filter: (cwd_user_1.active = '1'::numeric)
  • Rows Removed by Filter: 89628
  • Buffers: shared hit=5667
104. 0.429 237.788 ↑ 222.6 2,282 1

Materialize (cost=224,380.75..226,920.25 rows=507,899 width=663) (actual time=236.740..237.788 rows=2,282 loops=1)

  • Output: jiraissue.id, jiraissue.issuenum, jiraissue.summary, jiraissue.created, jiraissue.updated, jiraissue.duedate, jiraissue.environment, jiraissue.timespent, jiraissue.watches, jiraissue.assignee, project.pkey, project.pname, issuetype.pname, issuestatus.pname, priority.pname, (string_agg(((userhistoryitem.lastviewed)::character varying)::text, ', '::text)), (string_agg(((remotelink.resolved)::character varying)::text, ', '::text)), users_creator.display_name, (upper((jiraissue.assignee)::text))
  • Buffers: shared hit=5912
105. 2.829 237.359 ↑ 222.6 2,282 1

Sort (cost=224,380.75..225,650.50 rows=507,899 width=663) (actual time=236.735..237.359 rows=2,282 loops=1)

  • Output: jiraissue.id, jiraissue.issuenum, jiraissue.summary, jiraissue.created, jiraissue.updated, jiraissue.duedate, jiraissue.environment, jiraissue.timespent, jiraissue.watches, jiraissue.assignee, project.pkey, project.pname, issuetype.pname, issuestatus.pname, priority.pname, (string_agg(((userhistoryitem.lastviewed)::character varying)::text, ', '::text)), (string_agg(((remotelink.resolved)::character varying)::text, ', '::text)), users_creator.display_name, (upper((jiraissue.assignee)::text))
  • Sort Key: (upper((jiraissue.assignee)::text))
  • Sort Method: quicksort Memory: 771kB
  • Buffers: shared hit=5912
106. 9.387 234.530 ↑ 222.6 2,282 1

Merge Left Join (cost=16,779.86..26,950.70 rows=507,899 width=663) (actual time=208.227..234.530 rows=2,282 loops=1)

  • Output: jiraissue.id, jiraissue.issuenum, jiraissue.summary, jiraissue.created, jiraissue.updated, jiraissue.duedate, jiraissue.environment, jiraissue.timespent, jiraissue.watches, jiraissue.assignee, project.pkey, project.pname, issuetype.pname, issuestatus.pname, priority.pname, (string_agg(((userhistoryitem.lastviewed)::character varying)::text, ', '::text)), (string_agg(((remotelink.resolved)::character varying)::text, ', '::text)), users_creator.display_name, upper((jiraissue.assignee)::text)
  • Merge Cond: ((upper((jiraissue.creator)::text)) = (upper((users_creator.user_name)::text)))
  • Buffers: shared hit=5912
107. 3.238 16.601 ↓ 1.8 2,282 1

Sort (cost=609.78..613.00 rows=1,286 width=642) (actual time=15.939..16.601 rows=2,282 loops=1)

  • Output: jiraissue.id, jiraissue.issuenum, jiraissue.summary, jiraissue.created, jiraissue.updated, jiraissue.duedate, jiraissue.environment, jiraissue.timespent, jiraissue.watches, jiraissue.creator, jiraissue.assignee, project.pkey, project.pname, issuetype.pname, issuestatus.pname, priority.pname, (string_agg(((userhistoryitem.lastviewed)::character varying)::text, ', '::text)), (string_agg(((remotelink.resolved)::character varying)::text, ', '::text)), (upper((jiraissue.creator)::text))
  • Sort Key: (upper((jiraissue.creator)::text))
  • Sort Method: quicksort Memory: 730kB
  • Buffers: shared hit=245
108. 1.033 13.363 ↓ 1.8 2,282 1

Hash Left Join (cost=443.17..543.37 rows=1,286 width=642) (actual time=8.870..13.363 rows=2,282 loops=1)

  • Output: jiraissue.id, jiraissue.issuenum, jiraissue.summary, jiraissue.created, jiraissue.updated, jiraissue.duedate, jiraissue.environment, jiraissue.timespent, jiraissue.watches, jiraissue.creator, jiraissue.assignee, project.pkey, project.pname, issuetype.pname, issuestatus.pname, priority.pname, (string_agg(((userhistoryitem.lastviewed)::character varying)::text, ', '::text)), (string_agg(((remotelink.resolved)::character varying)::text, ', '::text)), upper((jiraissue.creator)::text)
  • Hash Cond: ((jiraissue.priority)::text = (priority.id)::text)
  • Buffers: shared hit=245
109. 0.775 12.319 ↓ 1.8 2,282 1

Hash Join (cost=442.06..528.88 rows=1,286 width=508) (actual time=8.842..12.319 rows=2,282 loops=1)

  • Output: jiraissue.id, jiraissue.issuenum, jiraissue.summary, jiraissue.created, jiraissue.updated, jiraissue.duedate, jiraissue.environment, jiraissue.timespent, jiraissue.watches, jiraissue.priority, jiraissue.creator, jiraissue.assignee, project.pkey, project.pname, issuetype.pname, issuestatus.pname, (string_agg(((userhistoryitem.lastviewed)::character varying)::text, ', '::text)), (string_agg(((remotelink.resolved)::character varying)::text, ', '::text))
  • Hash Cond: ((jiraissue.issuestatus)::text = (issuestatus.id)::text)
  • Buffers: shared hit=244
110. 0.747 11.517 ↓ 1.8 2,282 1

Hash Join (cost=440.75..509.88 rows=1,286 width=375) (actual time=8.801..11.517 rows=2,282 loops=1)

  • Output: jiraissue.id, jiraissue.issuenum, jiraissue.summary, jiraissue.created, jiraissue.updated, jiraissue.duedate, jiraissue.environment, jiraissue.timespent, jiraissue.watches, jiraissue.issuestatus, jiraissue.priority, jiraissue.creator, jiraissue.assignee, project.pkey, project.pname, issuetype.pname, (string_agg(((userhistoryitem.lastviewed)::character varying)::text, ', '::text)), (string_agg(((remotelink.resolved)::character varying)::text, ', '::text))
  • Hash Cond: ((jiraissue.issuetype)::text = (issuetype.id)::text)
  • Buffers: shared hit=243
111. 1.612 10.758 ↓ 1.8 2,282 1

Hash Right Join (cost=439.45..490.90 rows=1,286 width=243) (actual time=8.781..10.758 rows=2,282 loops=1)

  • Output: jiraissue.id, jiraissue.issuenum, jiraissue.summary, jiraissue.created, jiraissue.updated, jiraissue.duedate, jiraissue.environment, jiraissue.timespent, jiraissue.watches, jiraissue.issuetype, jiraissue.issuestatus, jiraissue.priority, jiraissue.creator, jiraissue.assignee, project.pkey, project.pname, (string_agg(((userhistoryitem.lastviewed)::character varying)::text, ', '::text)), (string_agg(((remotelink.resolved)::character varying)::text, ', '::text))
  • Hash Cond: (((userhistoryitem.entityid)::integer)::numeric = jiraissue.id)
  • Buffers: shared hit=242
112. 2.463 3.487 ↑ 1.1 1,372 1

HashAggregate (cost=178.75..197.12 rows=1,470 width=38) (actual time=3.106..3.487 rows=1,372 loops=1)

  • Output: userhistoryitem.entityid, string_agg(((userhistoryitem.lastviewed)::character varying)::text, ', '::text)
  • Group Key: userhistoryitem.entityid
  • Buffers: shared hit=94
113. 1.024 1.024 ↓ 1.0 3,021 1

Seq Scan on jira.userhistoryitem (cost=0.00..148.88 rows=2,987 width=16) (actual time=0.012..1.024 rows=3,021 loops=1)

  • Output: userhistoryitem.id, userhistoryitem.entitytype, userhistoryitem.entityid, userhistoryitem.username, userhistoryitem.lastviewed, userhistoryitem.data
  • Filter: ((userhistoryitem.entitytype)::text = 'Issue'::text)
  • Rows Removed by Filter: 1400
  • Buffers: shared hit=94
114. 1.006 5.659 ↓ 1.8 2,282 1

Hash (cost=244.63..244.63 rows=1,286 width=211) (actual time=5.659..5.659 rows=2,282 loops=1)

  • Output: jiraissue.id, jiraissue.issuenum, jiraissue.summary, jiraissue.created, jiraissue.updated, jiraissue.duedate, jiraissue.environment, jiraissue.timespent, jiraissue.watches, jiraissue.issuetype, jiraissue.issuestatus, jiraissue.priority, jiraissue.creator, jiraissue.assignee, project.pkey, project.pname, (string_agg(((remotelink.resolved)::character varying)::text, ', '::text))
  • Buckets: 4096 (originally 2048) Batches: 1 (originally 1) Memory Usage: 415kB
  • Buffers: shared hit=148
115. 0.864 4.653 ↓ 1.8 2,282 1

Hash Right Join (cost=227.10..244.63 rows=1,286 width=211) (actual time=3.706..4.653 rows=2,282 loops=1)

  • Output: jiraissue.id, jiraissue.issuenum, jiraissue.summary, jiraissue.created, jiraissue.updated, jiraissue.duedate, jiraissue.environment, jiraissue.timespent, jiraissue.watches, jiraissue.issuetype, jiraissue.issuestatus, jiraissue.priority, jiraissue.creator, jiraissue.assignee, project.pkey, project.pname, (string_agg(((remotelink.resolved)::character varying)::text, ', '::text))
  • Hash Cond: (remotelink.issueid = jiraissue.id)
  • Buffers: shared hit=148
116. 0.496 0.656 ↑ 1.1 519 1

HashAggregate (cost=36.73..43.75 rows=561 width=39) (actual time=0.556..0.656 rows=519 loops=1)

  • Output: remotelink.issueid, string_agg(((remotelink.resolved)::character varying)::text, ', '::text)
  • Group Key: remotelink.issueid
  • Buffers: shared hit=22
117. 0.160 0.160 ↓ 1.0 865 1

Seq Scan on jira.remotelink (cost=0.00..30.42 rows=842 width=15) (actual time=0.009..0.160 rows=865 loops=1)

  • Output: remotelink.id, remotelink.issueid, remotelink.globalid, remotelink.title, remotelink.summary, remotelink.url, remotelink.iconurl, remotelink.icontitle, remotelink.relationship, remotelink.resolved, remotelink.statusname, remotelink.statusdescription, remotelink.statusiconurl, remotelink.statusicontitle, remotelink.statusiconlink, remotelink.statuscategorykey, remotelink.statuscategorycolorname, remotelink.applicationtype, remotelink.applicationname
  • Buffers: shared hit=22
118. 0.990 3.133 ↓ 1.8 2,282 1

Hash (cost=174.29..174.29 rows=1,286 width=179) (actual time=3.133..3.133 rows=2,282 loops=1)

  • Output: jiraissue.id, jiraissue.issuenum, jiraissue.summary, jiraissue.created, jiraissue.updated, jiraissue.duedate, jiraissue.environment, jiraissue.timespent, jiraissue.watches, jiraissue.issuetype, jiraissue.issuestatus, jiraissue.priority, jiraissue.creator, jiraissue.assignee, project.pkey, project.pname
  • Buckets: 4096 (originally 2048) Batches: 1 (originally 1) Memory Usage: 415kB
  • Buffers: shared hit=126
119. 1.822 2.143 ↓ 1.8 2,282 1

Hash Join (cost=1.07..174.29 rows=1,286 width=179) (actual time=0.034..2.143 rows=2,282 loops=1)

  • Output: jiraissue.id, jiraissue.issuenum, jiraissue.summary, jiraissue.created, jiraissue.updated, jiraissue.duedate, jiraissue.environment, jiraissue.timespent, jiraissue.watches, jiraissue.issuetype, jiraissue.issuestatus, jiraissue.priority, jiraissue.creator, jiraissue.assignee, project.pkey, project.pname
  • Hash Cond: (jiraissue.project = project.id)
  • Buffers: shared hit=126
120. 0.305 0.305 ↑ 1.0 2,571 1

Seq Scan on jira.jiraissue (cost=0.00..150.71 rows=2,571 width=167) (actual time=0.005..0.305 rows=2,571 loops=1)

  • Output: jiraissue.id, jiraissue.pkey, jiraissue.issuenum, jiraissue.project, jiraissue.reporter, jiraissue.assignee, jiraissue.creator, jiraissue.issuetype, jiraissue.summary, jiraissue.description, jiraissue.environment, jiraissue.priority, jiraissue.resolution, jiraissue.issuestatus, jiraissue.created, jiraissue.updated, jiraissue.duedate, jiraissue.resolutiondate, jiraissue.votes, jiraissue.watches, jiraissue.timeoriginalestimate, jiraissue.timeestimate, jiraissue.timespent, jiraissue.workflow_id, jiraissue.security, jiraissue.fixfor, jiraissue.component
  • Buffers: shared hit=125
121. 0.002 0.016 ↑ 1.0 2 1

Hash (cost=1.05..1.05 rows=2 width=24) (actual time=0.016..0.016 rows=2 loops=1)

  • Output: project.pkey, project.pname, project.id
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
122. 0.014 0.014 ↑ 1.0 2 1

Seq Scan on jira.project (cost=0.00..1.05 rows=2 width=24) (actual time=0.012..0.014 rows=2 loops=1)

  • Output: project.pkey, project.pname, project.id
  • Filter: (project.id = ANY ('{10000,10300}'::numeric[]))
  • Rows Removed by Filter: 2
  • Buffers: shared hit=1
123. 0.006 0.012 ↓ 1.2 16 1

Hash (cost=1.13..1.13 rows=13 width=276) (actual time=0.012..0.012 rows=16 loops=1)

  • Output: issuetype.pname, issuetype.id
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
124. 0.006 0.006 ↓ 1.2 16 1

Seq Scan on jira.issuetype (cost=0.00..1.13 rows=13 width=276) (actual time=0.005..0.006 rows=16 loops=1)

  • Output: issuetype.pname, issuetype.id
  • Buffers: shared hit=1
125. 0.007 0.027 ↓ 1.4 19 1

Hash (cost=1.14..1.14 rows=14 width=276) (actual time=0.027..0.027 rows=19 loops=1)

  • Output: issuestatus.pname, issuestatus.id
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
126. 0.020 0.020 ↓ 1.4 19 1

Seq Scan on jira.issuestatus (cost=0.00..1.14 rows=14 width=276) (actual time=0.015..0.020 rows=19 loops=1)

  • Output: issuestatus.pname, issuestatus.id
  • Buffers: shared hit=1
127. 0.003 0.011 ↓ 1.2 6 1

Hash (cost=1.05..1.05 rows=5 width=276) (actual time=0.011..0.011 rows=6 loops=1)

  • Output: priority.pname, priority.id
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
128. 0.008 0.008 ↓ 1.2 6 1

Seq Scan on jira.priority (cost=0.00..1.05 rows=5 width=276) (actual time=0.006..0.008 rows=6 loops=1)

  • Output: priority.pname, priority.id
  • Buffers: shared hit=1
129. 68.294 208.542 ↑ 1.0 77,002 1

Sort (cost=16,170.08..16,367.55 rows=78,989 width=36) (actual time=190.693..208.542 rows=77,002 loops=1)

  • Output: users_creator.display_name, users_creator.user_name, (upper((users_creator.user_name)::text))
  • Sort Key: (upper((users_creator.user_name)::text))
  • Sort Method: quicksort Memory: 11219kB
  • Buffers: shared hit=5667
130. 19.375 140.248 ↑ 1.0 78,744 1

Subquery Scan on users_creator (cost=8,164.80..9,744.57 rows=78,989 width=36) (actual time=91.519..140.248 rows=78,744 loops=1)

  • Output: users_creator.display_name, users_creator.user_name, upper((users_creator.user_name)::text)
  • Buffers: shared hit=5667
131. 73.289 120.873 ↑ 1.0 78,744 1

HashAggregate (cost=8,164.80..8,954.68 rows=78,989 width=36) (actual time=91.510..120.873 rows=78,744 loops=1)

  • Output: cwd_user_2.user_name, cwd_user_2.display_name
  • Group Key: cwd_user_2.user_name, cwd_user_2.display_name
  • Buffers: shared hit=5667
132. 47.584 47.584 ↑ 1.0 78,744 1

Seq Scan on jira.cwd_user cwd_user_2 (cost=0.00..7,769.85 rows=78,989 width=36) (actual time=0.014..47.584 rows=78,744 loops=1)

  • Output: cwd_user_2.id, cwd_user_2.directory_id, cwd_user_2.user_name, cwd_user_2.lower_user_name, cwd_user_2.active, cwd_user_2.created_date, cwd_user_2.updated_date, cwd_user_2.first_name, cwd_user_2.lower_first_name, cwd_user_2.last_name, cwd_user_2.lower_last_name, cwd_user_2.display_name, cwd_user_2.lower_display_name, cwd_user_2.email_address, cwd_user_2.lower_email_address, cwd_user_2.credential, cwd_user_2.deleted_externally, cwd_user_2.external_id
  • Filter: (cwd_user_2.active = '1'::numeric)
  • Rows Removed by Filter: 89628
  • Buffers: shared hit=5667