On Sat, Feb 15, 2020 at 10:15:53PM +0100, Laurenz Albe 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. I have created a index on the category > > column, hoping that postgres can use this information when creating the partial indexes. > > However, postgres always performs full table scan. > > There is your problem. > > You don't need a partial index per category, you need a single index that *contains* the category. On Sun, Feb 16, 2020 at 10:30:05AM -0500, Tom Lane wrote: > 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 The OP mentioned having an index on "category", which they were hoping the creation of partial indexes would use: On Sat, Feb 15, 2020 at 07:04:48PM +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. I have > created a index on the category column, hoping that postgres can use this > information when creating the partial indexes. However, postgres always > performs full table scan. So the question is why they (think they) *also* need large number of partial indexes. I was reminded of reading this, but I think it's a pretty different case. https://heap.io/blog/engineering/running-10-million-postgresql-indexes-in-production -- Justin
