pgsql 10.23 , different systems, same table , same plan, different Buffers: shared hit

From: Achilleas Mantzios - cloud <a(dot)mantzios(at)cloud(dot)gatewaynet(dot)com>
To: pgsql-performance(at)lists(dot)postgresql(dot)org
Subject: pgsql 10.23 , different systems, same table , same plan, different Buffers: shared hit
Date: 2023-09-15 14:30:39
Message-ID: 24a36ca8-b5c0-d39f-c5f6-47cf2ef51abd@cloud.gatewaynet.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Dear All

I have a weird problem, I am trying to improve performance on this query :

SELECT text('foo(at)bar(dot)com') from mail_vessel_addressbook where
text('foo(at)bar(dot)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(at)[local]/dynacom=# explain (analyze,buffers) SELECT
text('foo(at)bar(dot)com') from mail_vessel_addressbook where
text('foo(at)bar(dot)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(at)bar(dot)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(at)[local]/dynacom=#

*Linux*

dynacom=# explain (analyze,buffers) SELECT text('foo(at)bar(dot)com') from
mail_vessel_addressbook where text('foo(at)bar(dot)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(at)bar(dot)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.

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Tom Lane 2023-09-15 15:23:49 Re: pgsql 10.23 , different systems, same table , same plan, different Buffers: shared hit
Previous Message Alvaro Herrera 2023-09-15 09:48:47 Re: Multixact wraparound monitoring