February 25th, 2013, 09:18 AM
How to improve postgres performance?
I have been trying to run BenchmarkSQL-2.3 with mysql and postgres servers and while doing that I have found out that postgres queries take much more time to execute than that of mysql. I tried tuning postgres by turning off the fsync, increasing the shared memory and effective cache size etc. but still postgresql's performance was quite low in comparison to mysql. So as a small test I ran a simple query like "SELECT * from order_line" multiple times on both the databases and noted down the query execution times. The table order_line has 900000 lines, for mysql the average time over 20 runs of this query was about 4.9 seconds and for postgresql-9.2 about 13 seconds. The difference seems quite a lot to me. Is there a way to speed up the execution on postgres. table shema is as follows,
create table order_line (
ol_w_id integer not null,
ol_d_id integer not null,
ol_o_id integer not null,
ol_number integer not null,
ol_i_id integer not null,
primary key (ol_w_id, ol_d_id, ol_o_id, ol_number);
Also, there are no indexes used here.
if anyone has any ideas please share. thanks in advance.