From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: Robert Haas <robertmhaas@gmail.com>
Cc: David Rowley <david.rowley@2ndquadrant.com>
Cc: Simon Riggs <simon@2ndquadrant.com>
Cc: Pg Hackers <pgsql-hackers@postgresql.org>
Cc: Maksim Milyutin <milyutinma@gmail.com>
Subject: Re: [HACKERS] Proposal: Local indexes for partitioned table
Date: Fri, 15 Dec 2017 18:02:53 -0300
Message-ID: <20171215210253.dhixou5smlw27kos@alvherre.pgsql> (raw)
In-Reply-To: <CA+TgmoZCgu0ZcZbS=V+fHwYw+n_q4fRGnkOumJq88ne4vJnEyA@mail.gmail.com>
Hmm, so I'm now unsure what the actual proposals for handling pg_dump
are. We seem to have the following three proposals:
1. Alvaro: use CREATE INDEX ON ONLY <parent> (not recursive ), followed
by CREATE INDEX ON <partition>, followed by ALTER INDEX <on_parent>
ATTACH PARTITION <on_partition>. I provide an ALTER INDEX DETACH
PARTITION for symmetry and because it can be used to replace the
index.
Pros: the database is always restored identically to what was in the
original.
Con: The index hierarchy might be "partial", that is, lack a
component index on some partition.
2. David's: use CREATE INDEX ON <partition>, followed by CREATE INDEX ON
<parent>. This will use the matching mechanism to automatically
attach the index on partition to index on parent. If some partition
lacks a matching index, one is created automatically by the creation
on parent.
If you want to replace the index on a partition, use a new (as yet
unimplemented) ALTER INDEX REPLACE.
No need to add ONLY to the table name in CREATE INDEX, since the
command always recurses. (This seems good to me, because I
Pro: the index is never "partial" (missing a partition).
Con: the matching mechanism might choose a different index on restore
than what was selected in the database being dumped.
3. Robert's: use CREATE INDEX ON ONLY <parent>, which creates a shell
index on parent only (no recursion), followed by CREATE INDEX ON
<partition>. DETACH is not provided. If you ATTACH an index for a
partition that already has one index attached, then (1) the newly
attached one replaces the original (i.e. effectively REPLACE) or (2)
you get an error and we implement a separate ALTER INDEX REPLACE
command. It's not clear to me how or when the shell index becomes a
real index.
Robert, can you please clarify the terms of your proposal? How is it
better than mine? Is David's concern about a "partial" index (i.e. an
index that doesn't exist in some partition) solved by it?
I have code for proposals 1 and 2. I don't like proposal 2, and David &
Ashutosh don't like (1). Maybe if we all understand (3) we can agree on
using that one?
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: alvherre@alvh.no-ip.org, robertmhaas@gmail.com, david.rowley@2ndquadrant.com, simon@2ndquadrant.com, milyutinma@gmail.com
Subject: Re: [HACKERS] Proposal: Local indexes for partitioned table
In-Reply-To: <20171215210253.dhixou5smlw27kos@alvherre.pgsql>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox