0

I am trying to speed up the following query, which currently takes 14 seconds:

SELECT
  COUNT(*) AS employee_count, e.jurisdiction_id, e.year
FROM
  employee_employee e
GROUP BY
  e.jurisdiction_id, e.year
ORDER BY
  employee_count ASC;

I add an index by running:

CREATE INDEX ON employee_employee (jurisdiction_id, year);

But that did very little to improve the timing because the GROUP BY is taking the longest to execute. Is there a way to index a GROUP BY function?

  • Is this something I could do as a stored procedure? – caseym Jan 13 '18 at 3:04
  • 2
    Please edit the question to include the query plan (EXPLAIN). – Michael Green Jan 13 '18 at 6:33
  • 1
    How much time does it actually take to execute the query? How big is the table (number of rows) and how many rows are returned in the result? And - it is probably related - what are the specs of the server (RAM) and settings of Postgres (shared_buffers and work_mem)? – ypercubeᵀᴹ Jan 13 '18 at 11:21
  • 1
    Please Edit your question and add the execution plan generated using explain (analyze, buffers). Formatted text please, no screen shots – a_horse_with_no_name Jan 13 '18 at 21:03
1

Taking a simple approach:

Execute inside psql, enable \timing and run your query. Also check query execution plan:

explain select <your query>;

Then run vacuum for your table:

vacuum (full, analyze, verbose) employee_employee;

And check if query execution plan was improved, you'll see what's the costly stage:

explain select <your query>;

Considering that your query is a simple query, there's no much to do beyond that inside the database. Optionally evaluate if a materialized view fits your need.

  • Did you applied the suggestions? Some improvement? – Rogerlr Jan 22 '18 at 19:01

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.