On 5/9/25 18:36, Peter Geoghegan wrote: > On Fri, May 9, 2025 at 12:28 PM Tomas Vondra <to...@vondra.me> wrote: >> Not sure if it matters, but this uses index-only scans, and the pages >> are all-visible, so maybe it's not much more expensive. > > You're still going to have to scan 85 full index pages on your > pgbench_accounts.bid index -- that's pretty expensive, even with an > index-only scan. >
Not sure I understand. Why would it need to scan 85 index pages? There's only 100 matching tuples total, spread over the 100 partitions. We'll need to scan maybe 1 page per partition. > Even if there was some kind of really obvious regression in > preprocessing (which seems almost impossible), I'd still expect it to > be drowned out for queries such as these. > >> Not sure, I did multiple tests with different queries, and it'd be a bit >> strange if this was the only one affected. Not impossible. > > The only thing that substantially differs between this > pgbench_accounts.bid query and traditional pgbench SELECT queries (on > pgbench_accounts.aid) is 1.) this query is quite a bit more expensive > at execution time, and 2.) that it involves the use of partitioning. > > I made sure to test pgbench SELECT quite thoroughly -- that certainly > wasn't regressed. So it seems very likely to have something to do with > partitioning. > Yeah. This type of query amplifies any overhead in the index scan, because it does one for every partition. I haven't seen the regression without the partitioning. regards -- Tomas Vondra
