Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtp (Exim 4.84_2)
	(envelope-from <pgsql-hackers-owner+archive@lists.postgresql.org>)
	id 1ePx8e-0005Un-Rc
	for pgsql-hackers@arkaria.postgresql.org; Fri, 15 Dec 2017 21:03:24 +0000
Received: from localhost ([127.0.0.1] helo=malur.postgresql.org)
	by malur.postgresql.org with esmtp (Exim 4.84_2)
	(envelope-from <pgsql-hackers-owner+archive@lists.postgresql.org>)
	id 1ePx8e-00051G-7E
	for pgsql-hackers@arkaria.postgresql.org; Fri, 15 Dec 2017 21:03:24 +0000
Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29])
	by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256)
	(Exim 4.84_2)
	(envelope-from <alvherre@alvh.no-ip.org>)
	id 1ePx8e-000515-0j
	for pgsql-hackers@lists.postgresql.org; Fri, 15 Dec 2017 21:03:24 +0000
Received: from smtprelay0048.b.hostedemail.com ([64.98.42.48]
 helo=smtprelay.b.hostedemail.com)
	by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256)
	(Exim 4.89)
	(envelope-from <alvherre@alvh.no-ip.org>)
	id 1ePx8U-0002gQ-Jp
	for pgsql-hackers@postgresql.org; Fri, 15 Dec 2017 21:03:23 +0000
Received: from filter.hostedemail.com (10.5.19.248.rfc1918.com [10.5.19.248])
	by smtprelay02.b.hostedemail.com (Postfix) with ESMTP id C8859D3995;
	Fri, 15 Dec 2017 21:02:56 +0000 (UTC)
X-Session-Marker: 616C76686572726540616C76682E6E6F2D69702E6F7267
X-Spam-Summary: 
 50,0,0,,d41d8cd98f00b204,alvherre@alvh.no-ip.org,:::::::::,RULES_HIT:41:355:379:599:960:967:973:988:989:1260:1277:1311:1312:1313:1314:1345:1359:1437:1515:1516:1518:1519:1534:1542:1593:1594:1595:1596:1711:1730:1747:1777:1792:2393:2525:2560:2563:2682:2685:2692:2693:2828:2859:2933:2937:2939:2942:2945:2947:2951:2954:3022:3138:3139:3140:3141:3142:3354:3865:3866:3867:3868:3870:3871:3872:3873:3874:3934:3936:3938:3941:3944:3947:3950:3953:3956:3959:4250:4605:5007:6261:7809:7903:8784:9025:9121:10004:10394:10400:10848:11658:11914:12296:12663:12679:13053:13439:13894:13895:21080:21220:21627:30012:30029:30054:30056:30069,0,RBL:none,CacheIP:none,Bayesian:0.5,0.5,0.5,Netcheck:none,DomainCache:0,MSF:not
 bulk,SPF:,MSBL:0,DNSBL:none,Custom_rules:0:0:0,LFtime:2,LUA_SUMMARY:none
X-HE-Tag: help11_70f22a9b28836
X-Filterd-Recvd-Size: 3414
Received: from alvin.alvh.no-ip.org (unknown [179.57.113.18])
	(Authenticated sender: alvherre@alvh.no-ip.org)
	by omf12.b.hostedemail.com (Postfix) with ESMTPA;
	Fri, 15 Dec 2017 21:02:56 +0000 (UTC)
Received: by alvin.alvh.no-ip.org (Postfix, from userid 1000)
	id F1DBC90F; Fri, 15 Dec 2017 18:02:53 -0300 (-03)
Date: Fri, 15 Dec 2017 18:02:53 -0300
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: Robert Haas <robertmhaas@gmail.com>
Cc: David Rowley <david.rowley@2ndquadrant.com>,
	Simon Riggs <simon@2ndquadrant.com>,
	Pg Hackers <pgsql-hackers@postgresql.org>,
	Maksim Milyutin <milyutinma@gmail.com>
Subject: Re: [HACKERS] Proposal: Local indexes for partitioned table
Message-ID: <20171215210253.dhixou5smlw27kos@alvherre.pgsql>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: 
 <CA+TgmoZCgu0ZcZbS=V+fHwYw+n_q4fRGnkOumJq88ne4vJnEyA@mail.gmail.com>
User-Agent: NeoMutt/20170306-137-4415bd-dirty (1.8.0)
List-Id: <pgsql-hackers.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-hackers@lists.postgresql.org>
List-Owner: <mailto:pgsql-hackers-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-hackers>
Precedence: bulk

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