matriv

"""
About
=====
Test cases for CrateDB using `asyncpg`.

Usage
=====
Normally, this will be executed through the main Python test suite
triggered through the toplevel `Jenkinsfile`.

matriv

/*
 * Licensed to the Apache Software Foundation (ASF) under one
 * or more contributor license agreements.  See the NOTICE file
 * distributed with this work for additional information
 * regarding copyright ownership.  The ASF licenses this file
 * to you under the Apache License, Version 2.0 (the
 * "License"); you may not use this file except in compliance
 * with the License.  You may obtain a copy of the License at
 *
 * http://www.apache.org/licenses/LICENSE-2.0

matriv

package org.sample;

import org.openjdk.jmh.annotations.Benchmark;
import org.openjdk.jmh.annotations.Scope;
import org.openjdk.jmh.annotations.State;
import org.openjdk.jmh.infra.Blackhole;

import java.util.ArrayDeque;
import java.util.Deque;
import java.util.HashMap;

matriv

package org.sample;

import org.openjdk.jmh.annotations.Benchmark;
import org.openjdk.jmh.annotations.Scope;
import org.openjdk.jmh.annotations.State;
import org.openjdk.jmh.infra.Blackhole;

import java.util.ArrayDeque;
import java.util.Deque;
import java.util.HashMap;

matriv

Left table 5x bigger Right table, Left table gets distributed to preserve the OrderBy
select t2.id1, t1.id1 from matriv.t2, matriv.t1 where t2.id1=t1.id1 order by 1 limit 2000
select t2.id1, t1.id1 from matriv.t2, matriv.t1 where t2.id1=t1.id1 and t2.id2=t1.id2 order by 1 limit 2000

                |  All rows match conc=1 | All rows match conc=15 | 4/5 of rows filtered out conc=1 | 4/5 of rows filtered out conc=1
================+========================+========================+=================================+================================
Distributed     |               2135.051 |               5307.234 |                        3383.737 |                       9010.810
----------------+------------------------+------------------------+---------------------------------+--------------------------------
Non-distributed |              10768.582 |              29048.164 |                       17138.134 |                      42107.285
----------------+------------------------+------------------------+----

matriv

                |  All rows match (Cart product) | 4/5 of rows filtered out
----------------+--------------------------------+-------------------------
concurrency = 1 |                                |
================+================================+=========================
Distributed     |                       2192.042 |              3634.034                       
----------------+--------------------------------+-------------------------
Non-distributed |                       1896.855 |             14893.225
---------------------------------------------------------------------------

matriv

####### SETUP #######
CREATE TABLE t1(
    id1 int,
    id2 int
) with (number_of_replicas=0);
CREATE TABLE t2(
    id1 int,
    id2 int
) with (number_of_replicas=0);

matriv

https://github.com/crate/crate/compare/mt/join-benchmarks?expand=1

# Run complete. Total time: 00:46:48

Benchmark                                    Mode  Cnt    Score    Error  Units
JoinBenchmark.measureBlockHash_leftSmall     avgt  200    0.334 ±  0.002  ms/op
JoinBenchmark.measureBlockHash_rightSmall    avgt  200    1.065 ±  0.015  ms/op
JoinBenchmark.measureBlockNL_leftSmall       avgt  200    0.223 ±  0.001  ms/op
JoinBenchmark.measureBlockNL_rightSmall      avgt  200  287.090 ±  2.272  ms/op
JoinBenchmark.measurePlainNL_leftSmall       avgt  200  684.082 ± 12.079  ms/op

matriv

import java.sql.BatchUpdateException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.Properties;

public class JdbcTest {

matriv

Simple Subselect
----------------
select x, a, b from (
  select x, a, b from t1 order by x asc limit 10
  ) t1 
order by x desc 
limit 3

Plan:
  Subplan