From: Tom Lane <tgl@sss.pgh.pa.us>
To: Laurenz Albe <laurenz.albe@cybertec.at>
cc: MingJu Wu <mingjuwu0505@gmail.com>, pgsql-performance@lists.postgresql.org
Subject: Re: Partial index creation always scans the entire table
In-reply-to: <43b329d274c3535c75d2fbd6912b5ae8e3e51582.camel@cybertec.at>
References: 
 <CAKVFrvFY-f7kgwMRMiPLbPYMmgjc8Y2jjUGK_Y0HVcYAmU6ymg@mail.gmail.com>
 <43b329d274c3535c75d2fbd6912b5ae8e3e51582.camel@cybertec.at>
Comments: In-reply-to Laurenz Albe <laurenz.albe@cybertec.at>
	message dated "Sat, 15 Feb 2020 22:15:53 +0100"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <19548.1581867005.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Sun, 16 Feb 2020 10:30:05 -0500
Message-ID: <19549.1581867005@sss.pgh.pa.us>
Precedence: bulk

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 i=
nto 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 tha=
t *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