About Query Performaces Problem
Hello everyone, I am currently running queries with the same table structures in 2 different virtual machines and 2 different versions. and I get results like below. Execution Query: select d.device_id from ats_devices d inner join ats_device_detays dd on dd.device_id=d.device_id; RESULTS: postgres v10 QUERY PLAN --- Nested Loop (cost=0.69..7398.76 rows=2325 width=8) (actual time=0.023..5.877 rows=2325 loops=1) -> Index Only Scan using ats_device_detays_device_id_idx on ats_device_detays det (cost=0.28..91.16 rows=2325 width=8) (actual time=0.006..0.483 rows=2325 Heap Fetches: 373 -> Index Only Scan using ats_devices_pkey1 on ats_devices d (cost=0.41..3.14 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=2325) Index Cond: (device_id = det.device_id) Heap Fetches: 528 Planning time: 0.180 ms Execution time: 6.006 ms (8 rows) ### postgres v14 QUERY PLAN Merge Join (cost=0.56..174.59 rows=2279 width=8) (actual time=0.065..2.264 rows=2304 loops=1) Merge Cond: (d.device_id = det.device_id) -> Index Only Scan using ats_devices_pkey1 on ats_devices d (cost=0.28..70.18 rows=2260 width=8) (actual time=0.033..0.603 rows=2304 loops=1) Heap Fetches: 0 -> Index Only Scan using ats_device_detays_pkey on ats_device_detays det (cost=0.28..70.47 rows=2279 width=8) (actual time=0.024..0.506 rows=2304 loops=1) Heap Fetches: 0 Planning Time: 0.666 ms Execution Time: 2.519 ms As a result of that; According to the result og explain analyzer, Although the performance of the machine on which Postgres v14 is installed is better than the performance of the machine on which v10 is installed and their configurations are the same, in reality it seems to be the opposite. I would appreciate it if you could let me know what could be the cause of this and which parameters I should look?
Re: About Query Performaces Problem
út 11. 1. 2022 v 9:41 odesílatel Hüseyin Ellezer napsal: > Hello everyone, > > I am currently running queries with the same table structures in 2 > different virtual machines and 2 different versions. and I get results like > below. > > > Execution Query: > > select d.device_id from ats_devices d inner join ats_device_detays dd on > dd.device_id=d.device_id; > > RESULTS: > > postgres v10 > > QUERY PLAN > > --- > Nested Loop (cost=0.69..7398.76 rows=2325 width=8) (actual > time=0.023..5.877 rows=2325 loops=1) > > > >-> Index Only Scan using ats_device_detays_device_id_idx on > ats_device_detays det (cost=0.28..91.16 rows=2325 width=8) (actual > time=0.006..0.483 rows=2325 > Heap Fetches: 373 >-> Index Only Scan using ats_devices_pkey1 on ats_devices d > (cost=0.41..3.14 rows=1 width=8) (actual time=0.002..0.002 rows=1 > loops=2325) > Index Cond: (device_id = det.device_id) > Heap Fetches: 528 > Planning time: 0.180 ms > Execution time: 6.006 ms > (8 rows) > > > ### > > postgres v14 > > QUERY PLAN > > > Merge Join (cost=0.56..174.59 rows=2279 width=8) (actual > time=0.065..2.264 rows=2304 loops=1) >Merge Cond: (d.device_id = det.device_id) >-> Index Only Scan using ats_devices_pkey1 on ats_devices d > (cost=0.28..70.18 rows=2260 width=8) (actual time=0.033..0.603 rows=2304 > loops=1) > Heap Fetches: 0 >-> Index Only Scan using ats_device_detays_pkey on ats_device_detays > det (cost=0.28..70.47 rows=2279 width=8) (actual time=0.024..0.506 > rows=2304 loops=1) > Heap Fetches: 0 > Planning Time: 0.666 ms > Execution Time: 2.519 ms > > As a result of that; > > According to the result og explain analyzer, Although the performance of > the machine on which Postgres v14 is installed is better than the > performance of the machine on which v10 is installed and their > configurations are the same, in reality it seems to be the opposite. I > would appreciate it if you could let me know what could be the cause of > this and which parameters I should look? > ??? PostgreSQL 10 - execution time 6 ms PostgreSQL 14 - execution time 2.5 ms Postgres 14 is about 2x faster Regards Pavel
