public inbox for [email protected]help / color / mirror / Atom feed
Query planning read a large amount of buffers for partitioned tables 9+ messages / 2 participants [nested] [flat]
* Query planning read a large amount of buffers for partitioned tables @ 2025-01-15 18:29 bruno vieira da silva <[email protected]> 0 siblings, 2 replies; 9+ messages in thread From: bruno vieira da silva @ 2025-01-15 18:29 UTC (permalink / raw) To: [email protected] Hello All. On pg 17 now we have better visibility on the I/O required during query 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. I observed that query planning seems to have a caching mechanism as subsequent similar queries require only a fraction of buffers read during 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 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 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? Thanks -- Bruno Vieira da Silva ^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Query planning read a large amount of buffers for partitioned tables @ 2025-01-15 18:32 bruno vieira da silva <[email protected]> parent: bruno vieira da silva <[email protected]> 1 sibling, 0 replies; 9+ messages in thread From: bruno vieira da silva @ 2025-01-15 18:32 UTC (permalink / raw) To: [email protected] correction: Is there a way to have *visibility* on its usage? thanks On Wed, Jan 15, 2025 at 1:29 PM bruno vieira da silva <[email protected]> wrote: > Hello All. > > On pg 17 now we have better visibility on the I/O required during query > 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. > > I observed that query planning seems to have a caching mechanism as > subsequent similar queries require only a fraction of buffers read during > 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 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 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? > > Thanks > -- > Bruno Vieira da Silva > -- Bruno Vieira da Silva ^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Query planning read a large amount of buffers for partitioned tables @ 2025-01-15 20:01 David Rowley <[email protected]> parent: bruno vieira da silva <[email protected]> 1 sibling, 1 reply; 9+ messages in thread From: David Rowley @ 2025-01-15 20:01 UTC (permalink / raw) To: bruno vieira da silva <[email protected]>; +Cc: [email protected] On Thu, 16 Jan 2025 at 07:29, bruno vieira da silva <[email protected]> wrote: > On pg 17 now we have better visibility on the I/O required during query 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 during 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 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 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 ^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Query planning read a large amount of buffers for partitioned tables @ 2025-01-16 14:56 bruno vieira da silva <[email protected]> parent: David Rowley <[email protected]> 0 siblings, 1 reply; 9+ messages in thread From: bruno vieira da silva @ 2025-01-16 14:56 UTC (permalink / raw) To: David Rowley <[email protected]>; +Cc: [email protected] 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 PM David Rowley <[email protected]> wrote: > On Thu, 16 Jan 2025 at 07:29, bruno vieira da silva > <[email protected]> wrote: > > On pg 17 now we have better visibility on the I/O required during query > 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 during > 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 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 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 ^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Query planning read a large amount of buffers for partitioned tables @ 2025-02-17 20:18 bruno vieira da silva <[email protected]> parent: bruno vieira da silva <[email protected]> 0 siblings, 2 replies; 9+ messages in thread From: bruno vieira da silva @ 2025-02-17 20:18 UTC (permalink / raw) To: David Rowley <[email protected]>; +Cc: [email protected] 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=bruno -e POSTGRES_PASSWORD=bruno -p 5500:5432 -v /home/bruno/pgdata17:/var/lib/postgresql/data --name postgresql postgres:17.2 export PGHOST="localhost" export PGPORT=5500 export PGDATABASE="postgres" export PGUSER="bruno" export PGPASSWORD="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 := 1000000; -- Number of series to create sops_per_series INT := 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 = (select series_uid from temp_series_id); Query plan: QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Append (cost=1.43..423.26 rows=50 width=128) (actual time=2.565..27.216 rows=20 loops=1) Buffers: shared hit=50 read=118, local hit=1 InitPlan 1 -> Seq Scan on temp_series_id (cost=0.00..1.01 rows=1 width=16) (actual time=0.006..0.007 rows=1 loops=1) Buffers: local hit=1 -> Index Scan using dicom_sops_100_p0_series_uid_idx on dicom_sops_100_p0 dicom_sops_100_part_1 (cost=0.42..8.44 rows=1 width=128) (actual time=0.846..0.846 rows=0 loops=1) Index Cond: (series_uid = (InitPlan 1).col1) .... -> Index Scan using dicom_sops_100_p49_series_uid_idx on dicom_sops_100_p49 dicom_sops_100_part_50 (cost=0.42..8.44 rows=1 width=128) (actual time=0.302..0.303 rows=0 loops=1) Index Cond: (series_uid = (InitPlan 1).col1) Buffers: shared hit=1 read=2 Planning: Buffers: shared hit=4180 Planning Time: 4.941 ms Execution Time: 27.682 ms (159 rows) Second query on the same session: QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Append (cost=1.43..423.26 rows=50 width=128) (actual time=9.759..9.770 rows=0 loops=1) Buffers: shared hit=100 read=50, local hit=1 InitPlan 1 -> Seq Scan on temp_series_id (cost=0.00..1.01 rows=1 width=16) (actual time=0.003..0.004 rows=1 loops=1) Buffers: local hit=1 -> Index Scan using dicom_sops_100_p0_series_uid_idx on dicom_sops_100_p0 dicom_sops_100_part_1 (cost=0.42..8.44 rows=1 width=128) (actual time=0.212..0.213 rows=0 loops=1) Index Cond: (series_uid = (InitPlan 1).col1) ... -> Index Scan using dicom_sops_100_p49_series_uid_idx on dicom_sops_100_p49 dicom_sops_100_part_50 (cost=0.42..8.44 rows=1 width=128) (actual time=0.236..0.236 rows=0 loops=1) Index Cond: (series_uid = (InitPlan 1).col1) Buffers: shared hit=2 read=1 Planning: Buffers: shared hit=5 Planning Time: 0.604 ms Execution Time: 10.011 ms (159 rows) On Thu, Jan 16, 2025 at 9:56 AM bruno vieira da silva <[email protected]> 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 PM David Rowley <[email protected]> wrote: > >> On Thu, 16 Jan 2025 at 07:29, bruno vieira da silva >> <[email protected]> wrote: >> > On pg 17 now we have better visibility on the I/O required during query >> 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 during >> 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 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 >> 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 > -- Bruno Vieira da Silva ^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Query planning read a large amount of buffers for partitioned tables @ 2025-02-17 20:25 bruno vieira da silva <[email protected]> parent: bruno vieira da silva <[email protected]> 1 sibling, 1 reply; 9+ messages in thread From: bruno vieira da silva @ 2025-02-17 20:25 UTC (permalink / raw) To: David Rowley <[email protected]>; +Cc: [email protected] Well, the query plans were generated with pg 17.3. and the buffer usage was half. did pg 17.3 had any fixes to reduce the planning buffer usage? On Mon, Feb 17, 2025 at 3:18 PM bruno vieira da silva <[email protected]> wrote: > 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=bruno -e POSTGRES_PASSWORD=bruno -p > 5500:5432 -v /home/bruno/pgdata17:/var/lib/postgresql/data --name > postgresql postgres:17.2 > export PGHOST="localhost" > export PGPORT=5500 > export PGDATABASE="postgres" > export PGUSER="bruno" > export PGPASSWORD="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 := 1000000; -- Number of series to create > sops_per_series INT := 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 = (select series_uid from temp_series_id); > > Query plan: > > > QUERY PLAN > > ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- > Append (cost=1.43..423.26 rows=50 width=128) (actual time=2.565..27.216 > rows=20 loops=1) > Buffers: shared hit=50 read=118, local hit=1 > InitPlan 1 > -> Seq Scan on temp_series_id (cost=0.00..1.01 rows=1 width=16) > (actual time=0.006..0.007 rows=1 loops=1) > Buffers: local hit=1 > -> Index Scan using dicom_sops_100_p0_series_uid_idx on > dicom_sops_100_p0 dicom_sops_100_part_1 (cost=0.42..8.44 rows=1 width=128) > (actual time=0.846..0.846 rows=0 loops=1) > Index Cond: (series_uid = (InitPlan 1).col1) > .... > -> Index Scan using dicom_sops_100_p49_series_uid_idx on > dicom_sops_100_p49 dicom_sops_100_part_50 (cost=0.42..8.44 rows=1 > width=128) (actual time=0.302..0.303 rows=0 loops=1) > Index Cond: (series_uid = (InitPlan 1).col1) > Buffers: shared hit=1 read=2 > Planning: > Buffers: shared hit=4180 > Planning Time: 4.941 ms > Execution Time: 27.682 ms > (159 rows) > > Second query on the same session: > > QUERY PLAN > > ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- > Append (cost=1.43..423.26 rows=50 width=128) (actual time=9.759..9.770 > rows=0 loops=1) > Buffers: shared hit=100 read=50, local hit=1 > InitPlan 1 > -> Seq Scan on temp_series_id (cost=0.00..1.01 rows=1 width=16) > (actual time=0.003..0.004 rows=1 loops=1) > Buffers: local hit=1 > -> Index Scan using dicom_sops_100_p0_series_uid_idx on > dicom_sops_100_p0 dicom_sops_100_part_1 (cost=0.42..8.44 rows=1 width=128) > (actual time=0.212..0.213 rows=0 loops=1) > Index Cond: (series_uid = (InitPlan 1).col1) > ... > -> Index Scan using dicom_sops_100_p49_series_uid_idx on > dicom_sops_100_p49 dicom_sops_100_part_50 (cost=0.42..8.44 rows=1 > width=128) (actual time=0.236..0.236 rows=0 loops=1) > Index Cond: (series_uid = (InitPlan 1).col1) > Buffers: shared hit=2 read=1 > Planning: > Buffers: shared hit=5 > Planning Time: 0.604 ms > Execution Time: 10.011 ms > (159 rows) > > > On Thu, Jan 16, 2025 at 9:56 AM bruno vieira da silva < > [email protected]> 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 PM David Rowley <[email protected]> >> wrote: >> >>> On Thu, 16 Jan 2025 at 07:29, bruno vieira da silva >>> <[email protected]> wrote: >>> > On pg 17 now we have better visibility on the I/O required during >>> query 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 during >>> 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 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 >>> 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 >> > > > -- > Bruno Vieira da Silva > -- Bruno Vieira da Silva ^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Query planning read a large amount of buffers for partitioned tables @ 2025-02-17 20:27 bruno vieira da silva <[email protected]> parent: bruno vieira da silva <[email protected]> 0 siblings, 0 replies; 9+ messages in thread From: bruno vieira da silva @ 2025-02-17 20:27 UTC (permalink / raw) To: David Rowley <[email protected]>; +Cc: [email protected] nevermind. The query plan was done on test data with 50 partitions. Sorry for the confusion. On Mon, Feb 17, 2025 at 3:25 PM bruno vieira da silva <[email protected]> wrote: > Well, the query plans were generated with pg 17.3. and the buffer usage > was half. > did pg 17.3 had any fixes to reduce the planning buffer usage? > > On Mon, Feb 17, 2025 at 3:18 PM bruno vieira da silva < > [email protected]> wrote: > >> 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=bruno -e POSTGRES_PASSWORD=bruno -p >> 5500:5432 -v /home/bruno/pgdata17:/var/lib/postgresql/data --name >> postgresql postgres:17.2 >> export PGHOST="localhost" >> export PGPORT=5500 >> export PGDATABASE="postgres" >> export PGUSER="bruno" >> export PGPASSWORD="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 := 1000000; -- Number of series to create >> sops_per_series INT := 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 = (select series_uid from temp_series_id); >> >> Query plan: >> >> >> QUERY PLAN >> >> ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- >> Append (cost=1.43..423.26 rows=50 width=128) (actual time=2.565..27.216 >> rows=20 loops=1) >> Buffers: shared hit=50 read=118, local hit=1 >> InitPlan 1 >> -> Seq Scan on temp_series_id (cost=0.00..1.01 rows=1 width=16) >> (actual time=0.006..0.007 rows=1 loops=1) >> Buffers: local hit=1 >> -> Index Scan using dicom_sops_100_p0_series_uid_idx on >> dicom_sops_100_p0 dicom_sops_100_part_1 (cost=0.42..8.44 rows=1 width=128) >> (actual time=0.846..0.846 rows=0 loops=1) >> Index Cond: (series_uid = (InitPlan 1).col1) >> .... >> -> Index Scan using dicom_sops_100_p49_series_uid_idx on >> dicom_sops_100_p49 dicom_sops_100_part_50 (cost=0.42..8.44 rows=1 >> width=128) (actual time=0.302..0.303 rows=0 loops=1) >> Index Cond: (series_uid = (InitPlan 1).col1) >> Buffers: shared hit=1 read=2 >> Planning: >> Buffers: shared hit=4180 >> Planning Time: 4.941 ms >> Execution Time: 27.682 ms >> (159 rows) >> >> Second query on the same session: >> >> QUERY PLAN >> >> ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- >> Append (cost=1.43..423.26 rows=50 width=128) (actual time=9.759..9.770 >> rows=0 loops=1) >> Buffers: shared hit=100 read=50, local hit=1 >> InitPlan 1 >> -> Seq Scan on temp_series_id (cost=0.00..1.01 rows=1 width=16) >> (actual time=0.003..0.004 rows=1 loops=1) >> Buffers: local hit=1 >> -> Index Scan using dicom_sops_100_p0_series_uid_idx on >> dicom_sops_100_p0 dicom_sops_100_part_1 (cost=0.42..8.44 rows=1 width=128) >> (actual time=0.212..0.213 rows=0 loops=1) >> Index Cond: (series_uid = (InitPlan 1).col1) >> ... >> -> Index Scan using dicom_sops_100_p49_series_uid_idx on >> dicom_sops_100_p49 dicom_sops_100_part_50 (cost=0.42..8.44 rows=1 >> width=128) (actual time=0.236..0.236 rows=0 loops=1) >> Index Cond: (series_uid = (InitPlan 1).col1) >> Buffers: shared hit=2 read=1 >> Planning: >> Buffers: shared hit=5 >> Planning Time: 0.604 ms >> Execution Time: 10.011 ms >> (159 rows) >> >> >> On Thu, Jan 16, 2025 at 9:56 AM bruno vieira da silva < >> [email protected]> 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 PM David Rowley <[email protected]> >>> wrote: >>> >>>> On Thu, 16 Jan 2025 at 07:29, bruno vieira da silva >>>> <[email protected]> wrote: >>>> > On pg 17 now we have better visibility on the I/O required during >>>> query 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 during >>>> 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 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 >>>> 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 >>> >> >> >> -- >> Bruno Vieira da Silva >> > > > -- > Bruno Vieira da Silva > -- Bruno Vieira da Silva ^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Query planning read a large amount of buffers for partitioned tables @ 2025-02-18 12:16 David Rowley <[email protected]> parent: bruno vieira da silva <[email protected]> 1 sibling, 1 reply; 9+ messages in thread From: David Rowley @ 2025-02-18 12:16 UTC (permalink / raw) To: bruno vieira da silva <[email protected]>; +Cc: [email protected] On Tue, 18 Feb 2025 at 09:18, bruno vieira da silva <[email protected]> wrote: > > 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? I didn't analyze this in great detail, but nothing looks too surprising to me. I get roughly the same numbers on the latest git master branch as you've shown above. A PostgreSQL backend will cache various metadata about relations the first time they're accessed in a backend. Building those caches requires accessing the system catalogue tables. I expect the majority of the buffer accesses are for those tables. If you're curious about what's being accessed and have a fresh test instance handy, you could use strace to see which buffers are being read. You'll need to ensure the shared buffers are not caching anything. Restarting PostgreSQL should clear those out sufficiently. You can translate the filenodes back into relation names by using a query such as: select relname from pg_class where pg_relation_filenode(oid)=1259; If this is causing you problems then maybe a connection pooler would help you. With one of those, the backend will live longer than just 1 query. You could also perhaps revisit your partition count to see if the number you've chosen gives you the best performance. It's very common for people to over-partition and not properly consider the overheads of partitioning. David ^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Query planning read a large amount of buffers for partitioned tables @ 2025-02-18 15:48 bruno vieira da silva <[email protected]> parent: David Rowley <[email protected]> 0 siblings, 0 replies; 9+ messages in thread From: bruno vieira da silva @ 2025-02-18 15:48 UTC (permalink / raw) To: David Rowley <[email protected]>; +Cc: [email protected] Hello, Your explanation aligns with the idea I had that having more shared_buffers and connection pooling are very important in the context of the partitioned tables. Thanks. Regards. On Tue, Feb 18, 2025 at 7:16 AM David Rowley <[email protected]> wrote: > On Tue, 18 Feb 2025 at 09:18, bruno vieira da silva > <[email protected]> wrote: > > > > 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? > > I didn't analyze this in great detail, but nothing looks too > surprising to me. I get roughly the same numbers on the latest git > master branch as you've shown above. > > A PostgreSQL backend will cache various metadata about relations the > first time they're accessed in a backend. Building those caches > requires accessing the system catalogue tables. I expect the majority > of the buffer accesses are for those tables. If you're curious about > what's being accessed and have a fresh test instance handy, you could > use strace to see which buffers are being read. You'll need to ensure > the shared buffers are not caching anything. Restarting PostgreSQL > should clear those out sufficiently. You can translate the filenodes > back into relation names by using a query such as: select relname from > pg_class where pg_relation_filenode(oid)=1259; > > If this is causing you problems then maybe a connection pooler would > help you. With one of those, the backend will live longer than just 1 > query. You could also perhaps revisit your partition count to see if > the number you've chosen gives you the best performance. It's very > common for people to over-partition and not properly consider the > overheads of partitioning. > > David > -- Bruno Vieira da Silva ^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2025-02-18 15:48 UTC | newest] Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2025-01-15 18:29 Query planning read a large amount of buffers for partitioned tables bruno vieira da silva <[email protected]> 2025-01-15 18:32 ` bruno vieira da silva <[email protected]> 2025-01-15 20:01 ` David Rowley <[email protected]> 2025-01-16 14:56 ` bruno vieira da silva <[email protected]> 2025-02-17 20:18 ` bruno vieira da silva <[email protected]> 2025-02-17 20:25 ` bruno vieira da silva <[email protected]> 2025-02-17 20:27 ` bruno vieira da silva <[email protected]> 2025-02-18 12:16 ` David Rowley <[email protected]> 2025-02-18 15:48 ` bruno vieira da silva <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox