Examples with AbstractPlanNode org.voltdb.plannodes.AbstractPlanNode used on opensource projects

Example 16 with AbstractPlanNode

use of org.voltdb.plannodes.AbstractPlanNode in project voltdb by VoltDB.

the class TestIndexSelection method testHeadToHeadFilters.

public void testHeadToHeadFilters() {
    // Each pair of strings contains an indexable query filter and a pattern that
    // its index optimization's plan will contain in its "explain" output.
    // The pairs are sorted in preference order, so we can generally expect to find
    // the pattern after planning a query with the filter by itself or in combination
    // with any filter listed later. There are currently exceptions, which are
    // counted below as "surprises", which we hope will not regress (increase)
    // as we evolve the cost calculations. The current costing does some particularly
    // surprising things with the relative costing of plans for predicates that
    // generally combine use of single and/or double-ended range filters
    // with unique and/or non-unique index filters, especially for compound indexes.
    String[][] head_to_head_filters = { { "uniquehashable = ?", "HASHUNIQUEHASH" }, { "component1 = ? and component2unique = ?", "COMPOUNDUNIQUE" }, { "primarykey = ?", "its primary" }, //{"uniquekey = ?", "POLYPOINTS_UNIQUEKEY" },
    { "contains(poly, ?)", "POINTSPOLY" }, { "component1 = ? and component2non = ?", "COMPOUNDNON" }, { "nonuniquekey = ?", "NONUNIQUE" }, { "component1 = ? and component2unique between ? and ?", "COMPOUNDUNIQUE" }, //{"primarykey between ? and ?", "its primary" },
    { "uniquekey between ? and ?", "POLYPOINTS_UNIQUEKEY" }, //{"component1 = ? and component2non between ? and ?", "" },
    { "nonuniquekey between ? and ?", "" }, { "component1 = ? and component2unique > ?", "" }, { "component1 = ? and component2non > ?", "COMPOUNDNON" }, { "primarykey > ?", "" }, { "uniquekey > ?", "UNIQUEKEY" }, { "nonuniquekey > ?", "" } };
    // Some number of "surprises" are deemed acceptable at least for now.
    // The number was determined empirically as of V6.1.
    final int ACCEPTABLE_SURPRISES = 5;
    int surprises = 0;
    StringBuffer surpriseDetails = new StringBuffer();
    for (int ii = 0; ii < head_to_head_filters.length; ++ii) {
        String[] filter1 = head_to_head_filters[ii];
        for (int jj = ii + 1; jj < head_to_head_filters.length; ++jj) {
            String[] filter2 = head_to_head_filters[jj];
            AbstractPlanNode pn = compile("select polys.point from polypoints polys " + "where " + filter1[0] + " and " + filter2[0] + " ;");
            if (pn.toExplainPlanString().contains(filter1[1])) {
                /* enable to debug */
                System.out.println();
            } else {
                String detail = "The query filtered by " + filter1[0] + " AND " + filter2[0] + " is not using " + filter1[1] + " index.";
                surpriseDetails.append(detail).append("\n");
                /* enable to debug */
                System.out.println("WARNING: " + ii + " vs. " + jj + " " + detail);
                //* enable to debug */ System.out.println("DEBUG: " + ii + " vs. " + jj + " " + pn.toExplainPlanString());
                ++surprises;
            }
        }
    }
    if (surprises != ACCEPTABLE_SURPRISES) {
        // Only report all of the surprise details when the number of surprises changes.
        System.out.println("DEBUG: total plan surprises: " + surprises + " out of " + (head_to_head_filters.length * (head_to_head_filters.length - 1) / 2) + ".");
        System.out.println(surpriseDetails);
        if (surprises < ACCEPTABLE_SURPRISES) {
            System.out.println("DEBUG: consider further constraining the baseline number to:");
            System.out.println("        final int ACCEPTABLE_SURPRISES = " + surprises + ";");
        }
        // Only fail the test when the number of surprises goes up.
        assertTrue(surprises < ACCEPTABLE_SURPRISES);
    }
}

Example 17 with AbstractPlanNode

use of org.voltdb.plannodes.AbstractPlanNode in project voltdb by VoltDB.

the class TestPlansApproxCountDistinct method testWithSubqueries.

public void testWithSubqueries() throws Exception {
    // Single-partition statement with a subquery (table agg)
    List<AbstractPlanNode> pn = compileToFragments("select * " + "from " + "  T1, " + "  (select approx_count_distinct(age) from t1) as subq");
    assertEquals(1, pn.size());
    assertFragContainsAggWithFunctions(pn.get(COORDINATOR_FRAG), AGGREGATE_APPROX_COUNT_DISTINCT);
    // Single-partition statement with a subquery (with group by)
    pn = compileToFragments("select * " + "from " + "  (select username, approx_count_distinct(age), avg(distinct points) " + "   from t2 " + "   group by username) as subq" + "  inner join t2 " + "  on t2.username = subq.username;");
    assertEquals(1, pn.size());
    assertFragContainsAggWithFunctions(pn.get(COORDINATOR_FRAG), AGGREGATE_APPROX_COUNT_DISTINCT, AGGREGATE_AVG);
    // multi-partition table agg
    pn = compileToFragments("select * " + "from " + "t1, " + "(SELECT sum(distinct ratio), approx_count_distinct(num) from P1) as subq");
    assertFragContainsAggWithFunctions(pn.get(COORDINATOR_FRAG), AGGREGATE_SUM, AGGREGATE_APPROX_COUNT_DISTINCT);
    assertFragContainsNoAggPlanNodes(pn.get(PARTITION_FRAG));
    // single-part plan on partitioned tables, with GB in subquery
    pn = compileToFragments("select * " + "from p1 " + "inner join " + "(SELECT id, sum(distinct ratio), approx_count_distinct(num) " + "from P1 " + "where id = 10 " + "group by id) as subq " + "on subq.id = p1.id");
    assertEquals(1, pn.size());
    assertFragContainsAggWithFunctions(pn.get(COORDINATOR_FRAG), AGGREGATE_SUM, AGGREGATE_APPROX_COUNT_DISTINCT);
    // multi-part plan on partitioned tables, with GB in subquery
    pn = compileToFragments("select * " + "from t1 " + "inner join " + "(SELECT id, approx_count_distinct(num) " + "from P1 " + "group by id) as subq " + "on subq.id = t1.id");
    for (AbstractPlanNode n : pn) {
        System.out.println(n.toExplainPlanString());
    }
    assertEquals(2, pn.size());
    assertFragContainsNoAggPlanNodes(pn.get(COORDINATOR_FRAG));
    assertFragContainsAggWithFunctions(pn.get(PARTITION_FRAG), AGGREGATE_APPROX_COUNT_DISTINCT);
}

Example 18 with AbstractPlanNode

use of org.voltdb.plannodes.AbstractPlanNode in project voltdb by VoltDB.

the class TestPlansCount method testCountStar26.

// test with FLOAT type
public void testCountStar26() {
    List<AbstractPlanNode> pn = compileToFragments("SELECT count(*) from P1 WHERE NUM = 1 AND RATIO >= ?");
    for (AbstractPlanNode nd : pn) System.out.println("PlanNode Explain string:\n" + nd.toExplainPlanString());
    AbstractPlanNode p = pn.get(0).getChild(0);
    assertTrue(p instanceof AggregatePlanNode);
    p = pn.get(1).getChild(0);
    assertTrue(p instanceof IndexCountPlanNode);
    pn = compileToFragments("SELECT count(1) from P1 WHERE NUM = 1 AND RATIO >= ?");
    for (AbstractPlanNode nd : pn) System.out.println("PlanNode Explain string:\n" + nd.toExplainPlanString());
    p = pn.get(0).getChild(0);
    assertTrue(p instanceof AggregatePlanNode);
    p = pn.get(1).getChild(0);
    assertTrue(p instanceof IndexCountPlanNode);
}

Example 19 with AbstractPlanNode

use of org.voltdb.plannodes.AbstractPlanNode in project voltdb by VoltDB.

the class TestPlansCount method testCountStar20.

// test with group by with Replicated table
public void testCountStar20() {
    List<AbstractPlanNode> pn = compileToFragments("SELECT AGE, count(*) from T2 WHERE USERNAME ='XIN' AND POINTS < 1 Group by AGE");
    for (AbstractPlanNode nd : pn) System.out.println("PlanNode Explain string:\n" + nd.toExplainPlanString());
    AbstractPlanNode p = pn.get(0).getChild(0);
    assertTrue(p instanceof IndexScanPlanNode);
    assertTrue(p.getInlinePlanNode(PlanNodeType.AGGREGATE) != null || p.getInlinePlanNode(PlanNodeType.HASHAGGREGATE) != null);
    pn = compileToFragments("SELECT AGE, count(1) from T2 WHERE USERNAME ='XIN' AND POINTS < 1 Group by AGE");
    for (AbstractPlanNode nd : pn) System.out.println("PlanNode Explain string:\n" + nd.toExplainPlanString());
    p = pn.get(0).getChild(0);
    assertTrue(p instanceof IndexScanPlanNode);
    assertTrue(p.getInlinePlanNode(PlanNodeType.AGGREGATE) != null || p.getInlinePlanNode(PlanNodeType.HASHAGGREGATE) != null);
}

Example 20 with AbstractPlanNode

use of org.voltdb.plannodes.AbstractPlanNode in project voltdb by VoltDB.

the class TestIndexSelection method testEng3850ComplexIndexablePlan.

// This tests recognition of a complex expression value
// -- an addition -- used as an indexable join key's search key value.
// Some time ago, this would throw a casting error in the planner.
public void testEng3850ComplexIndexablePlan() {
    AbstractPlanNode pn = compile("select id from a, t where a.id < (t.a + ?);");
    pn = pn.getChild(0);
    pn = pn.getChild(0);
    //*enable to debug*/System.out.println("DEBUG: " + pn.toExplainPlanString());
    assertTrue(pn instanceof NestLoopIndexPlanNode);
    IndexScanPlanNode indexScan = ((NestLoopIndexPlanNode) pn).getInlineIndexScan();
    assertEquals(IndexLookupType.LT, indexScan.getLookupType());
    assertEquals(HSQLInterface.AUTO_GEN_NAMED_CONSTRAINT_IDX + "ID", indexScan.getTargetIndexName());
    pn = pn.getChild(0);
    assertTrue(pn instanceof SeqScanPlanNode);
    SeqScanPlanNode sspn = (SeqScanPlanNode) pn;
    //*enable to debug*/System.out.println("DEBUG: " + pn.toJSONString());
    assertEquals("T", sspn.getTargetTableName());
}