Re: Allow parallel plan for referential integrity checks?

On 8/17/23 09:32, Frédéric Yhuel wrote:
> 
> 
> On 8/10/23 17:06, Juan José Santamaría Flecha wrote:
>> Recently I restored a database from a directory format backup and 
>> having this feature would have been quite useful
> 
> Hi,
> 
> Thanks for resuming work on this patch. I forgot to mention this in my 
> original email, but the motivation was also to speed up the restore 
> process. Parallelizing the FK checks could make a huge difference in 
> certain cases. We should probably provide such a test case (with perf 
> numbers), and maybe this is it what Robert asked for.

I have attached two scripts which demonstrate the following problems:

1a. if the tables aren't analyzed nor vacuumed before the post-data 
step, then they are index-only scanned, with a lot of heap fetches 
(depending on their size, the planner sometimes chooses a seq scan instead).

1b. if the tables have been analyzed but not vacuumed before the 
post-data-step, then they are scanned sequentially. Usually better, but 
still not so good without a parallel plan.

2. if the visibility maps have been created, then the tables are 
index-only scanned without heap fetches, but this can still be slower 
than a parallel seq scan.

So it would be nice if pg_restore could vacuum analyze the tables before 
the post-data step. I believe it would be faster in most cases.

And it would be nice to allow a parallel plan for RI checks.

Best regards,
Frédéric