> Any finally, any ideas on planner constants? Here's what I'm using:
>
> seq_page_cost = 0.5 # measured on an arbitrary scale
> random_page_cost = 1.0 # same scale as above
> cpu_tuple_cost = 0.001 # same scale as above
> cpu_index_tuple_cost = 0.0001 # same scale as above
> cpu_operator_cost = 0.00025 # same scale as above
> effective_cache_size = 679006
>
> I really don't remember how I came up with that effective_cache_size
> number....
I don't have much experience with the way your application works, but:
1) What is the size of the whole database? Does that fit in your memory?
That's the first thing I'd like to know and I can't find it in your
post.
I'm missing several other important values too - namely
shared_buffers
max_fsm_pages
work_mem
maintenance_work_mem
BTW, is the autovacuum daemon running? If yes, try to stop it during
the import (and run ANALYZE after the import of data).
2) What is the size of a disc page? Without that we can only guess what
doest the effective_cache_size number means - in the usual case it's
8kB thus giving about 5.2 GiB of memory.
As suggested in http://www.powerpostgresql.com/PerfList I'd increase
that to about 1.400.000 which about 10.5 GiB (about 2/3 of RAM).
Anyway - don't be afraid this breaks something. This is just an
information for PostgreSQL how much memory the OS is probably using
as a filesystem cache. PostgreSQL uses this to evaluate the
probability that the page is in a cache.
3) What is the value of maintenance_work_mem? This is a very important
value for CREATE INDEX (and some other). The lower this value is,
the slower the CREATE INDEX is. So try to increase the value as much
as you can - this could / should improve the import performance
considerably.
But be careful - this does influence the amount of memmory allocated
by PostgreSQL. Being in your position I wouldn't do this in the
postgresql.conf - I'd do that in the connection used by the import
using SET command, ie. something like
SET maintenance_work_mem = 524288;
CREATE INDEX ...
CREATE INDEX ...
CREATE INDEX ...
CREATE INDEX ...
for a 512 MiB of maintenance_work_mem. Maybe even a higher value
could be used (1 GiB?). Just try to fiddle with this a little.
4) Try to set up some performance monitoring - for example a 'dstat' is
a nice way to do that. This way you can find yout where's the
bottleneck (memory, I/O etc.)
That's basically all I can think of right now.
Tomas
