CODEWITHSUNDEEP
postgresql
Van Le
Van Le

Reputation: 47

Postgres - UNION ALL vs INSERT INTO which is better?

I have two query by union all and insert into temp table.

Query 1

select * 
from (
    select a.id as id, a.name as name from a
    union all
    select b.id as id, b.name as name from b
)

Query 2

drop table if exists temporary;

create temp table temporary as
select id as id, name as name 
from a;

insert into temporary
select id as id, name as name 
from b;

select * from temp;

Please tell me which one is better for performance?

Upvotes: 0

Views: 4783

Answers (2)

DerekN
DerekN

Reputation: 1

I think use union all is the better performance way, not sure, you can try it your self. In tab run of SQL application alway show time to run. I take a snapshot in oracle; mysql and sql sv have the same tool to see it click here to see image

Upvotes: -1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520978

I would expect the second option to have better performance, at least at the the database level. Both versions require doing a full table scan of both the a and b tables. But the first version would create an unnecessary intermediate table, used only for the purpose of the insert.

The only potential issue with doing two separate inserts is latency, i.e. the time it might take some process to get to and from the database. If you are worried about this, then you can limit to one insert statement:

INSERT INTO temporary (id, name)
SELECT id, name FROM a
UNION ALL
SELECT id, name FROM b;

This would just require one trip to the database.

Upvotes: 3

Related Questions