Message-ID: <1519909408.6586.5.camel@cybertec.at>
Subject: Re: Performance degrade in Planning Time to find appropriate
 Partial Index
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Meenatchi Sandanam <meen.opm@gmail.com>,
	pgsql-performance@lists.postgresql.org
Date: Thu, 01 Mar 2018 14:03:28 +0100
In-Reply-To: 
 <CAJJu9dMg3662E-wR25ZyAJwVdG7Cz56r-8Bw4ejfT9i42Oo2LA@mail.gmail.com>
References: 
	<CAJJu9dMg3662E-wR25ZyAJwVdG7Cz56r-8Bw4ejfT9i42Oo2LA@mail.gmail.com>
Organization: Cybertec
Content-Type: text/plain; charset="UTF-8"
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit
Precedence: bulk

Meenatchi Sandanam wrote:
> I have created a table with 301 columns(ID, 150 BIGINT, 150 TEXT). The table contains
> multiple form data differentiated by ID range. Hence a column contains more than one form data.
> To achieve Unique Constraint and Indexing per form, I chose PostgreSQL Partial Indexes
> which suits my requirement. I have created Partial Indexes with ID Range as criteria and
> it provides Uniqueness and Indexing per form basis as expected. But DML operations on a
> particular form scans all the Indexes created for the entire table instead of scanning
> the Indexes created for that particular form ID Range. This degrades Planner Performance
> and Query Time more than 10 times as below, 
> 
> Query Result for the table with 3000 Partial Indexes(15 Indexes per form) : 

It is crazy to create 3000 partial indexes on one table.

No wonder planning and DML statements take very long, they have to consider all the
indexes.

> explain analyse select id from form_data_copy where id between 3001 and 4000 and bigint50=789;

Use a single index on (bigint50, id) for best performance.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com