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

Weak performance of count queries on PostgreSQL

    XMLWordPrintable

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

      mgm-controller-panel

        This is the controller panel for Smart Panels app

        Attachments

          1. solution_ideas.txt
            2 kB
            Tobias Metzke-Bernstein

          Issue Links

            Activity

              People

                Unassigned Unassigned
                tobias.metzke Tobias Metzke-Bernstein
                Votes:
                1 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                  Created:
                  Updated:
                  Resolved:

                  Salesforce