Postgresql same query is slower on new hardware
0
votes
1
answer
160
views
I migrate my data to a new server hardware that after my benchmark looks faster in terms of CPU and disk i/o.
Unfortunatly my query seems slower on that new hardware with the same data and the same query.
Here is my query :
EXPLAIN ANALYZE SELECT AVG(CAST(completeness->>'fr' AS INTEGER)) as average FROM product INNER JOIN channel ON channel.id = product.channel_id WHERE channel_id = 'myUuid';
On old server my times are :
Planning Time: 0.281 ms
Execution Time: 50.683 ms
On the new server my times are:
Planning Time: 0.162 ms
Execution Time: 115.268 ms
That is significally slower, I already tried making a VACUUM FULL and REINDEX mydatabase
And also increasing the default_statistics_target but any of this changed anything.
Of course I'm using the same postgresql version (12.14) and everything I could found in similar questions, do you have any other solutions ?
EDIT :
For some more details :
Old and faster :
Aggregate (cost=58441.85..58441.86 rows=1 width=32) (actual time=66.284..66.285 rows=1 loops=1)
Buffers: shared hit=1132 read=18379 written=16
I/O Timings: read=36.167 write=0.101
-> Nested Loop (cost=594.96..58185.68 rows=25617 width=25) (actual time=5.003..61.783 rows=25985 loops=1)
Buffers: shared hit=1132 read=18379 written=16
I/O Timings: read=36.167 write=0.101
-> Seq Scan on channel (cost=0.00..1.30 rows=1 width=16) (actual time=0.028..0.031 rows=1 loops=1)
Filter: (id = 'myuuid'::uuid)
Rows Removed by Filter: 23
Buffers: shared hit=1
-> Bitmap Heap Scan on product (cost=594.96..57928.21 rows=25617 width=41) (actual time=4.969..59.276 rows=25985 loops=1)
Recheck Cond: (channel_id = 'myuuid'::uuid)
Heap Blocks: exact=19408
Buffers: shared hit=1131 read=18379 written=16
I/O Timings: read=36.167 write=0.101
-> Bitmap Index Scan on idx_8ac439d272f5a1aa (cost=0.00..588.55 rows=25617 width=0) (actual time=3.177..3.177 rows=25985 loops=1)
Index Cond: (channel_id = 'myuuid'::uuid)
Buffers: shared read=102
I/O Timings: read=0.389
Planning Time: 1.945 ms
Execution Time: 66.585 ms
(21 rows)
New and slower :
Aggregate (cost=59666.14..59666.15 rows=1 width=32) (actual time=146.506..146.508 rows=1 loops=1)
Buffers: shared hit=108 read=19371 written=172
I/O Timings: read=85.949 write=1.528
-> Nested Loop (cost=617.08..59402.07 rows=26407 width=25) (actual time=7.752..139.180 rows=25985 loops=1)
Buffers: shared hit=108 read=19371 written=172
I/O Timings: read=85.949 write=1.528
-> Seq Scan on channel (cost=0.00..1.30 rows=1 width=16) (actual time=0.012..0.016 rows=1 loops=1)
Filter: (id = 'myuuid'::uuid)
Rows Removed by Filter: 23
Buffers: shared hit=1
-> Bitmap Heap Scan on product (cost=617.08..59136.70 rows=26407 width=41) (actual time=7.737..134.826 rows=25985 loops=1)
Recheck Cond: (channel_id = 'myuuid'::uuid)
Heap Blocks: exact=19374
Buffers: shared hit=107 read=19371 written=172
I/O Timings: read=85.949 write=1.528
-> Bitmap Index Scan on idx_8ac439d272f5a1aa (cost=0.00..610.48 rows=26407 width=0) (actual time=5.073..5.073 rows=25987 loops=1)
Index Cond: (channel_id = 'myuuid'::uuid)
Buffers: shared read=102
I/O Timings: read=0.688
Planning Time: 0.773 ms
Execution Time: 146.750 ms
(21 rows)
Asked by TomLorenzi
(1 rep)
Jul 12, 2023, 01:24 PM
Last activity: Jul 17, 2025, 10:08 PM
Last activity: Jul 17, 2025, 10:08 PM