Re: why we do not create indexes on master

[Valerii Valeev <valerii.valeev@xxxxxxx>]

David,

thanks a lot for the comments and for clarity. As I already responded to Andreas, I’m going to get some test data and try to investigate myself.

Thought maybe I’m missing some common knowledge, that’s why asked here before taking deeper look.

Regards,

Val.

 

On Dec 27 2016, at 20:48, David G. Johnston <david.g.johnston@xxxxxxxxx> wrote:

On Tue, Dec 27, 2016 at 10:38 AM, Valerii Valeev <valerii.valeev@xxxxxxx> 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

​Only somewhat helpful...

​

- 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

​All retrieved data now exists in cache/buffers...

 

- after that my mate adds index on “master”(“field”) — again, all data is in child tables

- same query takes under 1sec

​As ​Andreas said if you really want to explore what is happening here you need to use EXPLAIN ANALYZE.

Given the flow described above I/O retrieval performance differences, or the attempt to query the table kicking off an ANALYZE, seems like possible contributing factors.

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? 

​No

​

- Q2: is it correct that index on master created before inserting record to child tables will not take into account this record? 

Yes

​

- Q3: are there any other bad sides of indexes on master table?

No​

David J.