From: Tom Lane <tgl@sss.pgh.pa.us>
To: Laurenz Albe <laurenz.albe@cybertec.at>
Cc: MingJu Wu <mingjuwu0505@gmail.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Partial index creation always scans the entire table
Date: Sun, 16 Feb 2020 10:30:05 -0500
Message-ID: <19549.1581867005@sss.pgh.pa.us> (raw)
In-Reply-To: <43b329d274c3535c75d2fbd6912b5ae8e3e51582.camel@cybertec.at>
References: <CAKVFrvFY-f7kgwMRMiPLbPYMmgjc8Y2jjUGK_Y0HVcYAmU6ymg@mail.gmail.com>
<43b329d274c3535c75d2fbd6912b5ae8e3e51582.camel@cybertec.at>
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
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, laurenz.albe@cybertec.at, mingjuwu0505@gmail.com, pgsql-performance@lists.postgresql.org
Subject: Re: Partial index creation always scans the entire table
In-Reply-To: <19549.1581867005@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox