2018-03-02 15:29 GMT+01:00 Pavel Stehule <pavel.stehule@xxxxxxxxx>:
2018-03-02 14:49 GMT+01:00 Nandakumar M <m.nanda92@xxxxxxxxx>:Hi,
https://heapanalytics.com/blog/engineering/running-10-millio n-postgresql-indexes-in-produc tion
>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.
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.
RegardsPavel
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
>
