explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7xsx : Jira reporting v1

Settings
# exclusive inclusive rows x rows loops node
1. 672.327 4,203.459 ↑ 1,035,637.7 65,247 1

Sort (cost=112,839,545,354.49..113,008,475,990.12 rows=67,572,254,252 width=828) (actual time=4,161.570..4,203.459 rows=65,247 loops=1)

  • Output: jiraissue.id, ((((project.pkey)::text || '-'::text) || (jiraissue.issuenum)::text)), issuetype.pname, jiraissue.summary, priority.pname, users_creator.display_name, users_assignee.display_name, (to_char(jiraissue.created, 'yyyy-mm-dd'::text)), (to_char(jiraissue.updated, 'yyyy-mm-dd'::text)), project.pname, jiraissue.duedate, issuestatus.pname, jiraissue.environment, jiraissue.timespent, jiraissue.watches, (string_agg(((userhistoryitem.lastviewed)::character varying)::text, ', '::text)), (string_agg(((remotelink.resolved)::character varying)::text, ', '::text)), custom.cfname, custom.cfvalue, project.pkey, jiraissue.issuenum
  • Sort Key: custom.cfname, project.pkey, jiraissue.issuenum
  • Sort Method: external merge Disk: 19312kB
  • Buffers: shared hit=30844, temp read=13266 written=9264
2. 923.054 3,531.132 ↑ 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=828) (actual time=2,761.474..3,531.132 rows=65,247 loops=1)

  • Output: jiraissue.id, (((project.pkey)::text || '-'::text) || (jiraissue.issuenum)::text), issuetype.pname, jiraissue.summary, priority.pname, users_creator.display_name, users_assignee.display_name, to_char(jiraissue.created, 'yyyy-mm-dd'::text), to_char(jiraissue.updated, 'yyyy-mm-dd'::text), project.pname, jiraissue.duedate, issuestatus.pname, jiraissue.environment, jiraissue.timespent, jiraissue.watches, (string_agg(((userhistoryitem.lastviewed)::character varying)::text, ', '::text)), (string_agg(((remotelink.resolved)::character varying)::text, ', '::text)), custom.cfname, custom.cfvalue, project.pkey, jiraissue.issuenum
  • Hash Cond: (custom.issue = jiraissue.id)
  • Buffers: shared hit=30844, temp read=10851 written=6849
3. 14.826 2,027.923 ↑ 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,674.135..2,027.923 rows=65,701 loops=1)

  • Output: custom.cfname, custom.cfvalue, custom.issue
  • Buffers: shared hit=19265, temp read=2311 written=2311
4. 8.247 2,013.097 ↑ 13.2 65,701 1

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

  • Buffers: shared hit=19265, temp read=2311 written=2311
5. 310.755 1,983.600 ↑ 13.7 63,208 1

GroupAggregate (cost=16,629,948.93..18,481,930.11 rows=864,880 width=61) (actual time=1,674.132..1,983.600 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
6. 360.655 1,486.244 ↑ 397.5 108,436 1

Sort (cost=16,629,948.93..16,737,697.83 rows=43,099,558 width=247) (actual time=1,440.048..1,486.244 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
7. 32.266 1,125.589 ↑ 397.5 108,436 1

Merge Right Join (cost=68,439.30..1,147,385.90 rows=43,099,558 width=247) (actual time=1,041.914..1,125.589 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
8. 69.554 309.728 ↑ 1.1 75,137 1

Sort (cost=16,170.08..16,367.55 rows=78,989 width=36) (actual time=292.043..309.728 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
9. 26.228 240.174 ↑ 1.0 78,743 1

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

  • Output: users.display_name, users.user_name, upper((users.user_name)::text)
  • Buffers: shared hit=5667
10. 117.168 213.946 ↑ 1.0 78,743 1

HashAggregate (cost=8,164.80..8,954.68 rows=78,989 width=36) (actual time=181.927..213.946 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
11. 96.778 96.778 ↑ 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.032..96.778 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
12. 10.955 783.595 ↑ 1.0 108,436 1

Materialize (cost=42,919.59..43,465.23 rows=109,128 width=219) (actual time=749.862..783.595 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
13. 190.818 772.640 ↑ 1.0 108,436 1

Sort (cost=42,919.59..43,192.41 rows=109,128 width=219) (actual time=749.857..772.640 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
14. 89.293 581.822 ↑ 1.0 108,436 1

Hash Left Join (cost=12,691.62..22,223.94 rows=109,128 width=219) (actual time=240.523..581.822 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
15. 23.202 378.784 ↑ 1.0 108,436 1

Hash Left Join (cost=12,670.71..21,512.66 rows=109,128 width=208) (actual time=102.777..378.784 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
16. 65.661 353.884 ↑ 1.0 108,436 1

Hash Left Join (cost=12,500.55..19,872.26 rows=109,128 width=203) (actual time=101.070..353.884 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
17. 86.208 287.667 ↑ 1.0 108,436 1

Hash Left Join (cost=12,462.85..17,788.41 rows=109,128 width=191) (actual time=100.469..287.667 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
18. 50.876 200.017 ↑ 1.0 108,436 1

Hash Join (cost=12,280.00..15,013.77 rows=109,128 width=149) (actual time=99.004..200.017 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
19. 26.724 148.909 ↑ 1.0 108,436 1

Merge Left Join (cost=12,260.34..13,493.60 rows=109,128 width=127) (actual time=98.756..148.909 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
20. 76.071 118.901 ↑ 1.0 108,436 1

Sort (cost=11,910.47..12,183.29 rows=109,128 width=123) (actual time=95.723..118.901 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
21. 42.830 42.830 ↑ 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..42.830 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
22. 1.170 3.284 ↑ 1.4 2,088 1

Sort (cost=349.87..357.04 rows=2,869 width=13) (actual time=3.026..3.284 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
23. 0.902 2.114 ↑ 1.0 2,771 1

Hash Right Join (cost=86.55..185.10 rows=2,869 width=13) (actual time=0.974..2.114 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
24. 0.273 0.273 ↑ 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.015..0.273 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
25. 0.410 0.939 ↑ 1.0 2,771 1

Hash (cost=50.69..50.69 rows=2,869 width=13) (actual time=0.938..0.939 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
26. 0.529 0.529 ↑ 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.036..0.529 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
27. 0.108 0.232 ↑ 1.0 384 1

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

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

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

  • Output: customfield.cfname, customfield.id
  • Buffers: shared hit=11
29. 0.670 1.442 ↑ 1.0 2,571 1

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

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

  • Output: jiraissue_1.summary, jiraissue_1.id
  • Buffers: shared hit=125
31. 0.147 0.274 ↓ 1.0 642 1

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

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

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

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

SubPlan (forHash Left Join)

34. 0.224 0.282 ↑ 1.0 104 1

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

  • Output: customfieldoption_2.customfield
  • Group Key: customfieldoption_2.customfield
  • Buffers: shared hit=7
35. 0.058 0.058 ↓ 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.058 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
36. 0.102 1.698 ↑ 1.2 574 1

Hash (cost=161.83..161.83 rows=666 width=13) (actual time=1.698..1.698 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
37. 0.519 1.596 ↑ 1.2 574 1

Hash Join (cost=93.72..161.83 rows=666 width=13) (actual time=0.777..1.596 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
38. 0.316 0.316 ↑ 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.007..0.316 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
39. 0.090 0.761 ↑ 1.3 574 1

Hash (cost=84.05..84.05 rows=774 width=8) (actual time=0.761..0.761 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
40. 0.369 0.671 ↑ 1.3 574 1

Hash Join (cost=6.45..84.05 rows=774 width=8) (actual time=0.091..0.671 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
41. 0.224 0.224 ↑ 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.006..0.224 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
42. 0.008 0.078 ↓ 1.0 54 1

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

  • Output: "AO_8542F1_IFJ_OBJ_TYPE_ATTR"."ID
  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
  • Buffers: shared hit=3
43. 0.070 0.070 ↓ 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.021..0.070 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
44. 0.087 0.221 ↑ 1.2 461 1

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

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

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

SubPlan (forHash Left Join)

47. 52.497 113.524 ↑ 1.0 78,743 1

HashAggregate (cost=8,164.80..9,152.16 rows=78,989 width=32) (actual time=89.133..113.524 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
48. 61.027 61.027 ↑ 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.013..61.027 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
49.          

SubPlan (forGroupAggregate)

50. 0.439 0.585 ↑ 1.0 104 1

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

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

Seq Scan on jira.customfieldoption customfieldoption_1 (cost=0.00..13.40 rows=640 width=7) (actual time=0.023..0.146 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
52. 0.902 0.902 ↑ 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.902 rows=2,771 loops=1)

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

  • Output: "AO_8542F1_IFJ_OBJ_1"."OBJECT_KEY
  • Buffers: shared hit=8
54. 75.807 184.899 ↑ 1.0 78,743 1

HashAggregate (cost=8,164.80..9,152.16 rows=78,989 width=32) (actual time=150.749..184.899 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
55. 109.092 109.092 ↑ 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.019..109.092 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
56. 0.287 14.189 ↓ 4.7 1,517 1

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

  • Output: "*SELECT* 2".issue, "*SELECT* 2".cfname, "*SELECT* 2".cfvalue
  • Buffers: shared hit=292
57. 3.008 13.902 ↓ 4.7 1,517 1

HashAggregate (cost=685.54..689.62 rows=326 width=70) (actual time=13.067..13.902 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
58. 0.326 10.894 ↑ 1.2 2,720 1

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

  • Buffers: shared hit=292
59. 1.748 6.476 ↑ 1.2 1,360 1

Hash Join (cost=185.22..314.25 rows=1,630 width=70) (actual time=2.323..6.476 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
60. 0.941 4.679 ↑ 1.2 1,360 1

Hash Join (cost=184.13..266.29 rows=1,630 width=533) (actual time=2.243..4.679 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
61. 1.146 1.583 ↑ 1.2 1,360 1

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

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

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

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

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

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

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

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

Hash (cost=150.71..150.71 rows=2,571 width=17) (actual time=2.155..2.155 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
66. 1.086 1.086 ↑ 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.015..1.086 rows=2,571 loops=1)

  • Output: issue_destination.issuenum, issue_destination.id, issue_destination.project
  • Buffers: shared hit=125
67. 0.007 0.049 ↑ 1.0 4 1

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

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

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

  • Output: proj_destination.pkey, proj_destination.id
  • Buffers: shared hit=1
69. 1.200 4.092 ↑ 1.2 1,360 1

Hash Join (cost=185.22..314.25 rows=1,630 width=71) (actual time=1.231..4.092 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
70. 0.676 2.877 ↑ 1.2 1,360 1

Hash Join (cost=184.13..266.29 rows=1,630 width=534) (actual time=1.198..2.877 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
71. 0.780 1.055 ↑ 1.2 1,360 1

Hash Join (cost=1.29..61.04 rows=1,630 width=529) (actual time=0.041..1.055 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
72. 0.244 0.244 ↓ 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.004..0.244 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
73. 0.007 0.031 ↓ 1.1 12 1

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

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

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

Hash (cost=150.71..150.71 rows=2,571 width=17) (actual time=1.146..1.146 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
76. 0.550 0.550 ↑ 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.550 rows=2,571 loops=1)

  • Output: issue_source.issuenum, issue_source.id, issue_source.project
  • Buffers: shared hit=125
77. 0.002 0.015 ↑ 1.0 4 1

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

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

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

  • Output: proj_source.pkey, proj_source.id
  • Buffers: shared hit=1
79. 0.758 4.491 ↓ 1.0 428 1

HashAggregate (cost=251.15..256.41 rows=421 width=70) (actual time=4.375..4.491 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
80. 0.331 3.733 ↓ 1.2 561 1

Hash Join (cost=183.94..244.05 rows=473 width=15) (actual time=2.375..3.733 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
81. 0.366 3.377 ↓ 1.2 561 1

Hash Join (cost=182.85..236.46 rows=473 width=17) (actual time=2.332..3.377 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
82. 0.129 0.801 ↓ 1.2 561 1

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

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

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

Seq Scan on jira.issuelink issuelink_2 (cost=0.00..41.23 rows=473 width=20) (actual time=0.091..0.656 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
85. 1.173 2.210 ↑ 1.0 2,571 1

Hash (cost=150.71..150.71 rows=2,571 width=17) (actual time=2.210..2.210 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
86. 1.037 1.037 ↑ 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.012..1.037 rows=2,571 loops=1)

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

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

  • Output: proj_destination_1.pkey, proj_destination_1.id
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
88. 0.019 0.019 ↑ 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.019 rows=4 loops=1)

  • Output: proj_destination_1.pkey, proj_destination_1.id
  • Buffers: shared hit=1
89. 0.609 2.570 ↓ 1.2 548 1

HashAggregate (cost=251.15..256.77 rows=450 width=71) (actual time=2.445..2.570 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
90. 0.211 1.961 ↓ 1.2 561 1

Hash Join (cost=183.94..244.05 rows=473 width=16) (actual time=1.136..1.961 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
91. 0.255 1.739 ↓ 1.2 561 1

Hash Join (cost=182.85..236.46 rows=473 width=18) (actual time=1.115..1.739 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
92. 0.075 0.429 ↓ 1.2 561 1

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

  • Output: issuelink_3.destination, issuelink_3.source
  • Buffers: shared hit=20
93. 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
94. 0.346 0.346 ↓ 1.2 561 1

Seq Scan on jira.issuelink issuelink_3 (cost=0.00..41.23 rows=473 width=20) (actual time=0.042..0.346 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
95. 0.561 1.055 ↑ 1.0 2,571 1

Hash (cost=150.71..150.71 rows=2,571 width=17) (actual time=1.054..1.055 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
96. 0.494 0.494 ↑ 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.008..0.494 rows=2,571 loops=1)

  • Output: issue_source_1.issuenum, issue_source_1.id, issue_source_1.project
  • Buffers: shared hit=125
97. 0.005 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
98. 0.006 0.006 ↑ 1.0 4 1

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

  • Output: proj_source_1.pkey, proj_source_1.id
  • Buffers: shared hit=1
99. 46.393 580.155 ↑ 87,901.9 2,282 1

Hash (cost=4,254,453.89..4,254,453.89 rows=200,592,171 width=684) (actual time=580.155..580.155 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
100. 9.941 533.762 ↑ 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=505.094..533.762 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
101. 65.855 286.634 ↑ 1.1 75,137 1

Sort (cost=16,170.08..16,367.55 rows=78,989 width=36) (actual time=267.351..286.634 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
102. 22.187 220.779 ↑ 1.0 78,743 1

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

  • Output: users_assignee.display_name, users_assignee.user_name, upper((users_assignee.user_name)::text)
  • Buffers: shared hit=5667
103. 108.291 198.592 ↑ 1.0 78,743 1

HashAggregate (cost=8,164.80..8,954.68 rows=78,989 width=36) (actual time=168.770..198.592 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
104. 90.301 90.301 ↑ 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.029..90.301 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
105. 0.447 237.187 ↑ 222.6 2,282 1

Materialize (cost=224,380.75..226,920.25 rows=507,899 width=663) (actual time=236.014..237.187 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
106. 2.813 236.740 ↑ 222.6 2,282 1

Sort (cost=224,380.75..225,650.50 rows=507,899 width=663) (actual time=236.009..236.740 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
107. 9.408 233.927 ↑ 222.6 2,282 1

Merge Left Join (cost=16,779.86..26,950.70 rows=507,899 width=663) (actual time=206.850..233.927 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
108. 3.065 15.403 ↓ 1.8 2,282 1

Sort (cost=609.78..613.00 rows=1,286 width=642) (actual time=14.730..15.403 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
109. 1.042 12.338 ↓ 1.8 2,282 1

Hash Left Join (cost=443.17..543.37 rows=1,286 width=642) (actual time=8.081..12.338 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
110. 0.741 11.286 ↓ 1.8 2,282 1

Hash Join (cost=442.06..528.88 rows=1,286 width=508) (actual time=8.056..11.286 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
111. 0.699 10.521 ↓ 1.8 2,282 1

Hash Join (cost=440.75..509.88 rows=1,286 width=375) (actual time=8.020..10.521 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
112. 1.509 9.808 ↓ 1.8 2,282 1

Hash Right Join (cost=439.45..490.90 rows=1,286 width=243) (actual time=7.998..9.808 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
113. 2.085 3.024 ↑ 1.1 1,371 1

HashAggregate (cost=178.75..197.12 rows=1,470 width=38) (actual time=2.708..3.024 rows=1,371 loops=1)

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

Seq Scan on jira.userhistoryitem (cost=0.00..148.88 rows=2,987 width=16) (actual time=0.011..0.939 rows=3,014 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
115. 0.994 5.275 ↓ 1.8 2,282 1

Hash (cost=244.63..244.63 rows=1,286 width=211) (actual time=5.275..5.275 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
116. 0.831 4.281 ↓ 1.8 2,282 1

Hash Right Join (cost=227.10..244.63 rows=1,286 width=211) (actual time=3.373..4.281 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
117. 0.495 0.645 ↑ 1.1 519 1

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

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

Seq Scan on jira.remotelink (cost=0.00..30.42 rows=842 width=15) (actual time=0.007..0.150 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
119. 1.035 2.805 ↓ 1.8 2,282 1

Hash (cost=174.29..174.29 rows=1,286 width=179) (actual time=2.805..2.805 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
120. 1.438 1.770 ↓ 1.8 2,282 1

Hash Join (cost=1.07..174.29 rows=1,286 width=179) (actual time=0.029..1.770 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
121. 0.317 0.317 ↑ 1.0 2,571 1

Seq Scan on jira.jiraissue (cost=0.00..150.71 rows=2,571 width=167) (actual time=0.004..0.317 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
122. 0.003 0.015 ↑ 1.0 2 1

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

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

Seq Scan on jira.project (cost=0.00..1.05 rows=2 width=24) (actual time=0.011..0.012 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
124. 0.003 0.014 ↓ 1.2 16 1

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

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

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

  • Output: issuetype.pname, issuetype.id
  • Buffers: shared hit=1
126. 0.008 0.024 ↓ 1.4 19 1

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

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

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

  • Output: issuestatus.pname, issuestatus.id
  • Buffers: shared hit=1
128. 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
129. 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
130. 64.728 209.116 ↑ 1.0 77,001 1

Sort (cost=16,170.08..16,367.55 rows=78,989 width=36) (actual time=190.474..209.116 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
131. 22.105 144.388 ↑ 1.0 78,743 1

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

  • Output: users_creator.display_name, users_creator.user_name, upper((users_creator.user_name)::text)
  • Buffers: shared hit=5667
132. 72.957 122.283 ↑ 1.0 78,743 1

HashAggregate (cost=8,164.80..8,954.68 rows=78,989 width=36) (actual time=93.842..122.283 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
133. 49.326 49.326 ↑ 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..49.326 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