From: | Nandakumar M <m(dot)nanda92(at)gmail(dot)com> |
---|---|
To: | Meenatchi Sandanam <meen(dot)opm(at)gmail(dot)com> |
Cc: | Pgsql Performance <pgsql-performance(at)lists(dot)postgresql(dot)org>, Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> |
Subject: | Re: Performance degrade in Planning Time to find appropriate Partial Index |
Date: | 2018-03-02 13:49:28 |
Message-ID: | CANcFUu5n7KQgeSm_jwVUL6pW=jiFPcvvyS655S+1FY03kEu5Nw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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(dot)albe(at)cybertec(dot)at> 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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Pavel Stehule | 2018-03-02 14:29:52 | Re: Performance degrade in Planning Time to find appropriate Partial Index |
Previous Message | Moreno Andreo | 2018-03-01 14:39:46 | Re: Performance degrade in Planning Time to find appropriate Partial Index |