Re: Choosing an index on partitioned tables.

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