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