Meenatchi Sandanam wrote:
> I have created a table with 301 columns(ID, 150 BIGINT, 150 TEXT). The table
> 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
> the Indexes created for that particular form ID Range. This degrades Planner
> 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.
Cybertec | https://www.cybertec-postgresql.com