From: Steve Crawford <scrawford@pinpointresearch.com>
To: Lee Wu <Lwu@mxlogic.com>
To: pgsql-admin@postgresql.org
Subject: Re: select * and save into a text file failed
Date: Fri, 10 Jun 2005 10:36:09 -0700
Message-ID: <200506101036.09527.scrawford@pinpointresearch.com> (raw)
In-Reply-To: <E1F0E4D89D8FA44789DFC065C8E755281A360E@mxlhq-exch01.corp.mxlogic.com>
References: <E1F0E4D89D8FA44789DFC065C8E755281A360E@mxlhq-exch01.corp.mxlogic.com>
On Friday 10 June 2005 10:03 am, Lee Wu wrote:
> That is exactly what I did:
>
> \o a_lot_room_to_hold_my_result
> select * from a_table
>
> either
> 1. out of memory for query result
> 2. killed
> 3. crash PG
>
> "If you have a very large table you can exhaust memory on the
> client side unless you are writing the data directly to a file."
> How besides "\o" and pg_dump?
>
> We have 4G RAM, and shared_buffers= 32768, it is a dedicate test
> box, while the table is about 2G.
Something to try (I don't know if it will work because I don't know
the exact internals of the PG libraries but it's something I've
observed): set the output formatting to unaligned (\pset format
unaligned).
I had a situation once where selecting a few thousand rows exhausted
my RAM. The problem was that one item in a text column was ~8k long
which meant that every other row had 8k of padding. And this was the
case for more than one column so the pager was having to swallow gobs
of data most of which was blank space. I ran the output to a file and
ran some tests. Switching to unaligned output dropped the size by
orders of magnitude. Depending on the nature of your data this may
help or do nothing at all.
Of course you are going to hit RAM or disk limitations on any given
machine. Cursors are there for your use and your project may require
them.
Cheers,
Steve
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: scrawford@pinpointresearch.com, Lwu@mxlogic.com
Subject: Re: select * and save into a text file failed
In-Reply-To: <200506101036.09527.scrawford@pinpointresearch.com>
References: <E1F0E4D89D8FA44789DFC065C8E755281A360E@mxlhq-exch01.corp.mxlogic.com> <200506101036.09527.scrawford@pinpointresearch.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox