Skip site navigation (1) Skip section navigation (2)

Re: SELECT INTO large FKyed table is slow

From: Mario Splivalo <mario(dot)splivalo(at)megafon(dot)hr>
To: Mladen Gogala <mladen(dot)gogala(at)vmsinfo(dot)com>
Cc: "jd(at)commandprompt(dot)com" <jd(at)commandprompt(dot)com>, "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Subject: Re: SELECT INTO large FKyed table is slow
Date: 2010-12-01 17:00:26
Message-ID: (view raw or whole thread)
Lists: pgsql-performance
On 12/01/2010 05:34 PM, Mladen Gogala wrote:
> Mario Splivalo wrote:
>> Yes, as Mladen Gogala had advised. No noticable change in performance -
>> it's still slow :)
> Declaring constraints as deferrable  doesn't do anything as such, you
> have to actually set the constraints deferred to have an effect. You
> have to do it within a transaction block. If done outside of the
> transaction block, there is no effect:

I understand, I did as you suggested.

Begin; Set constraints all deferred; select my_insert_drones_function(); 

> I was able to insert the same value twice, it only failed at the end of
> the transaction.
>> But, just for the sake of clarification - I tought that DEFERRABLE would
>> matter if I do a lot of INSERTs, inside a FOR loop or something like
>> that. Since I'm doing INSERT INTO ... SELECT, does it makes any difference?
> You cannot tell which part takes a long time, select or insert, without
> profiling. I certainly cannot do it over the internet.

If I first select to a dummy temprary table, that SELECT is fast. Just 

I'll try what Pierre suggested, on whole new filesystem. This one did 
get quite filled with thousands of files that I deleted while the 
database was working.


In response to


pgsql-performance by date

Next:From: Mladen GogalaDate: 2010-12-01 17:06:35
Subject: Re: Clarification, please
Previous:From: Richard BroersmaDate: 2010-12-01 16:57:01
Subject: Re: Clarification, please

Privacy Policy | About PostgreSQL
Copyright © 1996-2015 The PostgreSQL Global Development Group