Home // ICAS 2017, The Thirteenth International Conference on Autonomic and Autonomous Systems // View article

OptPLAN: Improving the Optimal Plan Calculation on Relational Databases

Authors:
Luis Fernando Orleans
Miguel Brito
Egberto Silva

Keywords: OptPlan; Query Optimization; Autonomous RDBMS

Abstract:
In order to find the best execution plan for an SQL query, a DBMS uses information regarding the cost of disk operations, notably the cost of a sequential page reading (seq page cost or just spc) and the cost of random pages reading (random page cost or just rpc). Such information is predefined by DBMS vendors and are rarely changed - although it can cause inaccuracies in the optimization phase. This paper lists some typical scenarios where disk access costs miscalculations can lead to sub-optimal query plans and presents the OptPLAN, a tool for the PostgreSQL DBMS that calculates the correct relationship between spc and rpc and automatically set their values at the configuration properties. In our experiments, we obtained up to 69% of query speed up after spc and rpc adjustment.

Pages: 7 to 11

Copyright: Copyright (c) IARIA, 2017

Publication date: May 21, 2017

Published in: conference

ISSN: 2308-3913

ISBN: 978-1-61208-555-5

Location: Barcelona, Spain

Dates: from May 21, 2017 to May 25, 2017