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
smallerindex, that's almost certainly going to be cheaper than the planner effort needed to select the appropriate one
ofthe partial indexes. The core of the problem is that the system does not understand the relationship among the
partialindexes, 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
partialindex 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