Choosing an index on partitioned tables.

From: Tim Uckun <timuckun(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Choosing an index on partitioned tables.
Date: 2021-09-07 03:44:35
Message-ID: CAGuHJrOjKGOt4O-baQhS1d=uBz3L6QipJCHYsLegr1ez2htMJg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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?

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Brent Wood 2021-09-07 04:06:27 Re: Choosing an index on partitioned tables.
Previous Message Peter Geoghegan 2021-09-06 22:09:53 Re: Behavior change in PostgreSQL 14Beta3 or bug?