[PERFORM] why we do not create indexes on master

От: Valerii Valeev
Тема: [PERFORM] why we do not create indexes on master
Дата: ,
Msg-id: 01F901FD-F29D-4A46-ACB4-4E6FD75EE7E6@mail.ru
(см: обсуждение, исходный текст)
Ответы: Re: [PERFORM] why we do not create indexes on master  ("David G. Johnston")
Re: [PERFORM] why we do not create indexes on master  (Andreas Kretschmer)
Список: pgsql-performance

Скрыть дерево обсуждения

[PERFORM] why we do not create indexes on master  (Valerii Valeev, )
 Re: [PERFORM] why we do not create indexes on master  ("David G. Johnston", )
  Re: [PERFORM] why we do not create indexes on master  (Valerii Valeev, )
   Re: [PERFORM] why we do not create indexes on master  (ProPAAS DBA, )
   Re: [PERFORM] why we do not create indexes on master  ("David G. Johnston", )
    Re: [PERFORM] why we do not create indexes on master  (Valerii Valeev, )
 Re: [PERFORM] why we do not create indexes on master  (Andreas Kretschmer, )

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.
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.

В списке pgsql-performance по дате сообщения:

От: "David G. Johnston"
Дата:
Сообщение: Re: [PERFORM] why we do not create indexes on master
От: Flávio Henrique
Дата:
Сообщение: [PERFORM] Slow query after 9.3 to 9.6 migration