官术网_书友最值得收藏!

Combined indexes

In my job as a professional PostgreSQL support vendor, I am often asked about the difference between a combined and individual indexes. In this section, I will try to shed some light on this question.

The general rule is this: if a single index can answer your question, it is usually the best choice. However, you cannot index all possible combinations of fields people are filtering on. What you can do is use the properties of combined indexes to achieve as much gain as possible.

Let us suppose we have a table containing three columns: postal_code, last_name, and first_name. A telephone book would make use of a combined index like that. You will see that data is ordered by location. Within the same location, data will be sorted by last name and first name.

The following table will show which operations are possible given the three column index:

If columns are indexes separately, you will most likely end up seeing bitmap scans. Of course, a single hand-tailored index is better.

主站蜘蛛池模板: 互助| 中超| 四子王旗| 政和县| 桐庐县| 濮阳市| 姚安县| 文登市| 宁城县| 叶城县| 尼玛县| 通州区| 乌海市| 新龙县| 宁都县| 红原县| 清丰县| 怀来县| 成都市| 闻喜县| 吉林省| 泰安市| 定南县| 遂川县| 即墨市| 衡阳市| 新野县| 巩留县| 额济纳旗| 扎鲁特旗| 改则县| 信宜市| 上林县| 陆良县| 鹰潭市| 久治县| 南川市| 康马县| 湖北省| 泽库县| 大余县|