From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
To: pgsql-performance@postgresql.org
Subject: Re: Index creation running now for 14 hours
Date: Thu, 27 Aug 2015 00:42:02 +0200
Message-ID: <55DE40BA.5000401@2ndquadrant.com> (raw)
In-Reply-To: <CAEaSS0ZcZ6XjnD62WOK=RHNrDTO0-O9E-YRkYG3Q-yy8fYeXwQ@mail.gmail.com>
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>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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
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 using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tomas.vondra@2ndquadrant.com
Subject: Re: Index creation running now for 14 hours
In-Reply-To: <55DE40BA.5000401@2ndquadrant.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