Horton

Re: Parallel CREATE INDEX for BRIN indexes

Matthias van de Meent <boekewurm+postgres@gmail.com>

View thread View on postgr.es
From: Matthias van de Meent <boekewurm+postgres@gmail.com>
To: Tomas Vondra <tomas.vondra@enterprisedb.com>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Date: 2023-07-04T21:53:41Z
Lists: pgsql-hackers 
On Thu, 8 Jun 2023 at 14:55, Tomas Vondra <tomas.vondra@enterprisedb.com> wrote:
>
> Hi,
>
> Here's a WIP patch allowing parallel CREATE INDEX for BRIN indexes. The
> infrastructure (starting workers etc.) is "inspired" by the BTREE code
> (i.e. copied from that and massaged a bit to call brin stuff).

Nice work.

> In both cases _brin_end_parallel then reads the summaries from worker
> files, and adds them into the index. In 0001 this is fairly simple,
> although we could do one more improvement and sort the ranges by range
> start to make the index nicer (and possibly a bit more efficient). This
> should be simple, because the per-worker results are already sorted like
> that (so a merge sort in _brin_end_parallel would be enough).

I see that you manually built the passing and sorting of tuples
between workers, but can't we use the parallel tuplesort
infrastructure for that? It already has similar features in place and
improves code commonality.

> For 0002 it's a bit more complicated, because with a single parallel
> scan brinbuildCallbackParallel can't decide if a range is assigned to a
> different worker or empty. And we want to generate summaries for empty
> ranges in the index. We could either skip such range during index build,
> and then add empty summaries in _brin_end_parallel (if needed), or add
> them and then merge them using "union".
>
>
> I just realized there's a third option to do this - we could just do
> regular parallel scan (with no particular regard to pagesPerRange), and
> then do "union" when merging results from workers. It doesn't require
> the sequence of TID scans, and the union would also handle the empty
> ranges. The per-worker results might be much larger, though, because
> each worker might produce up to the "full" BRIN index.

Would it be too much effort to add a 'min_chunk_size' argument to
table_beginscan_parallel (or ParallelTableScanDesc) that defines the
minimum granularity of block ranges to be assigned to each process? I
think that would be the most elegant solution that would require
relatively little effort: table_block_parallelscan_nextpage already
does parallel management of multiple chunk sizes, and I think this
modification would fit quite well in that code.

Kind regards,

Matthias van de Meent

Commits

  1. Remove incidental md5() function use from test

  2. Cleanup parallel BRIN index build code

  3. Stabilize test of BRIN parallel create

  4. Revert "Stabilize test of BRIN parallel create"

  5. Add regression test for BRIN parallel builds

  6. Use the correct PG_DETOAST_DATUM macro in BRIN

  7. Update nbits_set in brin_bloom_union

  8. Fix parallel BRIN builds with synchronized scans

  9. Allow parallel CREATE INDEX for BRIN indexes

  10. Add empty BRIN ranges during CREATE INDEX