public inbox for [email protected]  

help / color / mirror / Atom feed

From: David Rowley <[email protected]>
To: Ryohei Takahashi (Fujitsu) <[email protected]>
Cc: [email protected] <[email protected]>
Subject: Re: Can we use Statistics Import and Export feature to perforamance testing?
Date: Tue, 8 Apr 2025 19:09:00 +1200
Message-ID: <CAApHDvozyyTc19zhnWSqMhvW_dqYJCCkdh5mo3DA65zEFPu8fg@mail.gmail.com> (raw)
In-Reply-To: <TY3PR01MB118914FCCBBDC126C34AA287382B52@TY3PR01MB11891.jpnprd01.prod.outlook.com>
References: <TY3PR01MB118914FCCBBDC126C34AA287382B52@TY3PR01MB11891.jpnprd01.prod.outlook.com>

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

view thread (7+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: Can we use Statistics Import and Export feature to perforamance testing?
  In-Reply-To: <CAApHDvozyyTc19zhnWSqMhvW_dqYJCCkdh5mo3DA65zEFPu8fg@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox