Date: Fri, 08 Apr 2022 09:09:34 -0700
From: Andres Freund <andres@anarazel.de>
To: Matthias van de Meent <boekewurm+postgres@gmail.com>
CC: Nitin Jadhav <nitinjadhavpostgres@gmail.com>,
 Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>,
 Ashutosh Sharma <ashu.coek88@gmail.com>, Julien Rouhaud <rjuju123@gmail.com>,
 Bruce Momjian <bruce@momjian.us>, Tom Lane <tgl@sss.pgh.pa.us>,
 Magnus Hagander <magnus@hagander.net>,
 PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Subject: =?US-ASCII?Q?Re=3A_Size_of_pg=5Frewrite_=28Was=3A_Report_checkpoi?= =?US-ASCII?Q?nt_progress_with_pg=5Fstat=5Fprogress=5Fcheckpoint=29?=
User-Agent: K-9 Mail for Android
In-Reply-To: <CAEze2WgGexDM63dOvndLdAWwA6uSmSsc97jmrCuNmrF1JEDK7w@mail.gmail.com>
References: <CAMm1aWZ07VYUPpvmV9Ck_VX-o+xH=hEBs-67scd_Us6U6FKbHA@mail.gmail.com> <CAE9k0Png9tjW5M7wjMKzEoOqkHS-VToP9yC2pr=HX7aQ0yQRNA@mail.gmail.com> <CAMm1aWa8p=o60y1Z71bjAjWH8XH2Y+kZ6cNjQ21uK2_i65soOQ@mail.gmail.com> <CALj2ACUNnqY7oc5C5yGS1ZbHV1RtQgPGiMkPTm989czCDwag6g@mail.gmail.com> <CAEze2Wh_+P9Hs4RQ=tc=70rdB5h0omGx5Kz-BLhsJPRnKQ+cWw@mail.gmail.com> <CAMm1aWbuJLEnAjT4_1s8eArmZq=H7oi0Jv=J2GnRpBhkbC_PVw@mail.gmail.com> <CAMm1aWYWmqys2O7UekQ0=ZSWSpG9h2qoEW7w46yiWM0eO1Lu7g@mail.gmail.com> <CALj2ACXsmvEDsHtADoQeDE-NVyBh4A-D80NmNanTUc37g=FQ-Q@mail.gmail.com> <CAMm1aWabDx3V9Hw5L2eB+6EOtr60QWDybZShQsYLCCMNF-es_A@mail.gmail.com> <CAMm1aWaFmbc8QCbcPkG7ROV7gjshamS=sJRJd=oghLJM9uOnpQ@mail.gmail.com> <20220319001556.atuqbmafvc73pa2k@alap3.anarazel.de> <CAEze2WgGexDM63dOvndLdAWwA6uSmSsc97jmrCuNmrF1JEDK7w@mail.gmail.com>
Message-ID: <B6808F91-6F4D-4C75-A112-C0CDD721C849@anarazel.de>
MIME-Version: 1.0
Content-Type: text/plain;
 charset=utf-8
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/B6808F91-6F4D-4C75-A112-C0CDD721C849%40anarazel.de>
Precedence: bulk

Hi,=20

On April 8, 2022 7:52:07 AM PDT, Matthias van de Meent <boekewurm+postgres=
@gmail=2Ecom> wrote:
>On Sat, 19 Mar 2022 at 01:15, Andres Freund <andres@anarazel=2Ede> wrote:
>> pg_rewrite without pg_stat_progress_checkpoint: 745472, with: 753664
>>
>> pg_rewrite is the second biggest relation in an empty database already=
=2E=2E=2E
>
>Yeah, that's not great=2E Thanks for nerd-sniping me into looking into
>how views and pg_rewrite rules work, that was very interesting and I
>learned quite a lot=2E

Thanks for looking!


># Immediately potential, limited to progress views
>
>I noticed that the CASE-WHEN (used in translating progress stage index
>to stage names) in those progress reporting views can be more
>efficiently described (althoug with slightly worse behaviour around
>undefined values) using text array lookups (as attached)=2E That
>resulted in somewhat smaller rewrite entries for the progress views
>(toast compression was good old pglz):
>
>template1=3D# SELECT sum(octet_length(ev_action)),
>SUM(pg_column_size(ev_action)) FROM pg_rewrite WHERE
>ev_class::regclass::text LIKE '%progress%';
>
>master:
>  sum  |  sum
>-------+-------
> 97277 | 19956
>patched:
>  sum  |  sum
>-------+-------
> 77069 | 18417
>
>So this seems like a nice improvement of 20% uncompressed / 7% compressed=
=2E
>
>I tested various cases of phase number to text translations: `CASE =2E=2E
>WHEN`; `(ARRAY[]::text[])[index]` and `('{}'::text[])[index]`=2E See
>results below:
>
>postgres=3D# create or replace view arrayliteral_view as select
>(ARRAY['a','b','c','d','e','f']::text[])[index] as name from tst
>s(index);
>CREATE INDEX
>postgres=3D# create or replace view stringcast_view as select
>('{a,b,c,d,e,f}'::text[])[index] as name from tst s(index);
>CREATE INDEX
>postgres=3D# create or replace view split_stringcast_view as select
>(('{a,b,' || 'c,d,e,f}')::text[])[index] as name from tst s(index);
>CREATE VIEW
>postgres=3D# create or replace view case_view as select case index when
>0 then 'a' when 1 then 'b' when 2 then 'c' when 3 then 'd' when 4 then
>'e' when 5 then 'f' end as name from tst s(index);
>CREATE INDEX
>
>
>postgres=3D# postgres=3D# select ev_class::regclass::text,
>octet_length(ev_action), pg_column_size(ev_action) from pg_rewrite
>where ev_class in ('arrayliteral_view'::regclass::oid,
>'case_view'::regclass::oid, 'split_stringcast_view'::regclass::oid,
>'stringcast_view'::regclass::oid);
>       ev_class        | octet_length | pg_column_size
>-----------------------+--------------+----------------
> arrayliteral_view     |         3311 |           1322
> stringcast_view       |         2610 |           1257
> case_view             |         5170 |           1412
> split_stringcast_view |         2847 |           1350
>
>It seems to me that we could consider replacing the CASE statements
>with array literals and lookups if we really value our template
>database size=2E But, as text literal concatenations don't seem to get
>constant folded before storing them in the rules table, this rewrite
>of the views would result in long lines in the system_views=2Esql file,
>or we'd have to deal with the additional overhead of the append
>operator and cast nodes=2E

My inclination is that the mapping functions should be c functions=2E Ther=
e's really no point in doing it in SQL and it comes at a noticable price=2E=
 And, if done in C, we can fix mistakes in minor releases, which we can't i=
n SQL=2E


># Future work; nodeToString / readNode, all rewrite rules
>
>Additionally, we might want to consider other changes like default (or
>empty value) elision in nodeToString, if that is considered a
>reasonable option and if we really want to reduce the size of the
>pg_rewrite table=2E
>
>I think a lot of space can be recovered from that: A manual removal of
>what seemed to be fields with default values (and the removal of all
>query location related fields) in the current definition of
>pg_stat_progress_create_index reduces its uncompressed size from
>23226B raw and 4204B compressed to 13821B raw and 2784B compressed,
>for an on-disk space saving of 33% for this view's ev_action=2E
>
>Do note, however, that that would add significant branching in the
>nodeToString and readNode code, which might slow down that code
>significantly=2E I'm not planning on working on that; but in my opinion
>that is a viable path to reducing the size of new database catalogs=2E

We should definitely be careful about that=2E I do agree that there's a lo=
t of efficiency to be gained in the serialization format=2E Once we have th=
e automatic node func generation in place, we could have one representation=
 for human consumption, and one for density=2E=2E=2E

Andres
--=20
Sent from my Android device with K-9 Mail=2E Please excuse my brevity=2E