Re: Partial index creation always scans the entire table

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