public inbox for [email protected]  

help / color / mirror / Atom feed

From: Tom Lane <[email protected]>
To: Andres Freund <[email protected]>
Cc: Jeff Davis <[email protected]>
Cc: Corey Huinker <[email protected]>
Cc: Michael Paquier <[email protected]>
Cc: jian he <[email protected]>
Cc: Nathan Bossart <[email protected]>
Cc: Bruce Momjian <[email protected]>
Cc: Matthias van de Meent <[email protected]>
Cc: Magnus Hagander <[email protected]>
Cc: Stephen Frost <[email protected]>
Cc: Ashutosh Bapat <[email protected]>
Cc: Peter Smith <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Cc: [email protected]
Subject: Re: Statistics Import and Export
Date: Fri, 21 Feb 2025 16:24:38 -0500
Message-ID: <[email protected]> (raw)
In-Reply-To: <x5yo5ly5u3dhzfaq3hrzeqhju3io5zmwk4yqrkge3ywcflffck@ghbbgsfv56yb>
References: <CADkLM=fihosVw7fJak6Jq1AdCX8fuZ+7s1_-+AUZR=sdD13zAQ@mail.gmail.com>
	<CADkLM=cnYxaNFbAQRMLeOOga0j5pY_V6Tfcn+vjm_rXL=WOF7Q@mail.gmail.com>
	<[email protected]>
	<[email protected]>
	<CADkLM=fzX7QX6r78fShWDjNN3Vcr4PVAnvXxQ4DiGy6V=0bCUA@mail.gmail.com>
	<[email protected]>
	<CADkLM=cALMHZkXU+xZw9-WeR4XSXzDYj_-xERHg8haYq1zbjhg@mail.gmail.com>
	<[email protected]>
	<[email protected]>
	<sdno5atrplq5b67vcx7x6fkzevwkzojd23izbguy2yma424wmp@siwbi3ijxdxs>
	<x5yo5ly5u3dhzfaq3hrzeqhju3io5zmwk4yqrkge3ywcflffck@ghbbgsfv56yb>

Andres Freund <[email protected]> writes:
> Looking at the query log, the biggest culprit is a *lot* of additional
> queries, I think primarily these two:

> SELECT c.oid::regclass AS relation, current_setting('server_version_num') AS version, c.relpages, c.reltuples, c.relallvisible FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace WHERE n.nspname = 'public' AND c.relname = 'alpha_neg_p2'

> SELECT c.oid::regclass AS relation, s.attname,s.inherited,current_setting('server_version_num') AS version, s.null_frac,s.avg_width,s.n_distinct,s.most_common_vals,s.most_common_freqs,s.histogram_bounds,s.correlation,s.most_common_elems,s.most_common_elem_freqs,s.elem_count_histogram,s.range_length_histogram,s.range_empty_frac,s.range_bounds_histogram FROM pg_stats s JOIN pg_namespace n ON n.nspname = s.schemaname JOIN pg_class c ON c.relname = s.tablename AND c.relnamespace = n.oid WHERE s.schemaname = 'public' AND s.tablename = 'alpha_neg_p2' ORDER BY s.attname, s.inherited

Oy.  Those are outright horrid, even without any consideration of
pre-preparing them.  We know the OID of the table we want to dump,
we should be doing "FROM pg_class WHERE oid = whatever" and lose
the join to pg_namespace altogether.  The explicit casts to regclass
are quite expensive too to fetch information that pg_dump already
has.  It already knows the server version, too.

Moreover, the first of these shouldn't be a separate query at all.
I objected to fetching pg_statistic content for all tables at once,
but relpages/reltuples/relallvisible is a pretty small amount of
new info.  We should just collect those fields as part of getTables'
main query of pg_class (which, indeed, is already fetching relpages).

On the second one, if we want to go through the pg_stats view then
we can't rely on table OID, but I don't see why we need the joins
to anything else.  "WHERE s.schemaname = 'x' AND s.tablename = 'y'"
seems sufficient.

I wonder whether we ought to issue different queries depending on
whether we're superuser.  The pg_stats view is rather expensive
because of its security restrictions, and if we're superuser we
could just look directly at pg_statistic.  Maybe those checks are
fast enough not to matter, but ...

			regards, tom lane

view thread (60+ 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], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
  Subject: Re: Statistics Import and Export
  In-Reply-To: <[email protected]>

* 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