Re: Choosing an index on partitioned tables.

Thanks!

That's great about the Btree deduplication feature in 13.

On Tue, Sep 7, 2021 at 7:21 PM Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)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
>