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
