From: Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: pgsql 10.23 , different systems, same table , same plan, different Buffers: shared hit
Date: Fri, 15 Sep 2023 22:31:37 +0300
Message-ID: <ce9acb4c-aa59-32b6-44fd-cfe713a4bf5c@cloud.gatewaynet.com> (raw)
In-Reply-To: <3310210.1694791429@sss.pgh.pa.us>
References: <24a36ca8-b5c0-d39f-c5f6-47cf2ef51abd@cloud.gatewaynet.com>
<3310210.1694791429@sss.pgh.pa.us>
Στις 15/9/23 18:23, ο/η Tom Lane έγραψε:
> Achilleas Mantzios - cloud<a.mantzios@cloud.gatewaynet.com> writes:
>> *FreeBSD*
>>
>> -> 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
>>
>> *Linux*
>>
>> -> 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
>> The file in FreeBSD came by pg_dump from the linux system, I am puzzled
>> why this huge difference in Buffers: shared hit.
> The "rows removed" value is also quite a bit different, so it's not
> just a matter of buffer touches --- there's evidently some real difference
> in how much of the index is being scanned. I speculate that you are
> using different collations on the two systems, and FreeBSD's collation
> happens to place the first matching row earlier in the index.
Thank you, I see that both systems use en_US.UTF-8 as lc_collate and
lc_ctype, and that in both systems :
dynacom=# \dOS+
List of collations
Schema | Name | Collate | Ctype | Provider | Description
------------+---------+---------+-------+----------+------------------------------
pg_catalog | C | C | C | libc | standard C collation
pg_catalog | POSIX | POSIX | POSIX | libc | standard POSIX
collation
pg_catalog | default | | | default | database's default
collation
(3 rows)
dynacom=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access
privileges
-----------+----------+-----------+-------------+-------------+------------------------
dynacom | postgres | SQL_ASCII | en_US.UTF-8 | en_US.UTF-8 |
the below seems ok
FreeBSD :
postgres@[local]/dynacom=# select * from (values
('a'),('Z'),('_'),('.'),('0')) as qry order by column1::text;
column1
---------
_
.
0
a
Z
(5 rows)
Linux:
dynacom=# select * from (values ('a'),('Z'),('_'),('.'),('0')) as qry
order by column1::text;
column1
---------
_
.
0
a
Z
(5 rows)
dynacom=#
but :
Freebsd :
postgres@[local]/dynacom=# select distinct address_regex from
mail_vessel_addressbook order by address_regex::text ASC limit 5;
address_regex
----------------------------------------------------------
_cmo.ship.inf@<hide>.<hid>
_EMD_REEFER@hide>.<hid>
_OfficeHayPoint@hide>.<hid>
_Sabtank_PCQ1_All_SSVSSouth_area@hide>.<hid>
_Sabtank_PCQ1_Lead_OperatorsSouth_area@hide>.<hid>
(5 rows)
While in Linux :
dynacom=# select distinct address_regex from mail_vessel_addressbook
order by address_regex::text ASC limit 5;
address_regex
-----------------------------------
0033240902573@<hidden>.<hid>
0033442057364@<hidden>.<hid>
0072usl@<hidden>.<hid>
0081354426912@<hidden>.<hid>
00862163602861@<hidden>.<hid>
(5 rows)
somethings does not seem right.
>
> regards, tom lane
--
Achilleas Mantzios
IT DEV - HEAD
IT DEPT
Dynacom Tankers Mgmt
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: a.mantzios@cloud.gatewaynet.com, tgl@sss.pgh.pa.us, pgsql-performance@lists.postgresql.org
Subject: Re: pgsql 10.23 , different systems, same table , same plan, different Buffers: shared hit
In-Reply-To: <ce9acb4c-aa59-32b6-44fd-cfe713a4bf5c@cloud.gatewaynet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox