带索引的Postgres查询计划

Question

我有一个具有以下架构的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，然后执行联接，并且在此联接上进行了过滤？

Answer 1

该计划：

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)

基本上是指：

1. 使用Designation_place_name索引
2. 查找符合索引条件关系=“经理”的行
3. 仅保留符合timeOfJoining条件的行

在第2步中，磁盘页是“随机”访问的，而不是顺序访问的，也就是说，索引包含磁盘上匹配行的地址，而Postgres则按照索引指示的顺序访问这些地址。（顺便说一句，这可能会很昂贵。有时，计划者会决定便宜一点，即只读取整个表（seq扫描）或批量获取页面上的所有行，而忽略索引指示的顺序（位图索引扫描）。）

注意：该查询中没有（表）联接。如果有一个，您会看到额外的缩进级别。从缩进到缩进的顺序阅读它们。