st 12. 1. 2022 v 9:23 odesílatel Hüseyin Ellezer <ellez...@gmail.com> napsal:
> I mean, despite the execution times shown here PostgreSQL 10 is working > faster compared to PostgreSQL 14. Is this speed performance about the > cached or disk data? How can we see where the data comes from? > use EXPLAIN (ANALYZE, BUFFERS) SELECT ... https://www.postgresql.org/docs/current/sql-explain.html Regards Pavel > Best regards > > Pavel Stehule <pavel.steh...@gmail.com>, 11 Oca 2022 Sal, 16:31 tarihinde > şunu yazdı: > >> >> >> ú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 >> >
