In Sect. 6.1 we review the overall database query processing workflow, with focus on PostgreSQL. We discuss how the statistics collected by both PostgreSQL and by PostGIS are combined in MobilityDB for the temporal types, and we illustrate how these statistics are used to guide the query processor in selecting the best execution plan for queries. Then, the chapter continues studying indexing. We begin in Sect. 6.2 providing general background on indexes and introducing the Brussels synthetic dataset which is used to illustrate the indexes presented in the chapter. We continue in Sects. 6.3 and 6.4 with classic indexing structures, namely, B-trees and hash indexes. Section 6.5 covers inverted indexes and their implementation using the GIN (Generalized Inverted Index) access method. Then, in Sect. 6.6, we address R-trees which are implemented using the GiST (Generalized Search Trees) access method. In Sect. 6.7 we delve into k-d trees and quadtrees, which are implemented with the SP-GiST (Space-Partitioning Generalized Search Trees) access method. Finally, in Sect. 6.8 we study multi-entry search trees, which extend traditional trees such as those implemented in GiST and SP-GiST by enabling an indexed value to be represented by multiple bounding boxes. These indexes may be very efficient for spatiotemporal trajectories.

错误:搜索内容不能为空,请输入英文关键词
错误:关键词超出字数限制,请精简
高级检索

Query Processing and Indexing

  • Mahmoud Sakr,
  • Alejandro Vaisman,
  • Esteban Zimányi

摘要

In Sect. 6.1 we review the overall database query processing workflow, with focus on PostgreSQL. We discuss how the statistics collected by both PostgreSQL and by PostGIS are combined in MobilityDB for the temporal types, and we illustrate how these statistics are used to guide the query processor in selecting the best execution plan for queries. Then, the chapter continues studying indexing. We begin in Sect. 6.2 providing general background on indexes and introducing the Brussels synthetic dataset which is used to illustrate the indexes presented in the chapter. We continue in Sects. 6.3 and 6.4 with classic indexing structures, namely, B-trees and hash indexes. Section 6.5 covers inverted indexes and their implementation using the GIN (Generalized Inverted Index) access method. Then, in Sect. 6.6, we address R-trees which are implemented using the GiST (Generalized Search Trees) access method. In Sect. 6.7 we delve into k-d trees and quadtrees, which are implemented with the SP-GiST (Space-Partitioning Generalized Search Trees) access method. Finally, in Sect. 6.8 we study multi-entry search trees, which extend traditional trees such as those implemented in GiST and SP-GiST by enabling an indexed value to be represented by multiple bounding boxes. These indexes may be very efficient for spatiotemporal trajectories.