Hi,
I currently don't understand the problem, and I also don't understand the
patch. I think I will need a test case. The test case I have so far is:
private void testIndexUsage() throws Exception {
Connection conn = getConnection("optimizations");
Statement stat = conn.createStatement();
stat.execute("create table test(id int primary key, deleted int,
org_id int, type int)");
stat.execute("create index idx_type on test(type, deleted,
org_id)");
stat.execute("create index idx_org_id on test(org_id, deleted)");
stat.execute("insert into test select x, x, x/100, x " +
"from system_range(1, 1000)");
stat.execute("analyze");
ResultSet rs;
rs = stat.executeQuery(
"explain select type, count(*) from test " +
"where deleted=0 and org_id=0 " +
"group by type order by type");
rs.next();
String plan = rs.getString(1);
assertContains(plan, "IDX_ORG_ID:");
stat.execute("drop table test");
conn.close();
}
You wrote the index on idx_type would be faster for this query, but I don't
understand why that would be the case. This index is on type, deleted,
org_id; but the query constraint is on deleted and org_id, which would mean
the other index should be better. Maybe one of you can explain the problem?
The patch uses "double", but I don't see what that would change. Also, the
patch seems to have some unrelated changes for range queries. I will not
apply those unless somebody provides a test case or explains the changes.
Regards,
Thomas
On Saturday, January 25, 2014, Thomas Mueller <[email protected]>
wrote:
> Hi,
>
> Thanks a lot for the patch! I didn't have a look at it yet, but I (or
> another of the H2 committers) surely will. We monitor the Google Group, and
> there is usually no need to create a issue in the bugtracker.
>
> Regards,
> Thomas
>
>
>
> On Sat, Jan 25, 2014 at 9:31 AM, Pandu Purnama <[email protected]> wrote:
>
> Hi, thanks for the patch!
> I assume this is a bug then?
> If so, can we ask H2 dev team to apply this patch to repository?
> That way we don't have to manually apply this patch every time we upgrade
> H2 version.
>
> Thanks!
>
> On Friday, January 24, 2014 8:45:52 PM UTC+7, [email protected] wrote:
>
> replace Methode getCostRangeIndex in org.h2.index.BaseIndex
>
>
> protected long getCostRangeIndex(int[] masks, long rowCount,
> TableFilter filter, SortOrder sortOrder) {
> rowCount += Constants.COST_ROW_OFFSET;
> long cost = rowCount;
> long rows = rowCount;
> double totalSelectivity = 0;// totalSelectivity = 100 - ((100 - 1)
> *
> // (100 - 1) / 100) = 1,99 wennn
> // totalSelectivity is an int then
> // totalSelectivity stay = 1
> if (masks == null) {
> return cost;
> }
> for (int i = 0, len = columns.length; i < len; i++) {
> Column column = columns[i];
> int index = column.getColumnId();
> int mask = masks[index];
> if ((mask & IndexCondition.EQUALITY) ==
> IndexCondition.EQUALITY) {
> if (i == columns.length - 1 && getIndexType().isUnique()) {
> cost = 3;
> break;
> }
> totalSelectivity = 100 - ((100 - totalSelectivity) * (100
> - column.getSelectivity()) / 100);
> long distinctRows = (long) (rowCount * totalSelectivity /
> 100);
> if (distinctRows <= 0) {
> distinctRows = 1;
> }
> rows = Math.max(rowCount / distinctRows, 1);
> cost = 2 + rows;
> } else if ((mask & IndexCondition.RANGE) ==
> IndexCondition.RANGE) {
> cost = 2 + rows / 4;
> rows = cost;// for compare anothers columns
> } else if ((mask & IndexCondition.START) ==
> IndexCondition.START) {
> cost = 2 + rows / 3;
> rows = cost;// for compare anothers columns
> } else if ((mask & IndexCondition.END) == IndexCondition.END) {
> cost = rows / 3;
> rows = cost;// for compare anothers columns
> } else {
> break;
> }
> }
> // if the ORDER BY clause matches the ordering of this index,
> // it will be cheaper than another index, so adjust the cost
> accordingly
> if (sortOrder != null) {
> boolean sortOrderMatches = true;
> int coveringCount = 0;
> int[] sortTypes = sortOrder.getSortTypes();
> for (int i = 0, len = sortTypes.length; i < len; i++) {
> if (i >= indexColumns.length) {
> // we can still use this index if we are sorting by
> more
> // than it's columns, it's just that the coveringCount
> // is lower than with an index that contains
> // more of the order by columns
> break;
> }
> Column col = sortOrder.getColumn(i, filter);
> if (col == null) {
> sortOrderMatches = false;
> break;
> }
> IndexColumn indexCol = indexColumns[i];
> if (col != indexCol.column) {
>
>
>
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.
