Re: Partial index creation always scans the entire table

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(dot)albe(at)cybertec(dot)at> 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