I'm currently migrating an oracle schema to postgresql. In the oracle`s
schema there is a table partition that has partitions by range(date - for
every day) and each partition has a sub partition by list(some values..).
Moreover, the data is loaded from a csv in a bulk. One important thing is
that some data might be imported twice therefore there must but a unique
index on the table.

On PostgreSQL 10.1 I created the main table partitioned by range(date) and
I created all the sub partitions. I have 2 problems :

1)In the oracle main table there are global indexes for selects that
involve columns that arent part of the range or list partitions. According
to the documentation I need to create the indexes on each leaf. I have
partition for every day in the year so I'll have about 6(num of global
indexes in oracle)*365(days of year)*7(number of sub partitions) = 15330
indexes created every year. I guess that the performance that I will have
when I select columns that arent part of the partitions order will be
pretty bad. Any idea ?

2)Regarding the uniqueness, the only solution is to create a unique index
for every subpartition ?

3)Any suggestions how to improve queries that involve columns that arent
part of the paritions order ?

Thanks , Mariel.

Reply via email to