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

Weak performance of count queries on PostgreSQL

XMLWordPrintable

      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

        This is the controller panel for Smart Panels app

          1. solution_ideas.txt
            2 kB
            Tobias Metzke-Bernstein

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

                Created:
                Updated:
                Resolved: