From: | Mariel Cherkassky <mariel(dot)cherkassky(at)gmail(dot)com> |
---|---|
To: | PostgreSQL mailing lists <pgsql-performance(at)postgresql(dot)org> |
Subject: | PostgreSQL 10.1 partitions and indexes |
Date: | 2018-01-29 10:30:13 |
Message-ID: | CA+t6e1=QM_3ettgjRShnuiFXXsY8KvLyRUUYscT6POTyDb+qVw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Hi,
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Rick Otten | 2018-01-29 16:19:43 | dsa_allocate() faliure |
Previous Message | Pavan Teja | 2018-01-29 04:46:26 | Re: 8.2 Autovacuum BUG ? |