explain.depesz.com

PostgreSQL's explain analyze made readable

Result: heKG : Jira reporting no sort

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

Hash Right Join (cost=40,826,085.96..1,833,928,317.99 rows=67,572,254,252 width=819) (actual time=2,469.555..3,219.061 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=30844, temp read=10851 written=6849
2. 13.431 1,786.788 ↑ 13.2 65,701 1

Subquery Scan on custom (cost=16,629,948.93..18,500,454.45 rows=866,077 width=84) (actual time=1,477.351..1,786.788 rows=65,701 loops=1)

  • Output: custom.cfname, custom.cfvalue, custom.issue
  • Buffers: shared hit=19265, temp read=2311 written=2311
3. 8.209 1,773.357 ↑ 13.2 65,701 1

Append (cost=16,629,948.93..18,491,793.68 rows=866,077 width=84) (actual time=1,477.350..1,773.357 rows=65,701 loops=1)

  • Buffers: shared hit=19265, temp read=2311 written=2311
4. 255.938 1,749.479 ↑ 13.7 63,208 1

GroupAggregate (cost=16,629,948.93..18,481,930.11 rows=864,880 width=61) (actual time=1,477.349..1,749.479 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, temp read=2311 written=2311
5. 295.970 1,387.657 ↑ 397.5 108,436 1

Sort (cost=16,629,948.93..16,737,697.83 rows=43,099,558 width=247) (actual time=1,350.055..1,387.657 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: external merge Disk: 9800kB
  • Buffers: shared hit=12977, temp read=2311 written=2311
6. 42.409 1,091.687 ↑ 397.5 108,436 1

Merge Right Join (cost=68,439.30..1,147,385.90 rows=43,099,558 width=247) (actual time=986.945..1,091.687 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, temp read=1085 written=1085
7. 68.116 204.216 ↑ 1.1 75,137 1

Sort (cost=16,170.08..16,367.55 rows=78,989 width=36) (actual time=184.554..204.216 rows=75,137 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: 11173kB
  • Buffers: shared hit=5667
8. 22.659 136.100 ↑ 1.0 78,743 1

Subquery Scan on users (cost=8,164.80..9,744.57 rows=78,989 width=36) (actual time=87.546..136.100 rows=78,743 loops=1)

  • Output: users.display_name, users.user_name, upper((users.user_name)::text)
  • Buffers: shared hit=5667
9. 66.765 113.441 ↑ 1.0 78,743 1

HashAggregate (cost=8,164.80..8,954.68 rows=78,989 width=36) (actual time=87.537..113.441 rows=78,743 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. 46.676 46.676 ↑ 1.0 78,743 1

Seq Scan on jira.cwd_user (cost=0.00..7,769.85 rows=78,989 width=36) (actual time=0.037..46.676 rows=78,743 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. 13.752 845.062 ↑ 1.0 108,436 1

Materialize (cost=42,919.59..43,465.23 rows=109,128 width=219) (actual time=802.382..845.062 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)
  • Buffers: shared hit=7310, temp read=1085 written=1085
12. 223.383 831.310 ↑ 1.0 108,436 1

Sort (cost=42,919.59..43,192.41 rows=109,128 width=219) (actual time=802.378..831.310 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: external sort Disk: 8680kB
  • Buffers: shared hit=7310, temp read=1085 written=1085
13. 95.682 607.927 ↑ 1.0 108,436 1

Hash Left Join (cost=12,691.62..22,223.94 rows=109,128 width=219) (actual time=210.264..607.927 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. 26.832 417.073 ↑ 1.0 108,436 1

Hash Left Join (cost=12,670.71..21,512.66 rows=109,128 width=208) (actual time=94.630..417.073 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. 77.725 388.881 ↑ 1.0 108,436 1

Hash Left Join (cost=12,500.55..19,872.26 rows=109,128 width=203) (actual time=93.260..388.881 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. 102.047 310.669 ↑ 1.0 108,436 1

Hash Left Join (cost=12,462.85..17,788.41 rows=109,128 width=191) (actual time=92.724..310.669 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. 60.380 207.301 ↑ 1.0 108,436 1

Hash Join (cost=12,280.00..15,013.77 rows=109,128 width=149) (actual time=91.383..207.301 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. 31.310 146.701 ↑ 1.0 108,436 1

Merge Left Join (cost=12,260.34..13,493.60 rows=109,128 width=127) (actual time=91.147..146.701 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. 71.012 112.449 ↑ 1.0 108,436 1

Sort (cost=11,910.47..12,183.29 rows=109,128 width=123) (actual time=88.438..112.449 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.437 41.437 ↑ 1.0 108,436 1

Seq Scan on jira.customfieldvalue (cost=0.00..2,778.82 rows=109,128 width=123) (actual time=0.020..41.437 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.055 2.942 ↑ 1.4 2,088 1

Sort (cost=349.87..357.04 rows=2,869 width=13) (actual time=2.702..2.942 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.795 1.887 ↑ 1.0 2,771 1

Hash Right Join (cost=86.55..185.10 rows=2,869 width=13) (actual time=0.872..1.887 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.266 0.266 ↑ 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.029..0.266 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.344 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.482 0.482 ↑ 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.041..0.482 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.088 0.220 ↑ 1.0 384 1

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

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

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

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

Hash (cost=150.71..150.71 rows=2,571 width=48) (actual time=1.321..1.321 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.738 0.738 ↑ 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.015..0.738 rows=2,571 loops=1)

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

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

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

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

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

SubPlan (forHash Left Join)

33. 0.207 0.251 ↑ 1.0 104 1

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

  • Output: customfieldoption_2.customfield
  • Group Key: customfieldoption_2.customfield
  • Buffers: shared hit=7
34. 0.044 0.044 ↓ 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.044 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.076 1.360 ↑ 1.2 574 1

Hash (cost=161.83..161.83 rows=666 width=13) (actual time=1.360..1.360 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.400 1.284 ↑ 1.2 574 1

Hash Join (cost=93.72..161.83 rows=666 width=13) (actual time=0.696..1.284 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.200 0.200 ↑ 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.006..0.200 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.079 0.684 ↑ 1.3 574 1

Hash (cost=84.05..84.05 rows=774 width=8) (actual time=0.684..0.684 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.344 0.605 ↑ 1.3 574 1

Hash Join (cost=6.45..84.05 rows=774 width=8) (actual time=0.082..0.605 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.190 0.190 ↑ 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.004..0.190 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.007 0.071 ↓ 1.0 54 1

Hash (cost=5.80..5.80 rows=52 width=4) (actual time=0.071..0.071 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.064 0.064 ↓ 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.017..0.064 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.079 0.186 ↑ 1.2 461 1

Hash (cost=13.74..13.74 rows=574 width=17) (actual time=0.185..0.186 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.107 0.107 ↑ 1.0 574 1

Seq Scan on jira."AO_8542F1_IFJ_OBJ" (cost=0.00..13.74 rows=574 width=17) (actual time=0.012..0.107 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. 39.636 94.986 ↑ 1.0 78,743 1

HashAggregate (cost=8,164.80..9,152.16 rows=78,989 width=32) (actual time=77.992..94.986 rows=78,743 loops=1)

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

Seq Scan on jira.cwd_user cwd_user_4 (cost=0.00..7,967.32 rows=78,989 width=32) (actual time=0.016..55.350 rows=78,743 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.324 0.458 ↑ 1.0 104 1

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

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

Seq Scan on jira.customfieldoption customfieldoption_1 (cost=0.00..13.40 rows=640 width=7) (actual time=0.030..0.134 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.692 0.692 ↑ 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.692 rows=2,771 loops=1)

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

  • Output: "AO_8542F1_IFJ_OBJ_1"."OBJECT_KEY
  • Buffers: shared hit=8
53. 41.089 104.557 ↑ 1.0 78,743 1

HashAggregate (cost=8,164.80..9,152.16 rows=78,989 width=32) (actual time=88.598..104.557 rows=78,743 loops=1)

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

Seq Scan on jira.cwd_user cwd_user_3 (cost=0.00..7,967.32 rows=78,989 width=32) (actual time=0.022..63.468 rows=78,743 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.208 10.255 ↓ 4.7 1,517 1

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

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

HashAggregate (cost=685.54..689.62 rows=326 width=70) (actual time=9.344..10.047 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.181 7.718 ↑ 1.2 2,720 1

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

  • Buffers: shared hit=292
58. 1.046 3.845 ↑ 1.2 1,360 1

Hash Join (cost=185.22..314.25 rows=1,630 width=70) (actual time=1.342..3.845 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.576 2.772 ↑ 1.2 1,360 1

Hash Join (cost=184.13..266.29 rows=1,630 width=533) (actual time=1.289..2.772 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.684 0.967 ↑ 1.2 1,360 1

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

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

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

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

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

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

Seq Scan on jira.issuelinktype (cost=0.00..1.15 rows=11 width=536) (actual time=0.022..0.025 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.621 1.229 ↑ 1.0 2,571 1

Hash (cost=150.71..150.71 rows=2,571 width=17) (actual time=1.229..1.229 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.608 0.608 ↑ 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.019..0.608 rows=2,571 loops=1)

  • Output: issue_destination.issuenum, issue_destination.id, issue_destination.project
  • Buffers: shared hit=125
66. 0.002 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.025 0.025 ↑ 1.0 4 1

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

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

Hash Join (cost=185.22..314.25 rows=1,630 width=71) (actual time=1.233..3.692 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.560 2.619 ↑ 1.2 1,360 1

Hash Join (cost=184.13..266.29 rows=1,630 width=534) (actual time=1.203..2.619 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.655 0.900 ↑ 1.2 1,360 1

Hash Join (cost=1.29..61.04 rows=1,630 width=529) (actual time=0.033..0.900 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.229 0.229 ↓ 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.009..0.229 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.006 0.016 ↓ 1.1 12 1

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

  • Output: issuelinktype_1.inward, issuelinktype_1.id
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
73. 0.010 0.010 ↓ 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.010 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.577 1.159 ↑ 1.0 2,571 1

Hash (cost=150.71..150.71 rows=2,571 width=17) (actual time=1.159..1.159 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.582 0.582 ↑ 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.582 rows=2,571 loops=1)

  • Output: issue_source.issuenum, issue_source.id, issue_source.project
  • Buffers: shared hit=125
76. 0.002 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_source.pkey, proj_source.id
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
77. 0.011 0.011 ↑ 1.0 4 1

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

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

HashAggregate (cost=251.15..256.41 rows=421 width=70) (actual time=2.613..2.717 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.246 2.197 ↓ 1.2 561 1

Hash Join (cost=183.94..244.05 rows=473 width=15) (actual time=1.308..2.197 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.223 1.930 ↓ 1.2 561 1

Hash Join (cost=182.85..236.46 rows=473 width=17) (actual time=1.270..1.930 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.091 0.512 ↓ 1.2 561 1

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

  • Output: issuelink_2.source, issuelink_2.destination
  • Buffers: shared hit=20
82. 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.010..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
83. 0.408 0.408 ↓ 1.2 561 1

Seq Scan on jira.issuelink issuelink_2 (cost=0.00..41.23 rows=473 width=20) (actual time=0.051..0.408 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.551 1.195 ↑ 1.0 2,571 1

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

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

Hash (cost=1.04..1.04 rows=4 width=10) (actual time=0.021..0.021 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.017 0.017 ↑ 1.0 4 1

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

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

HashAggregate (cost=251.15..256.77 rows=450 width=71) (actual time=2.551..2.697 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.235 2.073 ↓ 1.2 561 1

Hash Join (cost=183.94..244.05 rows=473 width=16) (actual time=1.189..2.073 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.275 1.827 ↓ 1.2 561 1

Hash Join (cost=182.85..236.46 rows=473 width=18) (actual time=1.169..1.827 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.080 0.449 ↓ 1.2 561 1

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

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

Seq Scan on jira.issuelinktype issuelinktype_3 (cost=0.00..1.15 rows=1 width=20) (actual time=0.005..0.008 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.361 0.361 ↓ 1.2 561 1

Seq Scan on jira.issuelink issuelink_3 (cost=0.00..41.23 rows=473 width=20) (actual time=0.048..0.361 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.501 1.103 ↑ 1.0 2,571 1

Hash (cost=150.71..150.71 rows=2,571 width=17) (actual time=1.103..1.103 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.602 0.602 ↑ 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.602 rows=2,571 loops=1)

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

Hash (cost=1.04..1.04 rows=4 width=10) (actual time=0.011..0.011 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.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
98. 49.811 556.478 ↑ 87,901.9 2,282 1

Hash (cost=4,254,453.89..4,254,453.89 rows=200,592,171 width=684) (actual time=556.478..556.478 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: 32768 Batches: 16384 Memory Usage: 256kB
  • Buffers: shared hit=11579
99. 10.561 506.667 ↑ 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=478.915..506.667 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. 64.169 263.240 ↑ 1.1 75,137 1

Sort (cost=16,170.08..16,367.55 rows=78,989 width=36) (actual time=245.990..263.240 rows=75,137 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: 11173kB
  • Buffers: shared hit=5667
101. 22.109 199.071 ↑ 1.0 78,743 1

Subquery Scan on users_assignee (cost=8,164.80..9,744.57 rows=78,989 width=36) (actual time=152.039..199.071 rows=78,743 loops=1)

  • Output: users_assignee.display_name, users_assignee.user_name, upper((users_assignee.user_name)::text)
  • Buffers: shared hit=5667
102. 94.373 176.962 ↑ 1.0 78,743 1

HashAggregate (cost=8,164.80..8,954.68 rows=78,989 width=36) (actual time=152.013..176.962 rows=78,743 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. 82.589 82.589 ↑ 1.0 78,743 1

Seq Scan on jira.cwd_user cwd_user_1 (cost=0.00..7,769.85 rows=78,989 width=36) (actual time=0.030..82.589 rows=78,743 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.624 232.866 ↑ 222.6 2,282 1

Materialize (cost=224,380.75..226,920.25 rows=507,899 width=663) (actual time=231.384..232.866 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.999 232.242 ↑ 222.6 2,282 1

Sort (cost=224,380.75..225,650.50 rows=507,899 width=663) (actual time=231.379..232.242 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.701 229.243 ↑ 222.6 2,282 1

Merge Left Join (cost=16,779.86..26,950.70 rows=507,899 width=663) (actual time=204.229..229.243 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.030 15.668 ↓ 1.8 2,282 1

Sort (cost=609.78..613.00 rows=1,286 width=642) (actual time=15.054..15.668 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.023 12.638 ↓ 1.8 2,282 1

Hash Left Join (cost=443.17..543.37 rows=1,286 width=642) (actual time=8.382..12.638 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.746 11.605 ↓ 1.8 2,282 1

Hash Join (cost=442.06..528.88 rows=1,286 width=508) (actual time=8.359..11.605 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.735 10.826 ↓ 1.8 2,282 1

Hash Join (cost=440.75..509.88 rows=1,286 width=375) (actual time=8.316..10.826 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.493 10.080 ↓ 1.8 2,282 1

Hash Right Join (cost=439.45..490.90 rows=1,286 width=243) (actual time=8.298..10.080 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.172 3.159 ↑ 1.1 1,372 1

HashAggregate (cost=178.75..197.12 rows=1,470 width=38) (actual time=2.855..3.159 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. 0.987 0.987 ↓ 1.0 3,015 1

Seq Scan on jira.userhistoryitem (cost=0.00..148.88 rows=2,987 width=16) (actual time=0.014..0.987 rows=3,015 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.013 5.428 ↓ 1.8 2,282 1

Hash (cost=244.63..244.63 rows=1,286 width=211) (actual time=5.428..5.428 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.835 4.415 ↓ 1.8 2,282 1

Hash Right Join (cost=227.10..244.63 rows=1,286 width=211) (actual time=3.506..4.415 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.483 0.635 ↑ 1.1 519 1

HashAggregate (cost=36.73..43.75 rows=561 width=39) (actual time=0.554..0.635 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.152 0.152 ↓ 1.0 865 1

Seq Scan on jira.remotelink (cost=0.00..30.42 rows=842 width=15) (actual time=0.022..0.152 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. 1.044 2.945 ↓ 1.8 2,282 1

Hash (cost=174.29..174.29 rows=1,286 width=179) (actual time=2.945..2.945 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.517 1.901 ↓ 1.8 2,282 1

Hash Join (cost=1.07..174.29 rows=1,286 width=179) (actual time=0.031..1.901 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.368 0.368 ↑ 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.368 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.007 0.011 ↓ 1.2 16 1

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

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

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

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

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

  • Output: issuestatus.pname, issuestatus.id
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
126. 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.020..0.026 rows=19 loops=1)

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

Hash (cost=1.05..1.05 rows=5 width=276) (actual time=0.010..0.010 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. 63.087 203.874 ↑ 1.0 77,001 1

Sort (cost=16,170.08..16,367.55 rows=78,989 width=36) (actual time=187.683..203.874 rows=77,001 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: 11173kB
  • Buffers: shared hit=5667
130. 22.401 140.787 ↑ 1.0 78,743 1

Subquery Scan on users_creator (cost=8,164.80..9,744.57 rows=78,989 width=36) (actual time=90.366..140.787 rows=78,743 loops=1)

  • Output: users_creator.display_name, users_creator.user_name, upper((users_creator.user_name)::text)
  • Buffers: shared hit=5667
131. 69.721 118.386 ↑ 1.0 78,743 1

HashAggregate (cost=8,164.80..8,954.68 rows=78,989 width=36) (actual time=90.354..118.386 rows=78,743 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. 48.665 48.665 ↑ 1.0 78,743 1

Seq Scan on jira.cwd_user cwd_user_2 (cost=0.00..7,769.85 rows=78,989 width=36) (actual time=0.012..48.665 rows=78,743 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