[performance] Consider using CURSOR instead of LIMIT

[joubu]

We are experiencing performance issues, bottleneck is at disk level caused by postgres SELECT queries.

For instance the following query can take 30s-2min

SELECT "trans_change"."id", "trans_change"."unit_id",
"trans_change"."language_id", "trans_change"."project_id",
"trans_change"."component_id", "trans_change"."translation_id",
"trans_change"."comment_id", "trans_change"."suggestion_id",
"trans_change"."announcement_id", "trans_change"."screenshot_id",
"trans_change"."alert_id", "trans_change"."user_id",
"trans_change"."author_id", "trans_change"."timestamp",
"trans_change"."action", "trans_change"."target", "trans_change"."old",
"trans_change"."details" FROM "trans_change" WHERE
"trans_change"."translation_id" = 5331 ORDER BY "trans_change"."timestamp"
DESC LIMIT 10;

The same query using CURSOR takes few ms:

BEGIN;
DECLARE c CURSOR FOR SELECT "trans_change"."id", "trans_change"."unit_id",
"trans_change"."language_id", "trans_change"."project_id",
"trans_change"."component_id", "trans_change"."translation_id",
"trans_change"."comment_id", "trans_change"."suggestion_id",
"trans_change"."announcement_id", "trans_change"."screenshot_id",
"trans_change"."alert_id", "trans_change"."user_id",
"trans_change"."author_id", "trans_change"."timestamp",
"trans_change"."action", "trans_change"."target", "trans_change"."old",
"trans_change"."details" FROM "trans_change" WHERE
"trans_change"."translation_id" = 5331 ORDER BY "trans_change"."timestamp"
DESC;
FETCH 10 FROM c;
ROLLBACK;

Have you already considered using CURSOR instead of LIMIT? I am no postgres at all but got help from a postgres sysop, they told me that CURSOR would be more effective in this use case.

https://www.postgresql.org/docs/current/plpgsql-cursors.html

[nijel]

You seem to be right, using cursor performs better in this case. The question is how to use cursors effectively with Django. It uses them with iterator() call, but that way it doesn't support prefetching related fields.

[nijel]

#10728 attempts to implement this.

[joubu]

Thanks!
We are running the latest docker image which contains #10728
I will let you know how it goes!

[joubu]

This has been really beneficial for our server.

Since this has been applied the disk access (and so the loadavg) are way lower!

Thanks again!