Inefficient Query Execution

zN3utr4l · June 13, 2023, 9:57am

I use a custom implementation of this Pagination | Django documentation | Django,
i reimplement the count, like this Efficient Pagination: PostgreSQL and Django.

Can you tell us whether the number of queries you see on you sql log is the same as the number of pages?

This executes 1 query for all , but returns the objects in pieces of n, under the postgres logs, I did 3 page for cycles, the query is still one.

2023-06-13 09:48:00.932 GMT [76] LOG:  statement: SELECT (CAST(value_unnest AS double precision)) AS "value_conv", "collection_basic_arrays"."id", ("collection_basic_arrays"."updateat" + '2:00:00') AS "time" FROM "collection_basic_arrays" , LATERAL unnest(value) AS value_unnest WHERE ("collection_basic_arrays"."itemid" = 'test"' AND "collection_basic_arrays"."lineid" = '1' AND "collection_basic_arrays"."machineid" = '641c0b97d3c5a19affa77b7e' AND "collection_basic_arrays"."updateat" BETWEEN '2023-04-04 22:00:00+00:00' AND '2023-04-05 06:02:00+00:00') ORDER BY "collection_basic_arrays"."updateat" ASC
2023-06-13 09:48:02.510 GMT [76] LOG:  statement: SELECT "collection_basic_arrays"."id", "collection_basic_arrays"."itemid" FROM "collection_basic_arrays" WHERE "collection_basic_arrays"."id" = 5929741 LIMIT 21
2023-06-13 09:48:02.708 GMT [76] LOG:  statement: SELECT "collection_basic_arrays"."id", "collection_basic_arrays"."itemid" FROM "collection_basic_arrays" WHERE "collection_basic_arrays"."id" = 5929741 LIMIT 21
2023-06-13 09:48:02.723 GMT [76] LOG:  statement: SELECT "collection_basic_arrays"."id", "collection_basic_arrays"."itemid" FROM "collection_basic_arrays" WHERE "collection_basic_arrays"."id" = 5929741 LIMIT 21
2023-06-13 09:48:02.738 GMT [76] LOG:  statement: SELECT "collection_basic_arrays"."id", "collection_basic_arrays"."itemid" FROM "collection_basic_arrays" WHERE "collection_basic_arrays"."id" = 5929741 LIMIT 21
2023-06-13 09:48:02.753 GMT [76] LOG:  statement: SELECT "collection_basic_arrays"."id", "collection_basic_arrays"."itemid" FROM "collection_basic_arrays" WHERE "collection_basic_arrays"."id" = 5929741 LIMIT 21
2023-06-13 09:48:02.770 GMT [76] LOG:  statement: SELECT "collection_basic_arrays"."id", "collection_basic_arrays"."itemid" FROM "collection_basic_arrays" WHERE "collection_basic_arrays"."id" = 5929741 LIMIT 21
2023-06-13 09:48:02.787 GMT [76] LOG:  statement: SELECT "collection_basic_arrays"."id", "collection_basic_arrays"."itemid" FROM "collection_basic_arrays" WHERE "collection_basic_arrays"."id" = 5929741 LIMIT 21
2023-06-13 09:48:02.805 GMT [76] LOG:  statement: SELECT "collection_basic_arrays"."id", "collection_basic_arrays"."itemid" FROM "collection_basic_arrays" WHERE "collection_basic_arrays"."id" = 5929741 LIMIT 21
2023-06-13 09:48:02.821 GMT [76] LOG:  statement: SELECT "collection_basic_arrays"."id", "collection_basic_arrays"."itemid" FROM "collection_basic_arrays" WHERE "collection_basic_arrays"."id" = 5929741 LIMIT 21
2023-06-13 09:48:02.838 GMT [76] LOG:  statement: SELECT "collection_basic_arrays"."id", "collection_basic_arrays"."itemid" FROM "collection_basic_arrays" WHERE "collection_basic_arrays"."id" = 5929741 LIMIT 21
2023-06-13 09:48:09.436 GMT [40] FATAL:  terminating connection due to idle-session timeout
2023-06-13 09:48:11.581 GMT [47] FATAL:  terminating connection due to idle-session timeout
2023-06-13 09:48:15.311 GMT [76] LOG:  statement: SELECT "collection_basic_arrays"."id", "collection_basic_arrays"."updateat" FROM "collection_basic_arrays" WHERE "collection_basic_arrays"."id" = 5929741 LIMIT 21
2023-06-13 09:48:15.336 GMT [76] LOG:  statement: SELECT "collection_basic_arrays"."id", "collection_basic_arrays"."updateat" FROM "collection_basic_arrays" WHERE "collection_basic_arrays"."id" = 5929741 LIMIT 21
2023-06-13 09:48:15.356 GMT [76] LOG:  statement: SELECT "collection_basic_arrays"."id", "collection_basic_arrays"."updateat" FROM "collection_basic_arrays" WHERE "collection_basic_arrays"."id" = 5929741 LIMIT 21
2023-06-13 09:48:15.378 GMT [76] LOG:  statement: SELECT "collection_basic_arrays"."id", "collection_basic_arrays"."updateat" FROM "collection_basic_arrays" WHERE "collection_basic_arrays"."id" = 5929741 LIMIT 21
2023-06-13 09:48:15.398 GMT [76] LOG:  statement: SELECT "collection_basic_arrays"."id", "collection_basic_arrays"."updateat" FROM "collection_basic_arrays" WHERE "collection_basic_arrays"."id" = 5929741 LIMIT 21
2023-06-13 09:48:15.418 GMT [76] LOG:  statement: SELECT "collection_basic_arrays"."id", "collection_basic_arrays"."updateat" FROM "collection_basic_arrays" WHERE "collection_basic_arrays"."id" = 5929741 LIMIT 21
2023-06-13 09:48:15.439 GMT [76] LOG:  statement: SELECT "collection_basic_arrays"."id", "collection_basic_arrays"."updateat" FROM "collection_basic_arrays" WHERE "collection_basic_arrays"."id" = 5929741 LIMIT 21
2023-06-13 09:48:15.461 GMT [76] LOG:  statement: SELECT "collection_basic_arrays"."id", "collection_basic_arrays"."updateat" FROM "collection_basic_arrays" WHERE "collection_basic_arrays"."id" = 5929741 LIMIT 21
2023-06-13 09:48:15.483 GMT [76] LOG:  statement: SELECT "collection_basic_arrays"."id", "collection_basic_arrays"."updateat" FROM "collection_basic_arrays" WHERE "collection_basic_arrays"."id" = 5929741 LIMIT 21
2023-06-13 09:48:15.505 GMT [76] LOG:  statement: SELECT "collection_basic_arrays"."id", "collection_basic_arrays"."updateat" FROM "collection_basic_arrays" WHERE "collection_basic_arrays"."id" = 5929741 LIMIT 21
2023-06-13 09:48:19.211 GMT [76] LOG:  statement: SELECT "collection_basic_arrays"."id", "collection_basic_arrays"."itemid" FROM "collection_basic_arrays" WHERE "collection_basic_arrays"."id" = 5929741 LIMIT 21
2023-06-13 09:48:19.235 GMT [76] LOG:  statement: SELECT "collection_basic_arrays"."id", "collection_basic_arrays"."itemid" FROM "collection_basic_arrays" WHERE "collection_basic_arrays"."id" = 5929741 LIMIT 21
2023-06-13 09:48:19.253 GMT [76] LOG:  statement: SELECT "collection_basic_arrays"."id", "collection_basic_arrays"."itemid" FROM "collection_basic_arrays" WHERE "collection_basic_arrays"."id" = 5929741 LIMIT 21
2023-06-13 09:48:19.272 GMT [76] LOG:  statement: SELECT "collection_basic_arrays"."id", "collection_basic_arrays"."itemid" FROM "collection_basic_arrays" WHERE "collection_basic_arrays"."id" = 5929741 LIMIT 21
2023-06-13 09:48:19.290 GMT [76] LOG:  statement: SELECT "collection_basic_arrays"."id", "collection_basic_arrays"."itemid" FROM "collection_basic_arrays" WHERE "collection_basic_arrays"."id" = 5929741 LIMIT 21
2023-06-13 09:48:19.307 GMT [76] LOG:  statement: SELECT "collection_basic_arrays"."id", "collection_basic_arrays"."itemid" FROM "collection_basic_arrays" WHERE "collection_basic_arrays"."id" = 5929741 LIMIT 21
2023-06-13 09:48:19.326 GMT [76] LOG:  statement: SELECT "collection_basic_arrays"."id", "collection_basic_arrays"."itemid" FROM "collection_basic_arrays" WHERE "collection_basic_arrays"."id" = 5929741 LIMIT 21
2023-06-13 09:48:19.340 GMT [76] LOG:  statement: SELECT "collection_basic_arrays"."id", "collection_basic_arrays"."itemid" FROM "collection_basic_arrays" WHERE "collection_basic_arrays"."id" = 5929741 LIMIT 21
2023-06-13 09:48:19.356 GMT [76] LOG:  statement: SELECT "collection_basic_arrays"."id", "collection_basic_arrays"."itemid" FROM "collection_basic_arrays" WHERE "collection_basic_arrays"."id" = 5929741 LIMIT 21
2023-06-13 09:48:19.373 GMT [76] LOG:  statement: SELECT "collection_basic_arrays"."id", "collection_basic_arrays"."itemid" FROM "collection_basic_arrays" WHERE "collection_basic_arrays"."id" = 5929741 LIMIT 21

Also, why the need to run a raw query?
This is why:
"Unexpected ORM Query Translation: CAST and Unnest Functions in Django"

i need to perform unnest in lateral query

this optimizes the function, but it’s still too slow and repeated queries show up

def make_single_dict_from_model(self, list_plot: List[models.Model], value_key: str, time_key: str):
        """
        [{model: 1, 2}, {model: 3, 4}] -> {value_key: [1, 3], time_key: [2, 4]}
        """

        if not list_plot:
            return {}

        return {
            value_key: [item.value_conv for item in list_plot],
            time_key: [item.time for item in list_plot]
        }

In general, using getattr() is more flexible than accessing it directly using the dot (.) operator followed by the attribute name. However, in terms of execution speed, direct access using the dot operator is generally faster than using getattr().

Topic		Replies	Views
Query performance question Using Django	15	2255	September 15, 2021
How to query foreignkey and return only one object (prefetch) Using the ORM	3	493	October 12, 2023
"Unexpected ORM Query Translation: CAST and Unnest Functions in Django" Using the ORM	7	1450	May 16, 2023
Profile JSON field -> Python Using Django	12	1274	September 17, 2020
objects.filter(id=customer_id).first() vs objects.get(id=customer_id) Using the ORM	2	1671	October 12, 2023

Inefficient Query Execution

Related Topics