From: burcinyazici@gmail.com
To: David Rowley <dgrowleyml@gmail.com>
Cc: Dorian Hoxha <dorian.hoxha@gmail.com>
Cc: pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: Partitioning update-heavy queue with hash partitions vs partial indexes
Date: Fri, 11 Aug 2023 08:13:55 +0300
Message-ID: <CBF1A1B2-CF41-4DED-8FE2-4A40D67A04D5@gmail.com> (raw)
In-Reply-To: <CAApHDvo2Lvi+Lr6JuGYLaNF43wGALktbbZTi99rvnVtRYX_5iQ@mail.gmail.com>
References: <CAApHDvo2Lvi+Lr6JuGYLaNF43wGALktbbZTi99rvnVtRYX_5iQ@mail.gmail.com>
hi,
Consider adding id%10 as a new column?you will have one time burden but after creating index on it, update perf will satisfy.
Burcin 📱
> On 11 Aug 2023, at 07:49, David Rowley <dgrowleyml@gmail.com> wrote:
>
> On Thu, 10 Aug 2023 at 20:36, Dorian Hoxha <dorian.hoxha@gmail.com> wrote:
>>> Do Not Use Partial Indexes as a Substitute for Partitioning
>>> While a search in this larger index might have to descend through a couple more tree levels than a search in a smaller index, that's almost certainly going to be cheaper than the planner effort needed to select the appropriate one of the partial indexes. The core of the problem is that the system does not understand the relationship among the partial indexes, and will laboriously test each one to see if it's applicable to the current query.
>>
>> Would this be true in my case too?
>
> Yes. The process of determining which partial indexes are valid for
> the given query must consider each index one at a time and validate
> the index's WHERE clause against the query's WHERE clause to see if it
> can be used. There is no shortcut that sees you have a series of
> partial indexes with WHERE id % 10 = N; which just picks 1 index
> without searching all of them.
>
>> Is it faster for the planner to select a correct partition(hash partitioning on `id` column) instead of a correct partial index like in my case? I don't think I'll need more than ~32 partitions/partial-indexes in an extreme scenario.
>
> I mean, test it and find out, but probably, yes, the partition pruning
> code for hash partitioning is an O(1) operation and is very fast.
> Once the given Constants have been hashed, finding the partition is
> just a single divide operation away.
>
> David
>
>
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: burcinyazici@gmail.com, dgrowleyml@gmail.com, dorian.hoxha@gmail.com
Subject: Re: Partitioning update-heavy queue with hash partitions vs partial indexes
In-Reply-To: <CBF1A1B2-CF41-4DED-8FE2-4A40D67A04D5@gmail.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