Date: Fri, 19 Jul 2024 21:33:35 +0200 (CEST)
From: Dimitrios Apostolou <jimis@gmx.net>
To: David Rowley <dgrowleyml@gmail.com>
cc: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>, 
    pgsql-general@lists.postgresql.org
Subject: Re: Query on partitioned table needs memory n_partitions *
 work_mem
In-Reply-To: <e5ae2d08-75e1-3726-8905-1328e86453cd@gmx.net>
Message-ID: <af6ed790-a5fe-19aa-1141-927595604c01@gmx.net>
References: <3603c380-d094-136e-e333-610914fb3e80@gmx.net> <CAApHDvp_u6SbyTKtk9m7ssh8H4dXMoLMZrAq8a1sJk1cj6YxzQ@mail.gmail.com> <d26e67d3-74bc-60aa-bf24-2a8fb83efe9c@gmx.net> <CAApHDvpG7Hxb81exMZGuwpTQLbeApMNZvffOmfJ3yJnKAYxqYA@mail.gmail.com>
 <e5ae2d08-75e1-3726-8905-1328e86453cd@gmx.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII; format=flowed
UI-OutboundReport: notjunk:1;M01:P0:7SlowexIHGY=;7ks/NcooWuDgPNAs/xyaCy91qb5
 amEUiAp9YNNkDURczkcXvMXHVvgN6Wsd0RUjPwfKrPcnDTchagW5kuQ1Rx2FPhcg1BXEh6MYQ
 fSqfQCUOO/0nmwnB8fTdp5/780ppAWGUvVVWqqZ6SCVAnNoTshTK3hufX4qxWvv+VZpDCOxo3
 UMPwu4dku9DeVS/sTjcSmEA7KIu+dZS6HZp4NpQ9Ax4osEe8N+Z14P4qcvwR86In+Jj00LTWi
 R36YBhlK1LDm9QV8Ejbq82ZZ8Lu1onwLDcSy+qmrCXwUDtoKG3gEcBECHLW/5qoJyZyk9bblX
 HqZNXWqfS8+jTazt4cAfEyP3gxDz1rGAf0zXGT7mY0fcjA7pk7yj6BgdNp3hcyf8PeJx+6xz8
 m1d/yZPNVBdDTvLijyyF1t5yY4AVXTxq76eBhzE2qnSwJlFI5zUew7EPwMfevO37YU94BYX4g
 CHiQNkXQ6gE4FpzN1lPSlUSeESTavWVBUXfU4XstMbFkPq9niL54uPITSYpmIdIF/Z6XYiFvV
 O4ZysXVdvjYZdxZKhNRUGCgNPX9AZojZmci1IrQxTP5OgVJApnrUMethCBLewIop8IoAywZEY
 +A9Ir3QB9N0NY+Qz9K4McW/XdTnMd0aicoj/g77Xz832vBGt7ngQQi+V8+7zj6aSbM0h7xzGk
 VCkJRAmQQ/a8d+8X4RYvlYw570A0R/vt4xTStvGJKW66JB+B1qO9XP4y7K132SwOl0te/x6OW
 A8SC6F/4z6SE0KXXFp2oo2AeXd8aRilpy0MiXweO0FGarvlwJf+HntcYBnxvfVhoI8lnbkfYa
 E2/gH8k3vUliz7p2aMNUglZw==
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/af6ed790-a5fe-19aa-1141-927595604c01%40gmx.net>
Precedence: bulk

Ok I reproduced the OOM, with only 200 partitions as opposed to 2K that I
mentioned before. The keys to reproduce it I believe are:

1. Write millions of rows to *all* partitions
2. Run ANALYSE so that the planner knows about the sizes


Here are the correct steps now. Let me know if you manage to reproduce.
Kind reminder to set vm.overcommit_memory to 2, before you lose your
system like it happened to me. :-)


CREATE TABLE   partitioned_table1(
     run_n       bigint   GENERATED ALWAYS AS IDENTITY,
     workitem_n  integer  NOT NULL,
     label_n     smallint,
     result      smallint NOT NULL,
     PRIMARY KEY(workitem_n, run_n)
) PARTITION BY RANGE(workitem_n);

=2D- Create 200 partitions

DO $$
for i in range(0, 200):
     start =3D i     * 10 * 1000 * 1000
     end   =3D (i+1) * 10 * 1000 * 1000
     stmt =3D f'''
         CREATE TABLE part{i}
             PARTITION OF partitioned_table1
             FOR VALUES FROM ({start}) TO ({end})
     '''
     plpy.execute(stmt)
$$ LANGUAGE plpython3u;

=2D- Insert 20M rows per partition. This will take a while but I don't kno=
w
=2D- of a way to speed it up.

DO $$
for i in range(0, 2000):
     stmt =3D f'''
         INSERT INTO partitioned_table1(workitem_n, label_n, result)
             SELECT
                     j,
                     CAST(random()*1000 AS INTEGER),
                     CAST(random()*4    AS INTEGER)
                 FROM generate_series({i}*1000*1000, ({i}+1)*1000*1000 - 1=
, 1) as j
     '''
     plpy.info(stmt)
     plpy.execute(stmt)
     plpy.commit()
$$ LANGUAGE plpython3u;

=2D- Important to analyse! and set the right settings...

ANALYSE partitioned_table1;
SET SESSION max_parallel_workers_per_gather TO 0;
SET SESSION enable_incremental_sort         TO off;
SET SESSION enable_partitionwise_aggregate  TO on;
SET SESSION enable_partitionwise_join       TO on;

=2D- And then reproduce the issue:

\timing on
SET SESSION work_mem TO '4MB';

SELECT
       workitem_n, label_n, bool_or(result IN (2,3))
FROM
       partitioned_table1
GROUP BY
       workitem_n, label_n
LIMIT 10;


Repeat that last query while doubling the work_mem every time. You'll see
the process growing to immense dimensions. In my case, my 16GB of RAM were
exhausted when work mem was set to '128MB'.

Remarkably, the fastest execution is the one with the smallest work_mem,
where HashAggregate splits the work into many "partitions". As we grow the
work_mem, it becomes slower and slower (of course while not reaching the
limits of RAM yet; if it goes to swap then all measurements are off).

Let me know if you manage to reproduce the issue!

Dimitris