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;