It is annoying to say our end users that they can't use a public API endpoint because of it's poor performance and having no workarround or solution prevision from Atlassian

Any chance that this can be fixed in 9.4/9.12?

+ 1 agree with @jmto, modified query is perfect and more efficient, if the API under discussion is modified with the changed SQL query it helps lot of users ..

select cast(CHANGE_ITEM.oldvalue as int8), max(CHANGE_GROUP.created)
from public.changeitem CHANGE_ITEM
left join public.changegroup CHANGE_GROUP
on CHANGE_ITEM.groupid = CHANGE_GROUP.id
left join public.worklog WORKLOG
on cast(CHANGE_ITEM.oldvalue as int8) = WORKLOG.id
where CHANGE_ITEM.field = ? and (CHANGE_GROUP.created >= ? and CHANGE_GROUP.created <= ?)
and WORKLOG.id is null
and CHANGE_ITEM.oldvalue is not null
– and cast(CHANGE_ITEM.oldvalue as int8) not in
– (select WORKLOG.id from public.worklog WORKLOG) /* this statement for lookup is causing Table scan which is not efficient one , and the issue caused and mentioned in ticket is because of this */
group by cast(CHANGE_ITEM.oldvalue as int8)
order by max(CHANGE_GROUP.created) asc
limit ?;

+1 to this issue. I have the same sync-ing situation as @jmto mentioned above. We need the deleted worklogs for reporting and billing purposes. This is a critical business process. Please implement a fix, especially since good fixes have been proposed in the previous ticket.

This issue is open since 2017. 6 years to fix an SQL issue.

Come on jira. Please do better.

On PostgreSQL setting work_mem (for speedy sorting) really helps. This article explains how to find the correct value.

Executing this rest endpoint kills our Jira instance. It is producing so many sql queries behind that database engine is overloaded.  When request is abandoned the it causes lock on databases table.

Dan,

I would still prefer better performing SQL query than having to tweak the SQL-server configuration to be able to handle it,
but good alternative workaround. Some might not have the ability to configure SQL-server settings.

Mark - 

I had the same issue, the comment in the URL below helped me fix it. I had Postgres resource exhaustion.

https://jira.atlassian.com/browse/JRASERVER-70716?focusedCommentId=2373506&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-2373506

Impact to customers can be quite significant.  If we can't extract this data, the time logs are overdone to an extent difficult to measure.  Our reports for markets and government are far more difficult to produce.

If there is a simple solution - and one is already provided above - please could we have someone look at it?

As the JRACLOUD-65360 version of this issue has been closed, I hope that the server one does not get the same treatment.
That ticket had some conversation of what is the problem and what solves it.

Now as a workaround I have nginx+php_fpm in front of jira to handle this API with my own simple implementation, which isn't very nice.

The main part of the code is this more efficient SQL query to handle it:

        $res = pg_query_params($db, 'select
        cast(CHANGE_ITEM.oldvalue as int8), max(CHANGE_GROUP.created)
from
        public.changeitem CHANGE_ITEM
left join
        public.changegroup CHANGE_GROUP
        on CHANGE_ITEM.groupid = CHANGE_GROUP.id
left join
        public.worklog WORKLOG
        on cast(CHANGE_ITEM.oldvalue as int8)=WORKLOG.id
where
        CHANGE_ITEM.field = $1 and
        (CHANGE_GROUP.created >= $2 and CHANGE_GROUP.created <= $3) and
        WORKLOG.id IS NULL and
        CHANGE_ITEM.oldvalue IS NOT NULL
group by
        cast(CHANGE_ITEM.oldvalue as int8)
order by
        max(CHANGE_GROUP.created) asc
limit
        1001
', array('WorklogId', strftime('%Y-%m-%d %H:%M:%S', $since), strftime('%Y-%m-%d %H:%M:%S', time())));