Date: Sun, 25 Nov 2018 08:12:25 -0600
From: Justin Pryzby <pryzby@telsasoft.com>
To: Mariel Cherkassky <mariel.cherkassky@gmail.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: explain analyze faster then query
Message-ID: <20181125141225.GQ10913@telsasoft.com>
References: 
 <CA+t6e1nb40u+EN3wUjFc6CzvkZ+0vjFr=dX+Y4QmA6tAwXLWEg@mail.gmail.com>
 <20181125133002.GP10913@telsasoft.com>
 <CA+t6e1kk+OW8_3uMOzE5fghOK0ommQjhyoGOgOd8XsnpFaYCKg@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <CA+t6e1kk+OW8_3uMOzE5fghOK0ommQjhyoGOgOd8XsnpFaYCKg@mail.gmail.com>
User-Agent: Mutt/1.5.23 (2014-03-12)
Precedence: bulk

On Sun, Nov 25, 2018 at 03:37:46PM +0200, Mariel Cherkassky wrote:
> I run it from inside the machine on the local database.
> For example :
> 
> db=# create table rule_test as select generate_series(1,100000000);
> SELECT 100000000

> db=# explain analyze select generate_series from rule_test order by
> generate_series asc;

So it's returning 100M rows to the client, which nominally will require moving
400MB.

And pgsql is formatting the output.

I did a test with 10M rows:

[pryzbyj@database ~]$ command time -v psql postgres -c 'SELECT * FROM rule_test' |wc -c&
Command being timed: "psql postgres -c SELECT * FROM rule_test"
        User time (seconds): 11.52
        Percent of CPU this job got: 78%
        Elapsed (wall clock) time (h:mm:ss or m:ss): 0:17.25
        Maximum resident set size (kbytes): 396244
...
170000053

Explain analyze takes 0.8sec, but returning query results uses 11sec CPU time
on the *client*, needed 400MB RAM (ints now being represented as strings
instead of machine types), and wrote 170MB to stdout, Also, if the output is
being piped to less, the data is going to be buffered there, which means your
query is perhaps using 4GB RAM in psql + 4GB in less..

Is the server swapping ?   check "si" and "so" in output of "vmstat -w 1"

Justin