Message-ID: <55DE40BA.5000401@2ndquadrant.com>
Date: Thu, 27 Aug 2015 00:42:02 +0200
From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
 rv:31.0) Gecko/20100101 Thunderbird/31.7.0
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: Index creation running now for 14 hours
References: 
 <CAEaSS0atQonJQ4NrrrEaC-VW97R5ncp0zsDn0dJN2uX97wGMsA@mail.gmail.com>
	<A76B25F2823E954C9E45E32FA49D70ECCD512968@mail.corp.perceptron.com>
	<CAEaSS0YPFsNUNkw2f+j_kTaz_0mh1iLTYSsAFCY6DJf4rsf5nQ@mail.gmail.com>
	<A76B25F2823E954C9E45E32FA49D70ECCD5129AE@mail.corp.perceptron.com>
 <CAEaSS0ZcZ6XjnD62WOK=RHNrDTO0-O9E-YRkYG3Q-yy8fYeXwQ@mail.gmail.com>
In-Reply-To: 
 <CAEaSS0ZcZ6XjnD62WOK=RHNrDTO0-O9E-YRkYG3Q-yy8fYeXwQ@mail.gmail.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org


On 08/26/2015 10:26 PM, Tory M Blue wrote:

>
> the table is 90GB without indexes,  285GB with indexes and bloat,  The
> row count is not actually completing.. 125Million rows over 13 months,
> this table is probably close to 600million rows.

You don't need to do SELECT COUNT(*) if you only need an approximate 
number. You can look at pg_class.reltuples:

    SELECT reltuples FROM pg_class WHERE relname = 'impressions';

That should be a sufficiently accurate estimate.

> The above is when it had finished copying the table and started on the
> index..
>
> Well as I said I'm running out of storage as the index is creating some
> serious data on the filesystem, I'll have to kill it, try to massage the
> data a bit and increase the maintenance_work mem to use some of my 256GB
> of ram to try to get through this. Right now the 100% cpu process which
> is this index is only using 3.5GB and has been for the last 15 hours

Please post details on the configuration (shared_buffer, work_mem, 
maintenance_work_mem and such).

BTW while the the CREATE INDEX is reporting 3.5GB, it most likely wrote 
a lot of data into on-disk chunks when sorting the data. So it's 
actually using the memory through page cache (i.e. don't increase 
maintenance_work_mem too much, you don't want to force the data to disk 
needlessly).

regards

--
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance