IDEAS home Printed from https://ideas.repec.org/a/gam/jmathe/v11y2023i6p1383-d1095430.html
   My bibliography  Save this article

Selectivity Estimation of Inequality Joins in Databases

Author

Listed:
  • 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
    Faculty of Computer and Information Sciences, Ain Shams University, Cairo 11566, Egypt)

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.

Suggested Citation

  • Diogo Repas & Zhicheng Luo & Maxime Schoemans & Mahmoud Sakr, 2023. "Selectivity Estimation of Inequality Joins in Databases," Mathematics, MDPI, vol. 11(6), pages 1-18, March.
    • Handle: RePEc:gam:jmathe:v:11:y:2023:i:6:p:1383-:d:1095430
    as

    Download full text from publisher

    File URL: https://www.mdpi.com/2227-7390/11/6/1383/pdf
    Download Restriction: no
    File URL: https://www.mdpi.com/2227-7390/11/6/1383/
    Download Restriction: no
    ---><---

    References listed on IDEAS

    as
    1. Eduardo Fragoso-Navarro & Manuel Cedillo-Hernandez & Francisco Garcia-Ugalde & Robert Morelos-Zaragoza, 2022. "Reversible Data Hiding with a New Local Contrast Enhancement Approach," Mathematics, MDPI, vol. 10(5), pages 1-30, March.
    Full references (including those not matched with items on IDEAS)

    Most related items

    These are the items that most often cite the same works as this one and are cited by the same works as this one.
    1. Limengnan Zhou & Chongfu Zhang & Asad Malik & Hanzhou Wu, 2022. "Efficient Reversible Data Hiding Based on Connected Component Construction and Prediction Error Adjustment," Mathematics, MDPI, vol. 10(15), pages 1-15, August.

    Corrections

    All material on this site has been provided by the respective publishers and authors. You can help correct errors and omissions. When requesting a correction, please mention this item's handle: RePEc:gam:jmathe:v:11:y:2023:i:6:p:1383-:d:1095430. See general information about how to correct material in RePEc.

    If you have authored this item and are not yet registered with RePEc, we encourage you to do it here. This allows to link your profile to this item. It also allows you to accept potential citations to this item that we are uncertain about.

    If CitEc recognized a bibliographic reference but did not link an item in RePEc to it, you can help with this form .

    If you know of missing items citing this one, you can help us creating those links by adding the relevant references in the same way as above, for each refering item. If you are a registered author of this item, you may also want to check the "citations" tab in your RePEc Author Service profile, as there may be some citations waiting for confirmation.

    For technical questions regarding this item, or to correct its authors, title, abstract, bibliographic or download information, contact: MDPI Indexing Manager (email available below). General contact details of provider: https://www.mdpi.com .

    Please note that corrections may take a couple of weeks to filter through the various RePEc services.

    IDEAS is a RePEc service. RePEc uses bibliographic data supplied by the respective publishers.