我有一个具有以下架构的postgres表
Table "public.myTable" Column | Type | Modifiers ----------- +--------------------------+----------- serial_number | character varying(255) | name | character varying(255) | Designation | character varying(255) | place | character varying(255) | timeOfJoining | timestamp with time zone | timeOfLeaving | timestamp with time zone | Indexes: "name_Designation_place" btree (name, Designation, place) "Designation_place_name" btree (Designation, place, name) "Designation_name_place" btree (Designation, name, place) "timeOfJoining_timeOfLeaving" btree (timeOfJoining, timeOfLeaving) "timeOfJoining_timeOfLeaving" btree (timeOfJoining, timeOfLeaving)
现在,当我运行表单查询时:
explain analyze select place from myTable where Designation='Manager' and timeOfJoining>'1930-10-10';
我正在制定以下计划:
Index Scan using Designation_place_name on myTable (cost=0.00..67701.36 rows=22043 width=27) (actual time=0.061..3.796 rows=3376 loops=1) Index Cond: ((relation)::text = 'Manager'::text) Filter: (timeOfJoining > '1930-10-10 00:00:00+05:53:20'::timestamp with time zone) Total runtime: 4.082 ms (4 rows)
现在,我无法理解查询计划的执行方式。查询计划是否首先从myTable的索引Designation_place_name检索序列号,然后转到myTable并获取行,然后对timeOfJoining执行过滤
或者
查询计划是否同时获取索引timeOfJoining_timeOfLeaving和Designation_place_name,然后执行联接,并且在此联接上进行了过滤?
该计划:
基本上是指:
在第2步中,磁盘页是“随机”访问的,而不是顺序访问的,也就是说,索引包含磁盘上匹配行的地址,而Postgres则按照索引指示的顺序访问这些地址。(顺便说一句,这可能会很昂贵。有时,计划者会决定便宜一点,即只读取整个表(seq扫描)或批量获取页面上的所有行,而忽略索引指示的顺序(位图索引扫描)。)
注意:该查询中没有(表)联接。如果有一个,您会看到额外的缩进级别。从缩进到缩进的顺序阅读它们。