8000 Don't predict and learn on plans without any underlying plane tables. · postgrespro/aqo@4be14cf · GitHub
[go: up one dir, main page]
Skip to content

Commit 4be14cf

Browse files
danolivoAndrey Lepikhov
danolivo
authored and
Andrey Lepikhov
committed
Don't predict and learn on plans without any underlying plane tables.
Optimizer can do his work the best way without an AQO assistance. So, we excluded zero relids from a relids list. Improve the pgbench.pl TAP test of AQO extension and check correctness of cleaning procedure after deletion of all pgbench-related tables. TODO: here we found a possible deadlock on the AQO ignorance table creation. We have to solve ths problem or remove this machinery at all. Suppress the source of this problem in the test.
1 parent ab3924e commit 4be14cf

File tree

6 files changed

+147
-22
lines changed

6 files changed

+147
-22
lines changed

‎cardinality_estimation.c

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -33,6 +33,13 @@ predict_for_relation(List *clauses, List *selectivities,
3333
int rows;
3434
int i;
3535

36+
if (relids == NIL)
37+
/*
38+
* Don't make prediction for query plans without any underlying plane
39+
* tables. Use return value -4 for debug purposes.
40+
*/
41+
return -4.;
42+
3643
*fss_hash = get_fss_for_object(relids, clauses,
3744
selectivities, &nfeatures, &features);
3845

‎cardinality_hooks.c

Lines changed: 8 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -139,7 +139,7 @@ aqo_set_baserel_rows_estimate(PlannerInfo *root, RelOptInfo *rel)
139139
{
140140
double predicted;
141141
Oid relid;
142-
List *relids;
142+
List *relids = NIL;
143143
List *selectivities = NULL;
144144
List *clauses;
145145
int fss = 0;
@@ -158,7 +158,9 @@ aqo_set_baserel_rows_estimate(PlannerInfo *root, RelOptInfo *rel)
158158
}
159159

160160
relid = planner_rt_fetch(rel->relid, root)->relid;
161-
relids = list_make1_int(relid);
161+
if (OidIsValid(relid))
162+
/* Predict for a plane table only. */
163+
relids = list_make1_int(relid);
162164

163165
clauses = aqo_get_clauses(root, rel->baserestrictinfo);
164166
predicted = predict_for_relation(clauses, selectivities,
@@ -201,7 +203,7 @@ aqo_get_parameterized_baserel_size(PlannerInfo *root,
201203
{
202204
double predicted;
203205
Oid relid = InvalidOid;
204-
List *relids = NULL;
206+
List *relids = NIL;
205207
List *allclauses = NULL;
206208
List *selectivities = NULL;
207209
ListCell *l;
@@ -249,7 +251,9 @@ aqo_get_parameterized_baserel_size(PlannerInfo *root,
249251
param_clauses);
250252
}
251253

252-
relids = list_make1_int(relid);
254+
if (OidIsValid(relid))
255+
/* Predict for a plane table only. */
256+
relids = list_make1_int(relid);
253257

254258
predicted = predict_for_relation(allclauses, selectivities, relids, &fss);
255259

‎ignorance.c

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -15,7 +15,7 @@ set_ignorance(bool newval, void *extra)
1515
{
1616
/*
1717
* On postgres start we can't create any table.
18-
* It is not problem. We will check existence at each update and create this
18+
* It is not a problem. We will check existence at each update and create this
1919
* table in dynamic mode, if needed.
2020
*/
2121
if (IsUnderPostmaster && !IsParallelWorker() && newval &&
@@ -104,6 +104,8 @@ update_ignorance(int qhash, int fhash, int fss_hash, Plan *plan)
104104
reloid = RangeVarGetRelid(rv, NoLock, true);
105105
if (!OidIsValid(reloid))
106106
{
107+
elog(LOG, "Create AQO ignorance table.");
108+
107109
/* This table doesn't created on instance startup. Create now. */
108110
create_ignorance_table(false);
109111
reloid = RangeVarGetRelid(rv, NoLock, true);

‎path_utils.c

Lines changed: 7 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -126,11 +126,17 @@ get_list_of_relids(PlannerInfo *root, Relids relids)
126126
if (relids == NULL)
127127
return NIL;
128128

129+
/*
130+
* Check: don't take into account relations without underlying plane
131+
* source table.
132+
*/
133+
Assert(!bms_is_member(0, relids));
134+
129135
i = -1;
130136
while ((i = bms_next_member(relids, i)) >= 0)
131137
{
132138
entry = planner_rt_fetch(i, root);
133-
if (entry->relid != 0)
139+
if (OidIsValid(entry->relid))
134140
l = lappend_int(l, entry->relid);
135141
}
136142
return l;

‎postprocessing.c

Lines changed: 17 additions & 13 deletions
Original file line numberDiff line numberDiff line change
@@ -450,27 +450,31 @@ learnOnPlanState(PlanState *p, void *context)
450450
list_copy(aqo_node->clauses));
451451

452452
if (aqo_node->relids != NIL)
453+
{
453454
/*
454-
* This plan can be stored as cached plan. In the case we will have
455+
* This plan can be stored as a cached plan. In the case we will have
455456
* bogus path_relids field (changed by list_concat routine) at the
456457
* next usage (and aqo-learn) of this plan.
457458
*/
458459
ctx->relidslist = list_copy(aqo_node->relids);
459460

460-
if (p->instrument)
461-
{
462-
Assert(predicted >= 1. && learn_rows >= 1.);
463-
464-
if (ctx->learn)
461+
if (p->instrument)
465462
{
466-
if (IsA(p, AggState))
467-
learn_agg_sample(SubplanCtx.clauselist, NULL,
468-
aqo_node->relids, learn_rows,
463+
Assert(predicted >= 1. && learn_rows >= 1.);
464+
465+
if (ctx->learn)
466+
{
467+
if (IsA(p, AggState))
468+
learn_agg_sample(SubplanCtx.clauselist, NULL,
469+
aqo_node->relids, learn_rows,
470+
p->plan, notExecuted);
471+
472+
else
473+
learn_sample(SubplanCtx.clauselist,
474+
SubplanCtx.selectivities,
475+
aqo_node->relids, learn_rows,
469476
p->plan, notExecuted);
470-
471-
else
472-
learn_sample(SubplanCtx.clauselist, SubplanCtx.selectivities,
473-
aqo_node->relids, learn_rows, p->plan, notExecuted);
477+
}
474478
}
475479
}
476480
}

‎t/001_pgbench.pl

Lines changed: 105 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1,15 +1,15 @@
11
use strict;
22
use warnings;
33
use TestLib;
4-
use Test::More tests => 6;
4+
use Test::More tests => 11;
55
use PostgresNode;
66

77
my $node = PostgresNode->new('aqotest');
88
$node->init;
99
$node->append_conf('postgresql.conf', qq{
1010
shared_preload_libraries = 'aqo'
1111
aqo.mode = 'intelligent'
12-
aqo.log_ignorance = 'on'
12+
aqo.log_ignorance = 'off' # TODO: solve problems with deadlock on the table creation or remove this table at all.
1313
log_statement = 'ddl'
1414
});
1515

@@ -51,6 +51,108 @@
5151
"$TRANSACTIONS", '-c', "$CLIENTS", '-j', "$THREADS" ],
5252
'pgbench in frozen mode');
5353

54-
$node->safe_psql('postgres', "DROP EXTENSION aqo");
54+
# ##############################################################################
55+
#
56+
# Check procedure of ML-knowledge data cleaning.
57+
#
58+
# ##############################################################################
59+
60+
# Store OIDs of pgbench tables
61+
my $aoid = $node->safe_psql('postgres',
62+
"SELECT ('pgbench_accounts'::regclass)::oid");
63+
my $boid = $node->safe_psql('postgres',
64+
"SELECT ('pgbench_branches'::regclass)::oid");
65+
my $toid = $node->safe_psql('postgres',
66+
"SELECT ('pgbench_tellers'::regclass)::oid");
67+
my $hoid = $node->safe_psql('postgres',
68+
"SELECT ('pgbench_history'::regclass)::oid");
69+
note("oids: $aoid, $boid, $toid, $hoid");
70+
71+
# Add data into AQO to control that cleaning procedure won't delete nothing extra
72+
$node->safe_psql('postgres', "
73+
CREATE TABLE detector(a int);
74+
INSERT INTO detector (a) VALUES (1);
75+
UPDATE detector SET a = a + 1;
76+
DELETE FROM detector;
77+
SELECT count(*) FROM detector;
78+
");
79+
80+
# New queries won't add rows into AQO knowledge base.
81+
$node->safe_psql('postgres', "ALTER SYSTEM SET aqo.mode = 'disabled'");
82+
$node->restart();
83+
my $res = $node->safe_psql('postgres', "SHOW aqo.mode");
84+
is($res, 'disabled');
85+
86+
# Number of rows in aqo_data: related to pgbench test and total value.
87+
my $pgb_fss_count = $node->safe_psql('postgres', "
88+
SELECT count(*) FROM aqo_data
89+
WHERE $aoid = ANY(oids) OR
90+
$boid = ANY(oids) OR
91+
$toid = ANY(oids) OR
92+
$hoid = ANY(oids)
93+
");
94+
my $fss_count = $node->safe_psql('postgres', "SELECT count(*) FROM aqo_data;");
95+
96+
# Number of rows in aqo_queries: related to pgbench test and total value.
97+
my $pgb_fs_count = $node->safe_psql('postgres', "
98+
SELECT count(*) FROM aqo_queries
99+
WHERE fspace_hash IN (
100+
SELECT fspace_hash FROM aqo_data
101+
WHERE
102+
$aoid = ANY(oids) OR
103+
$boid = ANY(oids) OR
104+
$toid = ANY(oids) OR
105+
$hoid = ANY(oids)
106+
)
107+
");
108+
my $fs_count = $node->safe_psql('postgres', "SELECT count(*) FROM aqo_queries;");
109+
110+
# Number of rows in aqo_query_texts: related to pgbench test and total value.
111+
my $pgb_fs_samples_count = $node->safe_psql('postgres', "
112+
SELECT count(*) FROM aqo_query_texts
113+
WHERE query_hash IN (
114+
SELECT fspace_hash FROM aqo_data
115+
WHERE $aoid = ANY(oids) OR $boid = ANY(oids) OR $toid = ANY(oids) OR $hoid = ANY(oids)
116+
)
117+
");
118+
my $fs_samples_count = $node->safe_psql('postgres', "SELECT count(*) FROM aqo_query_texts;");
55119

120+
# Number of rows in aqo_query_stat: related to pgbench test and total value.
121+
my $pgb_stat_count = $node->safe_psql('postgres', "
122+
SELECT count(*) FROM aqo_query_texts
123+
WHERE query_hash IN (
124+
SELECT fspace_hash FROM aqo_data
125+
WHERE $aoid = ANY(oids) OR $boid = ANY(oids) OR $toid = ANY(oids) OR $hoid = ANY(oids)
126+
)
127+
");
128+
my $stat_count = $node->safe_psql('postgres', "SELECT count(*) FROM aqo_query_stat;");
129+
130+
note("pgbench-related rows: aqo_data - $pgb_fss_count/$fss_count,
131+
aqo_queries: $pgb_fs_count/$fs_count, aqo_query_texts: $pgb_fs_samples_count/$fs_samples_count,
132+
aqo_query_stat: $pgb_stat_count/$stat_count");
133+
134+
$node->safe_psql('postgres', "
135+
DROP TABLE pgbench_accounts, pgbench_branches, pgbench_tellers,
136+
pgbench_history CASCADE;");
137+
138+
# Clean unneeded AQO knowledge
139+
$node->safe_psql('postgres', "SELECT clean_aqo_data()");
140+
141+
# Calculate total number of rows in AQO-related tables.
142+
my $new_fs_count = $node->safe_psql('postgres', "SELECT count(*) FROM aqo_queries;");
143+
my $new_fss_count = $node->safe_psql('postgres', "SELECT count(*) FROM aqo_data;");
144+
my $new_fs_samples_count = $node->safe_psql('postgres', "SELECT count(*) FROM aqo_query_texts;");
145+
my $new_stat_count = $node->safe_psql('postgres', "SELECT count(*) FROM aqo_query_stat;");
146+
note("Total AQO rows after dropping pgbench-related tables:
147+
aqo_queries: $new_fs_count, aqo_data: $new_fss_count,
148+
aqo_query_texts: $new_fs_samples_count, aqo_query_stat: $new_stat_count");
149+
150+
# Check total number of rows in AQO knowledge base after removing of
151+
# pgbench-related data.
152+
is($new_fs_count == $fs_count - $pgb_fs_count, 1, 'Total number of feature spaces');
153+
is($new_fss_count == $fss_count - $pgb_fss_count, 1, 'Total number of feature subspaces');
154+
is($new_fs_samples_count == $fs_samples_count - $pgb_fs_samples_count, 1, 'Total number of samples in aqo_query_texts');
155+
is($new_stat_count == $stat_count - $pgb_stat_count, 1, 'Total number of samples in aqo_query_texts');
156+
157+
$node->safe_psql('postgres', "DROP EXTENSION aqo");
56158
$node->stop();

0 commit comments

Comments
 (0)
0