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

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-
> million-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.

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