út 11. 1. 2022 v 9:41 odesílatel Hüseyin Ellezer <ellez...@gmail.com> 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
