Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qhEXf-001lKC-W9 for pgsql-performance@arkaria.postgresql.org; Fri, 15 Sep 2023 19:31:52 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qhEXc-002lJl-OB for pgsql-performance@arkaria.postgresql.org; Fri, 15 Sep 2023 19:31:48 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qhEXc-002lJV-BC for pgsql-performance@lists.postgresql.org; Fri, 15 Sep 2023 19:31:48 +0000 Received: from cloud.gatewaynet.com ([185.90.37.94]) by magus.postgresql.org with esmtps (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qhEXV-005Isp-5O for pgsql-performance@lists.postgresql.org; Fri, 15 Sep 2023 19:31:47 +0000 Content-Type: multipart/alternative; boundary="------------VXJ0cdfPaLDPUQhhqnvJQAaV" Message-ID: Date: Fri, 15 Sep 2023 22:31:37 +0300 MIME-Version: 1.0 Subject: Re: pgsql 10.23 , different systems, same table , same plan, different Buffers: shared hit To: Tom Lane Cc: pgsql-performance@lists.postgresql.org References: <24a36ca8-b5c0-d39f-c5f6-47cf2ef51abd@cloud.gatewaynet.com> <3310210.1694791429@sss.pgh.pa.us> Content-Language: en-US From: Achilleas Mantzios In-Reply-To: <3310210.1694791429@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------VXJ0cdfPaLDPUQhhqnvJQAaV Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit Στις 15/9/23 18:23, ο/η Tom Lane έγραψε: > Achilleas Mantzios - cloud 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@. _EMD_REEFER@hide>. _OfficeHayPoint@hide>. _Sabtank_PCQ1_All_SSVSSouth_area@hide>. _Sabtank_PCQ1_Lead_OperatorsSouth_area@hide>. (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@. 0033442057364@. 0072usl@. 0081354426912@. 00862163602861@. (5 rows) somethings does not seem right. > > regards, tom lane -- Achilleas Mantzios IT DEV - HEAD IT DEPT Dynacom Tankers Mgmt --------------VXJ0cdfPaLDPUQhhqnvJQAaV Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit
Στις 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

  


--------------VXJ0cdfPaLDPUQhhqnvJQAaV--