Re: SELECT INTO large FKyed table is slow

On Thu, Dec 2, 2010 at 3:36 AM, Mario Splivalo
<mario(dot)splivalo(at)megafon(dot)hr> wrote:
> 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.

Perhaps - that's the point at which the WAL volume becomes large
enough to force a checkpoint in the middle of the operation? You
might try turning on log_checkpoints.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company