Dear colleagues,
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
<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.
