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

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.

主站蜘蛛池模板: 武威市| 兰溪市| 永嘉县| 岗巴县| 和田县| 万宁市| 南和县| 长宁区| 武汉市| 古交市| 阿巴嘎旗| 英德市| 平原县| 太白县| 瑞金市| 永仁县| 万全县| 西藏| 黎平县| 德钦县| 巍山| 五莲县| 环江| 新泰市| 龙海市| 江安县| 耿马| 禹城市| 五原县| 湖南省| 德钦县| 扎鲁特旗| 内黄县| 墨玉县| 吉水县| 徐州市| 手游| 夏河县| 儋州市| 阿勒泰市| 资源县|