From: Laurenz Albe <laurenz.albe@cybertec.at>
To: MingJu Wu <mingjuwu0505@gmail.com>
To: pgsql-performance@lists.postgresql.org
Subject: Re: Partial index creation always scans the entire table
Date: Sat, 15 Feb 2020 22:15:53 +0100
Message-ID: <43b329d274c3535c75d2fbd6912b5ae8e3e51582.camel@cybertec.at> (raw)
In-Reply-To: <CAKVFrvFY-f7kgwMRMiPLbPYMmgjc8Y2jjUGK_Y0HVcYAmU6ymg@mail.gmail.com>
References: <CAKVFrvFY-f7kgwMRMiPLbPYMmgjc8Y2jjUGK_Y0HVcYAmU6ymg@mail.gmail.com>
On Sat, 2020-02-15 at 19:04 +0800, MingJu Wu wrote:
> When creating partial indexes, can postgres utilize another index for figuring which rows
> should be included in the partial index, without performing a full table scan?
No; it has to be a full sequential scan.
> 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.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
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: 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: <43b329d274c3535c75d2fbd6912b5ae8e3e51582.camel@cybertec.at>
* 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