带有索引的 postgres 查询计划 [英] Query Plan of postgres with indexes

问题描述

我有一个具有以下架构的 postgres 表

I have a postgres table having the following schema

     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)

现在，当我运行表单查询时:

Now when I run the query of the form:

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 中检索 serial_number 然后转到 myTable 并获取行，然后在 timeOfJoining 上执行过滤

Now I am unable to understand as to how the query plan is executed. Does the query plan first retrieve the serial_number from the index Designation_place_name on myTable and then goes to myTable and fetches the rows and then performs the filtering on timeOfJoining

或

查询计划是否同时获取索引 timeOfJoining_timeOfLeaving 和 Designation_place_name 然后执行连接，并在此连接时完成过滤?

Does the query plan fetch both the indexes timeOfJoining_timeOfLeaving and Designation_place_name and then performs a join, and upon this join the filtering is done?

推荐答案

本方案:

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. 查找符合索引条件的行relation = 'Manager'
3. 仅保留符合 timeOfJoining 条件的行

在第2步中，磁盘页面被随机"访问，而不是顺序访问，也就是说索引包含磁盘上匹配行的地址，Postgres按照索引指示的顺序访问这些地址.(顺便说一句，这可能代价高昂.有时，规划器会决定只读取整个表(seq 扫描)或批量获取页面上的所有行而忽略索引指示的顺序(位图索引扫描)更便宜.)

During step 2, disk pages are accessed "randomly", rather than sequentially, which is to say the index contains the address of matching rows on disk, and Postgres visits these addresses in the order indicated by the index. (This can be costly, btw. Sometimes, the planner will decide its cheaper to just read the entire table (seq scan) or batch fetch all rows on a page while ignoring the order indicated by the index (bitmap index scan).)

注意:该查询中没有(表)连接.如果有的话，你会看到额外的缩进级别.从最缩进到最不缩进的顺序阅读.

Note: there are no (table) joins in that query. Had there been one, you'd have seen extra indentation levels. Read them from most indented to least indented.

这篇关于带有索引的 postgres 查询计划的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！