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

can anyone please explain, why we do not create indexes on master?

In my case master / child design blindly follows partitioning guide https://www.postgresql.org/docs/9.6/static/ddl-partitioning.html.

My collaborator was unhappy with performance of queries over master table with filtering by one of fields

SELECT * FROM “master" WHERE “field" BETWEEN x AND y

(there are indexes for “field” on child tables).

He has created index on master once and found that the query returns 100x faster.

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.

I’m curious is it right or it’s something less primitive.

Thanks and have a happy holidays!

Val.