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 <dgrowle...@gmail.com> wrote: > > On Thu, 10 Aug 2023 at 20:36, Dorian Hoxha <dorian.ho...@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 > >
