Re: Fwd: Tweaking PG (again)

>> 8.4 seconds is a very long time to spend looking up a single record.
>> Is this table bloated? What does
>>
>> vacuum verbose books;
>>
>> say about it? Look for a line like this:
>>
>> There were 243 unused item pointers
>
> Thanks but this table "books" has autovac on, and it's manually
> vacuumed every hour!

The table may still be bloated - the default autovacuum parameters may not
be agressive enough for heavily modified tables.

> Yes there is a table VISITCOUNT that has a foreign key on books(id).
> But why should that be invoked? Shouldn't that fk be called into
> question only when a row is being inserted/updated in VISITCOUNT table
> and not BOOKS?

I don't see a reason to check the VISITCOUNT -> books(id) foreign key, as
it is an insert. Are there any foreign keys referencing other tables (from
the books table)? According to the table structure you've sent earlier,
there are no such columns.

Try to determine whether the insert is CPU or I/O bound - run some
monitoring tool (dstat for example), run the insert and observe if there
is a lot of CPU activity, if the CPU waits for I/O operations to complete,
and if the I/O operations are mostly reads or writes. This will give you
an overview of the total I/O activity of the system.

BTW have you checked the postgresql.log? Are there any clues regarding the
insert (i.e. logs at the same time)? Don't forget to enable checkpoint
warnings in the config!

regards
Tomas