小编典典

带索引的Postgres查询计划

sql

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


阅读 165

收藏
2021-04-28

共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扫描)或批量获取页面上的所有行,而忽略索引指示的顺序(位图索引扫描)。)

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

2021-04-28