public inbox for [email protected]help / color / mirror / Atom feed
Re: Can we use Statistics Import and Export feature to perforamance testing? 7+ messages / 4 participants [nested] [flat]
* Re: Can we use Statistics Import and Export feature to perforamance testing? @ 2025-04-08 07:09 David Rowley <[email protected]> 2025-04-08 12:14 ` RE: Can we use Statistics Import and Export feature to perforamance testing? Ryohei Takahashi (Fujitsu) <[email protected]> 0 siblings, 1 reply; 7+ messages in thread From: David Rowley @ 2025-04-08 07:09 UTC (permalink / raw) To: Ryohei Takahashi (Fujitsu) <[email protected]>; +Cc: pgsql-hackers On Tue, 8 Apr 2025 at 12:21, Ryohei Takahashi (Fujitsu) <[email protected]> wrote: > By using Statistics Import and Export feature, is it possible to achieve the above request by following procedure? > (1) Export the statistics from production environment by using pg_dump --statistics-only. > (2) On the staging environment, set the autovacuum related parameters to prevent autovacuum from running. > (3) Import the statistics to staging environment by using the result of (1). You could certainly test the performance, but this method isn't guaranteed to give meaningful results just because the table stats match. One important thing to remember is that the planner also looks at the *actual size* of the relation and takes that into account when scaling the statistics (see table_block_relation_estimate_size() in tableam.c). If the table sizes don't match between the two servers then there's no guarantees the planner will produce the same plan. Also, there might be other subtleties regarding OIDs of indexes which are not guaranteed to be the same after dump/restore. Given some fuzzily close enough cost estimates (See add_path() and compare_path_costs_fuzzily()), it is possible a plan would switch to using another index if sorting the indexes by their OIDs didn't match on each server. The chances of that might be fairly small, but not zero. You'd also need to ensure the configs are the same in terms of GUCs that are used for costs. You could probably use get_relation_info_hook to overwrite the sizes and make sure the indexes are in the same order, etc. David ^ permalink raw reply [nested|flat] 7+ messages in thread
* RE: Can we use Statistics Import and Export feature to perforamance testing? 2025-04-08 07:09 Re: Can we use Statistics Import and Export feature to perforamance testing? David Rowley <[email protected]> @ 2025-04-08 12:14 ` Ryohei Takahashi (Fujitsu) <[email protected]> 2025-10-23 10:27 ` Re: Can we use Statistics Import and Export feature to perforamance testing? Yugo Nagata <[email protected]> 0 siblings, 1 reply; 7+ messages in thread From: Ryohei Takahashi (Fujitsu) @ 2025-04-08 12:14 UTC (permalink / raw) To: 'David Rowley' <[email protected]>; +Cc: pgsql-hackers Hi, Thank you for your reply. I understand that the access plans are not guaranteed to be the same. Can we add these notes to the pg_dump page in the PostgreSQL Documentation in order to prevent users from asking the same question? Regards, Ryohei Takahashi ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Can we use Statistics Import and Export feature to perforamance testing? 2025-04-08 07:09 Re: Can we use Statistics Import and Export feature to perforamance testing? David Rowley <[email protected]> 2025-04-08 12:14 ` RE: Can we use Statistics Import and Export feature to perforamance testing? Ryohei Takahashi (Fujitsu) <[email protected]> @ 2025-10-23 10:27 ` Yugo Nagata <[email protected]> 2025-10-24 00:00 ` RE: Can we use Statistics Import and Export feature to perforamance testing? Ryohei Takahashi (Fujitsu) <[email protected]> 0 siblings, 1 reply; 7+ messages in thread From: Yugo Nagata @ 2025-10-23 10:27 UTC (permalink / raw) To: Ryohei Takahashi (Fujitsu) <[email protected]>; +Cc: 'David Rowley' <[email protected]>; pgsql-hackers; Corey Huinker <[email protected]> Hi, On Tue, 8 Apr 2025 12:14:08 +0000 "Ryohei Takahashi (Fujitsu)" <[email protected]> wrote: > Thank you for your reply. > I understand that the access plans are not guaranteed to be the same. > > Can we add these notes to the pg_dump page in the PostgreSQL Documentation > in order to prevent users from asking the same question? I agree that it would be helpful to add this description, since the wording “statistics manipulation functions” might give the impression that they can influence generated plans. I’ve attached a patch that adds a new paragraph to the warning section of the documentation on statistics manipulation functions: Manually restored statistics do not guarantee that the same query plans will be generated as in the source environment, since factors such as relation sizes, index OIDs, and configuration parameters may affect planner behavior. What do you think? Regards, Yugo Nagata -- Yugo Nagata <[email protected]> Attachments: [text/x-diff] doc_add_warning_on_stats_manipulation_funcs.patch (738B, 2-doc_add_warning_on_stats_manipulation_funcs.patch) download | inline diff: diff --git a/doc/src/sgml/func/func-admin.sgml b/doc/src/sgml/func/func-admin.sgml index 1b465bc8ba7..bbdd20571d4 100644 --- a/doc/src/sgml/func/func-admin.sgml +++ b/doc/src/sgml/func/func-admin.sgml @@ -1959,6 +1959,12 @@ postgres=# SELECT '0/0'::pg_lsn + pd.segment_number * ps.setting::int + :offset <command>VACUUM</command> or <command>ANALYZE</command>) and should be considered temporary. </para> + <para> + Manually restored statistics do not guarantee that the same query plans + will be generated as in the source environment, since factors such as + relation sizes, index OIDs, and configuration parameters may affect + planner behavior. + </para> </warning> </para> ^ permalink raw reply [nested|flat] 7+ messages in thread
* RE: Can we use Statistics Import and Export feature to perforamance testing? 2025-04-08 07:09 Re: Can we use Statistics Import and Export feature to perforamance testing? David Rowley <[email protected]> 2025-04-08 12:14 ` RE: Can we use Statistics Import and Export feature to perforamance testing? Ryohei Takahashi (Fujitsu) <[email protected]> 2025-10-23 10:27 ` Re: Can we use Statistics Import and Export feature to perforamance testing? Yugo Nagata <[email protected]> @ 2025-10-24 00:00 ` Ryohei Takahashi (Fujitsu) <[email protected]> 2025-10-24 03:16 ` Re: Can we use Statistics Import and Export feature to perforamance testing? Yugo Nagata <[email protected]> 0 siblings, 1 reply; 7+ messages in thread From: Ryohei Takahashi (Fujitsu) @ 2025-10-24 00:00 UTC (permalink / raw) To: 'Yugo Nagata' <[email protected]>; +Cc: 'David Rowley' <[email protected]>; pgsql-hackers; Corey Huinker <[email protected]> Hi Nagata san, Thank you. Your patch is good for me. I think this kind of documentation can prevent users confusing. Regards, Ryohei Takahashi ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Can we use Statistics Import and Export feature to perforamance testing? 2025-04-08 07:09 Re: Can we use Statistics Import and Export feature to perforamance testing? David Rowley <[email protected]> 2025-04-08 12:14 ` RE: Can we use Statistics Import and Export feature to perforamance testing? Ryohei Takahashi (Fujitsu) <[email protected]> 2025-10-23 10:27 ` Re: Can we use Statistics Import and Export feature to perforamance testing? Yugo Nagata <[email protected]> 2025-10-24 00:00 ` RE: Can we use Statistics Import and Export feature to perforamance testing? Ryohei Takahashi (Fujitsu) <[email protected]> @ 2025-10-24 03:16 ` Yugo Nagata <[email protected]> 2026-05-27 05:54 ` Re: Can we use Statistics Import and Export feature to perforamance testing? vellaipandiyan sm <[email protected]> 0 siblings, 1 reply; 7+ messages in thread From: Yugo Nagata @ 2025-10-24 03:16 UTC (permalink / raw) To: Ryohei Takahashi (Fujitsu) <[email protected]>; +Cc: 'David Rowley' <[email protected]>; pgsql-hackers; Corey Huinker <[email protected]> On Fri, 24 Oct 2025 00:00:42 +0000 "Ryohei Takahashi (Fujitsu)" <[email protected]> wrote: > Hi Nagata san, > > > Thank you. > Your patch is good for me. > > I think this kind of documentation can prevent users confusing. Thank you for your review. I have registered this patch in the commitfest app. https://commitfest.postgresql.org/patch/6155/ Regards, Yugo Nagata -- Yugo Nagata <[email protected]> ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Can we use Statistics Import and Export feature to perforamance testing? 2025-04-08 07:09 Re: Can we use Statistics Import and Export feature to perforamance testing? David Rowley <[email protected]> 2025-04-08 12:14 ` RE: Can we use Statistics Import and Export feature to perforamance testing? Ryohei Takahashi (Fujitsu) <[email protected]> 2025-10-23 10:27 ` Re: Can we use Statistics Import and Export feature to perforamance testing? Yugo Nagata <[email protected]> 2025-10-24 00:00 ` RE: Can we use Statistics Import and Export feature to perforamance testing? Ryohei Takahashi (Fujitsu) <[email protected]> 2025-10-24 03:16 ` Re: Can we use Statistics Import and Export feature to perforamance testing? Yugo Nagata <[email protected]> @ 2026-05-27 05:54 ` vellaipandiyan sm <[email protected]> 2026-06-05 08:21 ` Re: Can we use Statistics Import and Export feature to perforamance testing? Yugo Nagata <[email protected]> 0 siblings, 1 reply; 7+ messages in thread From: vellaipandiyan sm @ 2026-05-27 05:54 UTC (permalink / raw) To: [email protected]; +Cc: Yugo Nagata <[email protected]> I prepared a small documentation follow-up patch adding a cross-reference to the planner statistics documentation section from the statistics manipulation warning. The patch builds cleanly with: `make -C doc/src/sgml html` I will send the patch to the mailing list thread as well. ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Can we use Statistics Import and Export feature to perforamance testing? 2025-04-08 07:09 Re: Can we use Statistics Import and Export feature to perforamance testing? David Rowley <[email protected]> 2025-04-08 12:14 ` RE: Can we use Statistics Import and Export feature to perforamance testing? Ryohei Takahashi (Fujitsu) <[email protected]> 2025-10-23 10:27 ` Re: Can we use Statistics Import and Export feature to perforamance testing? Yugo Nagata <[email protected]> 2025-10-24 00:00 ` RE: Can we use Statistics Import and Export feature to perforamance testing? Ryohei Takahashi (Fujitsu) <[email protected]> 2025-10-24 03:16 ` Re: Can we use Statistics Import and Export feature to perforamance testing? Yugo Nagata <[email protected]> 2026-05-27 05:54 ` Re: Can we use Statistics Import and Export feature to perforamance testing? vellaipandiyan sm <[email protected]> @ 2026-06-05 08:21 ` Yugo Nagata <[email protected]> 0 siblings, 0 replies; 7+ messages in thread From: Yugo Nagata @ 2026-06-05 08:21 UTC (permalink / raw) To: vellaipandiyan sm <[email protected]>; +Cc: [email protected] On Wed, 27 May 2026 05:54:06 +0000 vellaipandiyan sm <[email protected]> wrote: > I prepared a small documentation follow-up patch adding a cross-reference to the planner statistics documentation section from the statistics manipulation warning. > > The patch builds cleanly with: > > `make -C doc/src/sgml html` > > I will send the patch to the mailing list thread as well. I may have missed it, but I couldn't find the patch in the thread. Could you please point me to it? Regards, Yugo Nagata -- Yugo Nagata <[email protected]> ^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2026-06-05 08:21 UTC | newest] Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2025-04-08 07:09 Re: Can we use Statistics Import and Export feature to perforamance testing? David Rowley <[email protected]> 2025-04-08 12:14 ` Ryohei Takahashi (Fujitsu) <[email protected]> 2025-10-23 10:27 ` Yugo Nagata <[email protected]> 2025-10-24 00:00 ` Ryohei Takahashi (Fujitsu) <[email protected]> 2025-10-24 03:16 ` Yugo Nagata <[email protected]> 2026-05-27 05:54 ` vellaipandiyan sm <[email protected]> 2026-06-05 08:21 ` Yugo Nagata <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox