Date: Sun, 16 Feb 2020 09:59:19 -0600
From: Justin Pryzby <pryzby@telsasoft.com>
To: Laurenz Albe <laurenz.albe@cybertec.at>, Tom Lane <tgl@sss.pgh.pa.us>
Cc: MingJu Wu <mingjuwu0505@gmail.com>,
	pgsql-performance@lists.postgresql.org
Subject: Re: Partial index creation always scans the entire table
Message-ID: <20200216155919.GI31889@telsasoft.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <19549.1581867005@sss.pgh.pa.us>
 <43b329d274c3535c75d2fbd6912b5ae8e3e51582.camel@cybertec.at>
User-Agent: Mutt/1.5.24 (2015-08-30)
Precedence: bulk

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@cybertec.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