Horton

Re: doc: explain pgstatindex fragmentation

Benoit Lobréau <benoit.lobreau@dalibo.com>

View thread
From: Benoit Lobréau <benoit.lobreau@dalibo.com>
To: Laurenz Albe <laurenz.albe@cybertec.at>, Frédéric Yhuel <frederic.yhuel@dalibo.com>, Bertrand Drouvot <bertranddrouvot.pg@gmail.com>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Date: 2025-01-27T09:13:39Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. doc: explain pgstatindex fragmentation

Attachments

On 1/25/25 7:07 PM, Laurenz Albe wrote:
> Looks good to me.  I have one question left: the explanation for the performance
> penalty of a high leaf fragmentation sounds like it would only be relevant for
> disks where sequential reads are faster.  If that is correct, perhaps it would be
> worth mentioning.

Hi Laurenz,

Frédéric is in holiday this week. So he might not be able to answer, 
I'll try to do it in his stead.

Frederic noticed a performance hit even for on his laptop with a SSD.

On Fri, 2025-01-24 at 15:41 +0100, Frédéric Yhuel wrote:
 > I've noticed that maximum leaf_fragmentation can have a huge impact on
 > a range index-only scan, when reading all blocs from disks, even on my
 > laptop machine with SSD, but I don't know if this is the right place
 > to document this?

He reported to our team, that he did a test with two indexes on the same 
data. They had the same density but one had no fragmentation while the 
other had 100%. He got an execution time of ~90ms (0 frag) vs ~340ms 
100% frag).

I get similar result with my laptor (except my disk is significantly 
worse: ~152ms vs ~833ms).

Here are the scripts.

-- 
Benoit Lobréau
Consultant
http://dalibo.com