Re: why we do not create indexes on master

On Tue, Dec 27, 2016 at 10:38 AM, Valerii Valeev <valerii(dot)valeev(at)mail(dot)ru>
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.