2018-03-02 15:29 GMT+01:00 Pavel Stehule <pavel.steh...@gmail.com>: > > > 2018-03-02 14:49 GMT+01:00 Nandakumar M <m.nand...@gmail.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.a...@cybertec.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 >> > >> >> >
