Обсуждение: Preferred usage for 'copy to' for a subset of data

Поиск
Список
Период
Сортировка

Preferred usage for 'copy to' for a subset of data

От
"Jason L. Buberel"
Дата:
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

Re: Preferred usage for 'copy to' for a subset of data

От
Tom Lane
Дата:
"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