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

            is related to

            Bug Report - A problem which impairs or prevents the functions of the product. CAM-8733 Fetching total count of historic data takes too long

            • L3 - Default - Minor issue or issue with existing workaround, feature requests and questions
            • Closed

            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