Medium Issue Summary
When an SLA is part of several projects and we make a JQL related to that SLA, Jira creates an unnecessarily large Lucene query for the index.
Depending on the data, this can hurt the performance for SLA queries.
Steps to Reproduce
- Create many projects that use the same SLA (1000+)
- Have an admin run a query such as this one below or configure an A4J SLA trigger
"Time to resolution" < 0 AND project = ITSD
Expected Results
The Lucene query only queries the desired project and the SLA goals related to that project.
Actual Results
The results from the JQL are the expected ones.
However, for each SLA term, JSM add to the Lucene query:
- All the projects the user has access to;
- For each goal, it adds the goal and shifted start date for that goal
Here's one example we can see with com.atlassian.jira.issue.search.providers set to DEBUG:
2024-12-13 23:11:32,256+0000 automation-rule-executor:thread-4 DEBUG admin [c.a.j.i.search.providers.LuceneSearchProvider] JQL query: {cf[10120] > 0} AND {cf[10120] < "3m"} AND {project in (10000)}
2024-12-13 23:11:32,278+0000 automation-rule-executor:thread-4 DEBUG admin [c.a.j.i.search.providers.LuceneSearchProvider] JQL lucene query: +(+(+((+customfield_10120_last_cycle_state:paused +customfield_10120_paused_cycle_remaining_time:[1 TO 9223372036854775807]) (+customfield_10120_last_cycle_state:running +((+customfield_10120_goal_id:[176 TO 176] +customfield_10120_shifted_start_date:[1734080400001 TO 9223372036854775807]) (+customfield_10120_goal_id:[1306 TO 1306] +customfield_10120_shifted_start_date:[1734080400001 TO 9223372036854775807]) (+customfield_10120_goal_id:[2866 TO 2866] +customfield_10120_shifted_start_date:[1734080400001 TO 9223372036854775807]) (TRIMMED) (+customfield_10120_goal_id:[4205 TO 4205] +customfield_10120_shifted_start_date:[1734094800001 TO 9223372036854775807]) (+customfield_10120_goal_id:[1259 TO 1259] +customfield_10120_shifted_start_date:[1734080400001 TO 9223372036854775807]) (+customfield_10120_goal_id:[1535 TO 1535] +customfield_10120_shifted_start_date:[1734094800001 TO 9223372036854775807]) (+customfield_10120_goal_id:[826 TO 826] +customfield_10120_shifted_start_date:[1734080400001 TO 9223372036854775807]) (+customfield_10120_goal_id:[2425 TO 2425] +customfield_10120_shifted_start_date:[1734094800001 TO 9223372036854775807]) (+customfield_10120_goal_id:[2976 TO 2976] +customfield_10120_shifted_start_date:[1734080400001 TO 9223372036854775807]) (+customfield_10120_goal_id:[2156 TO 2156] +customfield_10120_shifted_start_date:[1734080400001 TO 9223372036854775807]) (+customfield_10120_goal_id:[3866 TO 3866] +customfield_10120_shifted_start_date:[1734080400001 TO 9223372036854775807]) (+customfield_10120_goal_id:[1796 TO 1796] +customfield_10120_shifted_start_date:[1734080400001 TO 9223372036854775807]) (+customfield_10120_goal_id:[3046 TO 3046] +customfield_10120_shifted_start_date:[1734080400001 TO 9223372036854775807])))) +(+(projid:10000 projid:10200 projid:10209 projid:10300 projid:10201 projid:10202 projid:10203 projid:10204 projid:10205 projid:10206 projid:10207 projid:10208 projid:10311 projid:10320 projid:10410 projid:10321 projid:10322 projid:10323 projid:10324 projid:10325 projid:10326 projid:10327 projid:10328 projid:10329 projid:10312 projid:10330 projid:10331 projid:10332 projid:10333 projid:10334 projid:10335 projid:10336 projid:10337 projid:10338 projid:10339 projid:10313 projid:10340 projid:10341 projid:10342 projid:10343 projid:10344 projid:10345 projid:10346 projid:10347 projid:10348 projid:10349 projid:10314 projid:10350 projid:10351 projid:10352 projid:10353 projid:10354 projid:10355 projid:10356 projid:10357 projid:10358 projid:10359 projid:10315 projid:10360 projid:10361 projid:10362 projid:10363 projid:10364 projid:10365 projid:10366 projid:10367 projid:10368 projid:10369 projid:10316 projid:10370 projid:10371 projid:10372 projid:10373 projid:10374 projid:10375 projid:10376 projid:10377 projid:10378 projid:10379 projid:10317 projid:10380 projid:10381 projid:10382 projid:10383 projid:10384 projid:10385 projid:10386 projid:10387 projid:10388 projid:10389 projid:10318 projid:10390 projid:10391 projid:10392 projid:10393 projid:10394 projid:10395 projid:10396 projid:10397 projid:10398 projid:10399 projid:10319 projid:10400 projid:10401 projid:10402 projid:10403 projid:10404 projid:10405 projid:10406 projid:10407 projid:10408 projid:10409 projid:10411 projid:10438 projid:10708 projid:10441 projid:10444 projid:10447 projid:10450 projid:10453 projid:10456 projid:10459 projid:10462 projid:10465 projid:10414 projid:10468 projid:10471 projid:10474 projid:10477 projid:10480 projid:10483 projid:10486 projid:10489 projid:10492 projid:10495 projid:10498 projid:10501 projid:10504 projid:10507 projid:10510 projid:10513 projid:10516 projid:10519 projid:10522 projid:10525 projid:10528 projid:10531 projid:10535 projid:10538 projid:10541 projid:10544 projid:10547 projid:10550 projid:10553 projid:10556 projid:10423 projid:10559 projid:10562 projid:10565 projid:10568 projid:10571 projid:10574 projid:10577 projid:10580 projid:10583 projid:10586 projid:10426 projid:10589 projid:10592 projid:10595 projid:10598 projid:10601 projid:10604 projid:10607 projid:10610 projid:10613 projid:10616 projid:10429 projid:10619 projid:10622 projid:10625 projid:10628 projid:10631 projid:10633 projid:10637 projid:10639 projid:10642 projid:10646 projid:10432 projid:10649 projid:10652 projid:10654 projid:10658 projid:10661 projid:10664 projid:10666 projid:10670 projid:10672 projid:10675 projid:10435 projid:10678 projid:10681 projid:10684 projid:10687 projid:10690 projid:10693 projid:10696 projid:10699 projid:10702 projid:10705 projid:10412 projid:10440 projid:10443 projid:10446 projid:10449 projid:10452 projid:10455 projid:10458 projid:10461 projid:10464 projid:10467 projid:10416 projid:10470 projid:10473 projid:10476 projid:10479 projid:10482 projid:10485 projid:10488 projid:10491 projid:10494 projid:10497 projid:10418 projid:10500 projid:10503 projid:10506 projid:10509 projid:10512 projid:10515 projid:10518 projid:10521 projid:10524 projid:10527 projid:10422 projid:10530 projid:10533 projid:10536 projid:10539 projid:10542 projid:10545 projid:10548 projid:10551 projid:10554 projid:10557 projid:10425 projid:10560 projid:10563 projid:10566 projid:10569 projid:10572 projid:10575 projid:10578 projid:10581 projid:10584 projid:10587 projid:10428 projid:10590 projid:10593 projid:10596 projid:10599 projid:10602 projid:10605 projid:10608 projid:10611 projid:10614 projid:10617 projid:10431 projid:10620 projid:10623 projid:10626 projid:10629 projid:10632 projid:10635 projid:10638 projid:10641 projid:10644 projid:10647 projid:10434 projid:10650 projid:10653 projid:10659 projid:10662 projid:10665 projid:10668 projid:10671 projid:10674 projid:10677 projid:10437 projid:10680 projid:10683 projid:10686 projid:10689 projid:10692 projid:10695 projid:10698 projid:10701 projid:10704 projid:10707 projid:10413 projid:10439 projid:10709 projid:10442 projid:10445 projid:10448 projid:10451 projid:10454 projid:10457 projid:10460 projid:10463 projid:10466 projid:10415 projid:10469 projid:10472 projid:10475 projid:10478 projid:10481 projid:10484 projid:10487 projid:10490 projid:10493 projid:10496 projid:10417 projid:10499 projid:10502 projid:10505 projid:10508 projid:10511 projid:10514 projid:10517 projid:10520 projid:10523 projid:10526 projid:10421 projid:10529 projid:10532 projid:10534 projid:10537 projid:10540 projid:10543 projid:10546 projid:10549 projid:10552 projid:10555 projid:10424 projid:10558 projid:10561 projid:10564 projid:10567 projid:10570 projid:10573 projid:10576 projid:10579 projid:10582 projid:10585 projid:10427 projid:10588 projid:10591 projid:10594 projid:10597 projid:10600 projid:10603 projid:10606 projid:10609 projid:10612 projid:10615 projid:10430 projid:10618 projid:10621 projid:10624 projid:10627 projid:10630 projid:10634 projid:10636 projid:10640 projid:10643 projid:10645 projid:10433 projid:10648 projid:10651 projid:10655 projid:10657 projid:10660 projid:10663 projid:10667 projid:10669 projid:10673 projid:10676 projid:10436 projid:10679 projid:10682 projid:10685 projid:10688 projid:10691 projid:10694 projid:10697 projid:10700 projid:10703 projid:10706 projid:10711 projid:10712 projid:10721 projid:10722 projid:10723 projid:10724 projid:10725 projid:10726 projid:10713 projid:10714 projid:10715 projid:10716 projid:10717 projid:10718 projid:10719 projid:10720 projid:10301 projid:10310 projid:10302 projid:10303 projid:10304 projid:10305 projid:10306 projid:10307 projid:10308 projid:10309 projid:10125))) +(+((+customfield_10120_last_cycle_state:paused +customfield_10120_paused_cycle_remaining_time:[-9223372036854775808 TO 179999]) (+customfield_10120_last_cycle_state:running +((+customfield_10120_goal_id:[176 TO 176] +customfield_10120_shifted_start_date:[-9223372036854775808 TO 1734080579999]) (+customfield_10120_goal_id:[1306 TO 1306] +customfield_10120_shifted_start_date:[-9223372036854775808 TO 1734080579999]) (+customfield_10120_goal_id:[2866 TO 2866] +customfield_10120_shifted_start_date:[-9223372036854775808 TO 1734080579999]) (+customfield_10120_goal_id:[1586 TO 1586] +customfield_10120_shifted_start_date:[-9223372036854775808 TO 1734080579999]) (+customfield_10120_goal_id:[2435 TO 2435] +customfield_10120_shifted_start_date:[-9223372036854775808 TO 1734094979999]) (TRIMMED))(+customfield_10120_goal_id:[826 TO 826] +customfield_10120_shifted_start_date:[-9223372036854775808 TO 1734080579999]) (+customfield_10120_goal_id:[2425 TO 2425] +customfield_10120_shifted_start_date:[-9223372036854775808 TO 1734094979999]) (+customfield_10120_goal_id:[2976 TO 2976] +customfield_10120_shifted_start_date:[-9223372036854775808 TO 1734080579999]) (+customfield_10120_goal_id:[2156 TO 2156] +customfield_10120_shifted_start_date:[-9223372036854775808 TO 1734080579999]) (+customfield_10120_goal_id:[3866 TO 3866] +customfield_10120_shifted_start_date:[-9223372036854775808 TO 1734080579999]) (+customfield_10120_goal_id:[1796 TO 1796] +customfield_10120_shifted_start_date:[-9223372036854775808 TO 1734080579999]) (+customfield_10120_goal_id:[3046 TO 3046] +customfield_10120_shifted_start_date:[-9223372036854775808 TO 1734080579999])))) +(+(projid:10000 projid:10200 projid:10209 projid:10300 projid:10201 projid:10202 projid:10203 projid:10204 projid:10205 projid:10206 projid:10207 projid:10208 projid:10311 projid:10320 projid:10410 projid:10321 projid:10322 projid:10323 projid:10324 projid:10325 projid:10326 projid:10327 projid:10328 projid:10329 projid:10312 projid:10330 projid:10331 projid:10332 projid:10333 projid:10334 projid:10335 projid:10336 projid:10337 projid:10338 projid:10339 projid:10313 projid:10340 projid:10341 projid:10342 projid:10343 projid:10344 projid:10345 projid:10346 projid:10347 projid:10348 projid:10349 projid:10314 projid:10350 projid:10351 projid:10352 projid:10353 projid:10354 projid:10355 projid:10356 projid:10357 projid:10358 projid:10359 projid:10315 projid:10360 projid:10361 projid:10362 projid:10363 projid:10364 projid:10365 projid:10366 projid:10367 projid:10368 projid:10369 projid:10316 projid:10370 projid:10371 projid:10372 projid:10373 projid:10374 projid:10375 projid:10376 projid:10377 projid:10378 projid:10379 projid:10317 projid:10380 projid:10381 projid:10382 projid:10383 projid:10384 projid:10385 projid:10386 projid:10387 projid:10388 projid:10389 projid:10318 projid:10390 projid:10391 projid:10392 projid:10393 projid:10394 projid:10395 projid:10396 projid:10397 projid:10398 projid:10399 projid:10319 projid:10400 projid:10401 projid:10402 projid:10403 projid:10404 projid:10405 projid:10406 projid:10407 projid:10408 projid:10409 projid:10411 projid:10438 projid:10708 projid:10441 projid:10444 projid:10447 projid:10450 projid:10453 projid:10456 projid:10459 projid:10462 projid:10465 projid:10414 projid:10468 projid:10471 projid:10474 projid:10477 projid:10480 projid:10483 projid:10486 projid:10489 projid:10492 projid:10495 projid:10498 projid:10501 projid:10504 projid:10507 projid:10510 projid:10513 projid:10516 projid:10519 projid:10522 projid:10525 projid:10528 projid:10531 projid:10535 projid:10538 projid:10541 projid:10544 projid:10547 projid:10550 projid:10553 projid:10556 projid:10423 projid:10559 projid:10562 projid:10565 projid:10568 projid:10571 projid:10574 projid:10577 projid:10580 projid:10583 projid:10586 projid:10426 projid:10589 projid:10592 projid:10595 projid:10598 projid:10601 projid:10604 projid:10607 projid:10610 projid:10613 projid:10616 projid:10429 projid:10619 projid:10622 projid:10625 projid:10628 projid:10631 projid:10633 projid:10637 projid:10639 projid:10642 projid:10646 projid:10432 projid:10649 projid:10652 projid:10654 projid:10658 projid:10661 projid:10664 projid:10666 projid:10670 projid:10672 projid:10675 projid:10435 projid:10678 projid:10681 projid:10684 projid:10687 projid:10690 projid:10693 projid:10696 projid:10699 projid:10702 projid:10705 projid:10412 projid:10440 projid:10443 projid:10446 projid:10449 projid:10452 projid:10455 projid:10458 projid:10461 projid:10464 projid:10467 projid:10416 projid:10470 projid:10473 projid:10476 projid:10479 projid:10482 projid:10485 projid:10488 projid:10491 projid:10494 projid:10497 projid:10418 projid:10500 projid:10503 projid:10506 projid:10509 projid:10512 projid:10515 projid:10518 projid:10521 projid:10524 projid:10527 projid:10422 projid:10530 projid:10533 projid:10536 projid:10539 projid:10542 projid:10545 projid:10548 projid:10551 projid:10554 projid:10557 projid:10425 projid:10560 projid:10563 projid:10566 projid:10569 projid:10572 projid:10575 projid:10578 projid:10581 projid:10584 projid:10587 projid:10428 projid:10590 projid:10593 projid:10596 projid:10599 projid:10602 projid:10605 projid:10608 projid:10611 projid:10614 projid:10617 projid:10431 projid:10620 projid:10623 projid:10626 projid:10629 projid:10632 projid:10635 projid:10638 projid:10641 projid:10644 projid:10647 projid:10434 projid:10650 projid:10653 projid:10659 projid:10662 projid:10665 projid:10668 projid:10671 projid:10674 projid:10677 projid:10437 projid:10680 projid:10683 projid:10686 projid:10689 projid:10692 projid:10695 projid:10698 projid:10701 projid:10704 projid:10707 projid:10413 projid:10439 projid:10709 projid:10442 projid:10445 projid:10448 projid:10451 projid:10454 projid:10457 projid:10460 projid:10463 projid:10466 projid:10415 projid:10469 projid:10472 projid:10475 projid:10478 projid:10481 projid:10484 projid:10487 projid:10490 projid:10493 projid:10496 projid:10417 projid:10499 projid:10502 projid:10505 projid:10508 projid:10511 projid:10514 projid:10517 projid:10520 projid:10523 projid:10526 projid:10421 projid:10529 projid:10532 projid:10534 projid:10537 projid:10540 projid:10543 projid:10546 projid:10549 projid:10552 projid:10555 projid:10424 projid:10558 projid:10561 projid:10564 projid:10567 projid:10570 projid:10573 projid:10576 projid:10579 projid:10582 projid:10585 projid:10427 projid:10588 projid:10591 projid:10594 projid:10597 projid:10600 projid:10603 projid:10606 projid:10609 projid:10612 projid:10615 projid:10430 projid:10618 projid:10621 projid:10624 projid:10627 projid:10630 projid:10634 projid:10636 projid:10640 projid:10643 projid:10645 projid:10433 projid:10648 projid:10651 projid:10655 projid:10657 projid:10660 projid:10663 projid:10667 projid:10669 projid:10673 projid:10676 projid:10436 projid:10679 projid:10682 projid:10685 projid:10688 projid:10691 projid:10694 projid:10697 projid:10700 projid:10703 projid:10706 projid:10711 projid:10712 projid:10721 projid:10722 projid:10723 projid:10724 projid:10725 projid:10726 projid:10713 projid:10714 projid:10715 projid:10716 projid:10717 projid:10718 projid:10719 projid:10720 projid:10301 projid:10310 projid:10302 projid:10303 projid:10304 projid:10305 projid:10306 projid:10307 projid:10308 projid:10309 projid:10125)))) +projid:10000
Workaround
Currently there is no known workaround for this behavior. A workaround will be added here when available
Use a user with permission to the smallest number of projects possible. This might be particularly useful for automations (for both A4J or REST API).
- links to
