Re: [PERFORM] why we do not create indexes on master - Mailing list pgsql-performance

From David G. Johnston
Subject Re: [PERFORM] why we do not create indexes on master
Date
Msg-id CAKFQuwbReSO7XxNm8rsi4PfWJ4h49qipiNbtQD_ntGYHw03Onw@mail.gmail.com
Whole thread Raw
In response to [PERFORM] why we do not create indexes on master  (Valerii Valeev <valerii.valeev@mail.ru>)
Responses Re: [PERFORM] why we do not create indexes on master  (Valerii Valeev <valerii.valeev@mail.ru>)
List pgsql-performance
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.

pgsql-performance by date:

Previous
From: Andreas Kretschmer
Date:
Subject: Re: [PERFORM] why we do not create indexes on master
Next
From: Valerii Valeev
Date:
Subject: Re: [PERFORM] why we do not create indexes on master