There are two tables containing hundreds of thousands of records:
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.