Uploaded image for project: 'Bamboo Data Center'
  1. Bamboo Data Center
  2. BAM-17755

Artifact download task configurator is inefficient and can cause the UI to timeout

    XMLWordPrintable

Details

    Description

      Summary

      • Bamboo is running the same query twice to retrieve shared artifacts
      • The query used to retrieve shared artifacts is inefficient, resulting in performance issues.
      • One symptom is the inability to edit any task because the edit window is never displayed. After some minutes, an error message is displayed indicating that a 500 internal error occurred.

      Environment

      • Bamboo v5.8.1
      • Bamboo v5.12.3.1

      Steps to Reproduce

      1. enable the following logs under "<bamboo-install>/atlassian-bamboo/WEB-INF/classes/log4j.properties": 
        ## log hibernate prepared statements/SQL queries (equivalent to setting 'hibernate.show_sql' to 'true')
log4j.category.org.hibernate.SQL=DEBUG
## log hibernate prepared statement parameter values
log4j.category.org.hibernate.type=TRACE
      2. restart Bamboo
      3. create a Project / Plan to publish shared artifact
      4. create a Deployment / Environment to consume the artifact above generated
      5. in both testing instances (v5.8.1 and v5.12.3.1) Bamboo is running the same queries when selecting the Artifact download task in deploy/config/configureEnvironmentTasks.action?environmentId=? 
        select ARTIFACT_DEFINITION.*
 from ARTIFACT_DEFINITION
 cross join CHAIN_STAGE 
 where (ARTIFACT_DEFINITION.PRODUCER_JOB_ID in (select BUILD.BUILD_ID from BUILD BUILD where CHAIN_STAGE.STAGE_ID=BUILD.STAGE_ID)) 
   and ARTIFACT_DEFINITION.CHAIN_ARTIFACT=true
   and CHAIN_STAGE.BUILD_ID=?

        select ARTIFACT_SUBSCRIPTION.*
  from ARTIFACT_SUBSCRIPTION 
 where ARTIFACT_DEFINITION_ID=?

        select ARTIFACT_DEFINITION.*
 from ARTIFACT_DEFINITION
 cross join CHAIN_STAGE 
 where (ARTIFACT_DEFINITION.PRODUCER_JOB_ID in (select BUILD.BUILD_ID from BUILD BUILD where CHAIN_STAGE.STAGE_ID=BUILD.STAGE_ID)) 
   and ARTIFACT_DEFINITION.CHAIN_ARTIFACT=true
   and CHAIN_STAGE.BUILD_ID=?

      Expected Results

      • Bamboo should run the query only once
      • The query should be more efficient 
        bsp26194=# EXPLAIN ANALYZE SELECT artifact_definition_id, 
                                  label, 
                                  src_directory, 
                                  copy_pattern, 
                                  chain_artifact,
                                  producer_job_id
                             FROM artifact_definition a,
                                  build j,
                                  chain_stage s
                            where a.producer_job_id = j.build_id                                                                                                                                                                       and j.stage_id =  s.stage_id
                              and s.build_id = '423264464';
                                                                     QUERY PLAN                                                                     
----------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=78.77..344.96 rows=29 width=85) (actual time=1.878..15.909 rows=182 loops=1)
   ->  Hash Join  (cost=78.49..333.59 rows=27 width=8) (actual time=1.448..13.223 rows=94 loops=1)
         Hash Cond: (j.stage_id = s.stage_id)
         ->  Seq Scan on build j  (cost=0.00..226.24 rows=7624 width=16) (actual time=0.016..8.716 rows=7624 loops=1)
         ->  Hash  (cost=78.30..78.30 rows=15 width=8) (actual time=1.327..1.327 rows=15 loops=1)
               Buckets: 1024  Batches: 1  Memory Usage: 1kB
               ->  Seq Scan on chain_stage s  (cost=0.00..78.30 rows=15 width=8) (actual time=0.557..1.320 rows=15 loops=1)
                     Filter: (build_id = 423264464::bigint)
                     Rows Removed by Filter: 3449
   ->  Index Scan using art_def_producer_idx on artifact_definition a  (cost=0.29..0.40 rows=2 width=85) (actual time=0.026..0.027 rows=2 loops=94)
         Index Cond: (producer_job_id = j.build_id)
 Planning time: 278.499 ms
 Execution time: 16.925 ms
(13 rows)

      Actual Results

      • From the Bamboo logs on testing environments we have:
        <bamboo-install-5.8.1>/logs/catalina.out
        2016-07-15 18:37:03,769 INFO [http-bio-8085-exec-17] [AccessLogFilter] admin GET http://bamboo.webserver/deploy/config/editEnvironmentTask.action?environmentId=655361&taskId=2&decorator=nothing&confirm=true&_=1468618604244 6975183kb
2016-07-15 18:37:03,803 DEBUG [http-bio-8085-exec-17] [SQL] select mutableenv0_.ENVIRONMENT_ID as ENVIRONM1_69_3_, mutableenv0_.NAME as NAME69_3_, mutableenv0_.DESCRIPTION as DESCRIPT3_69_3_, mutableenv0_.XML_DEFINITION_DATA as XML4_69_3_, mutableenv0_.TRIGGERS_XML_DATA as TRIGGERS5_69_3_, mutableenv0_.CONFIGURATION_STATE as CONFIGUR6_69_3_, mutableenv0_.PACKAGE_DEFINITION_ID as PACKAGE7_69_3_, mutableenv0_.REQUIREMENT_SET as REQUIREM8_69_3_, mutableenv0_.NOTIFICATION_SET as NOTIFICA9_69_3_, mutableenv0_.LIST_POSITION as LIST10_69_3_, mutabledep1_.DEPLOYMENT_PROJECT_ID as DEPLOYMENT1_71_0_, mutabledep1_.NAME as NAME71_0_, mutabledep1_.DESCRIPTION as DESCRIPT3_71_0_, mutabledep1_.PLAN_KEY as PLAN4_71_0_, requiremen2_.REQUIREMENT_SET_ID as REQUIREM1_40_1_, notificati3_.NOTIFICATION_SET_ID as NOTIFICA1_36_2_, notificati3_.SET_TYPE as SET2_36_2_ from DEPLOYMENT_ENVIRONMENT mutableenv0_ left outer join DEPLOYMENT_PROJECT mutabledep1_ on mutableenv0_.PACKAGE_DEFINITION_ID=mutabledep1_.DEPLOYMENT_PROJECT_ID left outer join REQUIREMENT_SET requiremen2_ on mutableenv0_.REQUIREMENT_SET=requiremen2_.REQUIREMENT_SET_ID left outer join NOTIFICATION_SETS notificati3_ on mutableenv0_.NOTIFICATION_SET=notificati3_.NOTIFICATION_SET_ID where mutableenv0_.ENVIRONMENT_ID=?
2016-07-15 18:37:03,804 TRACE [http-bio-8085-exec-17] [BasicBinder] binding parameter [1] as [BIGINT] - 655361
2016-07-15 18:37:03,805 TRACE [http-bio-8085-exec-17] [BasicExtractor] found [557057] as column [DEPLOYMENT1_71_0_]
2016-07-15 18:37:03,805 TRACE [http-bio-8085-exec-17] [BasicExtractor] found [491522] as column [REQUIREM1_40_1_]
2016-07-15 18:37:03,805 TRACE [http-bio-8085-exec-17] [BasicExtractor] found [393218] as column [NOTIFICA1_36_2_]
2016-07-15 18:37:03,805 TRACE [http-bio-8085-exec-17] [BasicExtractor] found [Deployment] as column [NAME71_0_]
2016-07-15 18:37:03,805 TRACE [http-bio-8085-exec-17] [BasicExtractor] found [] as column [DESCRIPT3_71_0_]
2016-07-15 18:37:03,805 TRACE [http-bio-8085-exec-17] [BasicExtractor] found [Environment] as column [NAME69_3_]
2016-07-15 18:37:03,805 TRACE [http-bio-8085-exec-17] [BasicExtractor] found [] as column [DESCRIPT3_69_3_]
2016-07-15 18:37:03,805 TRACE [http-bio-8085-exec-17] [BasicExtractor] found [557057] as column [PACKAGE7_69_3_]
2016-07-15 18:37:03,805 TRACE [http-bio-8085-exec-17] [BasicExtractor] found [491522] as column [REQUIREM8_69_3_]
2016-07-15 18:37:03,805 TRACE [http-bio-8085-exec-17] [BasicExtractor] found [393218] as column [NOTIFICA9_69_3_]
2016-07-15 18:37:03,805 TRACE [http-bio-8085-exec-17] [BasicExtractor] found [0] as column [LIST10_69_3_]
2016-07-15 18:37:03,806 DEBUG [http-bio-8085-exec-17] [SQL] select notificati0_.NOTIFICATION_SET as NOTIFICA4_36_1_, notificati0_.NOTIFICATION_RULE_ID as NOTIFICA1_1_, notificati0_.NOTIFICATION_RULE_ID as NOTIFICA1_37_0_, notificati0_.RECIPIENT_TYPE as RECIPIENT2_37_0_, notificati0_.RECIPIENT as RECIPIENT37_0_, notificati0_.NOTIFICATION_SET as NOTIFICA4_37_0_, notificati0_.CONDITION_KEY as CONDITION5_37_0_, notificati0_.CONDITION_DATA as CONDITION6_37_0_ from NOTIFICATIONS notificati0_ where notificati0_.NOTIFICATION_SET=?
2016-07-15 18:37:03,806 TRACE [http-bio-8085-exec-17] [BasicBinder] binding parameter [1] as [BIGINT] - 393218
2016-07-15 18:37:03,806 DEBUG [http-bio-8085-exec-17] [SQL] select requiremen0_.REQUIREMENT_SET as REQUIREM8_40_1_, requiremen0_.REQUIREMENT_ID as REQUIREM1_1_, requiremen0_.REQUIREMENT_ID as REQUIREM1_41_0_, requiremen0_.KEY_IDENTIFIER as KEY2_41_0_, requiremen0_.REGEX_MATCH as REGEX3_41_0_, requiremen0_.READONLY_REQ as READONLY4_41_0_, requiremen0_.MATCH_VALUE as MATCH5_41_0_, requiremen0_.PLUGIN_MODULE_KEY as PLUGIN6_41_0_, requiremen0_.UNIQUE_IDENTIFIER as UNIQUE7_41_0_, requiremen0_.REQUIREMENT_SET as REQUIREM8_41_0_ from REQUIREMENT requiremen0_ where requiremen0_.REQUIREMENT_SET=?
2016-07-15 18:37:03,806 TRACE [http-bio-8085-exec-17] [BasicBinder] binding parameter [1] as [BIGINT] - 491522
2016-07-15 18:37:03,824 DEBUG [http-bio-8085-exec-17] [SQL] select artifactde0_.ARTIFACT_DEFINITION_ID as ARTIFACT1_52_, artifactde0_.LABEL as LABEL52_, artifactde0_.SRC_DIRECTORY as SRC3_52_, artifactde0_.COPY_PATTERN as COPY4_52_, artifactde0_.CHAIN_ARTIFACT as CHAIN5_52_, artifactde0_.PRODUCER_JOB_ID as PRODUCER6_52_ from ARTIFACT_DEFINITION artifactde0_ cross join CHAIN_STAGE chainstage1_ where (artifactde0_.PRODUCER_JOB_ID in (select alljobs2_.BUILD_ID from BUILD alljobs2_ where chainstage1_.STAGE_ID=alljobs2_.STAGE_ID)) and artifactde0_.CHAIN_ARTIFACT=? and chainstage1_.BUILD_ID=?
2016-07-15 18:37:03,824 TRACE [http-bio-8085-exec-17] [BasicBinder] binding parameter [1] as [BIT] - true
2016-07-15 18:37:03,824 TRACE [http-bio-8085-exec-17] [BasicBinder] binding parameter [2] as [BIGINT] - 360449
2016-07-15 18:37:03,845 TRACE [http-bio-8085-exec-17] [BasicExtractor] found [524289] as column [ARTIFACT1_52_]
2016-07-15 18:37:03,845 TRACE [http-bio-8085-exec-17] [BasicExtractor] found [Artifact] as column [LABEL52_]
2016-07-15 18:37:03,845 TRACE [http-bio-8085-exec-17] [BasicExtractor] found [] as column [SRC3_52_]
2016-07-15 18:37:03,845 TRACE [http-bio-8085-exec-17] [BasicExtractor] found [*.txt] as column [COPY4_52_]
2016-07-15 18:37:03,845 TRACE [http-bio-8085-exec-17] [BasicExtractor] found [true] as column [CHAIN5_52_]
2016-07-15 18:37:03,845 TRACE [http-bio-8085-exec-17] [BasicExtractor] found [360450] as column [PRODUCER6_52_]
2016-07-15 18:37:03,845 DEBUG [http-bio-8085-exec-17] [SQL] select subscripti0_.ARTIFACT_DEFINITION_ID as ARTIFACT2_52_1_, subscripti0_.ARTIFACT_SUBSCRIPTION_ID as ARTIFACT1_1_, subscripti0_.ARTIFACT_SUBSCRIPTION_ID as ARTIFACT1_53_0_, subscripti0_.ARTIFACT_DEFINITION_ID as ARTIFACT2_53_0_, subscripti0_.CONSUMER_JOB_ID as CONSUMER3_53_0_, subscripti0_.DST_DIRECTORY as DST4_53_0_ from ARTIFACT_SUBSCRIPTION subscripti0_ where subscripti0_.ARTIFACT_DEFINITION_ID=?
2016-07-15 18:37:03,845 TRACE [http-bio-8085-exec-17] [BasicBinder] binding parameter [1] as [BIGINT] - 524289
2016-07-15 18:37:03,857 DEBUG [http-bio-8085-exec-17] [SQL] select artifactde0_.ARTIFACT_DEFINITION_ID as ARTIFACT1_52_, artifactde0_.LABEL as LABEL52_, artifactde0_.SRC_DIRECTORY as SRC3_52_, artifactde0_.COPY_PATTERN as COPY4_52_, artifactde0_.CHAIN_ARTIFACT as CHAIN5_52_, artifactde0_.PRODUCER_JOB_ID as PRODUCER6_52_ from ARTIFACT_DEFINITION artifactde0_ cross join CHAIN_STAGE chainstage1_ where (artifactde0_.PRODUCER_JOB_ID in (select alljobs2_.BUILD_ID from BUILD alljobs2_ where chainstage1_.STAGE_ID=alljobs2_.STAGE_ID)) and artifactde0_.CHAIN_ARTIFACT=? and chainstage1_.BUILD_ID=?
2016-07-15 18:37:03,857 TRACE [http-bio-8085-exec-17] [BasicBinder] binding parameter [1] as [BIT] - true
2016-07-15 18:37:03,857 TRACE [http-bio-8085-exec-17] [BasicBinder] binding parameter [2] as [BIGINT] - 360449
2016-07-15 18:37:03,858 TRACE [http-bio-8085-exec-17] [BasicExtractor] found [524289] as column [ARTIFACT1_52_]
        <bamboo-install-5.12.3.1>/logs/catalina.out
        2016-07-15 18:57:55,704 INFO [http-nio-8085-exec-13] [AccessLogFilter] admin GET http://bamboo.webserver/deploy/config/editEnvironmentTask.action?environmentId=688129&taskId=2&decorator=nothing&confirm=true&_=1468619865808 389995kb
2016-07-15 18:57:55,758 DEBUG [http-nio-8085-exec-13] [SQL] select mutableenv0_.ENVIRONMENT_ID as ENVIRONM1_72_3_, mutableenv0_.NAME as NAME72_3_, mutableenv0_.DESCRIPTION as DESCRIPT3_72_3_, mutableenv0_.XML_DEFINITION_DATA as XML4_72_3_, mutableenv0_.TRIGGERS_XML_DATA as TRIGGERS5_72_3_, mutableenv0_.CONFIGURATION_STATE as CONFIGUR6_72_3_, mutableenv0_.PACKAGE_DEFINITION_ID as PACKAGE7_72_3_, mutableenv0_.REQUIREMENT_SET as REQUIREM8_72_3_, mutableenv0_.NOTIFICATION_SET as NOTIFICA9_72_3_, mutableenv0_.LIST_POSITION as LIST10_72_3_, mutabledep1_.DEPLOYMENT_PROJECT_ID as DEPLOYMENT1_74_0_, mutabledep1_.OID as OID74_0_, mutabledep1_.NAME as NAME74_0_, mutabledep1_.DESCRIPTION as DESCRIPT4_74_0_, mutabledep1_.PLAN_KEY as PLAN5_74_0_, requiremen2_.REQUIREMENT_SET_ID as REQUIREM1_42_1_, notificati3_.NOTIFICATION_SET_ID as NOTIFICA1_38_2_, notificati3_.SET_TYPE as SET2_38_2_ from DEPLOYMENT_ENVIRONMENT mutableenv0_ left outer join DEPLOYMENT_PROJECT mutabledep1_ on mutableenv0_.PACKAGE_DEFINITION_ID=mutabledep1_.DEPLOYMENT_PROJECT_ID left outer join REQUIREMENT_SET requiremen2_ on mutableenv0_.REQUIREMENT_SET=requiremen2_.REQUIREMENT_SET_ID left outer join NOTIFICATION_SETS notificati3_ on mutableenv0_.NOTIFICATION_SET=notificati3_.NOTIFICATION_SET_ID where mutableenv0_.ENVIRONMENT_ID=?
2016-07-15 18:57:55,758 TRACE [http-nio-8085-exec-13] [BasicBinder] binding parameter [1] as [BIGINT] - 688129
2016-07-15 18:57:55,759 TRACE [http-nio-8085-exec-13] [BasicExtractor] found [589825] as column [DEPLOYMENT1_74_0_]
2016-07-15 18:57:55,759 TRACE [http-nio-8085-exec-13] [BasicExtractor] found [524290] as column [REQUIREM1_42_1_]
2016-07-15 18:57:55,759 TRACE [http-nio-8085-exec-13] [BasicExtractor] found [393218] as column [NOTIFICA1_38_2_]
2016-07-15 18:57:55,759 TRACE [http-nio-8085-exec-13] [BasicExtractor] found [Deployment] as column [NAME74_0_]
2016-07-15 18:57:55,759 TRACE [http-nio-8085-exec-13] [BasicExtractor] found [] as column [DESCRIPT4_74_0_]
2016-07-15 18:57:55,759 TRACE [http-nio-8085-exec-13] [BasicExtractor] found [Environment] as column [NAME72_3_]
2016-07-15 18:57:55,759 TRACE [http-nio-8085-exec-13] [BasicExtractor] found [] as column [DESCRIPT3_72_3_]
2016-07-15 18:57:55,759 TRACE [http-nio-8085-exec-13] [BasicExtractor] found [589825] as column [PACKAGE7_72_3_]
2016-07-15 18:57:55,759 TRACE [http-nio-8085-exec-13] [BasicExtractor] found [524290] as column [REQUIREM8_72_3_]
2016-07-15 18:57:55,759 TRACE [http-nio-8085-exec-13] [BasicExtractor] found [393218] as column [NOTIFICA9_72_3_]
2016-07-15 18:57:55,759 TRACE [http-nio-8085-exec-13] [BasicExtractor] found [0] as column [LIST10_72_3_]
2016-07-15 18:57:55,759 DEBUG [http-nio-8085-exec-13] [SQL] select notificati0_.NOTIFICATION_SET as NOTIFICA4_38_1_, notificati0_.NOTIFICATION_RULE_ID as NOTIFICA1_1_, notificati0_.NOTIFICATION_RULE_ID as NOTIFICA1_39_0_, notificati0_.RECIPIENT_TYPE as RECIPIENT2_39_0_, notificati0_.RECIPIENT as RECIPIENT39_0_, notificati0_.NOTIFICATION_SET as NOTIFICA4_39_0_, notificati0_.CONDITION_KEY as CONDITION5_39_0_, notificati0_.CONDITION_DATA as CONDITION6_39_0_ from NOTIFICATIONS notificati0_ where notificati0_.NOTIFICATION_SET=?
2016-07-15 18:57:55,759 TRACE [http-nio-8085-exec-13] [BasicBinder] binding parameter [1] as [BIGINT] - 393218
2016-07-15 18:57:55,760 DEBUG [http-nio-8085-exec-13] [SQL] select requiremen0_.REQUIREMENT_SET as REQUIREM8_42_1_, requiremen0_.REQUIREMENT_ID as REQUIREM1_1_, requiremen0_.REQUIREMENT_ID as REQUIREM1_43_0_, requiremen0_.KEY_IDENTIFIER as KEY2_43_0_, requiremen0_.REGEX_MATCH as REGEX3_43_0_, requiremen0_.READONLY_REQ as READONLY4_43_0_, requiremen0_.MATCH_VALUE as MATCH5_43_0_, requiremen0_.PLUGIN_MODULE_KEY as PLUGIN6_43_0_, requiremen0_.UNIQUE_IDENTIFIER as UNIQUE7_43_0_, requiremen0_.REQUIREMENT_SET as REQUIREM8_43_0_ from REQUIREMENT requiremen0_ where requiremen0_.REQUIREMENT_SET=?
2016-07-15 18:57:55,760 TRACE [http-nio-8085-exec-13] [BasicBinder] binding parameter [1] as [BIGINT] - 524290
2016-07-15 18:57:55,766 DEBUG [http-nio-8085-exec-13] [SQL] select artifactde0_.ARTIFACT_DEFINITION_ID as ARTIFACT1_55_, artifactde0_.LABEL as LABEL55_, artifactde0_.SRC_DIRECTORY as SRC3_55_, artifactde0_.COPY_PATTERN as COPY4_55_, artifactde0_.CHAIN_ARTIFACT as CHAIN5_55_, artifactde0_.PRODUCER_JOB_ID as PRODUCER6_55_, artifactde0_.OID as OID55_ from ARTIFACT_DEFINITION artifactde0_ cross join CHAIN_STAGE chainstage1_ where (artifactde0_.PRODUCER_JOB_ID in (select alljobs2_.BUILD_ID from BUILD alljobs2_ where chainstage1_.STAGE_ID=alljobs2_.STAGE_ID)) and artifactde0_.CHAIN_ARTIFACT=? and chainstage1_.BUILD_ID=?
2016-07-15 18:57:55,766 TRACE [http-nio-8085-exec-13] [BasicBinder] binding parameter [1] as [BIT] - true
2016-07-15 18:57:55,766 TRACE [http-nio-8085-exec-13] [BasicBinder] binding parameter [2] as [BIGINT] - 360449
2016-07-15 18:57:55,767 TRACE [http-nio-8085-exec-13] [BasicExtractor] found [557057] as column [ARTIFACT1_55_]
2016-07-15 18:57:55,767 TRACE [http-nio-8085-exec-13] [BasicExtractor] found [Artifact] as column [LABEL55_]
2016-07-15 18:57:55,767 TRACE [http-nio-8085-exec-13] [BasicExtractor] found [] as column [SRC3_55_]
2016-07-15 18:57:55,767 TRACE [http-nio-8085-exec-13] [BasicExtractor] found [foo.txt] as column [COPY4_55_]
2016-07-15 18:57:55,767 TRACE [http-nio-8085-exec-13] [BasicExtractor] found [true] as column [CHAIN5_55_]
2016-07-15 18:57:55,767 TRACE [http-nio-8085-exec-13] [BasicExtractor] found [360450] as column [PRODUCER6_55_]
2016-07-15 18:57:55,767 DEBUG [http-nio-8085-exec-13] [SQL] select subscripti0_.ARTIFACT_DEFINITION_ID as ARTIFACT2_55_1_, subscripti0_.ARTIFACT_SUBSCRIPTION_ID as ARTIFACT1_1_, subscripti0_.ARTIFACT_SUBSCRIPTION_ID as ARTIFACT1_56_0_, subscripti0_.ARTIFACT_DEFINITION_ID as ARTIFACT2_56_0_, subscripti0_.CONSUMER_JOB_ID as CONSUMER3_56_0_, subscripti0_.DST_DIRECTORY as DST4_56_0_ from ARTIFACT_SUBSCRIPTION subscripti0_ where subscripti0_.ARTIFACT_DEFINITION_ID=?
2016-07-15 18:57:55,767 TRACE [http-nio-8085-exec-13] [BasicBinder] binding parameter [1] as [BIGINT] - 557057
2016-07-15 18:57:55,770 DEBUG [http-nio-8085-exec-13] [SQL] select artifactde0_.ARTIFACT_DEFINITION_ID as ARTIFACT1_55_, artifactde0_.LABEL as LABEL55_, artifactde0_.SRC_DIRECTORY as SRC3_55_, artifactde0_.COPY_PATTERN as COPY4_55_, artifactde0_.CHAIN_ARTIFACT as CHAIN5_55_, artifactde0_.PRODUCER_JOB_ID as PRODUCER6_55_, artifactde0_.OID as OID55_ from ARTIFACT_DEFINITION artifactde0_ cross join CHAIN_STAGE chainstage1_ where (artifactde0_.PRODUCER_JOB_ID in (select alljobs2_.BUILD_ID from BUILD alljobs2_ where chainstage1_.STAGE_ID=alljobs2_.STAGE_ID)) and artifactde0_.CHAIN_ARTIFACT=? and chainstage1_.BUILD_ID=?
2016-07-15 18:57:55,770 TRACE [http-nio-8085-exec-13] [BasicBinder] binding parameter [1] as [BIT] - true
2016-07-15 18:57:55,770 TRACE [http-nio-8085-exec-13] [BasicBinder] binding parameter [2] as [BIGINT] - 360449
2016-07-15 18:57:55,770 TRACE [http-nio-8085-exec-13] [BasicExtractor] found [557057] as column [ARTIFACT1_55_]
      • By having a considerable amount of shared artifacts, the SQL queries above can take minutes to run: 
        bsp26194=# EXPLAIN ANALYZE select ARTIFACT_DEFINITION.*                                                                                                                                                                                from ARTIFACT_DEFINITION                                                                                                                                                                                                      cross join CHAIN_STAGE                                                                                                                                                                                                        where (ARTIFACT_DEFINITION.PRODUCER_JOB_ID in (select BUILD.BUILD_ID from BUILD BUILD where CHAIN_STAGE.STAGE_ID=BUILD.STAGE_ID))                                                                                               and ARTIFACT_DEFINITION.CHAIN_ARTIFACT=true                                                                                                                                                                                   and CHAIN_STAGE.BUILD_ID=423264464;
                                                         QUERY PLAN                                                         
----------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..12019242.18 rows=48990 width=85) (actual time=183303.986..233750.705 rows=164 loops=1)
   Join Filter: (SubPlan 1)
   Rows Removed by Join Filter: 97816
   ->  Seq Scan on artifact_definition  (cost=0.00..202.19 rows=6532 width=85) (actual time=0.016..8.496 rows=6532 loops=1)
         Filter: chain_artifact
         Rows Removed by Filter: 1687
   ->  Materialize  (cost=0.00..78.38 rows=15 width=8) (actual time=0.000..0.003 rows=15 loops=6532)
         ->  Seq Scan on chain_stage  (cost=0.00..78.30 rows=15 width=8) (actual time=0.688..1.262 rows=15 loops=1)
               Filter: (build_id = 423264464)
               Rows Removed by Filter: 3449
   SubPlan 1
     ->  Seq Scan on build  (cost=0.00..245.30 rows=2 width=8) (actual time=1.097..2.382 rows=6 loops=97980)
           Filter: (chain_stage.stage_id = stage_id)
           Rows Removed by Filter: 7612
 Planning time: 0.290 ms
 Execution time: 233750.842 ms
(16 rows)

Time: 233751.842 ms

      Notes

      No notes provided.

      Workaround

      No workaround provided.

      Attachments

        Issue Links

          is related to

          BDEV-11936 Loading...

          Activity

            People

              mgardias Marcin Gardias
              rsperafico Rafael Sperafico (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: