Message-ID: <4ab9371f1eb13334fe5cece7e788d9c8d5be08f6.camel@cybertec.at>
Subject: Re: multicolumn partitioning help
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: James Robertson <james@jsrobertson.net>
Cc: pgsql-performance@postgresql.org
Date: Wed, 15 Mar 2023 08:37:39 +0100
In-Reply-To: 
 <CAOA7M6u20MTMsRiWeKdtHUW4d15iEBiPN9GRBcZXyayFVhnBdQ@mail.gmail.com>
References: 
	<CAOA7M6tY5moDThZH=V2iFJ1ahxz6K2JRqGzDgu-gPP+GhLT4Lg@mail.gmail.com>
	 <784863d0cce3f14dcb41252fe90c11de1e8da902.camel@cybertec.at>
	 <CAOA7M6u20MTMsRiWeKdtHUW4d15iEBiPN9GRBcZXyayFVhnBdQ@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.46.4 (3.46.4-1.fc37) 
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/4ab9371f1eb13334fe5cece7e788d9c8d5be08f6.camel%40cybertec.at>
Precedence: bulk

On Tue, 2023-03-14 at 19:33 -0400, James Robertson wrote:
> Laurenz your solution is how I thought I would work around my (lack of) u=
nderstanding
> of partitioning. (nested partitions).
> I was hesitant=C2=A0because I didn't know what sort of performance proble=
ms I would create for myself.
>=20
> [...] more performance [...]

If you are thinking of subpartitioning primarily in terms of boosting perfo=
rmance,
you should know that you only get performance benefits from partitioning wi=
th
very special queries that effectively have to be designed together with the
partitioning strategy.  Other statements typically become somewhat slower
through partitioning.

So it is really impossible to discuss performance benefits without knowing
the exact query.  It may be best if you build a play database with realisti=
c amounts
of test data and use EXPLAIN and EXPLAIN (ANALYZE) to see the effects that
partitioning has on your queries.

Yours,
Laurenz Albe