Uploaded image for project: 'Jira Data Center'
  1. Jira Data Center
  2. JRASERVER-67735

Create index for columnlayoutitem table

XMLWordPrintable

    • 0
    • 1
    • We collect Jira feedback from various sources, and we evaluate what we've collected when planning our product roadmap. To understand how this piece of feedback will be reviewed, see our Implementation of New Features Policy.

      Problem Definition

      Table columnlayoutitem is used for storing Fields schema configs and it doesn't have optimal index structure.
      Snippet from the table:

      select * from columnlayoutitem limit 3;
  id   | columnlayout | fieldidentifier | horizontalposition
-------+--------------+-----------------+--------------------
 10300 |        10000 | issuetype       |                  0
 10301 |        10000 | issuekey        |                  1
 10302 |        10000 | summary         |                  2

      Indexes: 

      \d columnlayoutitem
             Table "public.columnlayoutitem"
       Column       |          Type          | Modifiers
--------------------+------------------------+-----------
 id                 | numeric(18,0)          | not null
 columnlayout       | numeric(18,0)          |
 fieldidentifier    | character varying(255) |
 horizontalposition | numeric(18,0)          |
Indexes:
    "pk_columnlayoutitem" PRIMARY KEY, btree (id)
    "idx_cli_fieldidentifier" btree (fieldidentifier)

      columnlayout is being used during data loading:

      com.atlassian.jira.issue.fields.layout.column.DefaultColumnLayoutManager#transformToColumnLayoutItems
...
 final List<GenericValue> columnLayoutItemGVs = ofBizDelegator.getRelated("ChildColumnLayoutItem", columnLayoutGV, ImmutableList.of("horizontalposition ASC"));

      Suggested Solution

      Add index for columnlayout column 

      CREATE INDEX [IX_columnlayoutitem_COLUMNLAYOUT] ON [jiraDB].[dbo].[columnlayoutitem] ([COLUMNLAYOUT])

      Workaround

      Add index manually

            [JRASERVER-67735] Create index for columnlayoutitem table

            Andrzej Kotas made changes -
            Remote Link New: This issue links to "Page (Confluence)" [ 633462 ]
            Andriy Yakovlev [Atlassian] made changes -
            Remote Link New: This issue links to "Page (Confluence)" [ 631803 ]
            SET Analytics Bot made changes -
            UIS Original: 2 New: 0
            SET Analytics Bot made changes -
            UIS New: 2
            Natalia Wroblewska made changes -
            Remote Link New: This issue links to "Page (Confluence)" [ 601695 ]
            Andrzej Kotas made changes -
            Remote Link New: This issue links to "Page (Confluence)" [ 600402 ]
            Andriy Yakovlev [Atlassian] made changes -
            Remote Link New: This issue links to "Page (Confluence)" [ 589493 ]
            Andriy Yakovlev [Atlassian] made changes -
            Description Original: h3. Problem Definition
            Table {{columnlayoutitem}} doesn't have optimal index structure.
            Snippet from the table:
            {noformat}
            select * from columnlayoutitem limit 3;
              id | columnlayout | fieldidentifier | horizontalposition
            -------+--------------+-----------------+--------------------
             10300 | 10000 | issuetype | 0
             10301 | 10000 | issuekey | 1
             10302 | 10000 | summary | 2
            {noformat}

            Indexes:
            {noformat}
            \d columnlayoutitem
                         Table "public.columnlayoutitem"
                   Column | Type | Modifiers
            --------------------+------------------------+-----------
             id | numeric(18,0) | not null
             columnlayout | numeric(18,0) |
             fieldidentifier | character varying(255) |
             horizontalposition | numeric(18,0) |
            Indexes:
                "pk_columnlayoutitem" PRIMARY KEY, btree (id)
                "idx_cli_fieldidentifier" btree (fieldidentifier)
            {noformat}

            h3. Suggested Solution
            Add index for _columnlayout_ column
            {code:SQL|borderStyle=solid}
            CREATE INDEX [IX_columnlayoutitem_COLUMNLAYOUT] ON [jiraDB].[dbo].[columnlayoutitem] ([COLUMNLAYOUT])
            {code}             		New: h3. Problem Definition
            Table {{columnlayoutitem}} is used for storing Fields schema configs and it doesn't have optimal index structure.
            Snippet from the table:
            {noformat}
            select * from columnlayoutitem limit 3;
              id | columnlayout | fieldidentifier | horizontalposition
            -------+--------------+-----------------+--------------------
             10300 | 10000 | issuetype | 0
             10301 | 10000 | issuekey | 1
             10302 | 10000 | summary | 2
            {noformat}

            Indexes:
            {noformat}
            \d columnlayoutitem
                         Table "public.columnlayoutitem"
                   Column | Type | Modifiers
            --------------------+------------------------+-----------
             id | numeric(18,0) | not null
             columnlayout | numeric(18,0) |
             fieldidentifier | character varying(255) |
             horizontalposition | numeric(18,0) |
            Indexes:
                "pk_columnlayoutitem" PRIMARY KEY, btree (id)
                "idx_cli_fieldidentifier" btree (fieldidentifier)
            {noformat}

            {{columnlayout}} is being used during data loading:
            {code:Java|borderStyle=solid}
            com.atlassian.jira.issue.fields.layout.column.DefaultColumnLayoutManager#transformToColumnLayoutItems
            ...
             final List<GenericValue> columnLayoutItemGVs = ofBizDelegator.getRelated("ChildColumnLayoutItem", columnLayoutGV, ImmutableList.of("horizontalposition ASC"));
            {code}

            h3. Suggested Solution
            Add index for _columnlayout_ column
            {code:SQL|borderStyle=solid}
            CREATE INDEX [IX_columnlayoutitem_COLUMNLAYOUT] ON [jiraDB].[dbo].[columnlayoutitem] ([COLUMNLAYOUT])
            {code}

            h3. Workaround
            Add index manually
            Andriy Yakovlev [Atlassian] made changes -
            Component/s New: Data Center - Database [ 55610 ]
            Component/s Original: System Administration - Others [ 43407 ]
            Andriy Yakovlev [Atlassian] made changes -
            Labels Original: database index New: database database-index index

              Unassigned Unassigned
              ayakovlev@atlassian.com Andriy Yakovlev [Atlassian]
              Votes:
              4 Vote for this issue
              Watchers:
              7 Start watching this issue

                Created:
                Updated: