E
E
Egor Khombak2021-11-17 15:21:45
PostgreSQL
Egor Khombak, 2021-11-17 15:21:45

Does the power of the computer/server affect the executor plan in Postgres?

Such a situation: there is a select query for which we draw up a query plan using explain analyze verbose. And by executing the query on 2 different devices, we get different values ​​for cost, rows and width:
for the first

"Limit  (cost=476.22..476.24 rows=5 width=40)\n  Output: augmented_report_table.description, (count(*) OVER (?))\n  CTE augmented_report_table\n    ->  Seq Scan on public.nc_objects  (cost=0.00..472.64 rows=73 width=32)\n          Output: nc_objects.description\n          Filter: ((nc_objects.name)::text = 'vni-0/0'::text)\n  ->  Sort  (cost=3.59..3.77 rows=73 width=40)\n        Output: augmented_report_table.description, (count(*) OVER (?))\n        Sort Key: augmented_report_table.description\n        ->  WindowAgg  (cost=0.00..2.37 rows=73 width=40)\n              Output: augmented_report_table.description, count(*) OVER (?)\n              ->  CTE Scan on augmented_report_table  (cost=0.00..1.46 rows=73 width=32)\n                    Output: augmented_report_table.description\n"

For the second:
"Limit  (cost=485.85..485.86 rows=5 width=40)\n  Output: augmented_report_table.description, (count(*) OVER (?))\n  CTE augmented_report_table\n    ->  Seq Scan on public.nc_objects  (cost=0.00..472.64 rows=269 width=9)\n          Output: nc_objects.description\n          Filter: ((nc_objects.name)::text = 'vni-0/0'::text)\n  ->  Sort  (cost=13.21..13.88 rows=269 width=40)\n        Output: augmented_report_table.description, (count(*) OVER (?))\n        Sort Key: augmented_report_table.description\n        ->  WindowAgg  (cost=0.00..8.74 rows=269 width=40)\n              Output: augmented_report_table.description, count(*) OVER (?)\n              ->  CTE Scan on augmented_report_table  (cost=0.00..5.38 rows=269 width=32)\n                    Output: augmented_report_table.description\n"

The databases are the same, version 11-9. The tables are also the same. The databases are deployed in docker. The only difference is PC.
Could this be the reason? Or what other options are there why the result is different?

Answer the question

In order to leave comments, you need to log in

2 answer(s)
E
Egor Khombak, 2021-11-17
@Khombachke

In general, no idea what was the reason. As a result, I updated docker to the latest version on both devices (was 20.10.8, updated to 20.10.10) and everything started, the results are the same

G
galaxy, 2021-11-17
@galaxy

Does not affect. Cost is calculated in parrots, which are calculated through coefficients (coefficients determine how many times slower or faster the operation is relative to seq_page_cost - page access during sequential reading).
The difference in Row/width is almost certainly a statistic issue. Do ANALYZE on both bases (you can even VACUUM FULL ANALYZE if the bases were used for recording).

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question