From: Justin Pryzby <pryzby@telsasoft.com>
To: Laurenz Albe <laurenz.albe@cybertec.at>
To: Tom Lane <tgl@sss.pgh.pa.us>
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 09:59:19 -0600
Message-ID: <20200216155919.GI31889@telsasoft.com> (raw)
In-Reply-To: <19549.1581867005@sss.pgh.pa.us> <43b329d274c3535c75d2fbd6912b5ae8e3e51582.camel@cybertec.at>
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
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: pryzby@telsasoft.com, laurenz.albe@cybertec.at, tgl@sss.pgh.pa.us, mingjuwu0505@gmail.com, pgsql-performance@lists.postgresql.org
Subject: Re: Partial index creation always scans the entire table
In-Reply-To: <20200216155919.GI31889@telsasoft.com>
* 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