Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
From: burcinyazici@gmail.com
Mime-Version: 1.0 (1.0)
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>
References: 
 <CAApHDvo2Lvi+Lr6JuGYLaNF43wGALktbbZTi99rvnVtRYX_5iQ@mail.gmail.com>
Cc: Dorian Hoxha <dorian.hoxha@gmail.com>,
 pgsql-performance <pgsql-performance@postgresql.org>
In-Reply-To: 
 <CAApHDvo2Lvi+Lr6JuGYLaNF43wGALktbbZTi99rvnVtRYX_5iQ@mail.gmail.com>
To: David Rowley <dgrowleyml@gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/CBF1A1B2-CF41-4DED-8FE2-4A40D67A04D5%40gmail.com>
Precedence: bulk

hi,
Consider adding id%10 as a new column?you will have one time burden but afte=
r creating index on it, update perf will satisfy.

Burcin =F0=9F=93=B1

> On 11 Aug 2023, at 07:49, David Rowley <dgrowleyml@gmail.com> wrote:
>=20
> =EF=BB=BFOn Thu, 10 Aug 2023 at 20:36, Dorian Hoxha <dorian.hoxha@gmail.co=
m> wrote:
>>> Do Not Use Partial Indexes as a Substitute for Partitioning
>>> While a search in this larger index might have to descend through a coup=
le more tree levels than a search in a smaller index, that's almost certainl=
y going to be cheaper than the planner effort needed to select the appropria=
te one of the partial indexes. The core of the problem is that the system do=
es not understand the relationship among the partial indexes, and will labor=
iously test each one to see if it's applicable to the current query.
>>=20
>> Would this be true in my case too?
>=20
> 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 =3D N; which just picks 1 index
> without searching all of them.
>=20
>> Is it faster for the planner to select a correct partition(hash partition=
ing on `id` column) instead of a correct partial index like in my case? I do=
n't think I'll need more than ~32 partitions/partial-indexes in an extreme s=
cenario.
>=20
> 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.
>=20
> David
>=20
>=20