Selectivity Estimation of Inequality Joins in Databases

Diogo Repas (), Zhicheng Luo (), Maxime Schoemans () and Mahmoud Sakr ()
Additional contact information
Diogo Repas: Data Science Lab, Université libre de Bruxelles (ULB), 1050 Brussels, Belgium
Zhicheng Luo: Data Science Lab, Université libre de Bruxelles (ULB), 1050 Brussels, Belgium
Maxime Schoemans: Data Science Lab, Université libre de Bruxelles (ULB), 1050 Brussels, Belgium
Mahmoud Sakr: Data Science Lab, Université libre de Bruxelles (ULB), 1050 Brussels, Belgium

Mathematics, 2023, vol. 11, issue 6, 1-18

Abstract: 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.

Keywords: SQL; query optimization; optimizer statistics (search for similar items in EconPapers)
JEL-codes: C (search for similar items in EconPapers)
Date: 2023
References: View references in EconPapers View complete reference list from CitEc
Citations:

Downloads: (external link)
https://www.mdpi.com/2227-7390/11/6/1383/pdf (application/pdf)
https://www.mdpi.com/2227-7390/11/6/1383/ (text/html)

Related works:
This item may be available elsewhere in EconPapers: Search for items with the same title.

Export reference: BibTeX RIS (EndNote, ProCite, RefMan) HTML/Text

Persistent link: https://EconPapers.repec.org/RePEc:gam:jmathe:v:11:y:2023:i:6:p:1383-:d:1095430

Access Statistics for this article

Mathematics is currently edited by Ms. Emma He

More articles in Mathematics from MDPI
Bibliographic data for series maintained by MDPI Indexing Manager ().