From: Shinoda, Noriyoshi (SXD Japan FSIP) <noriyoshi.shinoda@hpe.com>
To: Corey Huinker <corey.huinker@gmail.com>
To: Jeff Davis <pgsql@j-davis.com>
Cc: jian he <jian.universality@gmail.com>
Cc: Matthias van de Meent <boekewurm+postgres@gmail.com>
Cc: Bruce Momjian <bruce@momjian.us>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Nathan Bossart <nathandbossart@gmail.com>
Cc: Magnus Hagander <magnus@hagander.net>
Cc: Stephen Frost <sfrost@snowman.net>
Cc: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>
Cc: Peter Smith <smithpb2250@gmail.com>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Cc: alvherre@alvh.no-ip.org <alvherre@alvh.no-ip.org>
Subject: RE: Statistics Import and Export
Date: Tue, 22 Oct 2024 00:50:56 +0000
Message-ID: <DM4PR84MB17346B43F24501D8F07CD721EE4C2@DM4PR84MB1734.NAMPRD84.PROD.OUTLOOK.COM> (raw)
In-Reply-To: <CADkLM=cBF8rnphuTyHFi3KYzB9ByDgx57HwK9Rz2yp7S+Om87w@mail.gmail.com>
References: <CACJufxHeRNu9EJM-XsfhuSo87_3oRaPMuPcqdsH_D9-W4dLJOg@mail.gmail.com>
<e0660898b696b0bb77030f8b850d662ad3c663d2.camel@j-davis.com>
<DM4PR84MB17345E2DFF28A5557B7CBC3CEE7A2@DM4PR84MB1734.NAMPRD84.PROD.OUTLOOK.COM>
<CADkLM=cmGmuocZzW1GRsQKSQ6hU4dnP=TYogkhU7ZPEUkTZU2A@mail.gmail.com>
<CADkLM=cb1g7FupStPeo2rZn5EDARUrjQWdfBAXHSckZU8Ppnng@mail.gmail.com>
<f4a0cf7975f1ad42a20fcc91be9e938a4f71259d.camel@j-davis.com>
<CADkLM=dJBPefnUDq40UQhaYTNTMuYbK=h63tCnQysm1j5_0Log@mail.gmail.com>
<CADkLM=fgMQDbXWX3JYuQNGw6ATda2goNp5gc1BUa=ebZMvbf6w@mail.gmail.com>
<CADkLM=fajh1Lpcyr_XsMmq-9Z=SGk-u+_Zeac7Pt0RAN3uiVCg@mail.gmail.com>
<CADkLM=e_21rwvKYp_99x=knZXWvDEOSoT4ODcBBLSixFaNu8-Q@mail.gmail.com>
<d9352fd5aa761462ee3ade2b24d0a6602abf7a75.camel@j-davis.com>
<CADkLM=fuZgyuMr=x9p9Bas7coVpSnezpzebU0Dc8HGe_5hodWQ@mail.gmail.com>
<CADkLM=cBF8rnphuTyHFi3KYzB9ByDgx57HwK9Rz2yp7S+Om87w@mail.gmail.com>
> 1. Allow relpages to be set to -1 (partitioned tables with partitions have this value after ANALYZE).
> 2. Turn off autovacuum on tables (where possible) if they are going to be the target of pg_set_relation_stats().
> 3. Allow pg_set_relation_stats to continue past an out-of-range detection on one attribute, rather than immediately returning false
Thank you for developing this feature.
If the relpages option contains -1 only for partitioned tables, shouldn't pg_set_relation_stats restrict the values that can be
specified by table type? The attached patch limits the value to -1 or more if the target
is a partition table, and 0 or more otherwise.
Changing relpages to -1 on a non-partitioned table seems to significantly change the execution plan.
---
postgres=> EXPLAIN (COSTS OFF) SELECT * FROM data1 WHERE c1=1000;
QUERY PLAN
--------------------------------------
Index Scan using data1_pkey on data1
Index Cond: (c1 = 1000)
(2 rows)
postgres=> SELECT pg_set_relation_stats('data1', relpages=>-1);
pg_set_relation_stats
-----------------------
t
(1 row)
postgres=> EXPLAIN (COSTS OFF) SELECT * FROM data1 WHERE c1=1000;
QUERY PLAN
---------------------------------------
Bitmap Heap Scan on data1
Recheck Cond: (c1 = 1000)
-> Bitmap Index Scan on data1_pkey
Index Cond: (c1 = 1000)
(4 rows)
---
Regards,
Noriyoshi Shinoda
From: Corey Huinker <corey.huinker@gmail.com>
Sent: Saturday, October 19, 2024 10:00 AM
To: Jeff Davis <pgsql@j-davis.com>
Cc: Shinoda, Noriyoshi (SXD Japan FSIP) <noriyoshi.shinoda@hpe.com>; jian he <jian.universality@gmail.com>; Matthias van de Meent <boekewurm+postgres@gmail.com>; Bruce Momjian <bruce@momjian.us>; Tom Lane <tgl@sss.pgh.pa.us>; Nathan Bossart <nathandbossart@gmail.com>; Magnus Hagander <magnus@hagander.net>; Stephen Frost <sfrost@snowman.net>; Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>; Peter Smith <smithpb2250@gmail.com>; PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>; alvherre@alvh.no-ip.org
Subject: Re: Statistics Import and Export
Patch that allows relation_statistics_update to continue after one failed stat (0001) attached, along with bool->void change (0002).
Once more, with feeling:
Attachments:pg_set_relation_stats_patch_v1.diff (application/octet-stream, 1.7K) downloaddiff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgmlindex ad663c94d7..bb610357c3 100644--- a/doc/src/sgml/func.sgml+++ b/doc/src/sgml/func.sgml@@ -30197,7 +30197,8 @@ DETAIL: Make sure pg_wal_replay_wait() isn't called within a transaction with a
</para>
<para>
The value of <structfield>relpages</structfield> must be greater than
- or equal to <literal>-1</literal>,+ or equal to <literal>-1</literal> for partitioned table otherwise+ greater then or equal to <literal>0</literal>,
<structfield>reltuples</structfield> must be greater than or equal to
<literal>-1.0</literal>, and <structfield>relallvisible</structfield>
must be greater than or equal to <literal>0</literal>.
diff --git a/src/backend/statistics/relation_stats.c b/src/backend/statistics/relation_stats.cindex 1a6d1640c3..5ad3368463 100644--- a/src/backend/statistics/relation_stats.c+++ b/src/backend/statistics/relation_stats.c@@ -98,17 +98,21 @@ relation_statistics_update(FunctionCallInfo fcinfo, int elevel)
if (!PG_ARGISNULL(RELPAGES_ARG))
{
int32 relpages = PG_GETARG_INT32(RELPAGES_ARG);
+ int32 minpages = 0;
/*
* Partitioned tables may have relpages=-1. Note: for relations with
* no storage, relpages=-1 is not used consistently, but must be
* supported here.
*/
- if (relpages < -1)+ if (pgcform->relkind == RELKIND_PARTITIONED_TABLE)+ minpages = -1;++ if (relpages < minpages)
{
ereport(elevel,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
- errmsg("relpages cannot be < -1")));+ errmsg("relpages cannot be < %d", minpages)));
table_close(crel, RowExclusiveLock);
return false;
}
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: pgsql-hackers@postgresql.org
Cc: noriyoshi.shinoda@hpe.com, corey.huinker@gmail.com, pgsql@j-davis.com, jian.universality@gmail.com, boekewurm+postgres@gmail.com, bruce@momjian.us, tgl@sss.pgh.pa.us, nathandbossart@gmail.com, magnus@hagander.net, sfrost@snowman.net, ashutosh.bapat.oss@gmail.com, smithpb2250@gmail.com, pgsql-hackers@lists.postgresql.org, alvherre@alvh.no-ip.org
Subject: RE: Statistics Import and Export
In-Reply-To: <DM4PR84MB17346B43F24501D8F07CD721EE4C2@DM4PR84MB1734.NAMPRD84.PROD.OUTLOOK.COM>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox