Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tk7ZQ-000EbS-Tj for pgsql-performance@arkaria.postgresql.org; Mon, 17 Feb 2025 20:18:25 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tk7ZP-002iAj-Mv for pgsql-performance@arkaria.postgresql.org; Mon, 17 Feb 2025 20:18:23 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tk7ZP-002i9U-8w for pgsql-performance@lists.postgresql.org; Mon, 17 Feb 2025 20:18:23 +0000 Received: from mail-ot1-x336.google.com ([2607:f8b0:4864:20::336]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tk7ZM-001Q2w-29 for pgsql-performance@lists.postgresql.org; Mon, 17 Feb 2025 20:18:22 +0000 Received: by mail-ot1-x336.google.com with SMTP id 46e09a7af769-71e10e6a1ceso1444281a34.0 for ; Mon, 17 Feb 2025 12:18:20 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1739823499; x=1740428299; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=GD1wtbk6N5tx0HFtYll2IJV2arJNQyN3UCAyPZ/RLBo=; b=D//IzC/slqHp08eq3g+Se0XjLNkruNovlqCmcqZjU3UY3G7Q93GYrgERYXzAIWgLSp FfwNrygPLKYzm+sf1/9QedRqAqKSgtXdDYl9KjwStv8NCJfSmSxXwdxQNBBJCcxsB61A /a5YgjJkdR1sHLXpICgD9DeleBC57auuJs4x1R/zzLC+IuA6cEznCfRItlPPTNKvgL4E CoyHPb52dRjuH0elVuPFkhGaz2bs5Gehprds0Ar8e1APcikJhPpXRKSmBQV3GHHLxK3q cC/MpQrw5tIFtoO98OUWytQ4fIwBWQHsKmTgDfnZryzO9VyapuzINOfNb0/oKKuQdW16 XmMA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1739823499; x=1740428299; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=GD1wtbk6N5tx0HFtYll2IJV2arJNQyN3UCAyPZ/RLBo=; b=WSNP28CuBI5cFkwLu05zH86h1XOjgqSR8yGrpvlv/mCcf1iM9frT3zVEIzORgt4yNa MVNjwmBOFkxrxWQnoABP0U6UOf6NogiAyDEdCGRDOsx3o0CUPSiioWRtQ9Vo+FUgs1SN E7l1ZY+Ld70lrpyI+K6UmkoLGIkgO1UrRlkouRRGHnDhdLE3lOwVBEtT/uGfQZhKZRhJ 3o8SGMKriUN5PsrfWMeis/hVu/WrOlNQAlleTcLj0RP6PHUzSw0h7ld4p4VfPBHBbV6u 1cJVqa1wghgvXWNtE5FxYchLoRliFvXJDZFOIFMg/lMIFYBWYcQO6JTbVBYySWq7Knmr 1vjA== X-Gm-Message-State: AOJu0YwVNeFalDKCQRWwOrZkh9EZIf5VZdjdoFco6MObtti38iZ7pweB mYbpdewh+gTFOujStyebEaHAotKjtuisouGUDuLGAlTaBQrOGQhW7fK+rB+Z/wLpudi++D9Dsld 050fwK5AlYhsuHf5TQzylhOIH+ZxC1O5p X-Gm-Gg: ASbGncuDj8PWQB2OEK3/1DDNhL2AEamjK3nS37SvEcV2Q/bu5BSBG7wM8/gJD9ktcBJ arRpWwy7yyt1ckQNyjBOduWjUrt5I3X6BkxUf00rtczhuoWH1uVJS92UVieuJcldXk60sSSlP1/ U= X-Google-Smtp-Source: AGHT+IHFJrioGhqPQr0nbEuWb1QLC6bSL9B21bXi98/iJryjpZl0692f5lJqkWhrYRkKiyPOWZUsuYlVal7JHvi2dEg= X-Received: by 2002:a05:6808:318a:b0:3f4:7a9:7bb9 with SMTP id 5614622812f47-3f407a97e8bmr488859b6e.11.1739823497092; Mon, 17 Feb 2025 12:18:17 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: bruno vieira da silva Date: Mon, 17 Feb 2025 15:18:05 -0500 X-Gm-Features: AWEUYZklC9sjmT9qfzbqLW62d99CkEnyVsi47JhPXm11Y1J39YuwAb-q7CF2DlM Message-ID: Subject: Re: Query planning read a large amount of buffers for partitioned tables To: David Rowley Cc: pgsql-performance@lists.postgresql.org Content-Type: multipart/alternative; boundary="00000000000084274c062e5c39b0" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000084274c062e5c39b0 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Hello, I did a more comprehensive test with a different number of partitions and I found this: Summary buffers usage for the first call vs second call on the same session= . Query 200, 100, 50, and 10 partitions: 200 Partitions: 12,828 (100MB) 100 Partitions: 9,329 (72MB) 50 Partitions: 3,305 (25MB) 10 Partitions: 875 (7MB) Same query on the same session: 200 Partitions: 205 (1.6MB) 100 Partitions: 5 (40KB) 50 Partitions: 5 (40KB) 10 Partitions: 5 (40KB) I did test on PG 17.3 no relevant changes. Question is, does it make sense? *these are the steps to reproduce it:* docker pull postgres:17.2 docker run -itd -e POSTGRES_USER=3Dbruno -e POSTGRES_PASSWORD=3Dbruno -p 5500:5432 -v /home/bruno/pgdata17:/var/lib/postgresql/data --name postgresql postgres:17.2 export PGHOST=3D"localhost" export PGPORT=3D5500 export PGDATABASE=3D"postgres" export PGUSER=3D"bruno" export PGPASSWORD=3D"bruno" CREATE EXTENSION IF NOT EXISTS "pgcrypto"; -- Enables the gen_random_uuid function CREATE TABLE dicom_series ( series_uid UUID DEFAULT gen_random_uuid(), series_description VARCHAR(255), modality VARCHAR(16), body_part_examined VARCHAR(64), patient_id VARCHAR(64), study_uid UUID DEFAULT gen_random_uuid(), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); -- Create the parent table CREATE TABLE dicom_sops_100_part ( sop_uid UUID NOT NULL, series_uid UUID NOT NULL, instance_number INT, image_position_patient TEXT, image_orientation_patient TEXT, slice_thickness DECIMAL(10, 2), slice_location DECIMAL(10, 2), pixel_spacing TEXT, rows INT, columns INT, acquisition_date DATE, acquisition_time TIME, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) PARTITION BY HASH (sop_uid); -- Create 100 partitions DO $$ DECLARE partition_number INT; BEGIN FOR partition_number IN 0..99 LOOP EXECUTE format( 'CREATE TABLE dicom_sops_100_p%1$s PARTITION OF dicom_sops_100_part FOR VALUES WITH (MODULUS 100, REMAINDER %1$s);', partition_number ); END LOOP; END $$; *Data population:* DO $$ DECLARE series_count INT :=3D 1000000; -- Number of series to create sops_per_series INT :=3D 20; i INT; j INT; series_id UUID; sop_id UUID; BEGIN FOR i IN 1..series_count LOOP -- Insert into dicom_series table with a generated UUID INSERT INTO dicom_series ( series_description, modality, body_part_examined, patient_id ) VALUES ( 'Series Description ' || i, 'CT', 'Chest', 'PATIENT-' || i ) RETURNING series_uid INTO series_id; FOR j IN 1..sops_per_series LOOP -- Insert into dicom_sops_200_part table with a generated UUID INSERT INTO dicom_sops_100_part ( sop_uid, series_uid, instance_number, image_position_patient, image_orientation_patient, slice_thickness, slice_location, pixel_spacing, rows, columns, acquisition_date, acquisition_time ) VALUES ( gen_random_uuid(), series_id, j, '(0.0, 0.0, ' || j || ')', '(1.0, 0.0, 0.0, 0.0, 1.0, 0.0)', 1.0, j * 5.0, '1.0\\1.0', 512, 512, CURRENT_DATE, CURRENT_TIME ); END LOOP; END LOOP; END $$; *Add indexes and vacuum analyze:* CREATE UNIQUE INDEX idx_series_uid ON dicom_series(series_uid); CREATE INDEX dicom_sops_100_part_sop_uid_idx ON dicom_sops_100_part(sop_uid); CREATE INDEX dicom_sops_100_part_series_uid_idx ON dicom_sops_100_part(series_uid); vacuum freeze; analyze; *Testing:* disconnect and reconnect to the db with psql. Query used for test: drop table temp_series_id;CREATE TEMPORARY TABLE temp_series_id AS select series_uid from dicom_series order by random() limit 1; analyze temp_series_id; explain (analyze,buffers) select * from dicom_sops_100_part where series_uid =3D (select series_uid from temp_series_id); Query plan: QUERY PLAN ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ------------------------------- Append (cost=3D1.43..423.26 rows=3D50 width=3D128) (actual time=3D2.565..= 27.216 rows=3D20 loops=3D1) Buffers: shared hit=3D50 read=3D118, local hit=3D1 InitPlan 1 -> Seq Scan on temp_series_id (cost=3D0.00..1.01 rows=3D1 width=3D16= ) (actual time=3D0.006..0.007 rows=3D1 loops=3D1) Buffers: local hit=3D1 -> Index Scan using dicom_sops_100_p0_series_uid_idx on dicom_sops_100_p0 dicom_sops_100_part_1 (cost=3D0.42..8.44 rows=3D1 width= =3D128) (actual time=3D0.846..0.846 rows=3D0 loops=3D1) Index Cond: (series_uid =3D (InitPlan 1).col1) .... -> Index Scan using dicom_sops_100_p49_series_uid_idx on dicom_sops_100_p49 dicom_sops_100_part_50 (cost=3D0.42..8.44 rows=3D1 width=3D128) (actual time=3D0.302..0.303 rows=3D0 loops=3D1) Index Cond: (series_uid =3D (InitPlan 1).col1) Buffers: shared hit=3D1 read=3D2 Planning: Buffers: shared hit=3D4180 Planning Time: 4.941 ms Execution Time: 27.682 ms (159 rows) Second query on the same session: QUERY PLAN ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ------------------------------- Append (cost=3D1.43..423.26 rows=3D50 width=3D128) (actual time=3D9.759..= 9.770 rows=3D0 loops=3D1) Buffers: shared hit=3D100 read=3D50, local hit=3D1 InitPlan 1 -> Seq Scan on temp_series_id (cost=3D0.00..1.01 rows=3D1 width=3D16= ) (actual time=3D0.003..0.004 rows=3D1 loops=3D1) Buffers: local hit=3D1 -> Index Scan using dicom_sops_100_p0_series_uid_idx on dicom_sops_100_p0 dicom_sops_100_part_1 (cost=3D0.42..8.44 rows=3D1 width= =3D128) (actual time=3D0.212..0.213 rows=3D0 loops=3D1) Index Cond: (series_uid =3D (InitPlan 1).col1) ... -> Index Scan using dicom_sops_100_p49_series_uid_idx on dicom_sops_100_p49 dicom_sops_100_part_50 (cost=3D0.42..8.44 rows=3D1 width=3D128) (actual time=3D0.236..0.236 rows=3D0 loops=3D1) Index Cond: (series_uid =3D (InitPlan 1).col1) Buffers: shared hit=3D2 read=3D1 Planning: Buffers: shared hit=3D5 Planning Time: 0.604 ms Execution Time: 10.011 ms (159 rows) On Thu, Jan 16, 2025 at 9:56=E2=80=AFAM bruno vieira da silva wrote: > Hello, Thanks David. > > this pg test deployment. anyways I did a vacuum full on the db. and the > number of buffers read increased a bit. > > > On Wed, Jan 15, 2025 at 3:01=E2=80=AFPM David Rowley wrote: > >> On Thu, 16 Jan 2025 at 07:29, bruno vieira da silva >> wrote: >> > On pg 17 now we have better visibility on the I/O required during quer= y >> planning. >> > so, as part of an ongoing design work for table partitioning I was >> analyzing the performance implications of having more or less partitions= . >> > In one of my tests of a table with 200 partitions using explain showed >> a large amount of buffers read during planning. around 12k buffers. >> >> That's a suspiciously high number of buffers. >> >> > I observed that query planning seems to have a caching mechanism as >> subsequent similar queries require only a fraction of buffers read durin= g >> query planning. >> > However, this "caching" seems to be per session as if I end the client >> session and I reconnect the same query execution will require again to r= ead >> 12k buffer for query planning. >> > >> > Does pg have any mechanism to mitigate this issue ( new sessions need >> to read a large amount of buffers for query planning) ? or should I >> mitigate this issue by the use of connection pooling. >> > How is this caching done? Is there a way to have viability on its >> usage? Where is it stored? >> >> The caching is for relation meta-data and for various catalogue data. >> This is stored in local session hash tables. The caching is done >> lazily the first time something is looked up after the session starts. >> If you're doing very little work before ending the session, then >> you'll pay this overhead much more often than you would if you were to >> do more work in each session. A connection pooler would help you do >> that, otherwise it would need to be a redesign of how you're >> connecting to Postgres from your application. >> >> There's no easy way from EXPLAIN to see which tables or catalogue >> tables the IO is occurring on, however, you might want to try looking >> at pg_statio_all_tables directly before and after the query that's >> causing the 12k buffer accesses and then look at what's changed. >> >> I suspect if you're accessing 12k buffers to run EXPLAIN that you have >> some auto-vacuum starvation issues. Is auto-vacuum enabled and >> running? If you look at pg_stat_activity, do you see autovacuum >> running? It's possible that it's running but not configured to run >> quickly enough to keep up with demand. Alternatively, it may be >> keeping up now, but at some point in the past, it might not have been >> and you have some bloat either in an index or in a catalogue table as >> a result. >> >> David >> > > > -- > Bruno Vieira da Silva > --=20 Bruno Vieira da Silva --00000000000084274c062e5c39b0 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hello, I did a more comprehensive test with a different nu= mber of partitions and I found this:

Summary buffers usa= ge for the first call vs second call on the same session.

Query 200,= 100, 50, and 10 partitions:
200 Partitions: 12,828 (100MB)
100 Parti= tions: =C2=A09,329 (72MB)
=C2=A050 Partitions: =C2=A03,305 (25MB)
=C2= =A010 Partitions: =C2=A0 =C2=A0875 (7MB)

Same query on the same ses= sion:
200 Partitions: =C2=A0 =C2=A0205 (1.6MB)
100 Partitions: =C2=A0= =C2=A0 =C2=A05 (40KB)
50 =C2=A0Partitions: =C2=A0 =C2=A0 =C2=A05 (40KB)=
10 =C2=A0Partitions: =C2=A0 =C2=A0 =C2=A05 (40KB)

<= div>I did test on PG 17.3 no relevant changes.=C2=A0=C2=A0

Question is, does it make sense?=C2=A0

these are the steps to reproduce it:

docker p= ull postgres:17.2
docker run -itd -e POSTGRES_USER=3Dbruno -e POSTGRES_P= ASSWORD=3Dbruno -p 5500:5432 -v /home/bruno/pgdata17:/var/lib/postgresql/da= ta --name postgresql postgres:17.2
export PGHOST=3D"localhost"=
export PGPORT=3D5500
export PGDATABASE=3D"postgres"
exp= ort PGUSER=3D"bruno"
export PGPASSWORD=3D"bruno"

CREATE EXTENSION IF NOT EXISTS "pgcrypto"; = -- Enables the gen_random_uuid function

CREATE TABLE dicom_series (<= br>=C2=A0 =C2=A0 series_uid UUID DEFAULT gen_random_uuid(),
=C2=A0 =C2= =A0 series_description VARCHAR(255),
=C2=A0 =C2=A0 modality VARCHAR(16),=
=C2=A0 =C2=A0 body_part_examined VARCHAR(64),
=C2=A0 =C2=A0 patient_= id VARCHAR(64),
=C2=A0 =C2=A0 study_uid UUID DEFAULT gen_random_uuid(),<= br>=C2=A0 =C2=A0 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Create the parent table
CREATE TABLE dicom_sops_100_part (
=C2= =A0 =C2=A0 sop_uid UUID NOT NULL,
=C2=A0 =C2=A0 series_uid UUID NOT NULL= ,
=C2=A0 =C2=A0 instance_number INT,
=C2=A0 =C2=A0 image_position_pat= ient TEXT,
=C2=A0 =C2=A0 image_orientation_patient TEXT,
=C2=A0 =C2= =A0 slice_thickness DECIMAL(10, 2),
=C2=A0 =C2=A0 slice_location DECIMAL= (10, 2),
=C2=A0 =C2=A0 pixel_spacing TEXT,
=C2=A0 =C2=A0 rows INT,=C2=A0 =C2=A0 columns INT, =C2=A0 =C2=A0
=C2=A0 =C2=A0 acquisition_date= DATE,
=C2=A0 =C2=A0 acquisition_time TIME,
=C2=A0 =C2=A0 created_at = TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) PARTITION BY HASH (sop_uid);
-- Create 100 partitions
DO $$
DECLARE
=C2=A0 =C2=A0 partition_n= umber INT;
BEGIN
=C2=A0 =C2=A0 FOR partition_number IN 0..99 LOOP
= =C2=A0 =C2=A0 =C2=A0 =C2=A0 EXECUTE format(
=C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 'CREATE TABLE dicom_sops_100_p%1$s PARTITION OF dicom_sop= s_100_part FOR VALUES WITH (MODULUS 100, REMAINDER %1$s);',
=C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 partition_number
=C2=A0 =C2=A0 =C2=A0= =C2=A0 );
=C2=A0 =C2=A0 END LOOP;
END $$;

<= b>Data population:

DO $$
DECLARE
=C2=A0 = =C2=A0 series_count INT :=3D 1000000; -- Number of series to create
=C2= =A0 =C2=A0 sops_per_series INT :=3D 20;
=C2=A0 =C2=A0 i INT;
=C2=A0 = =C2=A0 j INT;
=C2=A0 =C2=A0 series_id UUID;
=C2=A0 =C2=A0 sop_id UUID= ;
BEGIN
=C2=A0 =C2=A0 FOR i IN 1..series_count LOOP
=C2=A0 =C2=A0 = =C2=A0 =C2=A0 -- Insert into dicom_series table with a generated UUID
= =C2=A0 =C2=A0 =C2=A0 =C2=A0 INSERT INTO dicom_series (
=C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 series_description,
=C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 modality,
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 body_= part_examined,
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 patient_id
= =C2=A0 =C2=A0 =C2=A0 =C2=A0 ) VALUES (
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 'Series Description ' || i,
=C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 'CT',
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 'Chest',
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 'PATI= ENT-' || i
=C2=A0 =C2=A0 =C2=A0 =C2=A0 )
=C2=A0 =C2=A0 =C2=A0 =C2= =A0 RETURNING series_uid INTO series_id;

=C2=A0 =C2=A0 =C2=A0 =C2=A0= FOR j IN 1..sops_per_series LOOP
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 -- Insert into dicom_sops_200_part table with a generated UUID
=C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 INSERT INTO dicom_sops_100_part (=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 sop_uid, =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0
=C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 series_uid,
=C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 instance_number,
=C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 image_position_patient,
=C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 image_orientation_patient,
= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 slice_thickness,=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 slice_location,=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 pixel_spacing,
= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 rows,
=C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 columns,
=C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 acquisition_date,
=C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 acquisition_time
=C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 ) VALUES (
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 gen_random_uuid(),
=C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 series_id,
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 j,
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 '(0.0, 0.0, ' || j || ')',
=C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 '(1.0, 0.0, 0.0, 0.0, 1.0, 0.= 0)',
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 1.0,=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 j * 5.0,
=C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 '1.0\\1.0',
= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 512,
=C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 512,
=C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 CURRENT_DATE,
=C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 CURRENT_TIME
=C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 );
=C2=A0 =C2=A0 =C2=A0 =C2=A0 END LOOP;
=C2=A0 =C2= =A0 END LOOP;
END $$;

Add indexes and vacuum= analyze:

CREATE UNIQUE INDEX idx_series_uid O= N dicom_series(series_uid);
CREATE INDEX dicom_sops_100_part_sop_uid_idx= ON dicom_sops_100_part(sop_uid);
CREATE INDEX dicom_sops_100_part_serie= s_uid_idx ON dicom_sops_100_part(series_uid);

vacuum freeze;
anal= yze;

Testing:
disconnect= =C2=A0and reconnect to the db with psql.

Query use= d for test:

drop table temp_series_id;CREATE TEMPO= RARY TABLE temp_series_id AS select series_uid from dicom_series order by r= andom() limit 1; analyze temp_series_id;
explain (analyze,buffers) selec= t * from dicom_sops_100_part where series_uid =3D (select series_uid from t= emp_series_id);

Query plan:

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0QUERY PLAN
---------------------------------------------------= ---------------------------------------------------------------------------= -------------------------------------------------------
=C2=A0Append =C2= =A0(cost=3D1.43..423.26 rows=3D50 width=3D128) (actual time=3D2.565..27.216= rows=3D20 loops=3D1)
=C2=A0 =C2=A0Buffers: shared hit=3D50 read=3D118, = local hit=3D1
=C2=A0 =C2=A0InitPlan 1
=C2=A0 =C2=A0 =C2=A0-> =C2= =A0Seq Scan on temp_series_id =C2=A0(cost=3D0.00..1.01 rows=3D1 width=3D16)= (actual time=3D0.006..0.007 rows=3D1 loops=3D1)
=C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0Buffers: local hit=3D1
=C2=A0 =C2=A0-> =C2=A0Inde= x Scan using dicom_sops_100_p0_series_uid_idx on dicom_sops_100_p0 dicom_so= ps_100_part_1 =C2=A0(cost=3D0.42..8.44 rows=3D1 width=3D128) (actual time= =3D0.846..0.846 rows=3D0 loops=3D1)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0In= dex Cond: (series_uid =3D (InitPlan 1).col1)
....
=C2= =A0 =C2=A0-> =C2=A0Index Scan using dicom_sops_100_p49_series_uid_idx on= dicom_sops_100_p49 dicom_sops_100_part_50 =C2=A0(cost=3D0.42..8.44 rows=3D= 1 width=3D128) (actual time=3D0.302..0.303 rows=3D0 loops=3D1)
=C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0Index Cond: (series_uid =3D (InitPlan 1).col1)=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Buffers: shared hit=3D1 read=3D2
=C2= =A0Planning:
=C2=A0 =C2=A0Buffers: shared hit=3D4180
=C2=A0Planning T= ime: 4.941 ms
=C2=A0Execution Time: 27.682 ms
(159 rows)
Second query on the same session:
=C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0QUERY PLAN--------------------------------------------------------------------------= ---------------------------------------------------------------------------= --------------------------------
=C2=A0Append =C2=A0(cost=3D1.43..423.26= rows=3D50 width=3D128) (actual time=3D9.759..9.770 rows=3D0 loops=3D1)
= =C2=A0 =C2=A0Buffers: shared hit=3D100 read=3D50, local hit=3D1
=C2=A0 = =C2=A0InitPlan 1
=C2=A0 =C2=A0 =C2=A0-> =C2=A0Seq Scan on temp_series= _id =C2=A0(cost=3D0.00..1.01 rows=3D1 width=3D16) (actual time=3D0.003..0.0= 04 rows=3D1 loops=3D1)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Buffers:= local hit=3D1
=C2=A0 =C2=A0-> =C2=A0Index Scan using dicom_sops_100_= p0_series_uid_idx on dicom_sops_100_p0 dicom_sops_100_part_1 =C2=A0(cost=3D= 0.42..8.44 rows=3D1 width=3D128) (actual time=3D0.212..0.213 rows=3D0 loops= =3D1)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Index Cond: (series_uid =3D (Ini= tPlan 1).col1)
...
=C2=A0 =C2=A0-> =C2=A0Index Scan = using dicom_sops_100_p49_series_uid_idx on dicom_sops_100_p49 dicom_sops_10= 0_part_50 =C2=A0(cost=3D0.42..8.44 rows=3D1 width=3D128) (actual time=3D0.2= 36..0.236 rows=3D0 loops=3D1)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Index Co= nd: (series_uid =3D (InitPlan 1).col1)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0Buffers: shared hit=3D2 read=3D1
=C2=A0Planning:
=C2=A0 =C2=A0Buff= ers: shared hit=3D5
=C2=A0Planning Time: 0.604 ms
=C2=A0Execution Tim= e: 10.011 ms
(159 rows)


On Thu= , Jan 16, 2025 at 9:56=E2=80=AFAM bruno vieira da silva <brunogiovs@gmail.com> wrote:
Hello, Thanks David.

this pg test deployment. anyway= s I did a vacuum=C2=A0full on the db. and the number of buffers read increa= sed a bit.=C2=A0


On Wed, Jan 15, 2025 at 3:01=E2= =80=AFPM David Rowley <dgrowleyml@gmail.com> wrote:
On Thu, 16 Jan 2025 at 07:29, bruno vieira da = silva
<brunogiovs@gm= ail.com> wrote:
> On pg 17 now we have better visibility on the I/O required during quer= y planning.
> so, as part of an ongoing design work for table partitioning I was ana= lyzing the performance implications of having more or less partitions.
> In one of my tests of a table with 200 partitions using explain showed= a large amount of buffers read during planning. around 12k buffers.

That's a suspiciously high number of buffers.

> I observed that query planning seems to have a caching mechanism as su= bsequent similar queries require only a fraction of buffers read during que= ry planning.
> However, this "caching" seems to be per session as if I end = the client session and I reconnect the same query execution will require ag= ain to read 12k buffer for query planning.
>
> Does pg have any mechanism to mitigate this issue ( new sessions need = to read a large amount of buffers for query planning) ? or should I mitigat= e this issue by the use of connection pooling.
> How is this caching done? Is there a way to have viability on its usag= e? Where is it stored?

The caching is for relation meta-data and for various catalogue data.
This is stored in local session hash tables. The caching is done
lazily the first time something is looked up after the session starts.
If you're doing very little work before ending the session, then
you'll pay this overhead much more often than you would if you were to<= br> do more work in each session. A connection pooler would help you do
that, otherwise it would need to be a redesign of how you're
connecting to Postgres from your application.

There's no easy way from EXPLAIN to see which tables or catalogue
tables the IO is occurring on, however, you might want to try looking
at pg_statio_all_tables directly before and after the query that's
causing the 12k buffer accesses and then look at what's changed.

I suspect if you're accessing 12k buffers to run EXPLAIN that you have<= br> some auto-vacuum starvation issues. Is auto-vacuum enabled and
running? If you look at pg_stat_activity, do you see autovacuum
running? It's possible that it's running but not configured to run<= br> quickly enough to keep up with demand.=C2=A0 Alternatively, it may be
keeping up now, but at some point in the past, it might not have been
and you have some bloat either in an index or in a catalogue table as
a result.

David


--
Bruno Vieira da Silva


--
Bruno Vieira da Silva
--00000000000084274c062e5c39b0--