On Tue, 2021-09-07 at 15:44 +1200, Tim Uckun wrote: > I have a series of tables which are going to be queries mostly on two > columns. A timestamp table and a metric type column. > > My plan is to partition by date ranges which means the primary key has > to include the timestamp column and the id column As far as I know > there is no way to specify an index type for those columns. > > The metric type is a text column and will not be very selective. It > will have somewhere around 200 types of metrics and they will all be > short, less than ten characters. > > Given that there will be a lot of records I was wondering what type of > index would be ideal for that column. Seems like hash indexes would be > ideal because only comparison will be = and they are smaller than > Btrees but for a while they were not recommended. > > Would hash be the best or would something work better? If you don't need to speed up searches by "id", you could define the primary key on (timestamp_col, id), which can be used to speed up searches by the timestamp column without defining an extra index. I would choose a B-tree index for the metrics column. With the B-tree deduplication feature added in v13, the index will be small, and I doubt that hash indexes would perform much better. If there is a dominant value, you could consider a partial index that excludes that value. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
