About Query Performaces Problem

From: Hüseyin Ellezer <ellezerh(at)gmail(dot)com>
To: pgsql-performance(at)lists(dot)postgresql(dot)org
Subject: About Query Performaces Problem
Date: 2022-01-11 08:41:05
Message-ID: CAG4tuQFV+vp7c5P6SpZ3FKcN32OwX4BL445_rKs2tBQk3G1vGg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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?

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Pavel Stehule 2022-01-11 13:31:07 Re: About Query Performaces Problem
Previous Message Vijaykumar Jain 2022-01-06 16:50:13 Re: Same query 10000x More Time