Re: About Query Performaces Problem

ú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