Sample Header Ad - 728x90

Different results from Postgres EXPLAIN ANALYZE

2 votes
1 answer
415 views
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