Publicação
Query optimizers based on machine learning techniques
| Resumo: | Query optimizers are considered one of the most relevant and sophisticated components in a database management system. However, despite currently producing nearly optimal results, optimizers rely on statistical estimates and heuristics to reduce the search space of alternative execution plans for a single query. As a result, for more complex queries, errors may grow exponentially, often translating into sub-optimal plans resulting in less than ideal performance. Recent advances in machine learning techniques have opened new opportunities for many of the existing problems related to system optimization. This document proposes a solution built on top of PostgreSQL that learns to select the most efficient set of optimizer strategy settings for a particular query. Instead of depending entirely on the optimizer’s estimates to compare different plans under different configurations, it relies on a greedy selection algorithm that supports several types of predictive modeling techniques, from more traditional modeling techniques to a deep learning approach. The system is evaluated experimentally with the standard TPC-H and Join Order ing Benchmark workloads to measure the cost and benefits of adding machine learning capabilities to traditional query optimizers. |
|---|---|
| Autores principais: | Souto, Rui Pedro Sousa Rodrigues do |
| Assunto: | Database tuning Machine learning Query optimization Aprendizagem automática Otimização de queries Tuning de base de dados |
| Ano: | 2021 |
| País: | Portugal |
| Tipo de documento: | dissertação de mestrado |
| Tipo de acesso: | acesso aberto |
| Instituição associada: | Universidade do Minho |
| Idioma: | inglês |
| Origem: | RepositóriUM - Universidade do Minho |
| _version_ | 1866269718653960192 |
|---|---|
| author | Souto, Rui Pedro Sousa Rodrigues do |
| author_facet | Souto, Rui Pedro Sousa Rodrigues do |
| author_role | author |
| contributor_name_str_mv | Coelho, Fábio André Castanheira Luís Vilaça, Ricardo Manuel Pereira Universidade do Minho |
| country_str | PT |
| creators_json_txt | [{\"Person.name\":\"Souto, Rui Pedro Sousa Rodrigues do\"}] |
| datacite.contributors.contributor.contributorName.fl_str_mv | Coelho, Fábio André Castanheira Luís Vilaça, Ricardo Manuel Pereira Universidade do Minho |
| datacite.creators.creator.creatorName.fl_str_mv | Souto, Rui Pedro Sousa Rodrigues do |
| datacite.date.Accepted.fl_str_mv | 2021-10-27T00:00:00Z |
| datacite.date.available.fl_str_mv | 2023-04-27T15:49:20Z |
| datacite.date.embargoed.fl_str_mv | 2023-04-27T15:49:20Z |
| datacite.rights.fl_str_mv | http://purl.org/coar/access_right/c_abf2 |
| datacite.subjects.subject.fl_str_mv | Database tuning Machine learning Query optimization Aprendizagem automática Otimização de queries Tuning de base de dados |
| datacite.titles.title.fl_str_mv | Query optimizers based on machine learning techniques |
| dc.contributor.none.fl_str_mv | Coelho, Fábio André Castanheira Luís Vilaça, Ricardo Manuel Pereira Universidade do Minho |
| dc.creator.none.fl_str_mv | Souto, Rui Pedro Sousa Rodrigues do |
| dc.date.Accepted.fl_str_mv | 2021-10-27T00:00:00Z |
| dc.date.available.fl_str_mv | 2023-04-27T15:49:20Z |
| dc.date.embargoed.fl_str_mv | 2023-04-27T15:49:20Z |
| dc.format.none.fl_str_mv | application/pdf |
| dc.identifier.none.fl_str_mv | https://hdl.handle.net/1822/84186 |
| dc.language.none.fl_str_mv | eng |
| dc.rights.cclincense.fl_str_mv | http://creativecommons.org/licenses/by/4.0/ |
| dc.rights.none.fl_str_mv | http://purl.org/coar/access_right/c_abf2 |
| dc.rights.rights.copyright.fl_str_mv | openAccess |
| dc.subject.none.fl_str_mv | Database tuning Machine learning Query optimization Aprendizagem automática Otimização de queries Tuning de base de dados |
| dc.title.fl_str_mv | Query optimizers based on machine learning techniques |
| dc.type.none.fl_str_mv | http://purl.org/coar/resource_type/c_bdcc |
| description | Query optimizers are considered one of the most relevant and sophisticated components in a database management system. However, despite currently producing nearly optimal results, optimizers rely on statistical estimates and heuristics to reduce the search space of alternative execution plans for a single query. As a result, for more complex queries, errors may grow exponentially, often translating into sub-optimal plans resulting in less than ideal performance. Recent advances in machine learning techniques have opened new opportunities for many of the existing problems related to system optimization. This document proposes a solution built on top of PostgreSQL that learns to select the most efficient set of optimizer strategy settings for a particular query. Instead of depending entirely on the optimizer’s estimates to compare different plans under different configurations, it relies on a greedy selection algorithm that supports several types of predictive modeling techniques, from more traditional modeling techniques to a deep learning approach. The system is evaluated experimentally with the standard TPC-H and Join Order ing Benchmark workloads to measure the cost and benefits of adding machine learning capabilities to traditional query optimizers. |
| dirty | 0 |
| eu_rights_str_mv | openAccess |
| format | masterThesis |
| fulltext.url.fl_str_mv | https://prod-dspace.uminho.pt/bitstreams/bc35ee58-2ed1-41cb-b0e9-b192d55c69b5/download |
| id | rum_e88e73efc819a6bf7d3723ff385a88e1 |
| identifier.url.fl_str_mv | https://hdl.handle.net/1822/84186 |
| instacron_str | repositorium |
| institution | Universidade do Minho |
| instname_str | Universidade do Minho |
| language | eng |
| network_acronym_str | rum |
| network_name_str | RepositóriUM - Universidade do Minho |
| oai_identifier_str | oai:repositorium.uminho.pt:1822/84186 |
| organization_str_mv | urn:organizationAcronym:repositorium |
| person_str_mv | Souto, Rui Pedro Sousa Rodrigues do |
| publishDate | 2021 |
| reponame_str | RepositóriUM - Universidade do Minho |
| repository_id_str | urn:repositoryAcronym:rum |
| service_str_mv | urn:repositoryAcronym:rum |
| spelling | engporQuery optimizers are considered one of the most relevant and sophisticated components in a database management system. However, despite currently producing nearly optimal results, optimizers rely on statistical estimates and heuristics to reduce the search space of alternative execution plans for a single query. As a result, for more complex queries, errors may grow exponentially, often translating into sub-optimal plans resulting in less than ideal performance. Recent advances in machine learning techniques have opened new opportunities for many of the existing problems related to system optimization. This document proposes a solution built on top of PostgreSQL that learns to select the most efficient set of optimizer strategy settings for a particular query. Instead of depending entirely on the optimizer’s estimates to compare different plans under different configurations, it relies on a greedy selection algorithm that supports several types of predictive modeling techniques, from more traditional modeling techniques to a deep learning approach. The system is evaluated experimentally with the standard TPC-H and Join Order ing Benchmark workloads to measure the cost and benefits of adding machine learning capabilities to traditional query optimizers.application/pdfporQuery optimizers based on machine learning techniquesSouto, Rui Pedro Sousa Rodrigues doCoelho, Fábio André Castanheira LuísVilaça, Ricardo Manuel PereiraHostingInstitutionOrganizationalUniversidade do Minhoe-mailmailto:repositorium@usdb.uminho.ptrepositorium@usdb.uminho.ptURNurn:tid:2032593352023-04-27T15:49:20Z2021-10-272021-072021-10-27T00:00:00ZHandlehttps://hdl.handle.net/1822/84186http://purl.org/coar/access_right/c_abf2open accessDatabase tuningMachine learningQuery optimizationAprendizagem automáticaOtimização de queriesTuning de base de dados1674965 bytesliteraturehttp://purl.org/coar/resource_type/c_bdccmaster thesis2021-10-27http://creativecommons.org/licenses/by/4.0/openAccesshttp://purl.org/coar/access_right/c_abf2application/pdffulltexthttps://prod-dspace.uminho.pt/bitstreams/bc35ee58-2ed1-41cb-b0e9-b192d55c69b5/download |
| spellingShingle | Query optimizers based on machine learning techniques Souto, Rui Pedro Sousa Rodrigues do Database tuning Machine learning Query optimization Aprendizagem automática Otimização de queries Tuning de base de dados |
| status | SINGLETON |
| subject.fl_str_mv | Database tuning Machine learning Query optimization Aprendizagem automática Otimização de queries Tuning de base de dados |
| title | Query optimizers based on machine learning techniques |
| title_full | Query optimizers based on machine learning techniques |
| title_fullStr | Query optimizers based on machine learning techniques |
| title_full_unstemmed | Query optimizers based on machine learning techniques |
| title_short | Query optimizers based on machine learning techniques |
| title_sort | Query optimizers based on machine learning techniques |
| topic | Database tuning Machine learning Query optimization Aprendizagem automática Otimização de queries Tuning de base de dados |
| topic_facet | Database tuning Machine learning Query optimization Aprendizagem automática Otimização de queries Tuning de base de dados |
| url | https://hdl.handle.net/1822/84186 |
| visible | 1 |