ICDE2024_QCFE：An Efficient Feature Engineering for Query Cost Estimation_华为.pdf

刺史武都

270

14页

2次

2024-05-24

免费下载

QCFE: An efﬁcient Feature engineering for

query cost estimation.

Yu Yan,Hongzhi Wang, Junfang Huang, Dake Zhong, Man Yang, Kaixin Zhang, Tao Yu, Tianqing Wang

Harbin Institute of Technology Harbin China, HUAWEI China

yuyan@hit.edu.cn,wangzh@hit.edu.cn

Abstract—Query cost estimation is a classical task for

database management. Recently, researchers apply the

AI-driven model to implement query cost estimation for

achieving high accuracy. However, two defects of feature

design lead to poor cost estimation accuracy-time efﬁciency.

On the one hand, existing works only encode the query plan

and data statistics while ignoring some other important

variables, like storage structure, hardware, database knobs,

etc. These variables also have signiﬁcant impact on the

query cost. On the other hand, due to the straightforward

encoding design, existing works suffer heavy representation

learning burden on ineffective dimensions of input. To meet

the above two problems, we ﬁrst propose an efﬁcient feature

engineering for query cost estimation, called QCFE. Specif-

ically, we design a novel feature called feature snapshot

to efﬁciently integrate the inﬂuences of the ignored vari-

ables. Further, we propose a difference-propagation feature

reduction method for query cost estimation to ﬁlter the

useless features. The experimental results demonstrate our

QCFE could largely improve the time-accuracy efﬁciency

on extensive benchmarks.

I. INTRODUCTION

Cost estimation plays a pivotal role in database man-

agement, forming the bedrock for database optimization

strategies encompassing query optimization [1], index

optimization [2], and storage efﬁciency, among other

aspects. The precision of cost estimation methodologies

stands as a linchpin for achieving optimal performance

in database operations. Regrettably, conventional tech-

niques reliant on cost equations may produce huge

estimation errors under complex workloads due to their

simplistic frameworks and underlying assumptions [3].

This shortcoming can engender sub-optimal optimization

outcomes, consequently undermining database perfor-

mance.

Hence, the database community has delved into the

application of neural network models to capture the

intricate correlation between queries and their associ-

ated costs, harnessing the formidable learning prowess

inherent in these deep networks. Extensive experimental

results [4], [5], [6], [3] have demonstrated that the learn-

ing approaches achieve high accuracy across various

complex benchmarks.

However, the utilization of neural networks for

databases poses an efﬁciency-accuracy dilemma. On one

hand, the query cost is related to multiple features, such

as relation table, query, etc.), compared to other ﬁelds,

such as natural language processing [7] and image recog-

nition [8], [9], as they contain different structure. To

accurately represent and ﬁt the query plan-performance

relationship, it is necessary to design complex net-

work models. For instance, the transformer query cost

model [10] has shown superior performance compared

to simpler models, primarily due to its deeper network

layers and attention mechanism that assigns weights to

features. Notably, such network structures require more

training and inference time than ordinary deep neural

network (DNN) models.

On the other hand, database cost estimation is a

frequently invoked component, which may be invoked

multiple times within a single query optimization [11].

For example, even PostgreSQL utilizes genetic algo-

rithms to reduce the number of estimation requests, the

cost estimation component is still called more than n

times, where n is the number of nodes in the query

plan. Additionally, large database management systems

receive thousands of query requests every minute. Con-

sequently, it is not feasible to allocate excessive time to

the fundamental cost estimation component.

Fig. 1. The average query cost (ms) of 1000 queries in TPCH and

Sysbench under different database environments.

Given the limitations of designing complex models,

for solving the efﬁciency-accuracy dilemma, a natural

arXiv:2310.00877v1 [cs.DB] 2 Oct 2023

approach should optimize the features to reduce the

burden of representation learning and ﬁtting learning in

the model. Existing AI-driven cost estimation methods

utilize relatively straightforward approaches to process-

ing input query features. Typically, the one-hot encoding

for tables [12], the one-hot encoding for indexes [13],

and the vector for numerical values are directly fed

into the evaluation model in a bottom-up path of the

query. Totally, we have identiﬁed two shortcomings

regarding feature design in existing AI-driven query cost

estimators:

(1) Missing Important Features: Current meth-

ods [3] primarily focus on encoding the query plan

and the table statistics, often overlooking the impact

of other database variables on query cost. However,

variables such as the storage format of data (e.g., B+

tree or LSM tree) and the hardware of the database

also play a signiﬁcant role in determining the query

cost. Our investigation, as depicted in Figure 1, demon-

strates substantial differences (2 times in TPCH and 3

times in Sysbench) in the average execution time of the

same queries under different database environments (ﬁve

database knob conﬁgurations). Therefore, neglecting the

database environment can result in signiﬁcant losses

when predicting query cost.

(2) Heavy Representation Learning: Existing meth-

ods directly utilize the table feature, index feature, oper-

ator feature, etc. as the input of the AI cost model. This

brings a large burden for representation learning [14],

which is used to learn the effective representation of

input features. Speciﬁcally, with the goal of simplifying

learned model (accelerating inference time), capturing

the relationships between the large amount of features

and the query cost can be a difﬁcult task. This intricate

logic relationship between multiple features necessitates

multiple nonlinear transformations to effectively capture.

These two questions appear to be a contradiction.

The absence of crucial features primarily results from

an incomplete modeling of the query cost estimation

problem, necessitating the incorporation of additional

features. The heavy representation learning stems from

the ineffective elements of the encoding, necessitating

the removal of some features. Nevertheless, when viewed

collectively, these issues can be categorized as feature

engineering challenges, implying that the task of query

cost estimation’s feature engineering has not been pro-

cessed optimally.

To solve the above problems, we design an effective

feature engineering for query cost estimation, called

QCFE. The core sights are as follows: (1) To avoid

missing important variables, we deﬁne a novel concept,

called feature snapshot (SF ) to integrate the charac-

teristics of ignored variables (deﬁned as the variable

set of database knobs, storage structure, hardware and

operating system). To the best of our knowledge, no one

has attempted to encode the ignored variables for the

query cost model. One possible reason may be that the

resource required to build an exact feature representation

is tantamount to build the database environment. Hence,

we propose an estimated method to obtain the snapshot

feature, ensuring high efﬁciency.

(2) For the heavy representation learning, we design

a difference-propagation feature reduction (FR) method,

to relieve the learning burden by pruning the ineffective

features. Speciﬁcally, depending on the relational table

and load type, certain features may not be effective. For

instance, the plan method employs columns with the

attribute’s length to encode the index. However, in pure

write scenarios, the database management system may

not create an index, resulting in an ineffective feature

with the length of the number of columns in the query

feature. These ineffective features not only increase the

training and inference cost of the AI evaluation model

but also reduce its accuracy [15].

Totally, the speciﬁc contributions are as follows:

• In order to improve the time-accuracy efﬁciency,

we propose a feature engineering for query cost

estimation, called QCFE.

• We ﬁrst propose the feature snapshot (in Section III)

concept for query cost estimation, integrating the

inﬂuential the ignored variables variables. Our core

goal is to make some reasonable assumptions to

calculate the feature snapshot with high time efﬁ-

ciency.

• We design the difference-propagation feature reduc-

tion method (in Section IV) to efﬁciently reduce the

useless feature, further improving model training

and inference efﬁciency.

• To clarify the effectiveness of our QCFE, we

demonstrate various comparisons (Section V) under

extensive popular benchmarks (TPC-H, job-light,

and Sysbench), including the evaluation of time-

accuracy efﬁciency, the ablation of QCFE, the ro-

bustness of QCFE, etc.

II. OVERVIEW

In this section, we overview the architecture and

workﬂow of our QCFE.

Firstly, we show the general feature engineering which

is widely used in existing works [16], [12], [17], to

clarify the effectiveness of our QCFE. As shown in

Figure 2, the general FE directly encodes the query

of 14

免费下载

关注

评论