@David Rowley Thanks for explaining. @Burcin I'm not too fond of the new column because it introduces a new index that needs to be maintained. I could change the index on `view_time` to `(shard_id, view_time)`, but I'm afraid that may increase the time to traverse the index (because all shard_id will be the same in a query). And maybe increase index size, but there is index compression for duplicate values on last versions.
Thank you On Fri, Aug 11, 2023 at 7:14 AM <burcinyaz...@gmail.com> wrote: > 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 > > > > >
