Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1cLtbi-00044V-N2 for pgsql-performance@arkaria.postgresql.org; Tue, 27 Dec 2016 15:24:07 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1cLtbg-0003qs-VC for pgsql-performance@arkaria.postgresql.org; Tue, 27 Dec 2016 15:24:05 +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 ) id 1cLta1-0001hQ-IC for pgsql-performance@postgresql.org; Tue, 27 Dec 2016 15:22:21 +0000 Received: from smtp37.i.mail.ru ([94.100.177.97]) by magus.postgresql.org with esmtps (TLS1.0:DHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1cLtZy-0007fu-9G for pgsql-performance@postgresql.org; Tue, 27 Dec 2016 15:22:20 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=mail.ru; s=mail2; h=Mime-Version:To:Date:Message-Id:Subject:Content-Type:From; bh=wBk2abZoYav+wOBT4LqmJPgmhSSjqnp25Q6BBi6IKFc=; b=U3DV8Da8vuPhGJC+2iiSidCzfb3W9Tzsrk6jPa9rRkft++WD7kMhXsudvps70auun8RBDVXYcDshZ/h4ozOBqOu9H02afWvxxk+44HSsApARDoDzpfCV1ib9fISer0QXQaFMApn++4w1EdR6DlXMNECkDIWjT4w5dZrn2w9Eh8o=; Received: from [93.185.29.123] (port=32334 helo=[172.16.17.13]) by smtp37.i.mail.ru with esmtpa (envelope-from ) id 1cLtZw-0005E5-FJ for pgsql-performance@postgresql.org; Tue, 27 Dec 2016 18:22:16 +0300 From: Valerii Valeev Content-Type: multipart/alternative; boundary="Apple-Mail=_1CBCC5ED-C9C7-4A57-9D34-3A8AF84BE012" Subject: why we do not create indexes on master Message-Id: <01F901FD-F29D-4A46-ACB4-4E6FD75EE7E6@mail.ru> Date: Tue, 27 Dec 2016 18:22:13 +0300 To: pgsql-performance@postgresql.org Mime-Version: 1.0 (Mac OS X Mail 8.2 \(2104\)) X-Mailer: Apple Mail (2.2104) Authentication-Results: smtp37.i.mail.ru; auth=pass smtp.auth=valerii.valeev@mail.ru smtp.mailfrom=valerii.valeev@mail.ru X-E1FCDC63: 805855F38AB0EDAFA10C1C8B4409CD2BADAB024B4846C063 X-E1FCDC64: 52AF5D3749EB5DA2B8796B85D6C03F7F8A04ACA7EDA16C81F80852C806DE70FA X-Mailru-Sender: EC7457530F4C53DB766EE7680B7DDE70A01B32C8A1FDA02D1E36167F49580AA316D75C8C6ECDF8B7C5C7FD16981B76AE X-Mras: OK X-Pg-Spam-Score: -2.7 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org --Apple-Mail=_1CBCC5ED-C9C7-4A57-9D34-3A8AF84BE012 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 Dear colleagues, can anyone please explain, why we do not create indexes on master? In my case master / child design blindly follows partitioning guide = https://www.postgresql.org/docs/9.6/static/ddl-partitioning.html = . My collaborator was unhappy with performance of queries over master = table with filtering by one of fields SELECT * FROM =E2=80=9Cmaster" WHERE =E2=80=9Cfield" BETWEEN x = AND y (there are indexes for =E2=80=9Cfield=E2=80=9D on child tables). He has created index on master once and found that the query returns = 100x faster. I have naive idea that it won=E2=80=99t help if index is created before = the data is there =E2=80=94 i.e. indexes on master aren=E2=80=99t = updated when data loaded to child table. I=E2=80=99m curious is it right or it=E2=80=99s something less = primitive. Thanks and have a happy holidays! Val.= --Apple-Mail=_1CBCC5ED-C9C7-4A57-9D34-3A8AF84BE012 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8 Dear colleagues,

can anyone please explain, why we do not create indexes on = master?
In my case master / child design blindly = follows partitioning guide https://www.postgresql.org/docs/9.6/static/ddl-partitioning.htm= l.
My collaborator was unhappy with performance = of queries over master table with filtering by one of fields

SELECT * = FROM =E2=80=9Cmaster" WHERE =E2=80=9Cfield" BETWEEN x AND y

(there are indexes for = =E2=80=9Cfield=E2=80=9D on child tables).
He has = created index on master once and found that the query returns 100x = faster.
I have naive idea that it won=E2=80=99t = help if index is created before the data is there  =E2=80=94 i.e. = indexes on master aren=E2=80=99t updated when data loaded to child = table.
I=E2=80=99m curious is it right or it=E2=80=99= s something less primitive.

Thanks and have a happy holidays!
Val.
= --Apple-Mail=_1CBCC5ED-C9C7-4A57-9D34-3A8AF84BE012--