Hi, https://heapanalytics.com/blog/engineering/running-10-million-postgresql-indexes-in-production >From the link shared above, it looks like what Meenatchi has done should work. Do the conditions on the partial index and query match exactly? ( greater than / greater than equals mismatch maybe?) If conditions for those partial indexes are mutually exclusive and the query has a matching condition then Postgres can use that index alone. Are we missing something here? Regards, Nanda On Thu, Mar 1, 2018 at 6:33 PM, Laurenz Albe <laurenz.albe@xxxxxxxxxxx> wrote: > 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 >
