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;