Uploaded image for project: 'camunda BPM'
  1. camunda BPM
  2. CAM-10282

Weak performance of count queries on PostgreSQL

    Details

      Description

      Reported in SUPPORT-5776:
      On a PostgreSQL 10.6 instance, the job count query

      select COUNT(DISTINCT RES.ID_)
      fom ACT_RU_JOB RES
      where  RES.SUSPENSION_STATE_ = 1
      

      performs about 4 times slower than

      select COUNT(RES.ID_)
      fom ACT_RU_JOB RES
      where  RES.SUSPENSION_STATE_ = 1
      

      and

      select COUNT(*) 
      from (
      	select DISTINCT RES.ID_ 
      	from ACT_RU_JOB RES
      	where RES.SUSPENSION_STATE_ = 1)
      

      The problem for this is explained here:
      https://stackoverflow.com/questions/11250253/postgresql-countdistinct-very-slow

      And here:
      https://www.postgresql.org/message-id/CAMkU%3D1wFdvb76NWVw%3DzLKF3H6-tn%3D%2Bz9o_Nv2oioWfy6Z8Xo0A%40mail.gmail.com

        Issue Links

          Activity

          There are no comments yet on this issue.

            People

            • Assignee:
              Unassigned
              Reporter:
              tobias.metzke Tobias Metzke
            • Votes:
              1 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development