Re: Performance degrade in Planning Time to find appropriate Partial Index

2018-03-02 15:29 GMT+01:00 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>:

>
>
> 2018-03-02 14:49 GMT+01:00 Nandakumar M <m(dot)nanda92(at)gmail(dot)com>:
>
>> Hi,
>>
>> https://heapanalytics.com/blog/engineering/running-10-millio
>> n-postgresql-indexes-in-production
>>
>> From the link shared above, it looks like what Meenatchi has done should
>> work.
>>
>
> It can be different situation, there are not specified indexes per table.
> And if some projects works, it doesn't mean, so they are well designed.
>
> PostgreSQL has not column storage. Look on column databases. They are
> designed for extra wide tables.
>

read the article:

1. Probably they use Citus

2. Since partial indexes are so easy to create and work with, we’ve wound
up with over 10 million partial indexes across our entire cluster.

> Regards
>
> Pavel
>
>
>>
>> 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
>> >
>>
>>
>