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

        1. solution_ideas.txt
          2 kB

            Assignee:
            Unassigned
            Reporter:
            Tobias Metzke-Bernstein
            Votes:
            1 Vote for this issue
            Watchers:
            3 Start watching this issue

              Created:
              Updated:
              Resolved: