I was investigating the performance of a query that hit the top of my list in PMM and noticed strange behavior. The first time the requests execution time is long, subsequent times quickly:
Aggregate (cost=1026.84..1026.85 rows=1 width=8) (actual time=17.657..17.659 rows=1 loops=1)
-> Nested Loop (cost=1.12..1026.80 rows=15 width=37) (actual time=0.844..17.625 rows=24 loops=1)
-> Index Scan using i01_recipients on recipients recipiente1_ (cost=0.56..32.94 rows=28 width=37) (actual time=0.129..0.994 rows=24 loops=1)
Index Cond: ((user_reference)::text = 'b8245f69-f429-4bc3-80c3-78de75ffb7b8'::text)
-> Index Scan using i01_messages on messages messageent0_ (cost=0.56..35.37 rows=12 width=74) (actual time=0.638..0.688 rows=1 loops=24)
Index Cond: ((recipient_id)::text = (recipiente1_.id)::text)
Filter: (((channel)::text = 'INTERNAL'::text) AND ((status)::text = ANY ('{SENT,DELIVERED,READ}'::text[])))
Rows Removed by Filter: 1
Planning Time: 0.639 ms
Execution Time: 17.713 ms
Aggregate (cost=1026.84..1026.85 rows=1 width=8) (actual time=0.790..0.792 rows=1 loops=1)
-> Nested Loop (cost=1.12..1026.80 rows=15 width=37) (actual time=0.092..0.779 rows=24 loops=1)
-> Index Scan using i01_recipients on recipients recipiente1_ (cost=0.56..32.94 rows=28 width=37) (actual time=0.031..0.078 rows=24 loops=1)
Index Cond: ((user_reference)::text = 'b8245f69-f429-4bc3-80c3-78de75ffb7b8'::text)
-> Index Scan using i01_messages on messages messageent0_ (cost=0.56..35.37 rows=12 width=74) (actual time=0.026..0.027 rows=1 loops=24)
Index Cond: ((recipient_id)::text = (recipiente1_.id)::text)
Filter: (((channel)::text = 'INTERNAL'::text) AND ((status)::text = ANY ('{SENT,DELIVERED,READ}'::text[])))
Rows Removed by Filter: 1
Planning Time: 0.505 ms
Execution Time: 0.839 ms
I'm not a big postgres expert. Could you explain to me why this is happening and why this request is loading my system.
Asked by Марат Бралиев
(21 rep)
Dec 8, 2022, 02:34 PM
Last activity: Jun 1, 2025, 11:03 AM
Last activity: Jun 1, 2025, 11:03 AM