Laurenz Albe <laurenz.albe@xxxxxxxxxxx> writes: > On Sat, 2020-02-15 at 19:04 +0800, MingJu Wu wrote: >> My scenario is that I have a table with 50M rows that are categorized into 10K categories. >> I need to create a partial index for each category. > You don't need a partial index per category, you need a single index that *contains* the category. Yeah, that's an anti-pattern. Essentially, you are trying to replace the first branching level of an index that includes the category column with a ton of system catalog entries and planner proof logic to select one of N indexes that don't include the category. It is *highly* unlikely that that's going to be a win. It's going to be a huge loss if the planner fails to make the proof you need, and even when it does, it's not really going to be faster overall --- you've traded off run-time for planning time, at a rather unfavorable exchange rate. Updates on the table are going to be enormously penalized, too, because the index machinery doesn't have any way to understand that only one of the indexes needs work. I've seen people try to do this before. I wonder if the manual page about partial indexes should explicitly say "don't do that". regards, tom lane
