Selectivity estimation refers to the ability of the SQL query optimizer to estimate the size of the results of a predicate in the query. It is the main calculation based on which the optimizer can select the least expensive plan to execute. While the problem has been known since the mid-1970s, we were surprised that there are no solutions in the literature for the selectivity estimation of inequality joins. By testing four common database systems: Oracle, SQL-Server, PostgreSQL, and MySQL, we found that the open-source systems PostgreSQL and MySQL lack this estimation. Oracle and SQL-Server make fairly accurate estimations, yet their algorithms are secret. This paper, thus, proposes an algorithm for inequality join selectivity estimation. The proposed algorithm was implemented in PostgreSQL and sent as a patch to be included in the next releases. We compared this implementation with the above DBMS for three different data distributions (uniform, normal, and Zipfian) and showed that our algorithm provides extremely accurate estimations (below 0.1% average error), outperforming the other systems by an order of magnitude.
Various applications process geospatial trajectories of moving objects, such as cars, ships and robots. There is thus a need for a common conceptual framework to model and manage these objects, as well as to enable data interoperability across tools. The International Organization for Standardization ISO® has responded to this need and created the standard ISO 19141-Schema for moving features. Among its types, it defines a schema for rigid temporal geometries, which represent the movement of spatial objects translating and rotating over time, while preserving a fixed shape. Despite the abundance of these objects in real-world, there exists no reference implementation of this type of data in a common system, which causes them to usually be represented as temporal points without taking into account their spatial extents and shapes. In this paper, we aim to provide an implementation of rigid temporal geometries into MobilityDB, an open-source moving object database, that extends PostgreSQL and PostGIS. We provide a data model for rigid temporal geometries and propose efficient algorithms for the operations defined in ISO 19141. A use case on real AIS ship trajectories is illustrated to validate the proposed implementation. A synthetic data generator for temporal geometries is also proposed. Finally, we review the standard from an implementation point of view and provide insights on possible improvements.
scite is a Brooklyn-based organization that helps researchers better discover and understand research articles through Smart Citations–citations that display the context of the citation and describe whether the article provides supporting or contrasting evidence. scite is used by students and researchers from around the world and is funded in part by the National Science Foundation and the National Institute on Drug Abuse of the National Institutes of Health.
hi@scite.ai
10624 S. Eastern Ave., Ste. A-614
Henderson, NV 89052, USA
Copyright © 2024 scite LLC. All rights reserved.
Made with 💙 for researchers
Part of the Research Solutions Family.