Reasonable amount of indices
От | Patric |
---|---|
Тема | Reasonable amount of indices |
Дата | |
Msg-id | 46E05E4D.3010003@p-dw.com обсуждение исходный текст |
Ответы |
Re: Reasonable amount of indices
|
Список | pgsql-performance |
Hi, I've a question about amount of indices. I explain my issue based on an example: Table which contains person information, one row per person. There will be lots of SELECTS doing search by special criteria, e.g.: Age, Gender. Now there will be 4 User groups which will select on the table: Group 1) Always doing reads on specific continents. Group 2) Always doing reads in specific country. Group 3) Always doing reads in specific region within a country. Group 4) Always doing reads in specific city. I 'm indexing the the important attributes. Would be about 5 to 6 independent indexes. As there will be millions of rows, quite a lot of hits will be returned, I guess it will generate big bitmaps to calculate the intersection of the indices. Ok to prevent this from happening I'd wanted to create 4 Indexes per attribute, with special predicate, so users which only query for a country don't scan an index which indexed the entire globe: e.g .. CREATE index BlaBla_city on table tblusers(dtage) WHERE dtcity='London'; CREATE index BlaBla_country on table tblusers(dtage) WHERE dtcountry='uk'; CREATE index BlaBla_continent on table tblusers(dtage) WHERE dtcontinent='europe'; etc. SELECT * FROM tblusers WHERE dtcontinent='europe' and age='23' would then postgres lead to use the special index made for europe. Now that I've 4 Indexes. an Insert or update will lead to some more overhead, but which would be ok. My Question now is: Is it wise to do so, and create hundreds or maybe thousands of Indices which partition the table for the selections. Does postgres scale good on the selecton of indices or is the predicate for indices not layed out for such a usage? (PS: Don't want partition with method postgres offers..) thanks in advance, patric
В списке pgsql-performance по дате отправления: