Thanks! That's great about the Btree deduplication feature in 13.
On Tue, Sep 7, 2021 at 7:21 PM Laurenz Albe <laurenz.a...@cybertec.at> wrote: > > 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 >
