Hello,
We have a large table and we use partition on that table. We keep data as monthly with partitions so partitions check date fields. I'm confused about adding second partition on same table but for check different field like state(character varying). By the way this partitions which check date has index for state column.
When a query run first check partitions. Sometimes it is really make slow queries than we expect. We want to get query check first state field during running that query. In brief we would like to prioritise this.
Can we separate partition structure for instance partitions for date and partitions for state on same table?
I'm not sure it is reasonable? Is there anyone performed this scenario?
Regards,