PostgreSQL 10.1 partitions and indexes

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.

Browse pgsql-performance by date

  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 ?