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