Re: Proposal: Local indexes for partitioned table

From: Maksim Milyutin <m(dot)milyutin(at)postgrespro(dot)ru>
To: Pg Hackers <pgsql-hackers(at)postgresql(dot)org>, Robert Haas <robertmhaas(at)gmail(dot)com>
Subject: Re: Proposal: Local indexes for partitioned table
Date: 2017-04-04 16:10:37
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: 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.
> 2.

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:
Russian Postgres Company

Attachment Content-Type Size
local_index_v1.patch text/x-patch 14.2 KB
local_index.sql application/sql 1.9 KB
local_index.out text/plain 3.6 KB

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message David Steele 2017-04-04 16:23:25 Re: PATCH: Make pg_stop_backup() archive wait optional
Previous Message Claudio Freire 2017-04-04 16:00:08 Re: Making clausesel.c Smarter