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

  • Mastering PostgreSQL 9.6
  • Hans Jurgen Schonig
  • 172字
  • 2021-07-09 19:57:18

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.

主站蜘蛛池模板: 宝坻区| 扶绥县| 巴林右旗| 江陵县| 聊城市| 金门县| 黄冈市| 禹城市| 六安市| 土默特右旗| 大埔县| 陈巴尔虎旗| 门头沟区| 临沧市| 溆浦县| 扶沟县| 枣强县| 淮安市| 岢岚县| 浦北县| 松原市| 石屏县| 四平市| 孟州市| 黄大仙区| 西平县| 德保县| 大竹县| 修武县| 泽普县| 吉隆县| 加查县| 织金县| 沙湾县| 吴忠市| 宁南县| 禹州市| 南阳市| 福州市| 闽侯县| 五峰|