Improve performance of dumpSequenceData().
Nathan Bossart <nathan@postgresql.org>
Improve performance of dumpSequenceData(). As one might guess, this function dumps the sequence data. It is called once per sequence, and each such call executes a query to retrieve the relevant data for a single sequence. This can cause pg_dump to take significantly longer, especially when there are many sequences. This commit improves the performance of this function by gathering all the sequence data with a single query at the beginning of pg_dump. This information is stored in a sorted array that dumpSequenceData() can bsearch() for what it needs. This follows a similar approach as previous commits that introduced sorted arrays for role information, pg_class information, and sequence metadata. As with those commits, this patch will cause pg_dump to use more memory, but that isn't expected to be too egregious. Note that we use the brand new function pg_sequence_read_tuple() in the query that gathers all sequence data, so we must continue to use the preexisting query-per-sequence approach for versions older than 18. Reviewed-by: Euler Taveira, Michael Paquier, Tom Lane Discussion: https://postgr.es/m/20240503025140.GA1227404%40nathanxps13
Files
| Path | Change | +/− |
|---|---|---|
| src/bin/pg_dump/pg_dump.c | modified | +63 −18 |
Discussion
- improve performance of pg_dump with many sequences 29 messages · 2024-05-03 → 2026-01-09