Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1cLvmh-00013V-62 for pgsql-performance@arkaria.postgresql.org; Tue, 27 Dec 2016 17:43:35 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1cLvmg-0002yc-MB for pgsql-performance@arkaria.postgresql.org; Tue, 27 Dec 2016 17:43:34 +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 1cLvmg-0002yU-2r for pgsql-performance@postgresql.org; Tue, 27 Dec 2016 17:43:34 +0000 Received: from mail-io0-x230.google.com ([2607:f8b0:4001:c06::230]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1cLvmb-0001mA-0b for pgsql-performance@postgresql.org; Tue, 27 Dec 2016 17:43:33 +0000 Received: by mail-io0-x230.google.com with SMTP id d9so317391452ioe.0 for ; Tue, 27 Dec 2016 09:43:28 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=propaas-com.20150623.gappssmtp.com; s=20150623; h=subject:to:references:from:message-id:date:user-agent:mime-version :in-reply-to; bh=t/vW9JeYanFIDr0zDVtWzRjkN9sDlJvbcnwuOt+9R+U=; b=A1gNMRwlSdS2hSgSmwLkScHAlKi0HMt0/3IlA5qNi4jKd0SMBPrUAB9xLSL2LlLSjz 4jA0yAC4bR4A4Ww4CY+MDSvR1dr16zwELOLLKMgylBh2rF+fOhBJhMflDNbmlh8YF7Ox jBjs/sj28JJu6LwXlz0/4tKZMzgnYfup33wXc1V2jK3iMjMYYh4b9dv6u6s31LRaBgMA tp4EBMHeTVuNlZUbB/jewhy4o5xX1WVwgilIxgvzmuVJ5iQegs3ZXipZiUVwlOkGidZD cZ0bUQQw8zRwRleZtJAxd0ghdYTS6SECyhz4lP+CoBZXdQ2fib6wTDfk/5ajsTbRnbqe ALhg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:subject:to:references:from:message-id:date :user-agent:mime-version:in-reply-to; bh=t/vW9JeYanFIDr0zDVtWzRjkN9sDlJvbcnwuOt+9R+U=; b=tjeEIjol1UQ8g2uvi4XAb5xd7d6uwolh4COugKBO7gg5zh8XjzqE6C4LJlJy8IpYxm 84jyUdUUbdC0DCiUDSh1pJvWT3rjbH14jhR0RHYeDntchygGEPLfantKDPq824B0xHSN wbFEubybRCKOPKbZzPWCeMQbHrHIIiCThvlD0VWDoqU7DmrOIycJ4/5LQkX0LGWVMV3o 07N3dX9Fy8ff4KddSlgESS6hoSaDmBs8UBOXrtSZK+7NynLuYISKvSIb3lH002x5A4av BHWlmxZD1d0qKnkUEWU0oCjPcXhhtWHyYdRXg4q7kEZddIJgS4CsQ2nOpf9JyYHt/fbA U0WA== X-Gm-Message-State: AIkVDXLuOasyyoUaEcNmuSHFdyE2fTsFr44rCazhJR00MJOKST6Oq4uGRMSWc9h6I5oRbA== X-Received: by 10.107.37.81 with SMTP id l78mr18656071iol.126.1482860606294; Tue, 27 Dec 2016 09:43:26 -0800 (PST) Received: from [192.168.1.17] (c-24-9-41-137.hsd1.co.comcast.net. [24.9.41.137]) by smtp.gmail.com with ESMTPSA id d25sm11391941ioj.25.2016.12.27.09.43.25 for (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Tue, 27 Dec 2016 09:43:25 -0800 (PST) Subject: Re: why we do not create indexes on master To: pgsql-performance@postgresql.org References: <01F901FD-F29D-4A46-ACB4-4E6FD75EE7E6@mail.ru> <9C72034F-A02B-41F8-A1F3-654815087D88@mail.ru> From: ProPAAS DBA Message-ID: <6b423221-99e3-3a5a-cd92-952821eb0d7e@propaas.com> Date: Tue, 27 Dec 2016 10:43:24 -0700 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:45.0) Gecko/20100101 Thunderbird/45.5.1 MIME-Version: 1.0 In-Reply-To: <9C72034F-A02B-41F8-A1F3-654815087D88@mail.ru> Content-Type: multipart/alternative; boundary="------------2EABEA16E9313405803C96F3" X-Pg-Spam-Score: -2.6 (--) 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 This is a multi-part message in MIME format. --------------2EABEA16E9313405803C96F3 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit Possibly some buffer caching is happening, what happens if you then remove the added index and run the query again? On 12/27/2016 10:38 AM, Valerii Valeev wrote: > Thank you David, > > I used same rationale to convince my colleague — it didn’t work :) > Sort of “pragmatic” person who does what seems working no matter what > happens tomorrow. > So I’m seeking for better understanding of what's happening to have > other cause to convince him. > > Let me break it down once again. The experience is as follows: > > - partitioning follows the guide > - master empty, no indexes > - child tables have index on field “field” > - query like > SELECT * FROM “master” WHERE “field” BETWEEN ‘1' AND ‘2’ > takes more than 100 sec > - after that my mate adds index on “master”(“field”) — again, all data > is in child tables > - same query takes under 1sec > > Questions I’d love to clarify: > > - Q1: is it correct that described situation happens because index > created on master does account data that is already there in child? > - Q2: is it correct that index on master created before inserting > record to child tables will not take into account this record? > - Q3: are there any other bad sides of indexes on master table? > > Regards, > Val. > >> On Dec 27 2016, at 19:19, David G. Johnston >> > wrote: >> >> On Tue, Dec 27, 2016 at 8:22 AM, Valerii Valeev >> >wrote: >> >> I have naive idea that it won’t help if index is created before >> the data is there — i.e. indexes on master aren’t updated when >> data loaded to child table. >> >> >> ​Indexes on the master table of a partition scheme never reflect the >> contents of child​ tables. >> >> In most partitioning schemes the master table is empty so even if it >> doesn't have an index on a particular field execution would typically >> be quick. This is why #4 on the page you linked to: >> >> """ >> For each partition, create an index on the key column(s), as well as >> any other indexes you might want. (The key index is not strictly >> necessary, but in most scenarios it is helpful. If you intend the key >> values to be unique then you should always create a unique or >> primary-key constraint for each partition.) >> """ >> >> doesn't say anything about creating other indexes on the master >> table. See #1 in that list for an explicit statement of this assumption. >> >> If the master is not empty, and of considerable size, and the field >> being searched is not indexed, then it is unsurprising that the query >> would take a long time to execute when obtaining rows from the master >> table. If this is the case then you've gotten away from the expected >> usage of partitions and so need to do things that aren't in the >> manual to make them work. >> >> David J. >> >> >> >> David J. >> > --------------2EABEA16E9313405803C96F3 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit

Possibly some buffer caching is happening, what happens if you then remove the added index and run the query again?



On 12/27/2016 10:38 AM, Valerii Valeev wrote:
Thank you David,

I used same rationale to convince my colleague — it didn’t work :)
Sort of “pragmatic” person who does what seems working no matter what happens tomorrow.
So I’m seeking for better understanding of what's happening to have other cause to convince him.

Let me break it down once again. The experience is as follows:

- partitioning follows the guide
- master empty, no indexes
- child tables have index on field “field”
- query like
SELECT * FROM “master” WHERE “field” BETWEEN ‘1' AND ‘2’
takes more than 100 sec
- after that my mate adds index on “master”(“field”) — again, all data is in child tables
- same query takes under 1sec

Questions I’d love to clarify:

- Q1: is it correct that described situation happens because index created on master does account data that is already there in child?
- Q2: is it correct that index on master created before inserting record to child tables will not take into account this record?
- Q3: are there any other bad sides of indexes on master table?

Regards,
Val.

On Dec 27 2016, at 19:19, David G. Johnston <david.g.johnston@gmail.com> wrote:

On Tue, Dec 27, 2016 at 8:22 AM, Valerii Valeev <valerii.valeev@mail.ru> wrote:
I have naive idea that it won’t help if index is created before the data is there  — i.e. indexes on master aren’t updated when data loaded to child table.

​Indexes on the master table of a partition scheme never reflect the contents of child​ tables.

In most partitioning schemes the master table is empty so even if it doesn't have an index on a particular field execution would typically be quick.  This is why #4 on the page you linked to:

"""
For each partition, create an index on the key column(s), as well as any other indexes you might want. (The key index is not strictly necessary, but in most scenarios it is helpful. If you intend the key values to be unique then you should always create a unique or primary-key constraint for each partition.)
"""

doesn't say anything about creating other indexes on the master table.  See #1 in that list for an explicit statement of this assumption.

If the master is not empty, and of considerable size, and the field being searched is not indexed, then it is unsurprising that the query would take a long time to execute when obtaining rows from the master table.  If this is the case then you've gotten away from the expected usage of partitions and so need to do things that aren't in the manual to make them work.

David J.



David J.



--------------2EABEA16E9313405803C96F3--