pgsql 10.23 , different systems, same table , same plan, different Buffers: shared hit
От | Achilleas Mantzios - cloud |
---|---|
Тема | pgsql 10.23 , different systems, same table , same plan, different Buffers: shared hit |
Дата | |
Msg-id | 24a36ca8-b5c0-d39f-c5f6-47cf2ef51abd@cloud.gatewaynet.com обсуждение исходный текст |
Ответы |
Re: pgsql 10.23 , different systems, same table , same plan, different Buffers: shared hit
(Tom Lane <tgl@sss.pgh.pa.us>)
|
Список | pgsql-performance |
Dear All
I have a weird problem, I am trying to improve performance on this query :
SELECT text('foo@bar.com') from mail_vessel_addressbook where text('foo@bar.com') ~* address_regex limit 1;
The first system (linux) is a linux hosted in a cloud, kernel 3.16.0-4-amd64, 32GB mem, SSD, 4 x Intel(R) Xeon(R) CPU E7-4860 v2 @ 2.60GHz ,
The second (freebsd) system, used as test, is my local FreeBSD 13.1-RELEASE workstation, 32GB mem, ZFS/magnetic disks ,16 x AMD Ryzen 7 5800X 3800.16-MHz .
Overall my workstation is faster, but my issue is not plain speed. The problem is as follows :
FreeBSD
postgres@[local]/dynacom=# explain (analyze,buffers) SELECT text('foo@bar.com') from mail_vessel_addressbook where text('foo@bar.com') ~* address_regex limit 1;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.42..5.11 rows=1 width=32) (actual time=96.705..96.706 rows=1 loops=1)
Buffers: shared hit=71
-> Index Only Scan using mail_vessel_addressbook_address_regex_idx on mail_vessel_addressbook (cost=0.42..2912.06 rows=620 width=32) (actual time=96.704..96.705 rows=1 loops=1)
Filter: ('foo@bar.com'::text ~* address_regex)
Rows Removed by Filter: 14738
Heap Fetches: 0
Buffers: shared hit=71
Planning time: 0.082 ms
Execution time: 96.725 ms
(9 rows)
Time: 97.038 ms
postgres@[local]/dynacom=#
Linux
dynacom=# explain (analyze,buffers) SELECT text('foo@bar.com') from mail_vessel_addressbook where text('foo@bar.com') ~* address_regex limit 1;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.42..5.12 rows=1 width=32) (actual time=1768.725..1768.727 rows=1 loops=1)
Buffers: shared hit=530
-> Index Only Scan using mail_vessel_addressbook_address_regex_idx on mail_vessel_addressbook (cost=0.42..2913.04 rows=620 width=32) (actual time=1768.724..1768.725 rows=1 loops=1)
Filter: ('foo@bar.com'::text ~* address_regex)
Rows Removed by Filter: 97781
Heap Fetches: 0
Buffers: shared hit=530
Planning time: 1.269 ms
Execution time: 1768.998 ms
(9 rows)
The file in FreeBSD came by pg_dump from the linux system, I am puzzled why this huge difference in Buffers: shared hit. All table/index sizes are identical on both systems, I did vacuum full on the linux one, and also did vacuum freeze on both. I analyzed both, reindexed both (several times). Still the FreeBSD seems to access about 7 times less number of blocks from shared_buffers than linux : 71 vs 530 . There is no bloat , I tested with newly fresh table in both systems as well.
Thank you for any help.
В списке pgsql-performance по дате отправления:
Следующее
От: Tom LaneДата:
Сообщение: Re: pgsql 10.23 , different systems, same table , same plan, different Buffers: shared hit