Thread: dumping 8M bit fields
Hi, I have a table with 8M rows and one of the fields is a bit (1024 bits) field. I am trying to dump the bit field for 8M rows to a file: psql -U cicc3 -A -F " " -t -o pubchem.fp -c "select cid,gfp from pubchem_compound; However I get out of memory for query result I am surprised since I did an equivalent query but selecting varchar field for 6.9M rows which worked fine. The machine is a 4-cpu Opteron machine, 16GB RAM. Does anybody know why this might be the case? Thanks, ------------------------------------------------------------------- Rajarshi Guha <rguha@indiana.edu> GPG Fingerprint: 0CCA 8EE2 2EEB 25E2 AB04 06F7 1BB9 E634 9B87 56EE ------------------------------------------------------------------- A mouse is an elephant built by the Japanese.
On Thu, Sep 21, 2006 at 08:14:49PM -0400, Rajarshi Guha wrote: > Hi, I have a table with 8M rows and one of the fields is a bit (1024 > bits) field. I am trying to dump the bit field for 8M rows to a file: <snip> > out of memory for query result > > I am surprised since I did an equivalent query but selecting varchar > field for 6.9M rows which worked fine. The memory needed is related to how much screen space it takes. So a 1024 bitfield takes 1024 bytes to display. Times 6.9M rows is about 7GB of data. If the itention is to get all the data out into a file, try COPY. That just dumps the data, without trying to store it first. Hope this helps, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
Attachment
On Thu, 2006-09-21 at 20:14 -0400, Rajarshi Guha wrote:
> Hi, I have a table with 8M rows and one of the fields is a bit (1024
> bits) field. I am trying to dump the bit field for 8M rows to a file:
>
> psql -U cicc3 -A -F " " -t -o pubchem.fp -c "select cid,gfp from
> pubchem_compound;
>
> However I get
>
> out of memory for query result
>
psql is trying to load all of the data into RAM before outputting any of
it. More specifically, it's trying to load all of the output
representations of all the data into RAM before outputting it.
For 1024 bits, the output representation will be 1k. For 8M rows that's
a lot of RAM needed.
It would be better to use something like COPY or a cursor.
Regards,
Jeff Davis