sql_bootstrap.Rmd

---
title: "SQL Bootstrap"
output: html_notebook
---

```{r}
library(boot)
library(data.table)
library(ggplot2)
```

```{r}
examples <- fread("example-data/examples.csv", stringsAsFactors = TRUE)
summary(examples)
```

## Running Times

```{r}
benchmark <- rbind(
  cbind(tool = "bq", fread("benchmark-bq.csv", stringsAsFactors = TRUE)),
  cbind(tool = "pg", fread("benchmark-pg.csv", stringsAsFactors = TRUE))
)
benchmark[, tool := factor(tool)]

# Filter out records that are used for checking (below) rather than
# benchmarking.
benchmark <- benchmark[trial <= 10 & replicates %in% c(1000)]

summary(benchmark)
```

```{r}
bootstrapConfidenceIntervals <- function(df, metaReplicates = 1000) {
  b <- boot(
    df,
    function(data, indexes) {
      t(data[indexes, .(mean(elapsed), var(elapsed))])
    },
    metaReplicates
  )
  ci <- if (max(b$t[, 1]) - min(b$t[, 1]) < 1e-3) {
    # not enough variance for boot.ci
    rep(c(min(b$t[, 1]), max(b$t[, 1])), 2)
  } else {
    c(
      boot.ci(b, type = "perc")$percent[4:5],
      boot.ci(b, type = "stud")$student[4:5]
    )
  }
  data.table(
    variable = factor("elapsed"),
    metaType = factor(c(rep(c("percent", "student"), each = 2), NA)),
    point = factor(c(rep(c("lo", "hi"), 2), "avg")),
    value = c(ci, b$t0[1])
  )
}
benchmarkCi <- benchmark[
  , bootstrapConfidenceIntervals(.SD),
  .(exampleId, tool, replicates, kind, type)
]
summary(benchmarkCi)
```

```{r}
benchmarkCiWithExamples <- merge(
  benchmarkCi, examples,
  by.x = "exampleId", by.y = "id"
)
summary(benchmarkCiWithExamples)
```

```{r}
p <- ggplot(
  dcast(
    benchmarkCiWithExamples[
      variable == "elapsed" & type == "percent" & (
        is.na(metaType) | metaType == "percent")
    ],
    tool + kind + numCatsOrder ~ point
  ),
  aes(
    x = numCatsOrder, y = avg,
    color = ifelse(kind == "pure", "Pure", "Poisson")
  )
) +
  geom_line(aes(linetype = ifelse(tool == "bq", "BigQuery", "PostgreSQL"))) +
  geom_errorbar(aes(ymin = lo, ymax = hi), width = 0.2) +
  scale_x_continuous(
    "Sample Size (Number of Cats)",
    labels = function(b) parse(text = paste0("10^", b))
  ) +
  scale_y_continuous("Mean Elapsed Time (s; lower is better)") +
  scale_linetype_discrete(name = "Tool") +
  scale_color_discrete(name = "Query Kind") +
  labs(
    title = "SQL Bootstrap Benchmarks",
    subtitle = "Elapsed times for 1000 bootstrap resamples"
  )
ggsave(file = "benchmark.svg", plot = p, width = 7, height = 4)
p
```

## Check Confidence Intervals

```{r}
load("check-r.RData")
checkR <- results[exampleId == 6]
rm(results)
checkR[, user := NULL]
checkR[, system := NULL]
checkR[, tool := factor("R")]
summary(checkR)
```

```{r}
checkPg <- fread("check-pg.csv", stringsAsFactors = TRUE)
checkPg[, tool := factor("pg")]
summary(checkPg)
```

```{r}
checkBq <- fread("check-bq.csv", stringsAsFactors = TRUE)
checkBq[, tool := factor("bq")]
summary(checkBq)
```

```{r}
check <- merge(
  rbind(checkR, checkPg, checkBq),
  examples, by.x = "exampleId", by.y = "id")
summary(check)
```

```{r}
checkCompare <- melt(
  check[
    replicates == 1000 & type != "bca",
    .(tool, trial, kind, type, lo = measureLo, hi = measureHi)
  ],
  measure.vars = c("lo", "hi"),
  variable.name = "measure"
)
# Repeat the reference values for postgres and bigquery.
checkCompare <- rbind(
  checkCompare,
  checkCompare[type %in% c("t", "z") & trial == 1, .(
    tool = "pg", trial, kind, type, measure, value
  )],
  checkCompare[type %in% c("t", "z") & trial == 1, .(
    tool = "bq", trial, kind, type, measure, value
  )])
levels(checkCompare$tool)[levels(checkCompare$tool) == "pg"] <- "PostgreSQL"
levels(checkCompare$tool)[levels(checkCompare$tool) == "bq"] <- "BigQuery"
levels(checkCompare$measure)[levels(checkCompare$measure) == "lo"] <-
  "Lower 95% CI"
levels(checkCompare$measure)[levels(checkCompare$measure) == "hi"] <-
  "Upper 95% CI"
levels(checkCompare$kind)[levels(checkCompare$kind) == "poisson"] <-
  "Poisson SQL"
levels(checkCompare$kind)[levels(checkCompare$kind) == "pure"] <-
  "Pure SQL"
head(checkCompare)
```

```{r}
referenceValuesLabels <- c(
  "t" = "Student's t",
  "z" = "Normal"
)
p <- ggplot(
  checkCompare[type == "percent"],
  aes(x = value, y = kind)) +
  geom_violin(
    aes(group = kind),
    position = "identity") +
  geom_vline(
    aes(xintercept = value, color = type),
    data = checkCompare[type %in% c("z", "t") & trial == 1]
  ) +
  scale_color_discrete("Reference CIs", labels = referenceValuesLabels) +
  xlab("Mass (kg)") +
  ylab("Method") +
  labs(
    title = "Checks on SQL Bootstrap Confidence Intervals",
    subtitle = expression(paste("For 100 cats with 1000 bootstrap resamples"))
  ) +
  facet_grid(tool ~ measure, scales = "free", drop = TRUE)
ggsave(file = "check.svg", plot = p, width = 9, height = 4)
p
```

```{r}
p <- ggplot(checkCompare, aes(value, trial)) +
  geom_point(
    aes(color = type, shape = kind),
    data = checkCompare[type %in% c("percent", "student")]
  ) +
  facet_grid(. ~ measure, scales = "free") +
  geom_vline(
    aes(xintercept = value, color = type, linetype = kind),
    data = checkCompare[
      ,
      .(value = mean(value)),
      .(kind, type, measure)
    ]
  )
ggsave(file = "check-all.svg", plot = p, width = 14, height = 8)
p
```