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.johns...@gmail.com <mailto:david.g.johns...@gmail.com>> wrote:
On Tue, Dec 27, 2016 at 8:22 AM, Valerii Valeev
<valerii.val...@mail.ru <mailto:valerii.val...@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.