From: ProPAAS DBA <dba@propaas.com>
To: pgsql-performance@postgresql.org
Subject: Re: why we do not create indexes on master
Date: Tue, 27 Dec 2016 10:43:24 -0700
Message-ID: <6b423221-99e3-3a5a-cd92-952821eb0d7e@propaas.com> (raw)
In-Reply-To: <9C72034F-A02B-41F8-A1F3-654815087D88@mail.ru>
References: <01F901FD-F29D-4A46-ACB4-4E6FD75EE7E6@mail.ru>
<CAKFQuwbReSO7XxNm8rsi4PfWJ4h49qipiNbtQD_ntGYHw03Onw@mail.gmail.com>
<9C72034F-A02B-41F8-A1F3-654815087D88@mail.ru>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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 <mailto:david.g.johnston@gmail.com>> wrote:
>>
>> On Tue, Dec 27, 2016 at 8:22 AM, Valerii Valeev
>> <valerii.valeev@mail.ru <mailto: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.
>>
>
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-performance@postgresql.org
Cc: dba@propaas.com
Subject: Re: why we do not create indexes on master
In-Reply-To: <6b423221-99e3-3a5a-cd92-952821eb0d7e@propaas.com>
* 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