Achilleas Mantzios - cloud <a.mantzios@xxxxxxxxxxxxxxxxxxxx> 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@xxxxxxx'::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@xxxxxxx'::text ~* address_regex) Rows Removed by Filter: 97781 Heap Fetches: 0 Buffers: shared hit=530The 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
