Re: SELECT INTO large FKyed table is slow

On 12/01/2010 09:43 AM, Pierre C wrote:
>
> Note that in both cases postgres reports that the FK checks take 92-120
> milliseconds... which is a normal time for about 4000 rows.
> Inserting 4000 lines with just a few fields like you got should take
> quite much less than 1 s...
>
> Where the rest of the time goes, I have no idea. Disk thrashing ? Locks
> ? Gremlins ?
>
> - try it on a fresh copy of all your tables (CREATE TABLE, INSERT INTO
> SELECT)
> - try to put the WAL on a separate physical disk (or do a check with
> fsync=off)
> - try it on another computer
> - try it on another harddisk
> - run oprofile on a debug compile of postgres
> - it could even be the process title updates (I don't think so but...)
> - try a ramdisk tablespace

I'm allready running it with fsync=off. The funny thing is, as I add new
realm it runs fine until the history table grows around 5M rows. After
that the slowdown is huge.

I'm trying this on new hardware this weekend, I'll post here the results.

Mario