On Wed, Dec 14, 2022 at 12:03 PM Rick Otten <rottenwindfish@xxxxxxxxx> wrote: > Assuming I can live with the slower inserts, is there any parameter in particular I can tweak that would make the time it takes to create the hash index closer to the btree index creation time? In particular if I wanted to try this on a several billion row table in a busy database? No. B-Tree index builds are parallelized, and are far better optimized in general. > - As long as the index fits in memory, varchar btree isn't really that much slower in postgresql 14 (the way it was a few years ago), so we'll probably just live with that for the forseeable future given the complexity of changing things at the moment. The other things to consider are 1.) the index size after retail inserts, 2.) the index size following some number of updates and deletes. Even if you just had plain inserts for your production workload, the picture will not match your test case (which I gather just looked at the index size after a CREATE INDEX ran). I think that B-Tree indexes will still come out ahead if you take this growth into account, and by quite a bit, but probably not due to any effect that your existing test case exercises. B-Tree indexes are good at accommodating unpredictable growth, without ever getting terrible performance on any metric of interest. So it's not just that they tend to have better performance on average than hash indexes (though they do); it's that they have much more *predictable* performance characteristics as conditions change. -- Peter Geoghegan
