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
evict_from_both_caches.sql
Description: application/sql
leaf_fragmentation.sql
Description: application/sql
