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 

> 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.

Laurenz Albe
Cybertec |

Reply via email to