Date: Sat, 15 Feb 2020 06:53:30 -0600
From: Justin Pryzby <pryzby@telsasoft.com>
To: MingJu Wu <mingjuwu0505@gmail.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Partial index creation always scans the entire table
Message-ID: <20200215125330.GE31889@telsasoft.com>
References: 
 <CAKVFrvFY-f7kgwMRMiPLbPYMmgjc8Y2jjUGK_Y0HVcYAmU6ymg@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <CAKVFrvFY-f7kgwMRMiPLbPYMmgjc8Y2jjUGK_Y0HVcYAmU6ymg@mail.gmail.com>
User-Agent: Mutt/1.5.24 (2015-08-30)
Precedence: bulk

On Sat, Feb 15, 2020 at 07:04:48PM +0800, MingJu Wu wrote:
> Hello,
> 
> 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?
> 
> 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.
> 
> I've tested with PostgreSQL 12.2. Below is an example setup showing the

I don't think it's possible, and an index scan wouldn't necessarily be faster,
anyway, since the reads might be unordered rather than sequantial, and might
hit large fractions of the table even though only returning a fraction of its
tuples.

But have you thought about partitioning on category rather than partial
indexes?  Possibly hash partition of (category).  If your queries usually
include category_id=X, that might be a win for performance anyway, since tables
can now be read sequentially rather than scannned by index (again, probably out
of order).

-- 
Justin