CODEWITHSUNDEEP
sqlperformancepostgresqlinsert
murison
murison

Reputation: 3983

Which is quicker in psql: 2 inserts from selects, or one insert from union of selects?

As in the title: I have a table that I need to populate. I have two sources of data to put in that table. Which will be quicker:

INSERT INTO foo
SELECT bar, baz FROM xxxx;

INSERT INTO foo
SELECT beq, que FROM yyyyy;

OR: 

INSERT INTO foo
SELECT * FROM (
SELECT bar, baz FROM xxxx
UNION
SELECT beq, que FROM yyyyy ) src;

I'm using PostgreSQL 9.1.

Upvotes: 2

Views: 172

Answers (2)

murison
murison

Reputation: 3983

In my case (inserting ~7500 rows), the difference was about 2secs (UNION ALL was quicker).

Upvotes: 1

roman
roman

Reputation: 117345

I'd say union all would be faster than two inserts, just because it's one transaction instead of two, but I'm not DBA at all, and I guess it could depend on number of rows / types of columns in the tables. Tested on SQL fiddle on PostgreSQL 9.2.4 on 2 table with 1 000 000 records each. Results:

2 inserts ~ 2600 ms
1 insert with union ~ 10800 ms - eliminating duplicates is expensive!
1 insert with union all ~ 2460 ms

see it on sql fiddle demo

Upvotes: 3

Related Questions