From: Laurenz Albe <laurenz.albe@cybertec.at>
To: James Robertson <james@jsrobertson.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: multicolumn partitioning help
Date: Wed, 15 Mar 2023 08:37:39 +0100
Message-ID: <4ab9371f1eb13334fe5cece7e788d9c8d5be08f6.camel@cybertec.at> (raw)
In-Reply-To: <CAOA7M6u20MTMsRiWeKdtHUW4d15iEBiPN9GRBcZXyayFVhnBdQ@mail.gmail.com>
References: <CAOA7M6tY5moDThZH=V2iFJ1ahxz6K2JRqGzDgu-gPP+GhLT4Lg@mail.gmail.com>
<784863d0cce3f14dcb41252fe90c11de1e8da902.camel@cybertec.at>
<CAOA7M6u20MTMsRiWeKdtHUW4d15iEBiPN9GRBcZXyayFVhnBdQ@mail.gmail.com>
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) understanding
> of partitioning. (nested partitions).
> I was hesitant because I didn't know what sort of performance problems I would create for myself.
>
> [...] more performance [...]
If you are thinking of subpartitioning primarily in terms of boosting performance,
you should know that you only get performance benefits from partitioning with
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 realistic amounts
of test data and use EXPLAIN and EXPLAIN (ANALYZE) to see the effects that
partitioning has on your queries.
Yours,
Laurenz Albe
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: laurenz.albe@cybertec.at, james@jsrobertson.net
Subject: Re: multicolumn partitioning help
In-Reply-To: <4ab9371f1eb13334fe5cece7e788d9c8d5be08f6.camel@cybertec.at>
* 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