Hi Alex, sorry, I missed your response somehow, got it only with today’s digest.
Thanks for the hint. I have basic idea how to investigate query perf issues. I thought maybe I miss some understanding of the grounds. It’ll take some time to get enough test data and I’ll try to take a look myself and come back after that. Regards, Val. > From: Andreas Kretschmer <akretsch...@spamfence.net > <mailto:akretsch...@spamfence.net>> > Subject: Re: why we do not create indexes on master > Date: Dec 27 2016 19:04:27 GMT+3 > To: pgsql-performance@postgresql.org <mailto:pgsql-performance@postgresql.org> > > > Valerii Valeev <valerii.val...@mail.ru <mailto:valerii.val...@mail.ru>> wrote: > >> 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 >> <http://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. > > please show us explain analyse with/without index on master. > > > > Regards, Andreas Kretschmer > -- > Andreas Kretschmer > http://www.2ndQuadrant.com/ <http://www.2ndquadrant.com/> > PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services