On Mon, Aug 7, 2023 at 6:51 AM Tomas Vondra <tomas.von...@enterprisedb.com> wrote: > The regression appears to be consistently ~3%, and v2 aimed to improve > that - at least for the case with just 100 rows. It even gains ~5% in a > couple cases. It's however a bit strange v2 doesn't really help the two > larger cases.
To me, that's an absolutely monumental regression for a change like this. Sure, lots of people have partitioned tables. But also, lots of people don't. Penalizing very simple queries by 2-3% seems completely over the top to me. I can't help wondering whether there's actually something wrong with the test, or the coding, because that seems huge to me. I would also argue that the results are actually not that great, because once you get past 64 partitions you're right back where you started, or maybe worse off. To me, there's nothing magical about cases between 16 and 64 relations that makes them deserve special treatment - plenty of people are going to want to use hundreds of partitions, and even if you only use a few dozen, this isn't going to help as soon as you join two or three partitioned tables, and I suspect it hurts whenever it doesn't help. I think we need a design that scales better. I don't really know what that would look like, exactly, but your idea of a hybrid approach seems like it might be worth further consideration. We don't have to store an infinite number of fast-path locks in an array that we search linearly, and it might be better that if we moved to some other approach we could avoid some of the regression. You mentioned a hash table; a partially associative cache might also be worth considering, like having an array of 1k slots but dividing it logically into 128 bins of 16 slots each and only allowing an OID to be recorded in the bin whose low 7 bits match the low 7 bits of the OID. But maybe first we need to understand where all the CPU cycles are going, because maybe that's optimizing completely the wrong thing and, again, it seems like an awfully large hit. Of course, another thing we could do is try to improve the main lock manager somehow. I confess that I don't have a great idea for that at the moment, but the current locking scheme there is from a very, very long time ago and clearly wasn't designed with modern hardware in mind. -- Robert Haas EDB: http://www.enterprisedb.com
