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 | 
    "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:
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


 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
   ->  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
         ->  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!

  • What is the type/domain definition for detection_status? – jjanes Apr 10 '18 at 2:57
  • Sure, it is it: public | detection_status | detection_status | 4 | new +| codechecker_user | | | | | | unresolved+| | | | | | | resolved +| | | | | | | reopened | | | – sylvestre Apr 10 '18 at 7:46
  • 1
    That isn't very readily when posted as a comment, but I'm gathering that this is an enum with 4 elements? – jjanes Apr 11 '18 at 0:05

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.

| improve this answer | |
  • The index removed the need of "external merge" but still slow, I guess I should indeed change the hardware :) – sylvestre Apr 12 '18 at 19:04
  • It should have gotten rid of the entire sort operation, not just the external merge part of it. – jjanes Apr 13 '18 at 11:43

Any idea how to improve the query or a missing index? Thanks!

You already have an index on reports.run_id. It's unused which means one of

  • resource problem? (configuration / memory / host load etc)
  • data distribution which makes index not suitable for the query (in some cases, index cannot help).

To test if the index can help at all, you could repeat the query with sequential scan preference set to zero:

SET enable_seqscan TO false;
/* repeat - your - query - here */

For better effect please post results of ANALYZE VERBOSE reports and SELECT * FROM pg_stats WHERE tablename = 'reports'. Also let us know the dataset size and resource configuration parameters (work_mem, shared_buffers) as well as database host parameters (OS, memory, disks).

| improve this answer | |
  • An index only on run_id can't be used here. The planner won't even bother to compute a cost estimate for its use . At a minimum it needs to be on run_id, detection_status. – jjanes Apr 11 '18 at 18:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.