Re: About Query Performaces Problem

st 12. 1. 2022 v 9:23 odesílatel Hüseyin Ellezer <ellezerh(at)gmail(dot)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(dot)stehule(at)gmail(dot)com>, 11 Oca 2022 Sal, 16:31 tarihinde
> şunu yazdı:
>
>>
>>
>> út 11. 1. 2022 v 9:41 odesílatel Hüseyin Ellezer <ellezerh(at)gmail(dot)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
>>
>