Re: Can't get Dell PE T420 (Perc H710) perform better than a MacMini with PostgreSQL - Mailing list pgsql-performance

On Tue, Apr 7, 2015 at 6:27 AM, Pietro Pugni <pietro.pugni@gmail.com> wrote:

Hi Jeff,

sorry for the latency but server was down due to a error I made in the sysctl.conf file.

Yes, but are the defaults for those two systems?  on psql, use \l to see.

\l returns the following:

T420 (Postgres 9.4.1)

                                  List of databases

   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   

-----------+----------+----------+-------------+-------------+-----------------------

 grep      | grep     | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 

 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 

 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +

           |          |          |             |             | postgres=CTc/postgres

 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +

           |          |          |             |             | postgres=CTc/postgres

(4 rows)

MacMini (Postgres 9.0.13)

                                    List of databases

       Name        |   Owner    | Encoding | Collation | Ctype |    Access privileges    

-------------------+------------+----------+-----------+-------+-------------------------

 caldav            | caldav     | UTF8     | C         | C     | 

 collab            | collab     | UTF8     | C         | C     | 

 device_management | _devicemgr | UTF8     | C         | C     | 

 pen               | pen        | UTF8     | C         | C     | 

 postgres          | _postgres  | UTF8     | C         | C     | 

 roundcubemail     | roundcube  | UTF8     | C         | C     | 

 template0         | _postgres  | UTF8     | C         | C     | =c/_postgres           +

                   |            |          |           |       | _postgres=CTc/_postgres

 template1         | _postgres  | UTF8     | C         | C     | =c/_postgres           +

                   |            |          |           |       | _postgres=CTc/_postgres

(8 rows)

The difference between the "C" and the "en_US" collation is entirely sufficient to explain the difference in performance.  "C" is the fastest possible collation as it never needs to look ahead or consult tables, it just compares raw bytes.

Cheers,

Jeff

Hi Jeff,

is there a way to set a default collection during compiling or in the configuration file? I have never specified one, so I suppose that somewhere on MacMini “C” collation type is set as the default one.

Thank you a lot.

Best regards,

 Pietro