Subject: Can we use Statistics Import and Export feature to perforamance testing?

From: "Ryohei Takahashi (Fujitsu)" <[email-masked]>
Date: Tue, 8 Apr 2025 00:20:58 +0000

Hi,

I have a question about Statistics Import and Export.

* Background
I'm working for PGEcons[1], which is the PostgreSQL consortium in Japan.
Several companies participating in PGEcons have the following request for PostgreSQL.

They have two environments, production environment and staging environment.
Production environment has real customer's data and staging environment has dummy testing data.
When adding some application, they want to run the performance test on staging environment and
then apply to the production environment.
In the performance test, they want to use the same statistics as the production environment
to reduce the trouble in production environment.

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

[1] PGECons is a non profit organization comprised of companies
in Japan to promote PostgreSQL (https://www.pgecons.org).

Regards,
Ryohei Takahashi

---

From: "Ryohei Takahashi (Fujitsu)" <[email-masked]>
Date: Tue, 8 Apr 2025 12:14:08 +0000

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

---

From: Yugo Nagata <[email-masked]>
Date: Thu, 23 Oct 2025 19:27:53 +0900

Hi,

On Tue, 8 Apr 2025 12:14:08 +0000

> 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-masked]>
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>

---

From: "Ryohei Takahashi (Fujitsu)" <[email-masked]>
Date: Fri, 24 Oct 2025 00:00:42 +0000

Hi Nagata san,

Thank you.
Your patch is good for me.

I think this kind of documentation can prevent users confusing.

Regards,
Ryohei Takahashi

---

From: Yugo Nagata <[email-masked]>
Date: Fri, 24 Oct 2025 12:16:39 +0900

On Fri, 24 Oct 2025 00:00:42 +0000

> 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

---

From: vellaipandiyan sm <[email-masked]>
Date: Wed, 27 May 2026 05:54:06 +0000

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.

---

From: Yugo Nagata <[email-masked]>
Date: Fri, 5 Jun 2026 17:21:39 +0900

On Wed, 27 May 2026 05:54:06 +0000

> 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