Thread: performance: pg vs pg!

At the suggestion of several people, I have increased the

default settings in postgresql.conf before continuing my

postgresql vs mysql performance tests.

To date, I have only been loading a (roughly) million-row

file, creating indexes during the load, running a vacuum analyze,

and a couple of simple queries.

I do intend on performing more complicated tests, but I did not

want to do that until people stopped telling me my methodology

for simple tests was...flawed.

I ran a thorough series of tests, varying shared_buffers from 1000 to 9000,

work_mem from 10000 to 90000 and maintenance_work_mem from 10000 to 90000.

The complete results are long (I will provide them if anyone is interested)

so I am only including a small selection.

Before I do that, I will confess that increasing memory made more of

a difference than I thought it would.  I know many of you are thinking

"no kidding" but I thought it would only be important for big

complicated queries, or a server with multiple concurrent requests.

No, it makes a big difference for "merely" loading a million rows and

indexing them.

                                   Time in seconds

shared_buffers work_mem m_work_mem COPY       VACUUM

 1000           10000    10000     186.154     9.814   

 3000           10000    10000      64.404     4.526   

 5000           50000    50000      65.036     3.435   

 9000           90000    90000      63.664     2.218   

---------- The relevant commands

create table data (

      fid   integer,

      rid   integer,

      range real,

      x     real,

      y     real,

      z     real,

      bs    real,

      snr   real,

      rvel  real,

      cfar  smallint);

create index fidrid_data on data (fid,rid);

create index fidx on data (x);

-- COPY a table with 934500 rows

COPY data (fid,rid,range,snr,bs,rvel,cfar,x,y,z) FROM '/home/rick/bed/data/data.dat';

-- VACUUM

vacuum analyze data;