Thread

1. creating hash indexes

   Rick Otten <rottenwindfish@gmail.com> — 2022-12-14T20:03:42Z

   I inherited a database with several single-digit billion row tables.  Those
   tables have a varchar(36) column populated with uuids (all connected to
   each other via FKs) each currently supported by a btree index.
   
   After the recent conversations about hash indexes I thought I'd do some
   comparisons to see if using a hash index could help and perhaps
   depriortize my burning desire to change the data type.  We never look up
   uuids with inequalities after all.  Indeed, in my test environments the
   hash index was half the size of the btree index, and the select performance
   was slightly faster than btree lookups. varchar(36) with hash index was
   roughly comparable to using a uuid data type (btree or hash index).
   
   I was pretty excited until I tried to create the index on a table with the
   data (instead of creating it ahead of time and then loading up the test
   data).
   
   Working in PG 14.5, on a tiny 9M row table, in an idle database, I found:
   - creating the btree index on the varchar(36) column to consistently take 7
   *seconds*
   - creating the hash index on the varchar(36) to consistently take 1 *hour*
   
   I was surprised at how dramatically slower it was.   I tried this on both
   partitioned and non-partitioned tables (with the same data set) and in both
   cases the timings came out similar.
   
   I also tried creating a hash index on a varchar(100) column, also with 9M
   rows. I gave up after it did not complete after several hours.  (it wasn't
   locked, just slow)
   
   While I was experimenting with the different index types, I did some insert
   tests.  After putting the hash index on the column, the inserts were
   significantly slower.  The btree index was *6-7x *slower than no index, and
   the hash index was *100x* slower than no index.
   
   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?
   
   ---
   
   FWIW, from my tests on my laptop, on a 250M row table last weekend, after
   100K selects:
   
   MEAN (ms)  | btree    |  hash
   ---------  | -------  |  ----
   varchar    | 28.14916 | 27.03769
   uuid       | 27.04855 | 27.64424
   
   and the sizes
   
   SIZE    | btree    | hash
   ----    | -----    | ----
   varchar | 12 GB    | 6212 MB
   uuid    | 6595 MB  | 6212 MB
   
   -  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.
   
   --
   Rick
   

2. Re: creating hash indexes

   Peter Geoghegan <pg@bowt.ie> — 2022-12-14T20:28:47Z

   On Wed, Dec 14, 2022 at 12:03 PM Rick Otten <rottenwindfish@gmail.com> 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