Postgresql: very slow results with "group by" I am trying fix some severe performance issues on codechecker. Issue reported here:
https://github.com/Ericsson/codechecker/issues/1399#issuecomment-368862492

The table is the following:

                                       Table "public.reports"
      Column      |            Type             |                      Modifiers                       
------------------+-----------------------------+------------------------------------------------------
 id               | integer                     | not null default nextval('reports_id_seq'::regclass)
 file_id          | integer                     | 
 run_id           | integer                     | 
 bug_id           | character varying           | 
 checker_id       | character varying           | 
 checker_cat      | character varying           | 
 bug_type         | character varying           | 
 severity         | integer                     | 
 line             | integer                     | 
 column           | integer                     | 
 checker_message  | character varying           | 
 detection_status | detection_status            | 
 detected_at      | timestamp without time zone | not null
 fixed_at         | timestamp without time zone | 
Indexes:
    "pk_reports" PRIMARY KEY, btree (id)
    "ix_reports_bug_id" btree (bug_id)
    "ix_reports_file_id" btree (file_id)
    "ix_reports_run_id" btree (run_id)
Foreign-key constraints:
    "fk_reports_file_id_files" FOREIGN KEY (file_id) REFERENCES files(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
    "fk_reports_run_id_runs" FOREIGN KEY (run_id) REFERENCES runs(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
Referenced by:
    TABLE "bug_path_events" CONSTRAINT "fk_bug_path_events_report_id_reports" FOREIGN KEY (report_id) REFERENCES reports(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
    TABLE "bug_report_points" CONSTRAINT "fk_bug_report_points_report_id_reports" FOREIGN KEY (report_id) REFERENCES reports(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED


You will notice the lack of indexes.

The following query takes a while to run (26 seconds) with 2 469 648 elements in the table.

SELECT reports.run_id AS reports_run_id,
  reports.detection_status AS reports_detection_status,
  count(DISTINCT reports.bug_id) AS count_1
FROM reports
GROUP BY reports.run_id, reports.detection_status


with

 GroupAggregate  (cost=566298.15..590994.88 rows=25 width=41) (actual time=6782.100..26183.127 rows=28 loops=1)
   Group Key: run_id, detection_status
   -&gt;  Sort  (cost=566298.15..572472.27 rows=2469648 width=41) (actual time=6768.582..9561.902 rows=2469648 loops=1)
         Sort Key: run_id, detection_status
         Sort Method: external merge  Disk: 123104kB
         -&gt;  Seq Scan on reports  (cost=0.00..152130.48 rows=2469648 width=41) (actual time=0.010..3248.899 rows=2469648 loops=1)
 Planning time: 0.066 ms
 Execution time: 26224.844 ms
(8 rows)


Any idea how to improve the query or a missing index? Thanks!
In my hands this query, on a simulated data set, is ten times faster than you observed, even on pretty crummy hardware. It has the same EXPLAIN plan and similar estimated costs, just much faster execution.  So either something very strange is going on, or your server is seriously overloaded.  Your best bet for investigating that might be outside of PostgreSQL, for example using top, perf, vmstat, etc.

As far as an index, the one best suited for this query would be:

create index on reports (run_id , detection_status, bug_id);


This allows it to use an index only scan to complete the query, and in my hands is 4 to 5 times faster.  But I don't know how that would translate to your server, as it seems to have an undiagnosed performance pathology.  Also, you need to keep reports well vacuumed for this to be effective, otherwise the index-only nature of the scan loses its benefit.  You might need to modify the tables autovac parameters to get it to be vacuumed enough, or set up your own manual vacuum scripts.