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

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.

主站蜘蛛池模板: 西安市| 金坛市| 海兴县| 新民市| 大余县| 株洲县| 和顺县| 泊头市| 繁昌县| 上饶市| 库尔勒市| 永福县| 肇源县| 昆山市| 筠连县| 视频| 舟曲县| 安化县| 安康市| 灵石县| 东乡族自治县| 方山县| 公主岭市| 祁阳县| 米易县| 诸城市| 桐城市| 阿瓦提县| 沾益县| 虹口区| 陵水| 固原市| 神木县| 北辰区| 寿宁县| 石家庄市| 华坪县| 黄冈市| 灵寿县| 同江市| 库伦旗|