High-Performance Row Pattern Recognition Using Joins-Reference-Cited by-同舟云学术

High-Performance Row Pattern Recognition Using Joins

Published:2023-01 Issue:5 Volume:16 Page:1181-1195
ISSN:2150-8097
Container-title:Proceedings of the VLDB Endowment
language:en
Short-container-title:Proc. VLDB Endow.

Author:

Zhu Erkang¹,Huang Silu¹,Chaudhuri Surajit¹

Affiliation:

1. Microsoft Research, Redmond, Washington, U.S.A.

Abstract

The SQL standard introduced MATCH_RECOGNIZE in 2016 for row pattern recognition. Since then, MATCH_RECOGNIZE has been supported by several leading relation systems, they implemented this function using Non-Deterministic Finite Automaton (NFA). While NFA is suitable for pattern recognition in streaming scenarios, the current uses of NFA by the relational systems for historical data analysis scenarios overlook important optimization opportunities. We propose a new approach to use Join to speed up row pattern recognition in historical analysis scenarios for relational systems. Implemented as a logical plan rewrite rule, the new approach first filters the input relation to MATCH_RECOGNIZE using Joins constructed based on a subset of symbols taken from the PATTERN expression, then run the NFA-based MATCH_RECOGNIZE on the filtered rows, reducing the net cost. The rule also includes a specialized cardinality model for the Joins and a cost model for the NFA-based MATCH_RECOGNIZE operator for choosing an appropriate symbol set. The rewrite rule is applicable when the query pattern's definition is self-contained and either the input table has no duplicates or there is a window condition. Applying the rewrite rule to a query benchmark with 1,800 queries spanning over 6 patterns and 3 pattern definitions, we observed median speedups of 5.4X on Trino (v373 with ORC files on Hive), 57.5X on SQL Server (2019) using column store and 41.6X on row store.

Publisher

Association for Computing Machinery (ACM)

Subject

General Earth and Planetary Sciences,Water Science and Technology,Geography, Planning and Development

Link

https://dl.acm.org/doi/pdf/10.14778/3579075.3579090

Reference56 articles.

1. 2022. Citus. https://github.com/citusdata/citus. 2022. Citus. https://github.com/citusdata/citus.

2. 2022. Columnstore indexes: Overview. https://docs.microsoft.com/en-us/sql/relational-databases/indexes/columnstore-indexes-overview. 2022. Columnstore indexes: Overview. https://docs.microsoft.com/en-us/sql/relational-databases/indexes/columnstore-indexes-overview.

3. 2022. Index Accelerated Pattern Matching on Persistent Event Streams. https://github.com/sigmod2021-index-pattern/index-pattern. 2022. Index Accelerated Pattern Matching on Persistent Event Streams. https://github.com/sigmod2021-index-pattern/index-pattern.

4. Jagrati Agrawal , Yanlei Diao , Daniel Gyllstrom , and Neil Immerman . 2008 . Efficient pattern matching over event streams . In Proceedings of the ACM SIGMOD International Conference on Management of Data, SIGMOD 2008 , Vancouver, BC, Canada , June 10-12, 2008, Jason Tsong-Li Wang (Ed.). ACM, 147--160. 10.1145/1376616.1376634 Jagrati Agrawal, Yanlei Diao, Daniel Gyllstrom, and Neil Immerman. 2008. Efficient pattern matching over event streams. In Proceedings of the ACM SIGMOD International Conference on Management of Data, SIGMOD 2008, Vancouver, BC, Canada, June 10-12, 2008, Jason Tsong-Li Wang (Ed.). ACM, 147--160. 10.1145/1376616.1376634

Cited by 4 articles. 订阅此论文施引文献订阅此论文施引文献，注册后可以免费订阅5篇论文的施引文献，订阅后可以查看论文全部施引文献

1. ACER: Accelerating Complex Event Recognition via Two-Phase Filtering under Range Bitmap-Based Indexes;Proceedings of the 30th ACM SIGKDD Conference on Knowledge Discovery and Data Mining;2024-08-24

2. Complex Event Recognition with Symbolic Register Transducers;Proceedings of the VLDB Endowment;2024-07

3. A new window Clause for SQL++;The VLDB Journal;2023-12-19

4. High-Performance Row Pattern Recognition Using Joins;Proceedings of the VLDB Endowment;2023-01