Date: Tue, 27 Dec 2016 17:04:27 +0100
From: Andreas Kretschmer <akretschmer@spamfence.net>
To: pgsql-performance@postgresql.org
Subject: Re: why we do not create indexes on master
Message-ID: <20161227160427.GA2843@tux>
References: <01F901FD-F29D-4A46-ACB4-4E6FD75EE7E6@mail.ru>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <01F901FD-F29D-4A46-ACB4-4E6FD75EE7E6@mail.ru>
User-Agent: Mutt/1.5.18 (2008-05-17)
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

Valerii Valeev <valerii.valeev@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.
> 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/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance