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

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