Inefficient Query Execution

zN3utr4l · June 13, 2023, 12:20pm

EXPLAIN ANALYZE 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" = 'ns=3;s="Motor"."11A1"."Out_Z"' 
	   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


"Nested Loop  (cost=0.45..4684.76 rows=220000 width=28) (actual time=10.021..33.881 rows=14750 loops=1)"
"  ->  Custom Scan (DecompressChunk) on collection_basic_arrays_70_chunk  (cost=0.44..9.58 rows=22000 width=1174) (actual time=9.976..10.106 rows=59 loops=1)"
"        Filter: ((updateat >= '2023-04-04 22:00:00+00'::timestamp with time zone) AND (updateat <= '2023-04-05 06:02:00+00'::timestamp with time zone))"
"        Rows Removed by Filter: 941"
"        ->  Index Scan using compress_hyper_4_146_chunk__compressed_hypertable_4_itemid_mach on compress_hyper_4_146_chunk  (cost=0.27..9.58 rows=22 width=166) (actual time=0.047..0.067 rows=1 loops=1)"
"              Index Cond: ((itemid = 'ns=3;s=""Motor"".""11A1"".""Out_Z""'::text) AND (machineid = '641c0b97d3c5a19affa77b7e'::text) AND (lineid = '1'::text))"
"              Filter: ((_ts_meta_max_1 >= '2023-04-04 22:00:00+00'::timestamp with time zone) AND (_ts_meta_min_1 <= '2023-04-05 06:02:00+00'::timestamp with time zone))"
"              Rows Removed by Filter: 89"
"  ->  Memoize  (cost=0.01..0.11 rows=10 width=32) (actual time=0.025..0.200 rows=250 loops=59)"
"        Cache Key: collection_basic_arrays_70_chunk.value"
"        Cache Mode: binary"
"        Hits: 0  Misses: 59  Evictions: 0  Overflows: 0  Memory Usage: 853kB"
"        ->  Function Scan on unnest value_unnest  (cost=0.00..0.10 rows=10 width=32) (actual time=0.020..0.073 rows=250 loops=59)"
"Planning Time: 5.759 ms"
"Execution Time: 37.401 ms"

EXPLAIN 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" = 'ns=3;s="Motor"."11A1"."Out_Z"' 
	   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


"Nested Loop  (cost=0.45..4684.76 rows=220000 width=28)"
"  ->  Custom Scan (DecompressChunk) on collection_basic_arrays_70_chunk  (cost=0.44..9.58 rows=22000 width=1174)"
"        Filter: ((updateat >= '2023-04-04 22:00:00+00'::timestamp with time zone) AND (updateat <= '2023-04-05 06:02:00+00'::timestamp with time zone))"
"        ->  Index Scan using compress_hyper_4_146_chunk__compressed_hypertable_4_itemid_mach on compress_hyper_4_146_chunk  (cost=0.27..9.58 rows=22 width=166)"
"              Index Cond: ((itemid = 'ns=3;s=""Motor"".""11A1"".""Out_Z""'::text) AND (machineid = '641c0b97d3c5a19affa77b7e'::text) AND (lineid = '1'::text))"
"              Filter: ((_ts_meta_max_1 >= '2023-04-04 22:00:00+00'::timestamp with time zone) AND (_ts_meta_min_1 <= '2023-04-05 06:02:00+00'::timestamp with time zone))"
"  ->  Memoize  (cost=0.01..0.11 rows=10 width=32)"
"        Cache Key: collection_basic_arrays_70_chunk.value"
"        Cache Mode: binary"
"        ->  Function Scan on unnest value_unnest  (cost=0.00..0.10 rows=10 width=32)"

EXPLAIN ANALYZE SELECT (CAST(value AS double precision[])) AS "value_conv", "collection_basic_arrays"."id", ("collection_basic_arrays"."updateat" + '2:00:00') AS "time" 
FROM "collection_basic_arrays" 
WHERE ("collection_basic_arrays"."itemid" = 'ns=3;s="Motor"."11A1"."Out_Z"' 
	   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


"Result  (cost=0.44..1494.58 rows=22000 width=52) (actual time=9.606..15.238 rows=59 loops=1)"
"  ->  Custom Scan (DecompressChunk) on collection_basic_arrays_70_chunk  (cost=0.44..9.58 rows=22000 width=1174) (actual time=9.519..9.628 rows=59 loops=1)"
"        Filter: ((updateat >= '2023-04-04 22:00:00+00'::timestamp with time zone) AND (updateat <= '2023-04-05 06:02:00+00'::timestamp with time zone))"
"        Rows Removed by Filter: 941"
"        ->  Index Scan using compress_hyper_4_146_chunk__compressed_hypertable_4_itemid_mach on compress_hyper_4_146_chunk  (cost=0.27..9.58 rows=22 width=166) (actual time=0.038..0.061 rows=1 loops=1)"
"              Index Cond: ((itemid = 'ns=3;s=""Motor"".""11A1"".""Out_Z""'::text) AND (machineid = '641c0b97d3c5a19affa77b7e'::text) AND (lineid = '1'::text))"
"              Filter: ((_ts_meta_max_1 >= '2023-04-04 22:00:00+00'::timestamp with time zone) AND (_ts_meta_min_1 <= '2023-04-05 06:02:00+00'::timestamp with time zone))"
"              Rows Removed by Filter: 89"
"Planning Time: 5.642 ms"
"Execution Time: 15.431 ms"

EXPLAIN ANALYZE SELECT value_unnest, "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" = 'ns=3;s="Motor"."11A1"."Out_Z"' 
	   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

"Nested Loop  (cost=0.45..3584.76 rows=220000 width=52) (actual time=10.239..28.340 rows=14750 loops=1)"
"  ->  Custom Scan (DecompressChunk) on collection_basic_arrays_70_chunk  (cost=0.44..9.58 rows=22000 width=1174) (actual time=10.195..10.317 rows=59 loops=1)"
"        Filter: ((updateat >= '2023-04-04 22:00:00+00'::timestamp with time zone) AND (updateat <= '2023-04-05 06:02:00+00'::timestamp with time zone))"
"        Rows Removed by Filter: 941"
"        ->  Index Scan using compress_hyper_4_146_chunk__compressed_hypertable_4_itemid_mach on compress_hyper_4_146_chunk  (cost=0.27..9.58 rows=22 width=166) (actual time=0.037..0.057 rows=1 loops=1)"
"              Index Cond: ((itemid = 'ns=3;s=""Motor"".""11A1"".""Out_Z""'::text) AND (machineid = '641c0b97d3c5a19affa77b7e'::text) AND (lineid = '1'::text))"
"              Filter: ((_ts_meta_max_1 >= '2023-04-04 22:00:00+00'::timestamp with time zone) AND (_ts_meta_min_1 <= '2023-04-05 06:02:00+00'::timestamp with time zone))"
"              Rows Removed by Filter: 89"
"  ->  Memoize  (cost=0.01..0.11 rows=10 width=32) (actual time=0.024..0.196 rows=250 loops=59)"
"        Cache Key: collection_basic_arrays_70_chunk.value"
"        Cache Mode: binary"
"        Hits: 0  Misses: 59  Evictions: 0  Overflows: 0  Memory Usage: 853kB"
"        ->  Function Scan on unnest value_unnest  (cost=0.00..0.10 rows=10 width=32) (actual time=0.019..0.072 rows=250 loops=59)"
"Planning Time: 5.422 ms"
"Execution Time: 31.493 ms"

Topic		Replies	Views
Proposal: Querying objects in batches for large collection ORM	11	3536	October 26, 2024
Need Help to optimize the query that returns the list of products Using the ORM	3	33	July 26, 2024
Fetch similar Django objects in one query if the objects have no direct relation Using the ORM	7	1908	September 24, 2022
Dynamic raw query (select clause) Django ORM	13	2109	April 5, 2022
Query performance question Using Django	15	2887	September 15, 2021

Inefficient Query Execution

Related topics