On 8/7/23 19:05, Robert Haas wrote: > 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'm the first to admit the coding (in my patches) is far from perfect, and this may easily be a consequence of that. My motivation was to get some quick measurements for the "bad case". > 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. > That's true, but doesn't that apply to any cache that can overflow? You could make the same argument about the default value of 16 slots - why not to have just 8? FWIW I wasn't really suggesting we should increase the value to 64, I was just trying to get a better idea of the costs at play (fast-path cache maintenance and regular locking). > 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. Yes, I agree. I don't know if this particular design would be the right one (1000 elements seems a bit too much for something included right in PGPROC). But yeah, something that flips from linear search to something else would be reasonable. > > 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. > Right. We're mostly just guessing what the issue is. > 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. > No idea, but I'd bet some of the trade offs may need re-evaluation. regards -- Tomas Vondra EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
