Date: Sun, 23 Feb 2020 09:10:38 -0600
From: Justin Pryzby <pryzby@telsasoft.com>
To: Ravi Garg <ravi.garg@yahoo.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: PostgreSQL 11 higher Planning time on Partitioned table
Message-ID: <20200223151038.GW31889@telsasoft.com>
References: <1789489940.5008704.1582451790367.ref@mail.yahoo.com>
 <1789489940.5008704.1582451790367@mail.yahoo.com>
 <20200223101209.GU31889@telsasoft.com>
 <1169437250.5002073.1582455449880@mail.yahoo.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <1169437250.5002073.1582455449880@mail.yahoo.com>
User-Agent: Mutt/1.5.24 (2015-08-30)
Precedence: bulk

On Sun, Feb 23, 2020 at 10:57:29AM +0000, Ravi Garg wrote:
>    - Currently we are thinking to have Daily partitions and as we need to keep 6 months of data thus 180 Partitions.However we have liberty to reduce the number of partitions to weekly/fortnightly/monthly, If we get comparable performance.   

I didn't hear how large the tables and indexes are.

>    - We need to look current partition and previous partition for all of our use-cases/queries.

Do you mean that a given query is only going to hit 2 partitions ?  Or do you
mean that all but the most recent 2 partitions are "archival" and won't be
needed by future queries ?

> Can you please suggest what sort of combinations/partition strategy we can test considering data-volume/vacuum etc. Also let me know if some of the pg_settings can help us tuning this (I have attached my pg_settings).

You should determine what an acceptable planning speed is, or the best balance
of planning/execution time.  Try to detach half your current partitions and, if
that gives acceptable performance, then partition by day/2 or more.  You could
make a graph of (planning and total) time vs npartitions, since I think it's
likely to be nonlinear.

I believe others have reported improved performance under v11 with larger
numbers of partitions, by using "partitions of partitions".  So you could try
making partitions by month themselves partitioned by day.

>    - Our use case is limited to simple selects (we don't join with the other
>    tables) however, we are expecting ~70 million records inserted per day
>    and there would be couple of updates on each records where average record
>    size would be ~ 1.5 KB.

>  shared_buffers                         | 1048576

If you care about INSERT performance, you probably need to make at least a
single partition's index fit within shared_buffers (or set shared_buffers such
that it fits).  Use transactions around your inserts.  If your speed is not
limited by I/O, you could further use multiple VALUES(),() inserts, or maybe
prepared statements.  Maybe synchronous_commit=off.

If you care about (consistent) SELECT performance, you should consider
VACUUMing the tables after bulk inserts, to set hint bits (and since
non-updated tuples won't be hit by autovacuum).  Or maybe VACUUM FREEZE to
freeze tuples (since it sounds like a typical page is unlikely to ever be
updated).

-- 
Justin