Re: Allow parallel plan for referential integrity checks?

On 8/17/23 14:00, Frédéric Yhuel wrote:
> 
> 
> 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:
> 

Let me add the plans for more clarity:

> 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).
> 

https://explain.dalibo.com/plan/7491ga22c5293683#raw

> 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.
> 

https://explain.dalibo.com/plan/e92c5161g880bdhf#raw

> 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.
> 

https://explain.dalibo.com/plan/de22bdb4ggc3dffg#raw

And at last, with the patch applied : 
https://explain.dalibo.com/plan/54612a09ffh2565a#raw