From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Meenatchi Sandanam <meen.opm@gmail.com>
To: pgsql-performance@lists.postgresql.org
Subject: Re: Performance degrade in Planning Time to find appropriate Partial Index
Date: Thu, 01 Mar 2018 14:03:28 +0100
Message-ID: <1519909408.6586.5.camel@cybertec.at> (raw)
In-Reply-To: <CAJJu9dMg3662E-wR25ZyAJwVdG7Cz56r-8Bw4ejfT9i42Oo2LA@mail.gmail.com>
References: <CAJJu9dMg3662E-wR25ZyAJwVdG7Cz56r-8Bw4ejfT9i42Oo2LA@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: laurenz.albe@cybertec.at, meen.opm@gmail.com, pgsql-performance@lists.postgresql.org
Subject: Re: Performance degrade in Planning Time to find appropriate Partial Index
In-Reply-To: <1519909408.6586.5.camel@cybertec.at>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox