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
