Re: SELECT INTO large FKyed table is slow - Mailing list pgsql-performance

From Joshua D. Drake
Subject Re: SELECT INTO large FKyed table is slow
Date
Msg-id 1291168052.20631.1963.camel@jd-desktop
Whole thread Raw
In response to SELECT INTO large FKyed table is slow  (Mario Splivalo <mario.splivalo@megafon.hr>)
Responses Re: SELECT INTO large FKyed table is slow
List pgsql-performance
On Sun, 2010-11-28 at 12:46 +0100, Mario Splivalo wrote:
> The database for monitoring certain drone statuses is quite simple:
>

> This is the slow part:
> INSERT INTO drones_history (sample_id, drone_id, drone_log_notice,
> drone_temperature, drone_pressure)
> SELECT * FROM tmpUpdate;
>
> For 100 rows this takes around 2 seconds. For 1000 rows this takes
> around 40 seconds. For 5000 rows this takes around 5 minutes.
> For 50k rows this takes around 30 minutes! Now this is where I start lag
> because I get new CSV every 10 minutes or so.

Have you considered making the foreign key check deferrable?

JD

--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
Consulting, Training, Support, Custom Development, Engineering
http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt


pgsql-performance by date:

Previous
From: bricklen
Date:
Subject: Re: Question about subselect/IN performance
Next
From: Bruce Momjian
Date:
Subject: Re: BBU Cache vs. spindles