explain.depesz.com

PostgreSQL's explain analyze made readable

Result: MXJ : Jira reporting wm

Settings
# exclusive inclusive rows x rows loops node
1. 1,877.731 4,241.736 ↑ 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=819) (actual time=2,391.967..4,241.736 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
  • Hash Cond: (custom.issue = jiraissue.id)
  • Buffers: shared hit=30850, temp read=4014 written=2219
2. 10.663 1,679.052 ↑ 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,435.001..1,679.052 rows=65,701 loops=1)

  • Output: custom.cfname, custom.cfvalue, custom.issue
  • Buffers: shared hit=19271
3. 6.003 1,668.389 ↑ 13.2 65,701 1

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

  • Buffers: shared hit=19271
4. 201.174 1,642.248 ↑ 13.7 63,208 1

GroupAggregate (cost=11,609,468.61..13,461,449.79 rows=864,880 width=61) (actual time=1,434.998..1,642.248 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=18687
5. 263.497 1,296.735 ↑ 397.5 108,436 1

Sort (cost=11,609,468.61..11,717,217.51 rows=43,099,558 width=247) (actual time=1,264.857..1,296.735 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=12983
6. 36.475 1,033.238 ↑ 397.5 108,436 1

Merge Right Join (cost=56,875.30..1,135,549.08 rows=43,099,558 width=247) (actual time=968.244..1,033.238 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=12980
7. 67.221 214.298 ↑ 1.1 75,138 1

Sort (cost=16,170.08..16,367.55 rows=78,989 width=36) (actual time=196.720..214.298 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
8. 20.047 147.077 ↑ 1.0 78,744 1

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

  • Output: users.display_name, users.user_name, upper((users.user_name)::text)
  • Buffers: shared hit=5667
9. 73.633 127.030 ↑ 1.0 78,744 1

HashAggregate (cost=8,164.80..8,954.68 rows=78,989 width=36) (actual time=100.470..127.030 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
10. 53.397 53.397 ↑ 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.019..53.397 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
11. 90.322 782.465 ↑ 1.0 108,436 1

Sort (cost=31,355.59..31,628.41 rows=109,128 width=219) (actual time=771.516..782.465 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=7313
12. 108.797 692.143 ↑ 1.0 108,436 1

Hash Left Join (cost=12,691.62..22,223.94 rows=109,128 width=219) (actual time=248.253..692.143 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=7313
13. 30.492 478.811 ↑ 1.0 108,436 1

Hash Left Join (cost=12,670.71..21,512.66 rows=109,128 width=208) (actual time=119.931..478.811 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=1638
14. 87.940 446.662 ↑ 1.0 108,436 1

Hash Left Join (cost=12,500.55..19,872.26 rows=109,128 width=203) (actual time=118.261..446.662 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=1589
15. 110.388 358.123 ↑ 1.0 108,436 1

Hash Left Join (cost=12,462.85..17,788.41 rows=109,128 width=191) (actual time=117.588..358.123 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=1575
16. 67.590 246.232 ↑ 1.0 108,436 1

Hash Join (cost=12,280.00..15,013.77 rows=109,128 width=149) (actual time=116.067..246.232 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=1450
17. 35.962 178.386 ↑ 1.0 108,436 1

Merge Left Join (cost=12,260.34..13,493.60 rows=109,128 width=127) (actual time=115.796..178.386 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=1439
18. 81.954 138.636 ↑ 1.0 108,436 1

Sort (cost=11,910.47..12,183.29 rows=109,128 width=123) (actual time=112.473..138.636 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
19. 56.682 56.682 ↑ 1.0 108,436 1

Seq Scan on jira.customfieldvalue (cost=0.00..2,778.82 rows=109,128 width=123) (actual time=0.025..56.682 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
20. 1.470 3.788 ↑ 1.4 2,088 1

Sort (cost=349.87..357.04 rows=2,869 width=13) (actual time=3.316..3.788 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=49
21. 1.005 2.318 ↑ 1.0 2,771 1

Hash Right Join (cost=86.55..185.10 rows=2,869 width=13) (actual time=1.082..2.318 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=49
22. 0.287 0.287 ↑ 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.010..0.287 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
23. 0.426 1.026 ↑ 1.0 2,771 1

Hash (cost=50.69..50.69 rows=2,869 width=13) (actual time=1.026..1.026 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
24. 0.600 0.600 ↑ 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.017..0.600 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
25. 0.104 0.256 ↑ 1.0 384 1

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

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

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

  • Output: customfield.cfname, customfield.id
  • Buffers: shared hit=11
27. 0.752 1.503 ↑ 1.0 2,571 1

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

  • Output: jiraissue_1.summary, jiraissue_1.id
  • Buckets: 4096 Batches: 1 Memory Usage: 238kB
  • Buffers: shared hit=125
28. 0.751 0.751 ↑ 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.026..0.751 rows=2,571 loops=1)

  • Output: jiraissue_1.summary, jiraissue_1.id
  • Buffers: shared hit=125
29. 0.170 0.314 ↓ 1.0 642 1

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

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

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

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

SubPlan (forHash Left Join)

32. 0.221 0.285 ↑ 1.0 104 1

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

  • Output: customfieldoption_2.customfield
  • Group Key: customfieldoption_2.customfield
  • Buffers: shared hit=7
33. 0.064 0.064 ↓ 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.064 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
34. 0.113 1.657 ↑ 1.2 574 1

Hash (cost=161.83..161.83 rows=666 width=13) (actual time=1.657..1.657 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
35. 0.498 1.544 ↑ 1.2 574 1

Hash Join (cost=93.72..161.83 rows=666 width=13) (actual time=0.817..1.544 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
36. 0.246 0.246 ↑ 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.246 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
37. 0.117 0.800 ↑ 1.3 574 1

Hash (cost=84.05..84.05 rows=774 width=8) (actual time=0.800..0.800 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
38. 0.374 0.683 ↑ 1.3 574 1

Hash Join (cost=6.45..84.05 rows=774 width=8) (actual time=0.107..0.683 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
39. 0.225 0.225 ↑ 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.225 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
40. 0.012 0.084 ↓ 1.0 54 1

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

  • Output: "AO_8542F1_IFJ_OBJ_TYPE_ATTR"."ID
  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
  • Buffers: shared hit=3
41. 0.072 0.072 ↓ 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.013..0.072 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
42. 0.114 0.255 ↑ 1.2 461 1

Hash (cost=13.74..13.74 rows=574 width=17) (actual time=0.255..0.255 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
43. 0.141 0.141 ↑ 1.0 574 1

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

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

SubPlan (forHash Left Join)

45. 45.089 104.280 ↑ 1.0 78,744 1

HashAggregate (cost=8,164.80..9,152.16 rows=78,989 width=32) (actual time=84.681..104.280 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
46. 59.191 59.191 ↑ 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.014..59.191 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
47.          

SubPlan (forGroupAggregate)

48. 0.474 0.630 ↑ 1.0 104 1

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

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

Seq Scan on jira.customfieldoption customfieldoption_1 (cost=0.00..13.40 rows=640 width=7) (actual time=0.028..0.156 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
50. 0.896 0.896 ↑ 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.012..0.896 rows=2,771 loops=1)

  • Output: "AO_8542F1_IFJ_OBJ_ATTR_VAL_2"."TEXT_VALUE
  • Buffers: shared hit=22
51. 0.199 0.199 ↑ 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.010..0.199 rows=574 loops=1)

  • Output: "AO_8542F1_IFJ_OBJ_1"."OBJECT_KEY
  • Buffers: shared hit=8
52. 56.295 142.614 ↑ 1.0 78,744 1

HashAggregate (cost=8,164.80..9,152.16 rows=78,989 width=32) (actual time=123.149..142.614 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
53. 86.319 86.319 ↑ 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.037..86.319 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
54. 0.361 12.867 ↓ 4.7 1,517 1

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

  • Output: "*SELECT* 2".issue, "*SELECT* 2".cfname, "*SELECT* 2".cfvalue
  • Buffers: shared hit=292
55. 3.352 12.506 ↓ 4.7 1,517 1

HashAggregate (cost=685.54..689.62 rows=326 width=70) (actual time=11.804..12.506 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
56. 0.268 9.154 ↑ 1.2 2,720 1

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

  • Buffers: shared hit=292
57. 1.057 4.202 ↑ 1.2 1,360 1

Hash Join (cost=185.22..314.25 rows=1,630 width=70) (actual time=1.497..4.202 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
58. 0.664 3.113 ↑ 1.2 1,360 1

Hash Join (cost=184.13..266.29 rows=1,630 width=533) (actual time=1.434..3.113 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
59. 0.744 1.115 ↑ 1.2 1,360 1

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

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

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

  • Output: issuelink.id, issuelink.linktype, issuelink.source, issuelink.destination, issuelink.sequence
  • Buffers: shared hit=19
61. 0.012 0.033 ↓ 1.1 12 1

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

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

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

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

Hash (cost=150.71..150.71 rows=2,571 width=17) (actual time=1.334..1.334 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
64. 0.679 0.679 ↑ 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.016..0.679 rows=2,571 loops=1)

  • Output: issue_destination.issuenum, issue_destination.id, issue_destination.project
  • Buffers: shared hit=125
65. 0.009 0.032 ↑ 1.0 4 1

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

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

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

  • Output: proj_destination.pkey, proj_destination.id
  • Buffers: shared hit=1
67. 1.267 4.684 ↑ 1.2 1,360 1

Hash Join (cost=185.22..314.25 rows=1,630 width=71) (actual time=1.713..4.684 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
68. 0.940 3.403 ↑ 1.2 1,360 1

Hash Join (cost=184.13..266.29 rows=1,630 width=534) (actual time=1.669..3.403 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
69. 0.830 1.119 ↑ 1.2 1,360 1

Hash Join (cost=1.29..61.04 rows=1,630 width=529) (actual time=0.046..1.119 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
70. 0.264 0.264 ↓ 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.005..0.264 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
71. 0.010 0.025 ↓ 1.1 12 1

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

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

Seq Scan on jira.issuelinktype issuelinktype_1 (cost=0.00..1.15 rows=11 width=536) (actual time=0.008..0.015 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
73. 0.747 1.344 ↑ 1.0 2,571 1

Hash (cost=150.71..150.71 rows=2,571 width=17) (actual time=1.344..1.344 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
74. 0.597 0.597 ↑ 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.004..0.597 rows=2,571 loops=1)

  • Output: issue_source.issuenum, issue_source.id, issue_source.project
  • Buffers: shared hit=125
75. 0.005 0.014 ↑ 1.0 4 1

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

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

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

  • Output: proj_source.pkey, proj_source.id
  • Buffers: shared hit=1
77. 1.060 4.220 ↓ 1.0 428 1

HashAggregate (cost=251.15..256.41 rows=421 width=70) (actual time=4.121..4.220 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
78. 0.402 3.160 ↓ 1.2 561 1

Hash Join (cost=183.94..244.05 rows=473 width=15) (actual time=1.700..3.160 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
79. 0.441 2.723 ↓ 1.2 561 1

Hash Join (cost=182.85..236.46 rows=473 width=17) (actual time=1.640..2.723 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
80. 0.128 0.803 ↓ 1.2 561 1

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

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

Seq Scan on jira.issuelinktype issuelinktype_2 (cost=0.00..1.15 rows=1 width=20) (actual time=0.011..0.013 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
82. 0.662 0.662 ↓ 1.2 561 1

Seq Scan on jira.issuelink issuelink_2 (cost=0.00..41.23 rows=473 width=20) (actual time=0.114..0.662 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
83. 0.880 1.479 ↑ 1.0 2,571 1

Hash (cost=150.71..150.71 rows=2,571 width=17) (actual time=1.479..1.479 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
84. 0.599 0.599 ↑ 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.008..0.599 rows=2,571 loops=1)

  • Output: issue_destination_1.issuenum, issue_destination_1.id, issue_destination_1.project
  • Buffers: shared hit=125
85. 0.015 0.035 ↑ 1.0 4 1

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

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

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

  • Output: proj_destination_1.pkey, proj_destination_1.id
  • Buffers: shared hit=1
87. 0.823 3.051 ↓ 1.2 548 1

HashAggregate (cost=251.15..256.77 rows=450 width=71) (actual time=2.932..3.051 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
88. 0.246 2.228 ↓ 1.2 561 1

Hash Join (cost=183.94..244.05 rows=473 width=16) (actual time=1.291..2.228 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
89. 0.289 1.948 ↓ 1.2 561 1

Hash Join (cost=182.85..236.46 rows=473 width=18) (actual time=1.239..1.948 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
90. 0.100 0.505 ↓ 1.2 561 1

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

  • Output: issuelink_3.destination, issuelink_3.source
  • Buffers: shared hit=20
91. 0.010 0.010 ↑ 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.010 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
92. 0.395 0.395 ↓ 1.2 561 1

Seq Scan on jira.issuelink issuelink_3 (cost=0.00..41.23 rows=473 width=20) (actual time=0.045..0.395 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
93. 0.665 1.154 ↑ 1.0 2,571 1

Hash (cost=150.71..150.71 rows=2,571 width=17) (actual time=1.154..1.154 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
94. 0.489 0.489 ↑ 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.005..0.489 rows=2,571 loops=1)

  • Output: issue_source_1.issuenum, issue_source_1.id, issue_source_1.project
  • Buffers: shared hit=125
95. 0.026 0.034 ↑ 1.0 4 1

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

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

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

  • Output: proj_source_1.pkey, proj_source_1.id
  • Buffers: shared hit=1
97. 26.714 684.953 ↑ 87,901.9 2,282 1

Hash (cost=4,254,453.89..4,254,453.89 rows=200,592,171 width=684) (actual time=684.953..684.953 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
98. 13.633 658.239 ↑ 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=623.814..658.239 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
99. 70.150 296.637 ↑ 1.1 75,138 1

Sort (cost=16,170.08..16,367.55 rows=78,989 width=36) (actual time=275.402..296.637 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
100. 19.827 226.487 ↑ 1.0 78,744 1

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

  • Output: users_assignee.display_name, users_assignee.user_name, upper((users_assignee.user_name)::text)
  • Buffers: shared hit=5667
101. 98.118 206.660 ↑ 1.0 78,744 1

HashAggregate (cost=8,164.80..8,954.68 rows=78,989 width=36) (actual time=182.056..206.660 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
102. 108.542 108.542 ↑ 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.081..108.542 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
103. 0.524 347.969 ↑ 222.6 2,282 1

Materialize (cost=224,380.75..226,920.25 rows=507,899 width=663) (actual time=346.732..347.969 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
104. 3.316 347.445 ↑ 222.6 2,282 1

Sort (cost=224,380.75..225,650.50 rows=507,899 width=663) (actual time=346.729..347.445 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
105. 12.561 344.129 ↑ 222.6 2,282 1

Merge Left Join (cost=16,779.86..26,950.70 rows=507,899 width=663) (actual time=313.815..344.129 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
106. 3.364 18.441 ↓ 1.8 2,282 1

Sort (cost=609.78..613.00 rows=1,286 width=642) (actual time=17.760..18.441 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
107. 1.143 15.077 ↓ 1.8 2,282 1

Hash Left Join (cost=443.17..543.37 rows=1,286 width=642) (actual time=10.462..15.077 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
108. 0.771 13.920 ↓ 1.8 2,282 1

Hash Join (cost=442.06..528.88 rows=1,286 width=508) (actual time=10.425..13.920 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
109. 0.772 13.119 ↓ 1.8 2,282 1

Hash Join (cost=440.75..509.88 rows=1,286 width=375) (actual time=10.386..13.119 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
110. 1.610 12.331 ↓ 1.8 2,282 1

Hash Right Join (cost=439.45..490.90 rows=1,286 width=243) (actual time=10.364..12.331 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
111. 3.204 4.381 ↑ 1.1 1,372 1

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

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

Seq Scan on jira.userhistoryitem (cost=0.00..148.88 rows=2,987 width=16) (actual time=0.017..1.177 rows=3,020 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
113. 1.273 6.340 ↓ 1.8 2,282 1

Hash (cost=244.63..244.63 rows=1,286 width=211) (actual time=6.340..6.340 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
114. 0.806 5.067 ↓ 1.8 2,282 1

Hash Right Join (cost=227.10..244.63 rows=1,286 width=211) (actual time=4.185..5.067 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
115. 0.522 0.810 ↑ 1.1 519 1

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

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

Seq Scan on jira.remotelink (cost=0.00..30.42 rows=842 width=15) (actual time=0.017..0.288 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
117. 1.262 3.451 ↓ 1.8 2,282 1

Hash (cost=174.29..174.29 rows=1,286 width=179) (actual time=3.451..3.451 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
118. 1.709 2.189 ↓ 1.8 2,282 1

Hash Join (cost=1.07..174.29 rows=1,286 width=179) (actual time=0.059..2.189 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
119. 0.448 0.448 ↑ 1.0 2,571 1

Seq Scan on jira.jiraissue (cost=0.00..150.71 rows=2,571 width=167) (actual time=0.008..0.448 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
120. 0.014 0.032 ↑ 1.0 2 1

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

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

Seq Scan on jira.project (cost=0.00..1.05 rows=2 width=24) (actual time=0.017..0.018 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
122. 0.004 0.016 ↓ 1.2 16 1

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

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

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

  • Output: issuetype.pname, issuetype.id
  • Buffers: shared hit=1
124. 0.004 0.030 ↓ 1.4 19 1

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

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

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

  • Output: issuestatus.pname, issuestatus.id
  • Buffers: shared hit=1
126. 0.004 0.014 ↓ 1.2 6 1

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

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

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

  • Output: priority.pname, priority.id
  • Buffers: shared hit=1
128. 99.854 313.127 ↑ 1.0 77,002 1

Sort (cost=16,170.08..16,367.55 rows=78,989 width=36) (actual time=294.483..313.127 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
129. 44.431 213.273 ↑ 1.0 78,744 1

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

  • Output: users_creator.display_name, users_creator.user_name, upper((users_creator.user_name)::text)
  • Buffers: shared hit=5667
130. 106.248 168.842 ↑ 1.0 78,744 1

HashAggregate (cost=8,164.80..8,954.68 rows=78,989 width=36) (actual time=120.950..168.842 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
131. 62.594 62.594 ↑ 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.010..62.594 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