Re: Proposal: Local indexes for partitioned table - Mailing list pgsql-hackers

On 01.03.2017 13:53, Maksim Milyutin wrote:
> Hi hackers!
>
> As I've understood from thread [1] the main issue of creating local
> indexes for partitions is supporting REINDEX and DROP INDEX operations
> on parent partitioned tables. Furthermore Robert Haas mentioned the
> problem of creating index on key that is represented in partitions with
> single value (or primitive interval) [1] i.e. under the
> list-partitioning or range-partitioning with unit interval.
>
> I would like to propose the following solution:
>
> 1. Create index for hierarchy of partitioned tables and partitions
> recursively. Don't create relfilenode for indexes on parents, only
> entries in catalog (much like the partitioned table's storage
> elimination in [2]). Abstract index for partitioned tables is only for
> the reference on indexes of child tables to perform REINDEX and DROP
> INDEX operations.
>
> 2. Specify created indexes in pg_depend table so that indexes of child
> tables depend on corresponding indexes of parent tables with type of
> dependency DEPENDENCY_NORMAL so that index could be removed separately
> for partitions and recursively/separately for partitioned tables.
>
> 3. REINDEX on index of partitioned table would perform this operation on
> existing indexes of corresponding partitions. In this case it is
> necessary to consider such operations as REINDEX SCHEMA | DATABASE |
> SYSTEM so that partitions' indexes wouldn't be re-indexed multiple times
> in a row.
>
> Any thoughts?
>
> 1.
> https://www.postgresql.org/message-id/CA+TgmoZUwj=QYnaK+F7xEf4w_e2g3XxdMnSNZMZjuinHRcOB8A@mail.gmail.com
>
> 2.
> https://www.postgresql.org/message-id/2b0d42f2-3a53-763b-c9c2-47139e4b1c2e%40lab.ntt.co.jp
>
>

I want to present the first version of patches that implement local 
indexes for partitioned tables and discuss some technical details of 
that implementation.


1. I have added a new relkind for local indexes named 
RELKIND_LOCAL_INDEX (literal 'l').

This was done because physical storage is created in the 'heap_create' 
function and we need to revoke the creating storage as with partitioned 
tables. Since information that this index belongs to partitioned tables 
is not available in 'heap_create' function (pg_index entry on the index 
is not created yet) I chose the least painful way - added a specific 
relkind for index on partitioned table.
I suppose that this act will require the integrating new relkind to 
different places of source code so I'm ready to consider another 
proposals on this point.

2. My implementation doesn't support the concurrent creating of local 
index (CREATE INDEX CONCURRENTLY). As I understand, this operation 
involves nontrivial manipulation with snapshots and I don't know how to 
implement concurrent creating of multiple indexes. In this point I ask 
help from community.

3. As I noticed early pg_depend table is used for cascade deleting 
indexes on partitioned table and its children. I also use pg_depend to 
determine relationship between parent and child indexes when reindex 
executes recursively on child indexes.

Perhaps, it's not good way to use pg_depend to determine the 
relationship between parent and child indexes because the kind of this 
relationship is not defined. I could propose to add into pg_index table 
specific field of 'oidvector' type that specify oids of dependent 
indexes for the current local index.


On this stage I want to discuss only technical details of local indexes' 
implementation. The problems related to merging existing indexes of 
partitions within local index tree, determination uniqueness of field in 
global sense through local index and syntax notes I want to arise later.


CC welcome!

-- 
Maksim Milyutin
Postgres Professional: http://www.postgrespro.com
Russian Postgres Company