Thread: Preferred usage for 'copy to' for a subset of data
For recent postgres releases, is there any effective difference (performance/memory/io) between:
create temp table foo as select * from bar where bar.date > '2007-01-01';
copy foo to '/tmp/bar.out';
drop table temp;
and this:
copy ( select * from bar where bar.date > '2007-01-01' ) to '/tmp/bar.out';
...that would lead me to use one method vs. the other on large data sets (1M+ records)?
Just wondering,
Jason
create temp table foo as select * from bar where bar.date > '2007-01-01';
copy foo to '/tmp/bar.out';
drop table temp;
and this:
copy ( select * from bar where bar.date > '2007-01-01' ) to '/tmp/bar.out';
...that would lead me to use one method vs. the other on large data sets (1M+ records)?
Just wondering,
Jason
"Jason L. Buberel" <jason@buberel.org> writes: > For recent postgres releases, is there any effective difference > (performance/memory/io) between: > create temp table foo as select * from bar where bar.date > '2007-01-01'; > copy foo to '/tmp/bar.out'; > drop table temp; > and this: > copy ( select * from bar where bar.date > '2007-01-01' ) to '/tmp/bar.out'; Surely the latter will be faster, since it doesn't bother to store all the data in a server temp table. regards, tom lane