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

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.

主站蜘蛛池模板: 靖宇县| 西吉县| 高平市| 铁力市| 准格尔旗| 马边| 集贤县| 麟游县| 莆田市| 宁蒗| 桐柏县| 丰都县| 奉贤区| 灵璧县| 京山县| 策勒县| 柞水县| 南阳市| 苏尼特左旗| 顺义区| 盈江县| 从江县| 桃江县| 玛多县| 泌阳县| 图们市| 屏南县| 栾川县| 海原县| 新闻| 昆山市| 漾濞| 诸城市| 同江市| 大港区| 屏东县| 垦利县| 茶陵县| 综艺| 化隆| 汝城县|