Re: Performance degrade in Planning Time to find appropriate Partial Index

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