Re: [SQL] improve performance

Alexaki Sofia <alexaki(at)ics(dot)forth(dot)gr> writes:
> Initially I loaded all data in one transaction. Subsequently, I increased
> the number of buffers and disabled fsync() (-o -F) and I loaded the
> data again but the performance was almost unchanged. Does it make sense??
> How can I improve performance? To note that no indexes are created on the
> tables and that I load both "insert into" and "create table" statements.

If you can load the data with a COPY command, instead of individual
INSERTs, it'll go a lot faster. See also
http://www.postgresql.org/devel-corner/docs/postgres/populate.htm

> I want to reduce the space (8 Kb) allocated by
> DBMS when more space is required to load the data in a table. I reduced
> the parameter BLCKSZ but the space allocated remains the same, that is 8
> KB.
> How can the allocated space be reduced? If the space was reduced
> would loading time and query time increase?

I've heard lots of people want to increase BLCKSZ, but you're the first
one who ever wanted to reduce it. You sure you want to do this? It's
going to make the maximum row length uncomfortably short.

Anyway, you probably forgot to do a full rebuild after changing config.h.
"make clean" before "make all" is the only way to be sure the
configuration change propagates to all the code. Don't forget you will
have to do an initdb, also, so back up your data with the old code first.

regards, tom lane