0

There are two tables containing hundreds of thousands of records: client and task. It's necessary to query the following:

SELECT
  id,
  (SELECT status
   FROM task t
   WHERE t.client_id = c.id AND t.status != 2
   ORDER BY task_date DESC
   LIMIT 1) AS task_status
FROM client c;

This query takes about five minutes, which is unacceptable. So, I tried to create indexes in the following ways:

CREATE INDEX CONCURRENTLY some_name_idx
  ON task (status, client_id);

execution: 1m 1s 475ms, fetching: 21ms

CREATE INDEX CONCURRENTLY some_name_idx
  ON task (status, client_id, task_date DESC);

execution: 1m 3s 269ms, fetching: 34ms

As you can see, the result is not impressive. Another one approach isn't correct, but it helped to achieve the expected result:

CREATE INDEX CONCURRENTLY some_name_idx
  ON task (client_id, task_date DESC)
  WHERE status != 2;

execution: 229ms, fetching: 36ms

I can't use this version because similar subqueries used in our system will get an incorrect result because of WHERE status != 2 clause, which specific only for a single query.

I would be grateful for help on query optimization in such cases.

Thanks.

1

Your best simple index is probably going to be on (client_id, task_date, status). client_id should go first, because it is the only one used with an equality operator. Including status will only help for index-only scans, so it depends on how well vacuumed task is and your version of PostgreSQL.

The partial index will not lead to incorrect results. Queries that cannot correctly use the partial index, will not use it. So if WHERE status != 2 is the most commonly queried case, or the most time-sensitive one, go ahead and make the partial index.

| improve this answer | |

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.