public inbox for [email protected]
help / color / mirror / Atom feedFrom: David Rowley <[email protected]>
To: bruno vieira da silva <[email protected]>
Cc: [email protected]
Subject: Re: Query planning read a large amount of buffers for partitioned tables
Date: Wed, 19 Feb 2025 01:16:09 +1300
Message-ID: <CAApHDvqi2ySSRi=JTH0YYUe3yUqw3+csJx=YAmOP=hY8f6sn4Q@mail.gmail.com> (raw)
In-Reply-To: <CAB+Nuk-JOJD3QA4=cTnpqs-AWQrdSWZWH8Ydf1E-z5AWNRUh7Q@mail.gmail.com>
References: <CAB+Nuk9NAH8MxDK9ng9G+Vvs8XAaaV7Jc=F7FWrbF8U8WWaDEw@mail.gmail.com>
<CAApHDvqCEWF-3Eg=Lmrv3SGFuFX0KzHf8XfZT22Q1XL=8KN0UA@mail.gmail.com>
<CAB+Nuk8bW4xcjGEjANy9manpxOpH=0pRFOQRufwzEUvkG_euDQ@mail.gmail.com>
<CAB+Nuk-JOJD3QA4=cTnpqs-AWQrdSWZWH8Ydf1E-z5AWNRUh7Q@mail.gmail.com>
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
view thread (9+ messages) latest in thread
reply
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: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: Query planning read a large amount of buffers for partitioned tables
In-Reply-To: <CAApHDvqi2ySSRi=JTH0YYUe3yUqw3+csJx=YAmOP=hY8f6sn4Q@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox