Re: plan_create_index_workers doesn't account for TOAST

On 6/29/23 10:12 AM, Jonathan S. Katz wrote:
> Hi,
> 
> plan_create_index_workers[1] does not consider the amount of tuples 
> existing in TOAST pages when determining the number of parallel workers 
> to use for a build. The estimation comes from estimate_rel_size[2], 
> which in this case, will just take the value from rel->rd_rel->relpages.
> 
> We probably don't notice this much with B-trees, given a B-tree is 
> typically used for data that does not require toasting. However, this 
> becomes more visible when working on custom index access methods that 
> implement their own parallel build strategy.
> 
> For example, pgvector[3] provides its own data types and index access 
> method for indexing vector data. Vectors can get quite large fairly 
> quickly, e.g. a 768-dimensional vector takes up 8 + 4*768 = 3080 bytes 
> on disk, which quickly clears the default TOAST tuple threshold.
> 
> In a recent patch proposal to allow for building indexes in parallel[4], 
> I performed a few experiments on how many parallel workers would be 
> spawned when indexing 1,000,000 (1MM) 768-dim vectors, both with 
> EXTEDNED (default) and PLAIN storage. In all cases, I allowed for leader 
> participation, but the leader is not considered in 
> plan_create_index_workers.
> 
> With EXTENDED, plan_create_index_workers recommended 2 workers. The 
> build time was ~2x faster than the serial build.
> 
> With PLAIN, plan_create_index_workers recommended 4 workers. The build 
> time was **~3X faster** than the serial build.
> 
> (I've been doing more detailed, less hand-waivy performance testing, but 
> I wanted to provide directional numbers here)
> 
> It seems like we're leaving some performance for columns with TOASTed 
> data that require indexing, so I wanted to propose allowing the pages in 
> TOASTed tables to be considered when we're trying to index a column with 
> TOASTed attributes.

Just to add to this: there is a lever to get more parallel workers by 
setting "min_parallel_table_scan_size" to a lower value, which does help 
in this case. However, it does mask the fact that a large chunk of the 
data required to build the index exists in the TOAST table, which is not 
intuitive to a user who rarely has to use tuning parameters.

Thanks,

Jonathan