Examples with PrimaryIndexSeek herddb.index.PrimaryIndexSeek used on opensource projects

Example 1 with PrimaryIndexSeek

use of herddb.index.PrimaryIndexSeek in project herddb by diennea.

the class SQLPlanner method discoverIndexOperations.

private void discoverIndexOperations(Expression expressionWhere, Table table, String mainTableAlias, SQLRecordPredicate where, TableSpaceManager tableSpaceManager) throws StatementExecutionException {
    SQLRecordKeyFunction keyFunction = findIndexAccess(expressionWhere, table.primaryKey, table, mainTableAlias, EqualsTo.class);
    IndexOperation result = null;
    if (keyFunction != null) {
        if (keyFunction.isFullPrimaryKey()) {
            result = new PrimaryIndexSeek(keyFunction);
        } else {
            result = new PrimaryIndexPrefixScan(keyFunction);
        }
    } else {
        SQLRecordKeyFunction rangeMin = findIndexAccess(expressionWhere, table.primaryKey, table, mainTableAlias, GreaterThanEquals.class);
        if (rangeMin != null && !rangeMin.isFullPrimaryKey()) {
            rangeMin = null;
        }
        if (rangeMin == null) {
            rangeMin = findIndexAccess(expressionWhere, table.primaryKey, table, mainTableAlias, GreaterThan.class);
            if (rangeMin != null && !rangeMin.isFullPrimaryKey()) {
                rangeMin = null;
            }
        }
        SQLRecordKeyFunction rangeMax = findIndexAccess(expressionWhere, table.primaryKey, table, mainTableAlias, MinorThanEquals.class);
        if (rangeMax != null && !rangeMax.isFullPrimaryKey()) {
            rangeMax = null;
        }
        if (rangeMax == null) {
            rangeMax = findIndexAccess(expressionWhere, table.primaryKey, table, mainTableAlias, MinorThan.class);
            if (rangeMax != null && !rangeMax.isFullPrimaryKey()) {
                rangeMax = null;
            }
        }
        if (rangeMin != null || rangeMax != null) {
            result = new PrimaryIndexRangeScan(table.primaryKey, rangeMin, rangeMax);
        }
    }
    if (result == null) {
        Map<String, AbstractIndexManager> indexes = tableSpaceManager.getIndexesOnTable(table.name);
        if (indexes != null) {
            // TODO: use some kind of statistics, maybe using an index is more expensive than a full table scan
            for (AbstractIndexManager index : indexes.values()) {
                if (!index.isAvailable()) {
                    continue;
                }
                IndexOperation secondaryIndexOperation = findSecondaryIndexOperation(index, expressionWhere, table);
                if (secondaryIndexOperation != null) {
                    result = secondaryIndexOperation;
                    break;
                }
            }
        }
    }
    where.setIndexOperation(result);
    Expression filterPk = findFiltersOnPrimaryKey(table, table.name, expressionWhere);
    where.setPrimaryKeyFilter(filterPk);
}

Example 2 with PrimaryIndexSeek

use of herddb.index.PrimaryIndexSeek in project herddb by diennea.

the class CalcitePlanner method scanForIndexAccess.

private IndexOperation scanForIndexAccess(CompiledSQLExpression expressionWhere, Table table, TableSpaceManager tableSpaceManager) {
    SQLRecordKeyFunction keyFunction = findIndexAccess(expressionWhere, table.primaryKey, table, "=", table);
    IndexOperation result = null;
    if (keyFunction != null) {
        if (keyFunction.isFullPrimaryKey()) {
            result = new PrimaryIndexSeek(keyFunction);
        } else {
            result = new PrimaryIndexPrefixScan(keyFunction);
        }
    } else {
        SQLRecordKeyFunction rangeMin = findIndexAccess(expressionWhere, table.primaryKey, table, ">=", table);
        if (rangeMin != null && !rangeMin.isFullPrimaryKey()) {
            rangeMin = null;
        }
        if (rangeMin == null) {
            rangeMin = findIndexAccess(expressionWhere, table.primaryKey, table, ">", table);
            if (rangeMin != null && !rangeMin.isFullPrimaryKey()) {
                rangeMin = null;
            }
        }
        SQLRecordKeyFunction rangeMax = findIndexAccess(expressionWhere, table.primaryKey, table, "<=", table);
        if (rangeMax != null && !rangeMax.isFullPrimaryKey()) {
            rangeMax = null;
        }
        if (rangeMax == null) {
            rangeMax = findIndexAccess(expressionWhere, table.primaryKey, table, "<", table);
            if (rangeMax != null && !rangeMax.isFullPrimaryKey()) {
                rangeMax = null;
            }
        }
        if (rangeMin != null || rangeMax != null) {
            result = new PrimaryIndexRangeScan(table.primaryKey, rangeMin, rangeMax);
        }
    }
    if (result == null) {
        Map<String, AbstractIndexManager> indexes = tableSpaceManager.getIndexesOnTable(table.name);
        if (indexes != null) {
            // TODO: use some kind of statistics, maybe using an index is more expensive than a full table scan
            for (AbstractIndexManager index : indexes.values()) {
                if (!index.isAvailable()) {
                    continue;
                }
                IndexOperation secondaryIndexOperation = findSecondaryIndexOperation(index, expressionWhere, table);
                if (secondaryIndexOperation != null) {
                    result = secondaryIndexOperation;
                    break;
                }
            }
        }
    }
    return result;
}

Example 3 with PrimaryIndexSeek

use of herddb.index.PrimaryIndexSeek in project herddb by diennea.

the class RawSQLTest method indexSeek.

@Test
public void indexSeek() throws Exception {
    String nodeId = "localhost";
    try (DBManager manager = new DBManager("localhost", new MemoryMetadataStorageManager(), new MemoryDataStorageManager(), new MemoryCommitLogManager(), null, null)) {
        manager.start();
        CreateTableSpaceStatement st1 = new CreateTableSpaceStatement("tblspace1", Collections.singleton(nodeId), nodeId, 1, 0, 0);
        manager.executeStatement(st1, StatementEvaluationContext.DEFAULT_EVALUATION_CONTEXT(), TransactionContext.NO_TRANSACTION);
        manager.waitForTablespace("tblspace1", 10000);
        execute(manager, "CREATE TABLE tblspace1.tsql (k1 string primary key,n1 int,s1 string)", Collections.emptyList());
        assertEquals(1, executeUpdate(manager, "INSERT INTO tblspace1.tsql(k1,n1) values(?,?)", Arrays.asList("mykey", Integer.valueOf(1234))).getUpdateCount());
        {
            TranslatedQuery translated = manager.getPlanner().translate(TableSpace.DEFAULT, " SELECT k1 as theKey,'one' as theStringConstant,3  LongConstant" + " FROM tblspace1.tsql" + " where k1 ='mykey'", Collections.emptyList(), true, true, false, -1);
            ScanStatement scan = translated.plan.mainStatement.unwrap(ScanStatement.class);
            assertTrue(scan.getPredicate().getIndexOperation() instanceof PrimaryIndexSeek);
            try (DataScanner scan1 = ((ScanResult) manager.executePlan(translated.plan, translated.context, TransactionContext.NO_TRANSACTION)).dataScanner) {
                List<DataAccessor> records = scan1.consume();
                assertEquals(1, records.size());
                assertEquals(3, records.get(0).getFieldNames().length);
                assertEquals(3, records.get(0).toMap().size());
                assertEquals("thekey", records.get(0).getFieldNames()[0].toLowerCase());
                assertEquals(RawString.of("mykey"), records.get(0).get("theKey"));
                assertEquals("thestringconstant", records.get(0).getFieldNames()[1].toLowerCase());
                assertEquals(RawString.of("one"), records.get(0).get("theStringConstant"));
                assertEquals("longconstant", records.get(0).getFieldNames()[2].toLowerCase());
                assertEquals(3L, ((Number) records.get(0).get("LongConstant")).longValue());
            }
        }
        {
            TranslatedQuery translate1 = manager.getPlanner().translate(TableSpace.DEFAULT, "SELECT k1 as theKey,'one' as theStringConstant,3  LongConstant FROM tblspace1.tsql where k1 = 'mykey_no'", Collections.emptyList(), true, true, false, -1);
            ScanStatement scan = translate1.plan.mainStatement.unwrap(ScanStatement.class);
            assertTrue(scan.getPredicate().getIndexOperation() instanceof PrimaryIndexSeek);
            try (DataScanner scan1 = manager.scan(scan, translate1.context, TransactionContext.NO_TRANSACTION)) {
                assertTrue(scan1.consume().isEmpty());
            }
        }
        {
            TranslatedQuery translate1 = manager.getPlanner().translate(TableSpace.DEFAULT, "SELECT k1 as theKey,'one' as theStringConstant,3  LongConstant FROM tblspace1.tsql where k1 = 'mykey' and n1<>1234", Collections.emptyList(), true, true, false, -1);
            ScanStatement scan = translate1.plan.mainStatement.unwrap(ScanStatement.class);
            assertTrue(scan.getPredicate().getIndexOperation() instanceof PrimaryIndexSeek);
            try (DataScanner scan1 = manager.scan(scan, translate1.context, TransactionContext.NO_TRANSACTION)) {
                assertTrue(scan1.consume().isEmpty());
            }
        }
    }
}

Example 4 with PrimaryIndexSeek

use of herddb.index.PrimaryIndexSeek in project herddb by diennea.

the class TableManager method accessTableData.

private void accessTableData(ScanStatement statement, StatementEvaluationContext context, ScanResultOperation consumer, Transaction transaction, boolean lockRequired, boolean forWrite) throws StatementExecutionException {
    statement.validateContext(context);
    Predicate predicate = statement.getPredicate();
    long _start = System.currentTimeMillis();
    boolean acquireLock = transaction != null || forWrite || lockRequired;
    LocalScanPageCache lastPageRead = acquireLock ? null : new LocalScanPageCache();
    try {
        IndexOperation indexOperation = predicate != null ? predicate.getIndexOperation() : null;
        boolean primaryIndexSeek = indexOperation instanceof PrimaryIndexSeek;
        AbstractIndexManager useIndex = getIndexForTbleAccess(indexOperation);
        BatchOrderedExecutor.Executor<Map.Entry<Bytes, Long>> scanExecutor = (List<Map.Entry<Bytes, Long>> batch) -> {
            for (Map.Entry<Bytes, Long> entry : batch) {
                Bytes key = entry.getKey();
                boolean keep_lock = false;
                boolean already_locked = transaction != null && transaction.lookupLock(table.name, key) != null;
                LockHandle lock = acquireLock ? (forWrite ? lockForWrite(key, transaction) : lockForRead(key, transaction)) : null;
                try {
                    if (transaction != null) {
                        if (transaction.recordDeleted(table.name, key)) {
                            // skip this record. inside current transaction it has been deleted
                            continue;
                        }
                        Record record = transaction.recordUpdated(table.name, key);
                        if (record != null) {
                            // use current transaction version of the record
                            if (predicate == null || predicate.evaluate(record, context)) {
                                consumer.accept(record);
                                keep_lock = true;
                            }
                            continue;
                        }
                    }
                    Long pageId = entry.getValue();
                    if (pageId != null) {
                        boolean pkFilterCompleteMatch = false;
                        if (!primaryIndexSeek && predicate != null) {
                            Predicate.PrimaryKeyMatchOutcome outcome = predicate.matchesRawPrimaryKey(key, context);
                            if (outcome == Predicate.PrimaryKeyMatchOutcome.FAILED) {
                                continue;
                            } else if (outcome == Predicate.PrimaryKeyMatchOutcome.FULL_CONDITION_VERIFIED) {
                                pkFilterCompleteMatch = true;
                            }
                        }
                        Record record = fetchRecord(key, pageId, lastPageRead);
                        if (record != null && (pkFilterCompleteMatch || predicate == null || predicate.evaluate(record, context))) {
                            consumer.accept(record);
                            keep_lock = true;
                        }
                    }
                } finally {
                    // release the lock on the key if it did not match scan criteria
                    if (transaction == null) {
                        if (lock != null) {
                            if (forWrite) {
                                locksManager.releaseWriteLockForKey(key, lock);
                            } else {
                                locksManager.releaseReadLockForKey(key, lock);
                            }
                        }
                    } else if (!keep_lock && !already_locked) {
                        transaction.releaseLockOnKey(table.name, key, locksManager);
                    }
                }
            }
        };
        BatchOrderedExecutor<Map.Entry<Bytes, Long>> executor = new BatchOrderedExecutor<>(SORTED_PAGE_ACCESS_WINDOW_SIZE, scanExecutor, SORTED_PAGE_ACCESS_COMPARATOR);
        Stream<Map.Entry<Bytes, Long>> scanner = keyToPage.scanner(indexOperation, context, tableContext, useIndex);
        boolean exit = false;
        try {
            scanner.forEach(executor);
            executor.finish();
        } catch (ExitLoop exitLoop) {
            exit = !exitLoop.continueWithTransactionData;
            if (LOGGER.isLoggable(Level.FINEST)) {
                LOGGER.log(Level.FINEST, "exit loop during scan {0}, started at {1}: {2}", new Object[] { statement, new java.sql.Timestamp(_start), exitLoop.toString() });
            }
        } catch (final HerdDBInternalException error) {
            LOGGER.log(Level.SEVERE, "error during scan", error);
            if (error.getCause() instanceof StatementExecutionException) {
                throw (StatementExecutionException) error.getCause();
            } else if (error.getCause() instanceof DataStorageManagerException) {
                throw (DataStorageManagerException) error.getCause();
            } else if (error instanceof StatementExecutionException) {
                throw (StatementExecutionException) error;
            } else if (error instanceof DataStorageManagerException) {
                throw (DataStorageManagerException) error;
            } else {
                throw new StatementExecutionException(error);
            }
        }
        if (!exit && transaction != null) {
            consumer.beginNewRecordsInTransactionBlock();
            Collection<Record> newRecordsForTable = transaction.getNewRecordsForTable(table.name);
            for (Record record : newRecordsForTable) {
                if (!transaction.recordDeleted(table.name, record.key) && (predicate == null || predicate.evaluate(record, context))) {
                    consumer.accept(record);
                }
            }
        }
    } catch (ExitLoop exitLoop) {
        if (LOGGER.isLoggable(Level.FINEST)) {
            LOGGER.log(Level.FINEST, "exit loop during scan {0}, started at {1}: {2}", new Object[] { statement, new java.sql.Timestamp(_start), exitLoop.toString() });
        }
    } catch (StatementExecutionException err) {
        LOGGER.log(Level.SEVERE, "error during scan {0}, started at {1}: {2}", new Object[] { statement, new java.sql.Timestamp(_start), err.toString() });
        throw err;
    } catch (HerdDBInternalException err) {
        LOGGER.log(Level.SEVERE, "error during scan {0}, started at {1}: {2}", new Object[] { statement, new java.sql.Timestamp(_start), err.toString() });
        throw new StatementExecutionException(err);
    }
}

Example 5 with PrimaryIndexSeek

use of herddb.index.PrimaryIndexSeek in project herddb by diennea.

the class TableManager method streamTableData.

private Stream<Record> streamTableData(ScanStatement statement, StatementEvaluationContext context, Transaction transaction, boolean lockRequired, boolean forWrite) throws StatementExecutionException {
    statement.validateContext(context);
    Predicate predicate = statement.getPredicate();
    boolean acquireLock = transaction != null || forWrite || lockRequired;
    LocalScanPageCache lastPageRead = acquireLock ? null : new LocalScanPageCache();
    IndexOperation indexOperation = predicate != null ? predicate.getIndexOperation() : null;
    boolean primaryIndexSeek = indexOperation instanceof PrimaryIndexSeek;
    AbstractIndexManager useIndex = getIndexForTbleAccess(indexOperation);
    Stream<Map.Entry<Bytes, Long>> scanner = keyToPage.scanner(indexOperation, context, tableContext, useIndex);
    Stream<Record> resultFromTable = scanner.map(entry -> {
        return accessRecord(entry, predicate, context, transaction, lastPageRead, primaryIndexSeek, forWrite, acquireLock);
    }).filter(r -> r != null);
    return resultFromTable;
}