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 usefulHi,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
create_and_fill_tables.sql
Description: application/sql
post_data.sql
Description: application/sql
