Forum: Building VoltDB Applications

Post: No index found to support min() / max() UPDATE and DELETE on Materialized View

No index found to support min() / max() UPDATE and DELETE on Materialized View
yzhu
Jun 18, 2014
Hi there,

I've another question on the warnings in the catalog report below:

No index found to support min() / max() UPDATE and DELETE on Materialized View ACCOUNTVIEW, and a sequential scan might be issued when current min / max value is updated / deleted

Here are our table definitions:

CREATE TABLE Account
(account_name VARCHAR(255) NOT NULL
,account_value TIMESTAMP DEFAULT NULL
,master_id VARCHAR(255) NOT NULL //Master_id is FK to another table.
,PRIMARY KEY (master_id, account_name)
);

CREATE VIEW ACCOUNTRVIEW(
account_name,
num_accounts,
min_account_value
) AS SELECT
account_name,
COUNT(*),
MIN(account_value)
FROM Acount GROUP BY account_name;

I found this from the voltDB source code on github https://github.com/VoltDB/voltdb/blob/master/src/frontend/org/voltdb/compiler/DDLCompiler.java.

if (hasMinOrMaxAgg) {
// TODO: deal with minMaxAggs, i.e. if only one min/max agg, try to find the index
// with group by cols followed by this agg col; if multiple min/max aggs, decide
// what to do (probably the index on group by cols is the best choice)
Index found = findBestMatchIndexForMatviewMinOrMax(matviewinfo, srcTable, groupbyExprs);
if (found != null) {
matviewinfo.setIndexforminmax(found.getTypeName());
} else {
matviewinfo.setIndexforminmax("");
m_compiler.addWarn("No index found to support min() / max() UPDATE and DELETE on Materialized View " +
matviewinfo.getTypeName() +
", and a sequential scan might be issued when current min / max value is updated / deleted.");
}

Firstly, should I be worried about this warning?

If we need to fix it, I am not completely clear on how to define this index, can you please advice?

Thanks in advance.

Regards,
Yaoling
xin
Jun 18, 2014
We should include suggestions in this warning message, will file a ticket on that.

Because of the MIN column in the view, there is a chance that source table min column has been deleted. Instead of figuring out the new MIN in the view, it's better to have an index. Currently, view support the index on group by columns for MIN/MAX. For your case, you can define an index on your group by column "account_name" on table "Acount".

Please let us know if you have more questions.

Xin
yzhu
Jun 19, 2014
Hi Xin,

Thanks for your reply. Can you please clarify if we need to create an index on which column(s)

option 1: CREATE INDEX account_value_idx ON Account (account_value) or
option 2: CREATE INDEX account_name_and_value_idx ON Account (account_name, account_value).

The comments in the voltdb coude seem to suggest that it tries to search for "group by cols followed by this agg col" which is the option 2. I don't quite follow how it would get a min(account_value) if we index on the account_name?

Finally, what's consequence if we don't create any index at all? What would voltdb do when the current min(account_value) has been deleted?

Thanks again,

Regards,
Yaoling
xin
Jun 19, 2014
Currently VoltDB only picks up your option 1 to update the new MIN/MAX. We have a ticket to improve its performance by using your index option 2.

FYI,
https://issues.voltdb.com/browse/ENG-6511

If there is no index defined, VoltDB currently has to scan the whole source table "Account" to find the new MIN/MAX if it has been deleted.

Please let us know if you have more questions.

Xin