{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# DuckDB GeoParquet Benchmarks\n",
    "\n",
    "This notebook measures direct DuckDB queries against local STAC GeoParquet outputs.\n",
    "\n",
    "The benchmark goal is to compare Parquet layouts, not hash generation speed. Run `scripts/sync-benchmark-data.sh` before timing queries. Use `scripts/generate-file-count-matched-geoparquet.sh` if you want to rebuild the 12-file hashed layout locally."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Setup\n",
    "\n",
    "Expected Python packages:\n",
    "\n",
    "- `duckdb`\n",
    "- `stac-hash`, only if you want to compute hash range parameters in Python\n",
    "\n",
    "The notebook uses DuckDB's spatial extension for exact geometry queries and `httpfs` for the optional remote S3 benchmark."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "ef1a6def",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'1.5.5'"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from __future__ import annotations\n",
    "\n",
    "from dataclasses import dataclass\n",
    "from datetime import datetime, timezone\n",
    "from pathlib import Path\n",
    "import json\n",
    "import statistics\n",
    "import time\n",
    "\n",
    "import duckdb\n",
    "import pandas as pd\n",
    "\n",
    "con = duckdb.connect(database=':memory:')\n",
    "con.execute('INSTALL spatial')\n",
    "con.execute('LOAD spatial')\n",
    "con.execute('INSTALL httpfs')\n",
    "con.execute('LOAD httpfs')\n",
    "con.execute(\"\"\"\n",
    "CREATE OR REPLACE SECRET benchmark_public_s3 (\n",
    "    TYPE s3,\n",
    "    PROVIDER config,\n",
    "    REGION 'us-west-2',\n",
    "    ENDPOINT 's3.us-west-2.amazonaws.com',\n",
    "    URL_STYLE 'path',\n",
    "    USE_SSL true\n",
    ")\n",
    "\"\"\")\n",
    "con.execute('PRAGMA threads = 8')\n",
    "con.execute('SET enable_external_file_cache = false')\n",
    "\n",
    "duckdb.__version__"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "21124d02",
   "metadata": {},
   "source": [
    "## Dataset Variants\n",
    "\n",
    "Point each local variant at a GeoParquet file or glob. Remote variants use the same Source Cooperative layouts over S3 so object-store listing, range reads, and network latency are included. Keep all variants semantically equivalent so row counts match across layouts."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "492f8030",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "({'microsoft': '../data/benchmarks/source/mspc-sentinel-2-l2a/*.parquet',\n",
       "  'hashed_128_files': '../data/benchmarks/source/mspc-sentinel-2-l2a-sorted/**/*.parquet',\n",
       "  'hashed_12_files': '../data/benchmarks/generated/mspc-sentinel-2-l2a-sorted-12-files/*.parquet'},\n",
       " {'remote_microsoft': 's3://us-west-2.opendata.source.coop/developmentseed/stac-geoparquet/mspc-sentinel-2-l2a/*.parquet',\n",
       "  'remote_hashed_128_files': 's3://us-west-2.opendata.source.coop/developmentseed/stac-geoparquet/mspc-sentinel-2-l2a-sorted/**/*.parquet',\n",
       "  'remote_hashed_12_files': 's3://us-west-2.opendata.source.coop/developmentseed/stac-geoparquet/mspc-sentinel-2-l2a-sorted-12-files/*.parquet'})"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "DATASETS = {\n",
    "    'microsoft': '../data/benchmarks/source/mspc-sentinel-2-l2a/*.parquet',\n",
    "    'hashed_128_files': '../data/benchmarks/source/mspc-sentinel-2-l2a-sorted/**/*.parquet',\n",
    "    'hashed_12_files': '../data/benchmarks/generated/mspc-sentinel-2-l2a-sorted-12-files/*.parquet',\n",
    "}\n",
    "\n",
    "SOURCE_COOPERATIVE_PREFIX = 's3://us-west-2.opendata.source.coop/developmentseed/stac-geoparquet'\n",
    "REMOTE_DATASETS = {\n",
    "    'remote_microsoft': f'{SOURCE_COOPERATIVE_PREFIX}/mspc-sentinel-2-l2a/*.parquet',\n",
    "    'remote_hashed_128_files': f'{SOURCE_COOPERATIVE_PREFIX}/mspc-sentinel-2-l2a-sorted/**/*.parquet',\n",
    "    'remote_hashed_12_files': f'{SOURCE_COOPERATIVE_PREFIX}/mspc-sentinel-2-l2a-sorted-12-files/*.parquet',\n",
    "}\n",
    "\n",
    "DATASETS, REMOTE_DATASETS"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "950d8fff",
   "metadata": {},
   "source": [
    "## Query Parameters\n",
    "\n",
    "Use fixed AOIs, time windows, collections, and ids so runs are comparable. Choose IDs that exist in every dataset variant. The source data currently covers 2025 Sentinel-2 L2A items.\n",
    "\n",
    "The following cell can auto-fill `PARAMS['id']` with a real item id if the placeholder is left unchanged.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "e675713b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'collection': 'sentinel-2-l2a',\n",
       " 'id': 'REPLACE_WITH_REAL_ITEM_ID',\n",
       " 'start_datetime': datetime.datetime(2025, 6, 1, 0, 0, tzinfo=datetime.timezone.utc),\n",
       " 'end_datetime': datetime.datetime(2025, 7, 1, 0, 0, tzinfo=datetime.timezone.utc),\n",
       " 'minx': -109.0,\n",
       " 'miny': 37.0,\n",
       " 'maxx': -102.0,\n",
       " 'maxy': 41.0,\n",
       " 'aoi_wkt': 'POLYGON((-109 37, -102 37, -102 41, -109 41, -109 37))',\n",
       " 'max_cloud_cover': 20.0,\n",
       " 'min_hash': 0,\n",
       " 'max_hash': 9223372036854775807}"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "PARAMS = {\n",
    "    'collection': 'sentinel-2-l2a',\n",
    "    'id': 'REPLACE_WITH_REAL_ITEM_ID',\n",
    "    'start_datetime': datetime(2025, 6, 1, tzinfo=timezone.utc),\n",
    "    'end_datetime': datetime(2025, 7, 1, tzinfo=timezone.utc),\n",
    "    'minx': -109.0,\n",
    "    'miny': 37.0,\n",
    "    'maxx': -102.0,\n",
    "    'maxy': 41.0,\n",
    "    'aoi_wkt': 'POLYGON((-109 37, -102 37, -102 41, -109 41, -109 37))',\n",
    "    'max_cloud_cover': 20.0,\n",
    "    # Fill these once the hash range strategy is selected.\n",
    "    'min_hash': 0,\n",
    "    'max_hash': 9223372036854775807,\n",
    "}\n",
    "\n",
    "PARAMS"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "0969c899",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'S2B_MSIL2A_20250101T031029_R075_T52VCJ_20250101T050301'"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Auto-fill a real item id for the needle-in-a-haystack query.\n",
    "# Override PARAMS['id'] manually above if you want a specific item.\n",
    "if PARAMS['id'] == 'REPLACE_WITH_REAL_ITEM_ID':\n",
    "    PARAMS['id'] = con.execute(\n",
    "        \"SELECT id FROM read_parquet(?, hive_partitioning = false) LIMIT 1\",\n",
    "        [DATASETS['microsoft']],\n",
    "    ).fetchone()[0]\n",
    "\n",
    "PARAMS['id']\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fb2f6590",
   "metadata": {},
   "source": [
    "## Helpers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "id": "c4b89a2f",
   "metadata": {},
   "outputs": [],
   "source": [
    "@dataclass(frozen=True)\n",
    "class BenchmarkResult:\n",
    "    dataset: str\n",
    "    query: str\n",
    "    rows: int | None\n",
    "    best_seconds: float\n",
    "    median_seconds: float\n",
    "    runs: tuple[float, ...]\n",
    "\n",
    "\n",
    "def sql_literal(value):\n",
    "    if isinstance(value, datetime):\n",
    "        return \"TIMESTAMPTZ '\" + value.isoformat().replace('+00:00', 'Z') + \"'\"\n",
    "    if isinstance(value, str):\n",
    "        return \"'\" + value.replace(\"'\", \"''\") + \"'\"\n",
    "    if value is None:\n",
    "        return 'NULL'\n",
    "    return str(value)\n",
    "\n",
    "\n",
    "def render(template: str, dataset_glob: str, params: dict) -> str:\n",
    "    values = {'parquet_glob': sql_literal(dataset_glob)}\n",
    "    values.update({key: sql_literal(value) for key, value in params.items()})\n",
    "    return template.format(**values)\n",
    "\n",
    "\n",
    "def run_sql(sql: str):\n",
    "    return con.execute(sql).fetchall()\n",
    "\n",
    "\n",
    "def time_query(sql: str, repeats: int = 5) -> tuple[int | None, tuple[float, ...]]:\n",
    "    rows = None\n",
    "    timings = []\n",
    "    for _ in range(repeats):\n",
    "        started = time.perf_counter()\n",
    "        result = con.execute(sql).fetchall()\n",
    "        timings.append(time.perf_counter() - started)\n",
    "        if len(result) == 1 and len(result[0]) == 1 and isinstance(result[0][0], int):\n",
    "            rows = result[0][0]\n",
    "        else:\n",
    "            rows = len(result)\n",
    "    return rows, tuple(timings)\n",
    "\n",
    "\n",
    "def explain_analyze(sql: str) -> str:\n",
    "    rows = con.execute('EXPLAIN ANALYZE ' + sql).fetchall()\n",
    "    return '\\n'.join(str(row[1] if len(row) > 1 else row[0]) for row in rows)\n",
    "\n",
    "\n",
    "def explain_analyze_json(sql: str):\n",
    "    rows = con.execute('EXPLAIN (ANALYZE, FORMAT json) ' + sql).fetchall()\n",
    "    payload = rows[0][1] if len(rows[0]) > 1 else rows[0][0]\n",
    "    return json.loads(payload)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5e729410",
   "metadata": {},
   "source": [
    "## Parquet Metadata\n",
    "\n",
    "Run this before timing queries. It verifies file counts, row groups, and whether the important columns have useful row-group min/max statistics."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "id": "d01c2588",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "## microsoft\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>file_name</th>\n",
       "      <th>row_groups</th>\n",
       "      <th>max_row_group_rows</th>\n",
       "      <th>compressed_bytes</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>../data/benchmarks/source/mspc-sentinel-2-l2a/...</td>\n",
       "      <td>161</td>\n",
       "      <td>2048</td>\n",
       "      <td>1.010473e+11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>../data/benchmarks/source/mspc-sentinel-2-l2a/...</td>\n",
       "      <td>144</td>\n",
       "      <td>2048</td>\n",
       "      <td>9.619865e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>../data/benchmarks/source/mspc-sentinel-2-l2a/...</td>\n",
       "      <td>211</td>\n",
       "      <td>2048</td>\n",
       "      <td>1.346860e+11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>../data/benchmarks/source/mspc-sentinel-2-l2a/...</td>\n",
       "      <td>226</td>\n",
       "      <td>2048</td>\n",
       "      <td>1.577516e+11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>../data/benchmarks/source/mspc-sentinel-2-l2a/...</td>\n",
       "      <td>234</td>\n",
       "      <td>2048</td>\n",
       "      <td>1.592138e+11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>../data/benchmarks/source/mspc-sentinel-2-l2a/...</td>\n",
       "      <td>225</td>\n",
       "      <td>2048</td>\n",
       "      <td>1.498662e+11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>../data/benchmarks/source/mspc-sentinel-2-l2a/...</td>\n",
       "      <td>232</td>\n",
       "      <td>2048</td>\n",
       "      <td>1.465726e+11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>../data/benchmarks/source/mspc-sentinel-2-l2a/...</td>\n",
       "      <td>231</td>\n",
       "      <td>2048</td>\n",
       "      <td>1.465465e+11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>../data/benchmarks/source/mspc-sentinel-2-l2a/...</td>\n",
       "      <td>225</td>\n",
       "      <td>2048</td>\n",
       "      <td>1.437244e+11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>../data/benchmarks/source/mspc-sentinel-2-l2a/...</td>\n",
       "      <td>215</td>\n",
       "      <td>2048</td>\n",
       "      <td>1.363797e+11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>../data/benchmarks/source/mspc-sentinel-2-l2a/...</td>\n",
       "      <td>173</td>\n",
       "      <td>2048</td>\n",
       "      <td>1.094628e+11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>../data/benchmarks/source/mspc-sentinel-2-l2a/...</td>\n",
       "      <td>186</td>\n",
       "      <td>2048</td>\n",
       "      <td>1.175325e+11</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                            file_name  row_groups  \\\n",
       "0   ../data/benchmarks/source/mspc-sentinel-2-l2a/...         161   \n",
       "1   ../data/benchmarks/source/mspc-sentinel-2-l2a/...         144   \n",
       "2   ../data/benchmarks/source/mspc-sentinel-2-l2a/...         211   \n",
       "3   ../data/benchmarks/source/mspc-sentinel-2-l2a/...         226   \n",
       "4   ../data/benchmarks/source/mspc-sentinel-2-l2a/...         234   \n",
       "5   ../data/benchmarks/source/mspc-sentinel-2-l2a/...         225   \n",
       "6   ../data/benchmarks/source/mspc-sentinel-2-l2a/...         232   \n",
       "7   ../data/benchmarks/source/mspc-sentinel-2-l2a/...         231   \n",
       "8   ../data/benchmarks/source/mspc-sentinel-2-l2a/...         225   \n",
       "9   ../data/benchmarks/source/mspc-sentinel-2-l2a/...         215   \n",
       "10  ../data/benchmarks/source/mspc-sentinel-2-l2a/...         173   \n",
       "11  ../data/benchmarks/source/mspc-sentinel-2-l2a/...         186   \n",
       "\n",
       "    max_row_group_rows  compressed_bytes  \n",
       "0                 2048      1.010473e+11  \n",
       "1                 2048      9.619865e+10  \n",
       "2                 2048      1.346860e+11  \n",
       "3                 2048      1.577516e+11  \n",
       "4                 2048      1.592138e+11  \n",
       "5                 2048      1.498662e+11  \n",
       "6                 2048      1.465726e+11  \n",
       "7                 2048      1.465465e+11  \n",
       "8                 2048      1.437244e+11  \n",
       "9                 2048      1.363797e+11  \n",
       "10                2048      1.094628e+11  \n",
       "11                2048      1.175325e+11  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "## hashed_128_files\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>file_name</th>\n",
       "      <th>row_groups</th>\n",
       "      <th>max_row_group_rows</th>\n",
       "      <th>compressed_bytes</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>../data/benchmarks/source/mspc-sentinel-2-l2a-...</td>\n",
       "      <td>1</td>\n",
       "      <td>39307</td>\n",
       "      <td>5.473829e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>../data/benchmarks/source/mspc-sentinel-2-l2a-...</td>\n",
       "      <td>1</td>\n",
       "      <td>39307</td>\n",
       "      <td>5.607146e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>../data/benchmarks/source/mspc-sentinel-2-l2a-...</td>\n",
       "      <td>1</td>\n",
       "      <td>39308</td>\n",
       "      <td>5.866470e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>../data/benchmarks/source/mspc-sentinel-2-l2a-...</td>\n",
       "      <td>1</td>\n",
       "      <td>39307</td>\n",
       "      <td>5.287036e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>../data/benchmarks/source/mspc-sentinel-2-l2a-...</td>\n",
       "      <td>1</td>\n",
       "      <td>39308</td>\n",
       "      <td>5.906282e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>123</th>\n",
       "      <td>../data/benchmarks/source/mspc-sentinel-2-l2a-...</td>\n",
       "      <td>1</td>\n",
       "      <td>39308</td>\n",
       "      <td>5.430317e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>124</th>\n",
       "      <td>../data/benchmarks/source/mspc-sentinel-2-l2a-...</td>\n",
       "      <td>1</td>\n",
       "      <td>39307</td>\n",
       "      <td>5.536509e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>125</th>\n",
       "      <td>../data/benchmarks/source/mspc-sentinel-2-l2a-...</td>\n",
       "      <td>1</td>\n",
       "      <td>39308</td>\n",
       "      <td>5.454389e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>126</th>\n",
       "      <td>../data/benchmarks/source/mspc-sentinel-2-l2a-...</td>\n",
       "      <td>1</td>\n",
       "      <td>39307</td>\n",
       "      <td>5.435148e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>127</th>\n",
       "      <td>../data/benchmarks/source/mspc-sentinel-2-l2a-...</td>\n",
       "      <td>1</td>\n",
       "      <td>39308</td>\n",
       "      <td>5.564445e+09</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>128 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                             file_name  row_groups  \\\n",
       "0    ../data/benchmarks/source/mspc-sentinel-2-l2a-...           1   \n",
       "1    ../data/benchmarks/source/mspc-sentinel-2-l2a-...           1   \n",
       "2    ../data/benchmarks/source/mspc-sentinel-2-l2a-...           1   \n",
       "3    ../data/benchmarks/source/mspc-sentinel-2-l2a-...           1   \n",
       "4    ../data/benchmarks/source/mspc-sentinel-2-l2a-...           1   \n",
       "..                                                 ...         ...   \n",
       "123  ../data/benchmarks/source/mspc-sentinel-2-l2a-...           1   \n",
       "124  ../data/benchmarks/source/mspc-sentinel-2-l2a-...           1   \n",
       "125  ../data/benchmarks/source/mspc-sentinel-2-l2a-...           1   \n",
       "126  ../data/benchmarks/source/mspc-sentinel-2-l2a-...           1   \n",
       "127  ../data/benchmarks/source/mspc-sentinel-2-l2a-...           1   \n",
       "\n",
       "     max_row_group_rows  compressed_bytes  \n",
       "0                 39307      5.473829e+09  \n",
       "1                 39307      5.607146e+09  \n",
       "2                 39308      5.866470e+09  \n",
       "3                 39307      5.287036e+09  \n",
       "4                 39308      5.906282e+09  \n",
       "..                  ...               ...  \n",
       "123               39308      5.430317e+09  \n",
       "124               39307      5.536509e+09  \n",
       "125               39308      5.454389e+09  \n",
       "126               39307      5.435148e+09  \n",
       "127               39308      5.564445e+09  \n",
       "\n",
       "[128 rows x 4 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "## hashed_12_files\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>file_name</th>\n",
       "      <th>row_groups</th>\n",
       "      <th>max_row_group_rows</th>\n",
       "      <th>compressed_bytes</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>../data/benchmarks/generated/mspc-sentinel-2-l...</td>\n",
       "      <td>4</td>\n",
       "      <td>122880</td>\n",
       "      <td>4.857495e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>../data/benchmarks/generated/mspc-sentinel-2-l...</td>\n",
       "      <td>4</td>\n",
       "      <td>122880</td>\n",
       "      <td>5.161138e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>../data/benchmarks/generated/mspc-sentinel-2-l...</td>\n",
       "      <td>4</td>\n",
       "      <td>122880</td>\n",
       "      <td>5.364427e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>../data/benchmarks/generated/mspc-sentinel-2-l...</td>\n",
       "      <td>4</td>\n",
       "      <td>122880</td>\n",
       "      <td>4.633041e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>../data/benchmarks/generated/mspc-sentinel-2-l...</td>\n",
       "      <td>4</td>\n",
       "      <td>122880</td>\n",
       "      <td>5.140086e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>../data/benchmarks/generated/mspc-sentinel-2-l...</td>\n",
       "      <td>4</td>\n",
       "      <td>122880</td>\n",
       "      <td>5.413710e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>../data/benchmarks/generated/mspc-sentinel-2-l...</td>\n",
       "      <td>4</td>\n",
       "      <td>122880</td>\n",
       "      <td>4.819585e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>../data/benchmarks/generated/mspc-sentinel-2-l...</td>\n",
       "      <td>4</td>\n",
       "      <td>122880</td>\n",
       "      <td>5.287735e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>../data/benchmarks/generated/mspc-sentinel-2-l...</td>\n",
       "      <td>4</td>\n",
       "      <td>122880</td>\n",
       "      <td>5.418228e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>../data/benchmarks/generated/mspc-sentinel-2-l...</td>\n",
       "      <td>4</td>\n",
       "      <td>122880</td>\n",
       "      <td>4.735390e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>../data/benchmarks/generated/mspc-sentinel-2-l...</td>\n",
       "      <td>4</td>\n",
       "      <td>122880</td>\n",
       "      <td>5.370755e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>../data/benchmarks/generated/mspc-sentinel-2-l...</td>\n",
       "      <td>4</td>\n",
       "      <td>122880</td>\n",
       "      <td>5.168503e+10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                            file_name  row_groups  \\\n",
       "0   ../data/benchmarks/generated/mspc-sentinel-2-l...           4   \n",
       "1   ../data/benchmarks/generated/mspc-sentinel-2-l...           4   \n",
       "2   ../data/benchmarks/generated/mspc-sentinel-2-l...           4   \n",
       "3   ../data/benchmarks/generated/mspc-sentinel-2-l...           4   \n",
       "4   ../data/benchmarks/generated/mspc-sentinel-2-l...           4   \n",
       "5   ../data/benchmarks/generated/mspc-sentinel-2-l...           4   \n",
       "6   ../data/benchmarks/generated/mspc-sentinel-2-l...           4   \n",
       "7   ../data/benchmarks/generated/mspc-sentinel-2-l...           4   \n",
       "8   ../data/benchmarks/generated/mspc-sentinel-2-l...           4   \n",
       "9   ../data/benchmarks/generated/mspc-sentinel-2-l...           4   \n",
       "10  ../data/benchmarks/generated/mspc-sentinel-2-l...           4   \n",
       "11  ../data/benchmarks/generated/mspc-sentinel-2-l...           4   \n",
       "\n",
       "    max_row_group_rows  compressed_bytes  \n",
       "0               122880      4.857495e+10  \n",
       "1               122880      5.161138e+10  \n",
       "2               122880      5.364427e+10  \n",
       "3               122880      4.633041e+10  \n",
       "4               122880      5.140086e+10  \n",
       "5               122880      5.413710e+10  \n",
       "6               122880      4.819585e+10  \n",
       "7               122880      5.287735e+10  \n",
       "8               122880      5.418228e+10  \n",
       "9               122880      4.735390e+10  \n",
       "10              122880      5.370755e+10  \n",
       "11              122880      5.168503e+10  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "metadata_sql = \"\"\"\n",
    "SELECT\n",
    "    file_name,\n",
    "    count(DISTINCT row_group_id) AS row_groups,\n",
    "    max(row_group_num_rows) AS max_row_group_rows,\n",
    "    sum(row_group_compressed_bytes) AS compressed_bytes\n",
    "FROM parquet_metadata({parquet_glob})\n",
    "GROUP BY file_name\n",
    "ORDER BY file_name\n",
    "\"\"\"\n",
    "\n",
    "for name, glob in DATASETS.items():\n",
    "    print('\\n##', name)\n",
    "    try:\n",
    "        display(con.execute(render(metadata_sql, glob, PARAMS)).df())\n",
    "    except Exception as error:\n",
    "        print(error)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "id": "aa43939c",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "## microsoft\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>path_in_schema</th>\n",
       "      <th>row_groups</th>\n",
       "      <th>global_min</th>\n",
       "      <th>global_max</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>collection</td>\n",
       "      <td>2463</td>\n",
       "      <td>sentinel-2-l2a</td>\n",
       "      <td>sentinel-2-l2a</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>datetime</td>\n",
       "      <td>2463</td>\n",
       "      <td>2025-01-01 00:04:39.024+00</td>\n",
       "      <td>2025-12-31 23:51:41.025+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>id</td>\n",
       "      <td>2463</td>\n",
       "      <td>S2A_MSIL2A_20250101T004031_R002_T56TPT_2025010...</td>\n",
       "      <td>S2C_MSIL2A_20251231T235141_R073_T59UPV_2026010...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  path_in_schema  row_groups  \\\n",
       "0     collection        2463   \n",
       "1       datetime        2463   \n",
       "2             id        2463   \n",
       "\n",
       "                                          global_min  \\\n",
       "0                                     sentinel-2-l2a   \n",
       "1                         2025-01-01 00:04:39.024+00   \n",
       "2  S2A_MSIL2A_20250101T004031_R002_T56TPT_2025010...   \n",
       "\n",
       "                                          global_max  \n",
       "0                                     sentinel-2-l2a  \n",
       "1                         2025-12-31 23:51:41.025+00  \n",
       "2  S2C_MSIL2A_20251231T235141_R073_T59UPV_2026010...  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "## hashed_128_files\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>path_in_schema</th>\n",
       "      <th>row_groups</th>\n",
       "      <th>global_min</th>\n",
       "      <th>global_max</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>collection</td>\n",
       "      <td>128</td>\n",
       "      <td>sentinel-2-l2a</td>\n",
       "      <td>sentinel-2-l2a</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>datetime</td>\n",
       "      <td>128</td>\n",
       "      <td>2025-01-01 00:04:39.024+00</td>\n",
       "      <td>2025-12-31 23:51:41.025+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>hash:hash</td>\n",
       "      <td>128</td>\n",
       "      <td>1060626393919962612</td>\n",
       "      <td>974454625439932830</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>id</td>\n",
       "      <td>128</td>\n",
       "      <td>S2A_MSIL2A_20250101T004031_R002_T56TPT_2025010...</td>\n",
       "      <td>S2C_MSIL2A_20251231T235141_R073_T59UPV_2026010...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  path_in_schema  row_groups  \\\n",
       "0     collection         128   \n",
       "1       datetime         128   \n",
       "2      hash:hash         128   \n",
       "3             id         128   \n",
       "\n",
       "                                          global_min  \\\n",
       "0                                     sentinel-2-l2a   \n",
       "1                         2025-01-01 00:04:39.024+00   \n",
       "2                                1060626393919962612   \n",
       "3  S2A_MSIL2A_20250101T004031_R002_T56TPT_2025010...   \n",
       "\n",
       "                                          global_max  \n",
       "0                                     sentinel-2-l2a  \n",
       "1                         2025-12-31 23:51:41.025+00  \n",
       "2                                 974454625439932830  \n",
       "3  S2C_MSIL2A_20251231T235141_R073_T59UPV_2026010...  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "## hashed_12_files\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>path_in_schema</th>\n",
       "      <th>row_groups</th>\n",
       "      <th>global_min</th>\n",
       "      <th>global_max</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>collection</td>\n",
       "      <td>48</td>\n",
       "      <td>sentinel-2-l2a</td>\n",
       "      <td>sentinel-2-l2a</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>datetime</td>\n",
       "      <td>48</td>\n",
       "      <td>2025-01-01 00:04:39.024+00</td>\n",
       "      <td>2025-12-31 23:51:41.025+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>hash:hash</td>\n",
       "      <td>48</td>\n",
       "      <td>1081327013277922969</td>\n",
       "      <td>9186779992691430940</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>id</td>\n",
       "      <td>48</td>\n",
       "      <td>S2A_MSIL2A_20250101T004031_R002_T56TPT_2025010...</td>\n",
       "      <td>S2C_MSIL2A_20251231T235141_R073_T59UPV_2026010...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  path_in_schema  row_groups  \\\n",
       "0     collection          48   \n",
       "1       datetime          48   \n",
       "2      hash:hash          48   \n",
       "3             id          48   \n",
       "\n",
       "                                          global_min  \\\n",
       "0                                     sentinel-2-l2a   \n",
       "1                         2025-01-01 00:04:39.024+00   \n",
       "2                                1081327013277922969   \n",
       "3  S2A_MSIL2A_20250101T004031_R002_T56TPT_2025010...   \n",
       "\n",
       "                                          global_max  \n",
       "0                                     sentinel-2-l2a  \n",
       "1                         2025-12-31 23:51:41.025+00  \n",
       "2                                9186779992691430940  \n",
       "3  S2C_MSIL2A_20251231T235141_R073_T59UPV_2026010...  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "stats_sql = \"\"\"\n",
    "SELECT\n",
    "    path_in_schema,\n",
    "    count(*) AS row_groups,\n",
    "    min(stats_min_value) AS global_min,\n",
    "    max(stats_max_value) AS global_max\n",
    "FROM parquet_metadata({parquet_glob})\n",
    "WHERE path_in_schema IN (\n",
    "    'hash:hash',\n",
    "    'datetime',\n",
    "    'collection',\n",
    "    'id',\n",
    "    'bbox.xmin',\n",
    "    'bbox.ymin',\n",
    "    'bbox.xmax',\n",
    "    'bbox.ymax'\n",
    ")\n",
    "GROUP BY path_in_schema\n",
    "ORDER BY path_in_schema\n",
    "\"\"\"\n",
    "\n",
    "for name, glob in DATASETS.items():\n",
    "    print('\\n##', name)\n",
    "    try:\n",
    "        display(con.execute(render(stats_sql, glob, PARAMS)).df())\n",
    "    except Exception as error:\n",
    "        print(error)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bf4d3c3b",
   "metadata": {},
   "source": [
    "## Query Suite"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "id": "1f7bd68a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['q01_full_dataset_count',\n",
       " 'q02_time_range_count',\n",
       " 'q03_bbox_count',\n",
       " 'q04_stac_search_count',\n",
       " 'q05_hash_range_search',\n",
       " 'q06_search_page_datetime_order',\n",
       " 'q06_search_page_hash_order',\n",
       " 'q07_attribute_filter',\n",
       " 'q08_exact_geometry_intersects',\n",
       " 'q09_grouped_aggregation',\n",
       " 'q10_collection_latest_items',\n",
       " 'q11_specific_id_lookup',\n",
       " 'q11_scoped_id_lookup']"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "QUERIES = {\n",
    "    'q01_full_dataset_count': \"\"\"\n",
    "SELECT count(*)\n",
    "FROM read_parquet({parquet_glob}, hive_partitioning = false)\n",
    "\"\"\",\n",
    "    'q02_time_range_count': \"\"\"\n",
    "SELECT count(*)\n",
    "FROM read_parquet({parquet_glob}, hive_partitioning = false)\n",
    "WHERE datetime >= {start_datetime}\n",
    "  AND datetime < {end_datetime}\n",
    "\"\"\",\n",
    "    'q03_bbox_count': \"\"\"\n",
    "SELECT count(*)\n",
    "FROM read_parquet({parquet_glob}, hive_partitioning = false)\n",
    "WHERE bbox.xmax >= {minx}\n",
    "  AND bbox.xmin <= {maxx}\n",
    "  AND bbox.ymax >= {miny}\n",
    "  AND bbox.ymin <= {maxy}\n",
    "\"\"\",\n",
    "    'q04_stac_search_count': \"\"\"\n",
    "SELECT count(*)\n",
    "FROM read_parquet({parquet_glob}, hive_partitioning = false)\n",
    "WHERE collection = {collection}\n",
    "  AND datetime >= {start_datetime}\n",
    "  AND datetime < {end_datetime}\n",
    "  AND bbox.xmax >= {minx}\n",
    "  AND bbox.xmin <= {maxx}\n",
    "  AND bbox.ymax >= {miny}\n",
    "  AND bbox.ymin <= {maxy}\n",
    "\"\"\",\n",
    "    'q05_hash_range_search': \"\"\"\n",
    "SELECT count(*)\n",
    "FROM read_parquet({parquet_glob}, hive_partitioning = false)\n",
    "WHERE \"hash:hash\" BETWEEN {min_hash} AND {max_hash}\n",
    "  AND datetime >= {start_datetime}\n",
    "  AND datetime < {end_datetime}\n",
    "  AND bbox.xmax >= {minx}\n",
    "  AND bbox.xmin <= {maxx}\n",
    "  AND bbox.ymax >= {miny}\n",
    "  AND bbox.ymin <= {maxy}\n",
    "\"\"\",\n",
    "    'q06_search_page_datetime_order': \"\"\"\n",
    "SELECT id, collection, datetime\n",
    "FROM read_parquet({parquet_glob}, hive_partitioning = false)\n",
    "WHERE collection = {collection}\n",
    "  AND datetime >= {start_datetime}\n",
    "  AND datetime < {end_datetime}\n",
    "  AND bbox.xmax >= {minx}\n",
    "  AND bbox.xmin <= {maxx}\n",
    "  AND bbox.ymax >= {miny}\n",
    "  AND bbox.ymin <= {maxy}\n",
    "ORDER BY datetime, id\n",
    "LIMIT 100\n",
    "\"\"\",\n",
    "    'q06_search_page_hash_order': \"\"\"\n",
    "SELECT id, collection, datetime\n",
    "FROM read_parquet({parquet_glob}, hive_partitioning = false)\n",
    "WHERE collection = {collection}\n",
    "  AND datetime >= {start_datetime}\n",
    "  AND datetime < {end_datetime}\n",
    "  AND bbox.xmax >= {minx}\n",
    "  AND bbox.xmin <= {maxx}\n",
    "  AND bbox.ymax >= {miny}\n",
    "  AND bbox.ymin <= {maxy}\n",
    "ORDER BY \"hash:hash\", id\n",
    "LIMIT 100\n",
    "\"\"\",\n",
    "    'q07_attribute_filter': \"\"\"\n",
    "SELECT count(*)\n",
    "FROM read_parquet({parquet_glob}, hive_partitioning = false)\n",
    "WHERE collection = {collection}\n",
    "  AND datetime >= {start_datetime}\n",
    "  AND datetime < {end_datetime}\n",
    "  AND bbox.xmax >= {minx}\n",
    "  AND bbox.xmin <= {maxx}\n",
    "  AND bbox.ymax >= {miny}\n",
    "  AND bbox.ymin <= {maxy}\n",
    "  AND \"eo:cloud_cover\" <= {max_cloud_cover}\n",
    "\"\"\",\n",
    "    'q08_exact_geometry_intersects': \"\"\"\n",
    "SELECT count(*)\n",
    "FROM read_parquet({parquet_glob}, hive_partitioning = false)\n",
    "WHERE datetime >= {start_datetime}\n",
    "  AND datetime < {end_datetime}\n",
    "  AND bbox.xmax >= {minx}\n",
    "  AND bbox.xmin <= {maxx}\n",
    "  AND bbox.ymax >= {miny}\n",
    "  AND bbox.ymin <= {maxy}\n",
    "  AND ST_Intersects(geometry, ST_GeomFromText({aoi_wkt}))\n",
    "\"\"\",\n",
    "    'q09_grouped_aggregation': \"\"\"\n",
    "SELECT\n",
    "    collection,\n",
    "    date_trunc('month', datetime) AS month,\n",
    "    count(*) AS items\n",
    "FROM read_parquet({parquet_glob}, hive_partitioning = false)\n",
    "GROUP BY collection, month\n",
    "ORDER BY collection, month\n",
    "\"\"\",\n",
    "    'q10_collection_latest_items': \"\"\"\n",
    "SELECT id, collection, datetime\n",
    "FROM read_parquet({parquet_glob}, hive_partitioning = false)\n",
    "WHERE collection = {collection}\n",
    "ORDER BY datetime DESC\n",
    "LIMIT 100\n",
    "\"\"\",\n",
    "    'q11_specific_id_lookup': \"\"\"\n",
    "SELECT id, collection, datetime\n",
    "FROM read_parquet({parquet_glob}, hive_partitioning = false)\n",
    "WHERE id = {id}\n",
    "\"\"\",\n",
    "    'q11_scoped_id_lookup': \"\"\"\n",
    "SELECT id, collection, datetime\n",
    "FROM read_parquet({parquet_glob}, hive_partitioning = false)\n",
    "WHERE collection = {collection}\n",
    "  AND id = {id}\n",
    "\"\"\",\n",
    "}\n",
    "\n",
    "list(QUERIES)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3b704df6",
   "metadata": {},
   "source": [
    "## Run Benchmarks\n",
    "\n",
    "This cell runs the full query suite across all dataset variants. Hash-only queries are automatically skipped for the Microsoft dataset because it does not have the `\"hash:hash\"` column.\n",
    "\n",
    "Use `median_seconds` for comparisons. `best_seconds` is useful for spotting warm-cache potential, but it can be optimistic.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "04940b05",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "microsoft / q01_full_dataset_count: rows=5031349 best=0.8521s median=0.8705s\n",
      "microsoft / q02_time_range_count: rows=459093 best=0.1377s median=0.1416s\n",
      "microsoft / q03_bbox_count: rows=8842 best=0.2135s median=0.2149s\n",
      "microsoft / q04_stac_search_count: rows=783 best=0.1478s median=0.1499s\n",
      "microsoft / q06_search_page_datetime_order: rows=100 best=0.1429s median=0.1449s\n",
      "microsoft / q07_attribute_filter: rows=488 best=0.1490s median=0.1537s\n",
      "microsoft / q08_exact_geometry_intersects: rows=783 best=0.1540s median=0.1859s\n",
      "microsoft / q09_grouped_aggregation: rows=13 best=0.1961s median=0.1988s\n",
      "microsoft / q10_collection_latest_items: rows=100 best=0.1869s median=0.1905s\n",
      "microsoft / q11_specific_id_lookup: rows=1 best=0.1832s median=0.1839s\n",
      "microsoft / q11_scoped_id_lookup: rows=1 best=0.1941s median=0.1957s\n",
      "hashed_128_files / q01_full_dataset_count: rows=5031349 best=0.0320s median=0.0323s\n",
      "hashed_128_files / q02_time_range_count: rows=459093 best=0.0147s median=0.0152s\n",
      "hashed_128_files / q03_bbox_count: rows=8842 best=0.0149s median=0.0152s\n",
      "hashed_128_files / q04_stac_search_count: rows=783 best=0.0143s median=0.0144s\n",
      "hashed_128_files / q05_hash_range_search: rows=783 best=0.0165s median=0.0167s\n",
      "hashed_128_files / q06_search_page_datetime_order: rows=100 best=0.0158s median=0.0165s\n",
      "hashed_128_files / q06_search_page_hash_order: rows=100 best=0.0155s median=0.0157s\n",
      "hashed_128_files / q07_attribute_filter: rows=488 best=0.0155s median=0.0158s\n",
      "hashed_128_files / q08_exact_geometry_intersects: rows=783 best=0.0157s median=0.0160s\n",
      "hashed_128_files / q09_grouped_aggregation: rows=13 best=0.0337s median=0.0349s\n",
      "hashed_128_files / q10_collection_latest_items: rows=100 best=0.0223s median=0.0245s\n",
      "hashed_128_files / q11_specific_id_lookup: rows=1 best=0.0340s median=0.0365s\n",
      "hashed_128_files / q11_scoped_id_lookup: rows=1 best=0.0354s median=0.0355s\n",
      "hashed_12_files / q01_full_dataset_count: rows=5031349 best=0.0141s median=0.0149s\n",
      "hashed_12_files / q02_time_range_count: rows=459093 best=0.0045s median=0.0046s\n",
      "hashed_12_files / q03_bbox_count: rows=8842 best=0.0085s median=0.0094s\n",
      "hashed_12_files / q04_stac_search_count: rows=783 best=0.0060s median=0.0060s\n",
      "hashed_12_files / q05_hash_range_search: rows=783 best=0.0067s median=0.0067s\n",
      "hashed_12_files / q06_search_page_datetime_order: rows=100 best=0.0093s median=0.0095s\n",
      "hashed_12_files / q06_search_page_hash_order: rows=100 best=0.0101s median=0.0103s\n",
      "hashed_12_files / q07_attribute_filter: rows=488 best=0.0073s median=0.0073s\n",
      "hashed_12_files / q08_exact_geometry_intersects: rows=783 best=0.0202s median=0.0212s\n",
      "hashed_12_files / q09_grouped_aggregation: rows=13 best=0.0141s median=0.0144s\n",
      "hashed_12_files / q10_collection_latest_items: rows=100 best=0.0204s median=0.0212s\n",
      "hashed_12_files / q11_specific_id_lookup: rows=1 best=0.0270s median=0.0291s\n",
      "hashed_12_files / q11_scoped_id_lookup: rows=1 best=0.0282s median=0.0284s\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>query</th>\n",
       "      <th>dataset</th>\n",
       "      <th>rows</th>\n",
       "      <th>best_seconds</th>\n",
       "      <th>median_seconds</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>q01_full_dataset_count</td>\n",
       "      <td>hashed_12_files</td>\n",
       "      <td>5031349</td>\n",
       "      <td>0.014133</td>\n",
       "      <td>0.014875</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>q01_full_dataset_count</td>\n",
       "      <td>hashed_128_files</td>\n",
       "      <td>5031349</td>\n",
       "      <td>0.031987</td>\n",
       "      <td>0.032251</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>q01_full_dataset_count</td>\n",
       "      <td>microsoft</td>\n",
       "      <td>5031349</td>\n",
       "      <td>0.852051</td>\n",
       "      <td>0.870463</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>q02_time_range_count</td>\n",
       "      <td>hashed_12_files</td>\n",
       "      <td>459093</td>\n",
       "      <td>0.004529</td>\n",
       "      <td>0.004574</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>q02_time_range_count</td>\n",
       "      <td>hashed_128_files</td>\n",
       "      <td>459093</td>\n",
       "      <td>0.014691</td>\n",
       "      <td>0.015225</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>q02_time_range_count</td>\n",
       "      <td>microsoft</td>\n",
       "      <td>459093</td>\n",
       "      <td>0.137697</td>\n",
       "      <td>0.141585</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>q03_bbox_count</td>\n",
       "      <td>hashed_12_files</td>\n",
       "      <td>8842</td>\n",
       "      <td>0.008538</td>\n",
       "      <td>0.009385</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>q03_bbox_count</td>\n",
       "      <td>hashed_128_files</td>\n",
       "      <td>8842</td>\n",
       "      <td>0.014915</td>\n",
       "      <td>0.015246</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>q03_bbox_count</td>\n",
       "      <td>microsoft</td>\n",
       "      <td>8842</td>\n",
       "      <td>0.213523</td>\n",
       "      <td>0.214881</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>q04_stac_search_count</td>\n",
       "      <td>hashed_12_files</td>\n",
       "      <td>783</td>\n",
       "      <td>0.005985</td>\n",
       "      <td>0.006046</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>q04_stac_search_count</td>\n",
       "      <td>hashed_128_files</td>\n",
       "      <td>783</td>\n",
       "      <td>0.014251</td>\n",
       "      <td>0.014443</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>q04_stac_search_count</td>\n",
       "      <td>microsoft</td>\n",
       "      <td>783</td>\n",
       "      <td>0.147753</td>\n",
       "      <td>0.149947</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>q05_hash_range_search</td>\n",
       "      <td>hashed_12_files</td>\n",
       "      <td>783</td>\n",
       "      <td>0.006652</td>\n",
       "      <td>0.006733</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>q05_hash_range_search</td>\n",
       "      <td>hashed_128_files</td>\n",
       "      <td>783</td>\n",
       "      <td>0.016515</td>\n",
       "      <td>0.016729</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>q06_search_page_datetime_order</td>\n",
       "      <td>hashed_12_files</td>\n",
       "      <td>100</td>\n",
       "      <td>0.009334</td>\n",
       "      <td>0.009472</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>q06_search_page_datetime_order</td>\n",
       "      <td>hashed_128_files</td>\n",
       "      <td>100</td>\n",
       "      <td>0.015756</td>\n",
       "      <td>0.016459</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>q06_search_page_datetime_order</td>\n",
       "      <td>microsoft</td>\n",
       "      <td>100</td>\n",
       "      <td>0.142908</td>\n",
       "      <td>0.144857</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>q06_search_page_hash_order</td>\n",
       "      <td>hashed_12_files</td>\n",
       "      <td>100</td>\n",
       "      <td>0.010146</td>\n",
       "      <td>0.010341</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>q06_search_page_hash_order</td>\n",
       "      <td>hashed_128_files</td>\n",
       "      <td>100</td>\n",
       "      <td>0.015487</td>\n",
       "      <td>0.015679</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>q07_attribute_filter</td>\n",
       "      <td>hashed_12_files</td>\n",
       "      <td>488</td>\n",
       "      <td>0.007301</td>\n",
       "      <td>0.007330</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>q07_attribute_filter</td>\n",
       "      <td>hashed_128_files</td>\n",
       "      <td>488</td>\n",
       "      <td>0.015520</td>\n",
       "      <td>0.015809</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>q07_attribute_filter</td>\n",
       "      <td>microsoft</td>\n",
       "      <td>488</td>\n",
       "      <td>0.148953</td>\n",
       "      <td>0.153744</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>q08_exact_geometry_intersects</td>\n",
       "      <td>hashed_128_files</td>\n",
       "      <td>783</td>\n",
       "      <td>0.015719</td>\n",
       "      <td>0.015985</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>q08_exact_geometry_intersects</td>\n",
       "      <td>hashed_12_files</td>\n",
       "      <td>783</td>\n",
       "      <td>0.020163</td>\n",
       "      <td>0.021185</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>q08_exact_geometry_intersects</td>\n",
       "      <td>microsoft</td>\n",
       "      <td>783</td>\n",
       "      <td>0.154022</td>\n",
       "      <td>0.185925</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>q09_grouped_aggregation</td>\n",
       "      <td>hashed_12_files</td>\n",
       "      <td>13</td>\n",
       "      <td>0.014076</td>\n",
       "      <td>0.014371</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>q09_grouped_aggregation</td>\n",
       "      <td>hashed_128_files</td>\n",
       "      <td>13</td>\n",
       "      <td>0.033675</td>\n",
       "      <td>0.034884</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>q09_grouped_aggregation</td>\n",
       "      <td>microsoft</td>\n",
       "      <td>13</td>\n",
       "      <td>0.196065</td>\n",
       "      <td>0.198808</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>q10_collection_latest_items</td>\n",
       "      <td>hashed_12_files</td>\n",
       "      <td>100</td>\n",
       "      <td>0.020415</td>\n",
       "      <td>0.021245</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>q10_collection_latest_items</td>\n",
       "      <td>hashed_128_files</td>\n",
       "      <td>100</td>\n",
       "      <td>0.022329</td>\n",
       "      <td>0.024467</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>q10_collection_latest_items</td>\n",
       "      <td>microsoft</td>\n",
       "      <td>100</td>\n",
       "      <td>0.186883</td>\n",
       "      <td>0.190527</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>q11_scoped_id_lookup</td>\n",
       "      <td>hashed_12_files</td>\n",
       "      <td>1</td>\n",
       "      <td>0.028247</td>\n",
       "      <td>0.028409</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>q11_scoped_id_lookup</td>\n",
       "      <td>hashed_128_files</td>\n",
       "      <td>1</td>\n",
       "      <td>0.035403</td>\n",
       "      <td>0.035527</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>q11_scoped_id_lookup</td>\n",
       "      <td>microsoft</td>\n",
       "      <td>1</td>\n",
       "      <td>0.194143</td>\n",
       "      <td>0.195707</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>q11_specific_id_lookup</td>\n",
       "      <td>hashed_12_files</td>\n",
       "      <td>1</td>\n",
       "      <td>0.026963</td>\n",
       "      <td>0.029061</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>q11_specific_id_lookup</td>\n",
       "      <td>hashed_128_files</td>\n",
       "      <td>1</td>\n",
       "      <td>0.033988</td>\n",
       "      <td>0.036518</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>q11_specific_id_lookup</td>\n",
       "      <td>microsoft</td>\n",
       "      <td>1</td>\n",
       "      <td>0.183208</td>\n",
       "      <td>0.183944</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                             query           dataset     rows  best_seconds  \\\n",
       "0           q01_full_dataset_count   hashed_12_files  5031349      0.014133   \n",
       "1           q01_full_dataset_count  hashed_128_files  5031349      0.031987   \n",
       "2           q01_full_dataset_count         microsoft  5031349      0.852051   \n",
       "3             q02_time_range_count   hashed_12_files   459093      0.004529   \n",
       "4             q02_time_range_count  hashed_128_files   459093      0.014691   \n",
       "5             q02_time_range_count         microsoft   459093      0.137697   \n",
       "6                   q03_bbox_count   hashed_12_files     8842      0.008538   \n",
       "7                   q03_bbox_count  hashed_128_files     8842      0.014915   \n",
       "8                   q03_bbox_count         microsoft     8842      0.213523   \n",
       "9            q04_stac_search_count   hashed_12_files      783      0.005985   \n",
       "10           q04_stac_search_count  hashed_128_files      783      0.014251   \n",
       "11           q04_stac_search_count         microsoft      783      0.147753   \n",
       "12           q05_hash_range_search   hashed_12_files      783      0.006652   \n",
       "13           q05_hash_range_search  hashed_128_files      783      0.016515   \n",
       "14  q06_search_page_datetime_order   hashed_12_files      100      0.009334   \n",
       "15  q06_search_page_datetime_order  hashed_128_files      100      0.015756   \n",
       "16  q06_search_page_datetime_order         microsoft      100      0.142908   \n",
       "17      q06_search_page_hash_order   hashed_12_files      100      0.010146   \n",
       "18      q06_search_page_hash_order  hashed_128_files      100      0.015487   \n",
       "19            q07_attribute_filter   hashed_12_files      488      0.007301   \n",
       "20            q07_attribute_filter  hashed_128_files      488      0.015520   \n",
       "21            q07_attribute_filter         microsoft      488      0.148953   \n",
       "22   q08_exact_geometry_intersects  hashed_128_files      783      0.015719   \n",
       "23   q08_exact_geometry_intersects   hashed_12_files      783      0.020163   \n",
       "24   q08_exact_geometry_intersects         microsoft      783      0.154022   \n",
       "25         q09_grouped_aggregation   hashed_12_files       13      0.014076   \n",
       "26         q09_grouped_aggregation  hashed_128_files       13      0.033675   \n",
       "27         q09_grouped_aggregation         microsoft       13      0.196065   \n",
       "28     q10_collection_latest_items   hashed_12_files      100      0.020415   \n",
       "29     q10_collection_latest_items  hashed_128_files      100      0.022329   \n",
       "30     q10_collection_latest_items         microsoft      100      0.186883   \n",
       "31            q11_scoped_id_lookup   hashed_12_files        1      0.028247   \n",
       "32            q11_scoped_id_lookup  hashed_128_files        1      0.035403   \n",
       "33            q11_scoped_id_lookup         microsoft        1      0.194143   \n",
       "34          q11_specific_id_lookup   hashed_12_files        1      0.026963   \n",
       "35          q11_specific_id_lookup  hashed_128_files        1      0.033988   \n",
       "36          q11_specific_id_lookup         microsoft        1      0.183208   \n",
       "\n",
       "    median_seconds  \n",
       "0         0.014875  \n",
       "1         0.032251  \n",
       "2         0.870463  \n",
       "3         0.004574  \n",
       "4         0.015225  \n",
       "5         0.141585  \n",
       "6         0.009385  \n",
       "7         0.015246  \n",
       "8         0.214881  \n",
       "9         0.006046  \n",
       "10        0.014443  \n",
       "11        0.149947  \n",
       "12        0.006733  \n",
       "13        0.016729  \n",
       "14        0.009472  \n",
       "15        0.016459  \n",
       "16        0.144857  \n",
       "17        0.010341  \n",
       "18        0.015679  \n",
       "19        0.007330  \n",
       "20        0.015809  \n",
       "21        0.153744  \n",
       "22        0.015985  \n",
       "23        0.021185  \n",
       "24        0.185925  \n",
       "25        0.014371  \n",
       "26        0.034884  \n",
       "27        0.198808  \n",
       "28        0.021245  \n",
       "29        0.024467  \n",
       "30        0.190527  \n",
       "31        0.028409  \n",
       "32        0.035527  \n",
       "33        0.195707  \n",
       "34        0.029061  \n",
       "35        0.036518  \n",
       "36        0.183944  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "HASH_ONLY_QUERIES = {\n",
    "    'q05_hash_range_search',\n",
    "    'q06_search_page_hash_order',\n",
    "}\n",
    "HASHED_DATASET_SUFFIXES = ('hashed_128_files', 'hashed_12_files')\n",
    "def dataset_has_hash(dataset_name: str) -> bool:\n",
    "    return dataset_name.endswith(HASHED_DATASET_SUFFIXES)\n",
    "\n",
    "\n",
    "def run_benchmark_matrix(datasets: dict[str, str], queries: dict[str, str], repeats: int = 5):\n",
    "    results = []\n",
    "    for dataset_name, dataset_glob in datasets.items():\n",
    "        for query_name, template in queries.items():\n",
    "            if query_name in HASH_ONLY_QUERIES and not dataset_has_hash(dataset_name):\n",
    "                continue\n",
    "            sql = render(template, dataset_glob, PARAMS)\n",
    "            try:\n",
    "                rows, timings = time_query(sql, repeats=repeats)\n",
    "            except Exception as error:\n",
    "                print(f'{dataset_name} / {query_name}: {error}')\n",
    "                continue\n",
    "            results.append(BenchmarkResult(\n",
    "                dataset=dataset_name,\n",
    "                query=query_name,\n",
    "                rows=rows,\n",
    "                best_seconds=min(timings),\n",
    "                median_seconds=statistics.median(timings),\n",
    "                runs=timings,\n",
    "            ))\n",
    "            print(f'{dataset_name} / {query_name}: rows={rows} best={min(timings):0.4f}s median={statistics.median(timings):0.4f}s')\n",
    "    return results\n",
    "\n",
    "\n",
    "def results_table(results: list[BenchmarkResult]):\n",
    "    rows = [\n",
    "        {\n",
    "            'query': result.query,\n",
    "            'dataset': result.dataset,\n",
    "            'rows': result.rows,\n",
    "            'best_seconds': result.best_seconds,\n",
    "            'median_seconds': result.median_seconds,\n",
    "        }\n",
    "        for result in results\n",
    "    ]\n",
    "    if not rows:\n",
    "        return pd.DataFrame(columns=['query', 'dataset', 'rows', 'best_seconds', 'median_seconds'])\n",
    "    return pd.DataFrame(rows).sort_values(['query', 'median_seconds']).reset_index(drop=True)\n",
    "\n",
    "\n",
    "results = run_benchmark_matrix(DATASETS, QUERIES, repeats=5)\n",
    "summary = results_table(results)\n",
    "display(summary)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7be4bbc4",
   "metadata": {},
   "source": [
    "### Remote Object-Store Run\n",
    "\n",
    "Run this cell to benchmark the same query suite directly against Source Cooperative S3. These timings include object-store listing, network latency, and HTTP range reads, so compare them separately from the local-first results above."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "id": "905032a4",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "remote_microsoft / q01_full_dataset_count: rows=5031349 best=2.5981s median=2.9397s\n",
      "remote_microsoft / q02_time_range_count: rows=459093 best=3.6311s median=3.9620s\n",
      "remote_microsoft / q03_bbox_count: rows=8842 best=71.0025s median=72.0031s\n",
      "remote_microsoft / q04_stac_search_count: rows=783 best=12.0099s median=12.2135s\n",
      "remote_microsoft / q06_search_page_datetime_order: rows=100 best=7.9093s median=8.7381s\n",
      "remote_microsoft / q07_attribute_filter: rows=488 best=14.3093s median=14.6723s\n",
      "remote_microsoft / q08_exact_geometry_intersects: rows=783 best=10.6404s median=11.3679s\n",
      "remote_microsoft / q09_grouped_aggregation: rows=13 best=52.6225s median=54.3369s\n",
      "remote_microsoft / q10_collection_latest_items: rows=100 best=26.3156s median=28.1502s\n",
      "remote_microsoft / q11_specific_id_lookup: rows=1 best=25.9754s median=26.3912s\n",
      "remote_microsoft / q11_scoped_id_lookup: rows=1 best=38.5690s median=38.6381s\n",
      "remote_hashed_128_files / q01_full_dataset_count: rows=5031349 best=4.0914s median=4.3767s\n",
      "remote_hashed_128_files / q02_time_range_count: rows=459093 best=4.8746s median=4.9197s\n",
      "remote_hashed_128_files / q03_bbox_count: rows=8842 best=5.2841s median=5.2943s\n",
      "remote_hashed_128_files / q04_stac_search_count: rows=783 best=4.5157s median=4.6776s\n",
      "remote_hashed_128_files / q05_hash_range_search: rows=783 best=4.3911s median=4.4901s\n",
      "remote_hashed_128_files / q06_search_page_datetime_order: rows=100 best=4.4843s median=4.5451s\n",
      "remote_hashed_128_files / q06_search_page_hash_order: rows=100 best=4.5019s median=4.6599s\n",
      "remote_hashed_128_files / q07_attribute_filter: rows=488 best=4.0703s median=4.3655s\n",
      "remote_hashed_128_files / q08_exact_geometry_intersects: rows=783 best=4.6839s median=4.8462s\n",
      "remote_hashed_128_files / q09_grouped_aggregation: rows=13 best=7.3265s median=7.9991s\n",
      "remote_hashed_128_files / q10_collection_latest_items: rows=100 best=5.5255s median=5.7226s\n",
      "remote_hashed_128_files / q11_specific_id_lookup: rows=1 best=7.4086s median=7.6844s\n",
      "remote_hashed_128_files / q11_scoped_id_lookup: rows=1 best=8.1795s median=8.3105s\n",
      "remote_hashed_12_files / q01_full_dataset_count: rows=5031349 best=0.7411s median=0.7971s\n",
      "remote_hashed_12_files / q02_time_range_count: rows=459093 best=1.0877s median=1.0941s\n",
      "remote_hashed_12_files / q03_bbox_count: rows=8842 best=1.4447s median=1.4833s\n",
      "remote_hashed_12_files / q04_stac_search_count: rows=783 best=1.5439s median=1.6488s\n",
      "remote_hashed_12_files / q05_hash_range_search: rows=783 best=0.9995s median=1.2122s\n",
      "remote_hashed_12_files / q06_search_page_datetime_order: rows=100 best=1.5180s median=1.5477s\n",
      "remote_hashed_12_files / q06_search_page_hash_order: rows=100 best=1.5915s median=1.6447s\n",
      "remote_hashed_12_files / q07_attribute_filter: rows=488 best=1.6306s median=1.6514s\n",
      "remote_hashed_12_files / q08_exact_geometry_intersects: rows=783 best=1.2469s median=1.5986s\n",
      "remote_hashed_12_files / q09_grouped_aggregation: rows=13 best=2.0327s median=2.2132s\n",
      "remote_hashed_12_files / q10_collection_latest_items: rows=100 best=2.3241s median=2.3461s\n",
      "remote_hashed_12_files / q11_specific_id_lookup: rows=1 best=2.0912s median=2.1962s\n",
      "remote_hashed_12_files / q11_scoped_id_lookup: rows=1 best=2.7425s median=2.8153s\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>query</th>\n",
       "      <th>dataset</th>\n",
       "      <th>rows</th>\n",
       "      <th>best_seconds</th>\n",
       "      <th>median_seconds</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>q01_full_dataset_count</td>\n",
       "      <td>remote_hashed_12_files</td>\n",
       "      <td>5031349</td>\n",
       "      <td>0.741087</td>\n",
       "      <td>0.797104</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>q01_full_dataset_count</td>\n",
       "      <td>remote_microsoft</td>\n",
       "      <td>5031349</td>\n",
       "      <td>2.598067</td>\n",
       "      <td>2.939739</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>q01_full_dataset_count</td>\n",
       "      <td>remote_hashed_128_files</td>\n",
       "      <td>5031349</td>\n",
       "      <td>4.091397</td>\n",
       "      <td>4.376748</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>q02_time_range_count</td>\n",
       "      <td>remote_hashed_12_files</td>\n",
       "      <td>459093</td>\n",
       "      <td>1.087656</td>\n",
       "      <td>1.094050</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>q02_time_range_count</td>\n",
       "      <td>remote_microsoft</td>\n",
       "      <td>459093</td>\n",
       "      <td>3.631058</td>\n",
       "      <td>3.962012</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>q02_time_range_count</td>\n",
       "      <td>remote_hashed_128_files</td>\n",
       "      <td>459093</td>\n",
       "      <td>4.874556</td>\n",
       "      <td>4.919652</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>q03_bbox_count</td>\n",
       "      <td>remote_hashed_12_files</td>\n",
       "      <td>8842</td>\n",
       "      <td>1.444669</td>\n",
       "      <td>1.483335</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>q03_bbox_count</td>\n",
       "      <td>remote_hashed_128_files</td>\n",
       "      <td>8842</td>\n",
       "      <td>5.284142</td>\n",
       "      <td>5.294268</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>q03_bbox_count</td>\n",
       "      <td>remote_microsoft</td>\n",
       "      <td>8842</td>\n",
       "      <td>71.002482</td>\n",
       "      <td>72.003055</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>q04_stac_search_count</td>\n",
       "      <td>remote_hashed_12_files</td>\n",
       "      <td>783</td>\n",
       "      <td>1.543881</td>\n",
       "      <td>1.648777</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>q04_stac_search_count</td>\n",
       "      <td>remote_hashed_128_files</td>\n",
       "      <td>783</td>\n",
       "      <td>4.515744</td>\n",
       "      <td>4.677643</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>q04_stac_search_count</td>\n",
       "      <td>remote_microsoft</td>\n",
       "      <td>783</td>\n",
       "      <td>12.009948</td>\n",
       "      <td>12.213530</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>q05_hash_range_search</td>\n",
       "      <td>remote_hashed_12_files</td>\n",
       "      <td>783</td>\n",
       "      <td>0.999522</td>\n",
       "      <td>1.212228</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>q05_hash_range_search</td>\n",
       "      <td>remote_hashed_128_files</td>\n",
       "      <td>783</td>\n",
       "      <td>4.391119</td>\n",
       "      <td>4.490073</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>q06_search_page_datetime_order</td>\n",
       "      <td>remote_hashed_12_files</td>\n",
       "      <td>100</td>\n",
       "      <td>1.518044</td>\n",
       "      <td>1.547722</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>q06_search_page_datetime_order</td>\n",
       "      <td>remote_hashed_128_files</td>\n",
       "      <td>100</td>\n",
       "      <td>4.484266</td>\n",
       "      <td>4.545145</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>q06_search_page_datetime_order</td>\n",
       "      <td>remote_microsoft</td>\n",
       "      <td>100</td>\n",
       "      <td>7.909347</td>\n",
       "      <td>8.738074</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>q06_search_page_hash_order</td>\n",
       "      <td>remote_hashed_12_files</td>\n",
       "      <td>100</td>\n",
       "      <td>1.591461</td>\n",
       "      <td>1.644658</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>q06_search_page_hash_order</td>\n",
       "      <td>remote_hashed_128_files</td>\n",
       "      <td>100</td>\n",
       "      <td>4.501911</td>\n",
       "      <td>4.659922</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>q07_attribute_filter</td>\n",
       "      <td>remote_hashed_12_files</td>\n",
       "      <td>488</td>\n",
       "      <td>1.630555</td>\n",
       "      <td>1.651420</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>q07_attribute_filter</td>\n",
       "      <td>remote_hashed_128_files</td>\n",
       "      <td>488</td>\n",
       "      <td>4.070319</td>\n",
       "      <td>4.365493</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>q07_attribute_filter</td>\n",
       "      <td>remote_microsoft</td>\n",
       "      <td>488</td>\n",
       "      <td>14.309264</td>\n",
       "      <td>14.672340</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>q08_exact_geometry_intersects</td>\n",
       "      <td>remote_hashed_12_files</td>\n",
       "      <td>783</td>\n",
       "      <td>1.246926</td>\n",
       "      <td>1.598570</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>q08_exact_geometry_intersects</td>\n",
       "      <td>remote_hashed_128_files</td>\n",
       "      <td>783</td>\n",
       "      <td>4.683900</td>\n",
       "      <td>4.846160</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>q08_exact_geometry_intersects</td>\n",
       "      <td>remote_microsoft</td>\n",
       "      <td>783</td>\n",
       "      <td>10.640445</td>\n",
       "      <td>11.367901</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>q09_grouped_aggregation</td>\n",
       "      <td>remote_hashed_12_files</td>\n",
       "      <td>13</td>\n",
       "      <td>2.032735</td>\n",
       "      <td>2.213163</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>q09_grouped_aggregation</td>\n",
       "      <td>remote_hashed_128_files</td>\n",
       "      <td>13</td>\n",
       "      <td>7.326469</td>\n",
       "      <td>7.999093</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>q09_grouped_aggregation</td>\n",
       "      <td>remote_microsoft</td>\n",
       "      <td>13</td>\n",
       "      <td>52.622470</td>\n",
       "      <td>54.336930</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>q10_collection_latest_items</td>\n",
       "      <td>remote_hashed_12_files</td>\n",
       "      <td>100</td>\n",
       "      <td>2.324149</td>\n",
       "      <td>2.346078</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>q10_collection_latest_items</td>\n",
       "      <td>remote_hashed_128_files</td>\n",
       "      <td>100</td>\n",
       "      <td>5.525502</td>\n",
       "      <td>5.722617</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>q10_collection_latest_items</td>\n",
       "      <td>remote_microsoft</td>\n",
       "      <td>100</td>\n",
       "      <td>26.315647</td>\n",
       "      <td>28.150243</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>q11_scoped_id_lookup</td>\n",
       "      <td>remote_hashed_12_files</td>\n",
       "      <td>1</td>\n",
       "      <td>2.742462</td>\n",
       "      <td>2.815297</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>q11_scoped_id_lookup</td>\n",
       "      <td>remote_hashed_128_files</td>\n",
       "      <td>1</td>\n",
       "      <td>8.179514</td>\n",
       "      <td>8.310530</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>q11_scoped_id_lookup</td>\n",
       "      <td>remote_microsoft</td>\n",
       "      <td>1</td>\n",
       "      <td>38.568969</td>\n",
       "      <td>38.638126</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>q11_specific_id_lookup</td>\n",
       "      <td>remote_hashed_12_files</td>\n",
       "      <td>1</td>\n",
       "      <td>2.091235</td>\n",
       "      <td>2.196206</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>q11_specific_id_lookup</td>\n",
       "      <td>remote_hashed_128_files</td>\n",
       "      <td>1</td>\n",
       "      <td>7.408601</td>\n",
       "      <td>7.684417</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>q11_specific_id_lookup</td>\n",
       "      <td>remote_microsoft</td>\n",
       "      <td>1</td>\n",
       "      <td>25.975423</td>\n",
       "      <td>26.391167</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                             query                  dataset     rows  \\\n",
       "0           q01_full_dataset_count   remote_hashed_12_files  5031349   \n",
       "1           q01_full_dataset_count         remote_microsoft  5031349   \n",
       "2           q01_full_dataset_count  remote_hashed_128_files  5031349   \n",
       "3             q02_time_range_count   remote_hashed_12_files   459093   \n",
       "4             q02_time_range_count         remote_microsoft   459093   \n",
       "5             q02_time_range_count  remote_hashed_128_files   459093   \n",
       "6                   q03_bbox_count   remote_hashed_12_files     8842   \n",
       "7                   q03_bbox_count  remote_hashed_128_files     8842   \n",
       "8                   q03_bbox_count         remote_microsoft     8842   \n",
       "9            q04_stac_search_count   remote_hashed_12_files      783   \n",
       "10           q04_stac_search_count  remote_hashed_128_files      783   \n",
       "11           q04_stac_search_count         remote_microsoft      783   \n",
       "12           q05_hash_range_search   remote_hashed_12_files      783   \n",
       "13           q05_hash_range_search  remote_hashed_128_files      783   \n",
       "14  q06_search_page_datetime_order   remote_hashed_12_files      100   \n",
       "15  q06_search_page_datetime_order  remote_hashed_128_files      100   \n",
       "16  q06_search_page_datetime_order         remote_microsoft      100   \n",
       "17      q06_search_page_hash_order   remote_hashed_12_files      100   \n",
       "18      q06_search_page_hash_order  remote_hashed_128_files      100   \n",
       "19            q07_attribute_filter   remote_hashed_12_files      488   \n",
       "20            q07_attribute_filter  remote_hashed_128_files      488   \n",
       "21            q07_attribute_filter         remote_microsoft      488   \n",
       "22   q08_exact_geometry_intersects   remote_hashed_12_files      783   \n",
       "23   q08_exact_geometry_intersects  remote_hashed_128_files      783   \n",
       "24   q08_exact_geometry_intersects         remote_microsoft      783   \n",
       "25         q09_grouped_aggregation   remote_hashed_12_files       13   \n",
       "26         q09_grouped_aggregation  remote_hashed_128_files       13   \n",
       "27         q09_grouped_aggregation         remote_microsoft       13   \n",
       "28     q10_collection_latest_items   remote_hashed_12_files      100   \n",
       "29     q10_collection_latest_items  remote_hashed_128_files      100   \n",
       "30     q10_collection_latest_items         remote_microsoft      100   \n",
       "31            q11_scoped_id_lookup   remote_hashed_12_files        1   \n",
       "32            q11_scoped_id_lookup  remote_hashed_128_files        1   \n",
       "33            q11_scoped_id_lookup         remote_microsoft        1   \n",
       "34          q11_specific_id_lookup   remote_hashed_12_files        1   \n",
       "35          q11_specific_id_lookup  remote_hashed_128_files        1   \n",
       "36          q11_specific_id_lookup         remote_microsoft        1   \n",
       "\n",
       "    best_seconds  median_seconds  \n",
       "0       0.741087        0.797104  \n",
       "1       2.598067        2.939739  \n",
       "2       4.091397        4.376748  \n",
       "3       1.087656        1.094050  \n",
       "4       3.631058        3.962012  \n",
       "5       4.874556        4.919652  \n",
       "6       1.444669        1.483335  \n",
       "7       5.284142        5.294268  \n",
       "8      71.002482       72.003055  \n",
       "9       1.543881        1.648777  \n",
       "10      4.515744        4.677643  \n",
       "11     12.009948       12.213530  \n",
       "12      0.999522        1.212228  \n",
       "13      4.391119        4.490073  \n",
       "14      1.518044        1.547722  \n",
       "15      4.484266        4.545145  \n",
       "16      7.909347        8.738074  \n",
       "17      1.591461        1.644658  \n",
       "18      4.501911        4.659922  \n",
       "19      1.630555        1.651420  \n",
       "20      4.070319        4.365493  \n",
       "21     14.309264       14.672340  \n",
       "22      1.246926        1.598570  \n",
       "23      4.683900        4.846160  \n",
       "24     10.640445       11.367901  \n",
       "25      2.032735        2.213163  \n",
       "26      7.326469        7.999093  \n",
       "27     52.622470       54.336930  \n",
       "28      2.324149        2.346078  \n",
       "29      5.525502        5.722617  \n",
       "30     26.315647       28.150243  \n",
       "31      2.742462        2.815297  \n",
       "32      8.179514        8.310530  \n",
       "33     38.568969       38.638126  \n",
       "34      2.091235        2.196206  \n",
       "35      7.408601        7.684417  \n",
       "36     25.975423       26.391167  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "remote_results = run_benchmark_matrix(REMOTE_DATASETS, QUERIES, repeats=3)\n",
    "remote_summary = results_table(remote_results)\n",
    "display(remote_summary)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "97f0622b",
   "metadata": {},
   "source": [
    "## Analyze Results\n",
    "\n",
    "These derived tables make the first-pass interpretation easier. The speedup table compares median runtimes by query. The file summary helps identify whether differences are caused by hash ordering or by lower-level Parquet characteristics such as file size and row-group layout.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "id": "0d1e5809",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>dataset</th>\n",
       "      <th>query</th>\n",
       "      <th>hashed_128_files</th>\n",
       "      <th>hashed_12_files</th>\n",
       "      <th>microsoft</th>\n",
       "      <th>microsoft_vs_hashed_12_speedup</th>\n",
       "      <th>hashed_128_vs_hashed_12_speedup</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>q01_full_dataset_count</td>\n",
       "      <td>0.032251</td>\n",
       "      <td>0.014875</td>\n",
       "      <td>0.870463</td>\n",
       "      <td>58.517549</td>\n",
       "      <td>2.168101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>q02_time_range_count</td>\n",
       "      <td>0.015225</td>\n",
       "      <td>0.004574</td>\n",
       "      <td>0.141585</td>\n",
       "      <td>30.952661</td>\n",
       "      <td>3.328333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>q03_bbox_count</td>\n",
       "      <td>0.015246</td>\n",
       "      <td>0.009385</td>\n",
       "      <td>0.214881</td>\n",
       "      <td>22.895957</td>\n",
       "      <td>1.624508</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>q04_stac_search_count</td>\n",
       "      <td>0.014443</td>\n",
       "      <td>0.006046</td>\n",
       "      <td>0.149947</td>\n",
       "      <td>24.800210</td>\n",
       "      <td>2.388825</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>q05_hash_range_search</td>\n",
       "      <td>0.016729</td>\n",
       "      <td>0.006733</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.484600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>q06_search_page_datetime_order</td>\n",
       "      <td>0.016459</td>\n",
       "      <td>0.009472</td>\n",
       "      <td>0.144857</td>\n",
       "      <td>15.292767</td>\n",
       "      <td>1.737593</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>q06_search_page_hash_order</td>\n",
       "      <td>0.015679</td>\n",
       "      <td>0.010341</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.516112</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>q07_attribute_filter</td>\n",
       "      <td>0.015809</td>\n",
       "      <td>0.007330</td>\n",
       "      <td>0.153744</td>\n",
       "      <td>20.975397</td>\n",
       "      <td>2.156838</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>q08_exact_geometry_intersects</td>\n",
       "      <td>0.015985</td>\n",
       "      <td>0.021185</td>\n",
       "      <td>0.185925</td>\n",
       "      <td>8.776355</td>\n",
       "      <td>0.754545</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>q09_grouped_aggregation</td>\n",
       "      <td>0.034884</td>\n",
       "      <td>0.014371</td>\n",
       "      <td>0.198808</td>\n",
       "      <td>13.833688</td>\n",
       "      <td>2.427357</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>q10_collection_latest_items</td>\n",
       "      <td>0.024467</td>\n",
       "      <td>0.021245</td>\n",
       "      <td>0.190527</td>\n",
       "      <td>8.968233</td>\n",
       "      <td>1.151668</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>q11_scoped_id_lookup</td>\n",
       "      <td>0.035527</td>\n",
       "      <td>0.028409</td>\n",
       "      <td>0.195707</td>\n",
       "      <td>6.888803</td>\n",
       "      <td>1.250527</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>q11_specific_id_lookup</td>\n",
       "      <td>0.036518</td>\n",
       "      <td>0.029061</td>\n",
       "      <td>0.183944</td>\n",
       "      <td>6.329475</td>\n",
       "      <td>1.256586</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "dataset                           query  hashed_128_files  hashed_12_files  \\\n",
       "0                q01_full_dataset_count          0.032251         0.014875   \n",
       "1                  q02_time_range_count          0.015225         0.004574   \n",
       "2                        q03_bbox_count          0.015246         0.009385   \n",
       "3                 q04_stac_search_count          0.014443         0.006046   \n",
       "4                 q05_hash_range_search          0.016729         0.006733   \n",
       "5        q06_search_page_datetime_order          0.016459         0.009472   \n",
       "6            q06_search_page_hash_order          0.015679         0.010341   \n",
       "7                  q07_attribute_filter          0.015809         0.007330   \n",
       "8         q08_exact_geometry_intersects          0.015985         0.021185   \n",
       "9               q09_grouped_aggregation          0.034884         0.014371   \n",
       "10          q10_collection_latest_items          0.024467         0.021245   \n",
       "11                 q11_scoped_id_lookup          0.035527         0.028409   \n",
       "12               q11_specific_id_lookup          0.036518         0.029061   \n",
       "\n",
       "dataset  microsoft  microsoft_vs_hashed_12_speedup  \\\n",
       "0         0.870463                       58.517549   \n",
       "1         0.141585                       30.952661   \n",
       "2         0.214881                       22.895957   \n",
       "3         0.149947                       24.800210   \n",
       "4              NaN                             NaN   \n",
       "5         0.144857                       15.292767   \n",
       "6              NaN                             NaN   \n",
       "7         0.153744                       20.975397   \n",
       "8         0.185925                        8.776355   \n",
       "9         0.198808                       13.833688   \n",
       "10        0.190527                        8.968233   \n",
       "11        0.195707                        6.888803   \n",
       "12        0.183944                        6.329475   \n",
       "\n",
       "dataset  hashed_128_vs_hashed_12_speedup  \n",
       "0                               2.168101  \n",
       "1                               3.328333  \n",
       "2                               1.624508  \n",
       "3                               2.388825  \n",
       "4                               2.484600  \n",
       "5                               1.737593  \n",
       "6                               1.516112  \n",
       "7                               2.156838  \n",
       "8                               0.754545  \n",
       "9                               2.427357  \n",
       "10                              1.151668  \n",
       "11                              1.250527  \n",
       "12                              1.256586  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>dataset</th>\n",
       "      <th>query</th>\n",
       "      <th>remote_hashed_128_files</th>\n",
       "      <th>remote_hashed_12_files</th>\n",
       "      <th>remote_microsoft</th>\n",
       "      <th>remote_microsoft_vs_hashed_12_speedup</th>\n",
       "      <th>remote_hashed_128_vs_hashed_12_speedup</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>q01_full_dataset_count</td>\n",
       "      <td>4.376748</td>\n",
       "      <td>0.797104</td>\n",
       "      <td>2.939739</td>\n",
       "      <td>3.688025</td>\n",
       "      <td>5.490814</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>q02_time_range_count</td>\n",
       "      <td>4.919652</td>\n",
       "      <td>1.094050</td>\n",
       "      <td>3.962012</td>\n",
       "      <td>3.621417</td>\n",
       "      <td>4.496734</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>q03_bbox_count</td>\n",
       "      <td>5.294268</td>\n",
       "      <td>1.483335</td>\n",
       "      <td>72.003055</td>\n",
       "      <td>48.541317</td>\n",
       "      <td>3.569164</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>q04_stac_search_count</td>\n",
       "      <td>4.677643</td>\n",
       "      <td>1.648777</td>\n",
       "      <td>12.213530</td>\n",
       "      <td>7.407630</td>\n",
       "      <td>2.837038</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>q05_hash_range_search</td>\n",
       "      <td>4.490073</td>\n",
       "      <td>1.212228</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.703984</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>q06_search_page_datetime_order</td>\n",
       "      <td>4.545145</td>\n",
       "      <td>1.547722</td>\n",
       "      <td>8.738074</td>\n",
       "      <td>5.645763</td>\n",
       "      <td>2.936667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>q06_search_page_hash_order</td>\n",
       "      <td>4.659922</td>\n",
       "      <td>1.644658</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.833369</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>q07_attribute_filter</td>\n",
       "      <td>4.365493</td>\n",
       "      <td>1.651420</td>\n",
       "      <td>14.672340</td>\n",
       "      <td>8.884681</td>\n",
       "      <td>2.643478</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>q08_exact_geometry_intersects</td>\n",
       "      <td>4.846160</td>\n",
       "      <td>1.598570</td>\n",
       "      <td>11.367901</td>\n",
       "      <td>7.111293</td>\n",
       "      <td>3.031559</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>q09_grouped_aggregation</td>\n",
       "      <td>7.999093</td>\n",
       "      <td>2.213163</td>\n",
       "      <td>54.336930</td>\n",
       "      <td>24.551702</td>\n",
       "      <td>3.614325</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>q10_collection_latest_items</td>\n",
       "      <td>5.722617</td>\n",
       "      <td>2.346078</td>\n",
       "      <td>28.150243</td>\n",
       "      <td>11.998853</td>\n",
       "      <td>2.439227</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>q11_scoped_id_lookup</td>\n",
       "      <td>8.310530</td>\n",
       "      <td>2.815297</td>\n",
       "      <td>38.638126</td>\n",
       "      <td>13.724351</td>\n",
       "      <td>2.951919</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>q11_specific_id_lookup</td>\n",
       "      <td>7.684417</td>\n",
       "      <td>2.196206</td>\n",
       "      <td>26.391167</td>\n",
       "      <td>12.016707</td>\n",
       "      <td>3.498950</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "dataset                           query  remote_hashed_128_files  \\\n",
       "0                q01_full_dataset_count                 4.376748   \n",
       "1                  q02_time_range_count                 4.919652   \n",
       "2                        q03_bbox_count                 5.294268   \n",
       "3                 q04_stac_search_count                 4.677643   \n",
       "4                 q05_hash_range_search                 4.490073   \n",
       "5        q06_search_page_datetime_order                 4.545145   \n",
       "6            q06_search_page_hash_order                 4.659922   \n",
       "7                  q07_attribute_filter                 4.365493   \n",
       "8         q08_exact_geometry_intersects                 4.846160   \n",
       "9               q09_grouped_aggregation                 7.999093   \n",
       "10          q10_collection_latest_items                 5.722617   \n",
       "11                 q11_scoped_id_lookup                 8.310530   \n",
       "12               q11_specific_id_lookup                 7.684417   \n",
       "\n",
       "dataset  remote_hashed_12_files  remote_microsoft  \\\n",
       "0                      0.797104          2.939739   \n",
       "1                      1.094050          3.962012   \n",
       "2                      1.483335         72.003055   \n",
       "3                      1.648777         12.213530   \n",
       "4                      1.212228               NaN   \n",
       "5                      1.547722          8.738074   \n",
       "6                      1.644658               NaN   \n",
       "7                      1.651420         14.672340   \n",
       "8                      1.598570         11.367901   \n",
       "9                      2.213163         54.336930   \n",
       "10                     2.346078         28.150243   \n",
       "11                     2.815297         38.638126   \n",
       "12                     2.196206         26.391167   \n",
       "\n",
       "dataset  remote_microsoft_vs_hashed_12_speedup  \\\n",
       "0                                     3.688025   \n",
       "1                                     3.621417   \n",
       "2                                    48.541317   \n",
       "3                                     7.407630   \n",
       "4                                          NaN   \n",
       "5                                     5.645763   \n",
       "6                                          NaN   \n",
       "7                                     8.884681   \n",
       "8                                     7.111293   \n",
       "9                                    24.551702   \n",
       "10                                   11.998853   \n",
       "11                                   13.724351   \n",
       "12                                   12.016707   \n",
       "\n",
       "dataset  remote_hashed_128_vs_hashed_12_speedup  \n",
       "0                                      5.490814  \n",
       "1                                      4.496734  \n",
       "2                                      3.569164  \n",
       "3                                      2.837038  \n",
       "4                                      3.703984  \n",
       "5                                      2.936667  \n",
       "6                                      2.833369  \n",
       "7                                      2.643478  \n",
       "8                                      3.031559  \n",
       "9                                      3.614325  \n",
       "10                                     2.439227  \n",
       "11                                     2.951919  \n",
       "12                                     3.498950  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "pivot = summary.pivot(index='query', columns='dataset', values='median_seconds')\n",
    "speedups = pivot.copy()\n",
    "if {'microsoft', 'hashed_12_files'}.issubset(speedups.columns):\n",
    "    speedups['microsoft_vs_hashed_12_speedup'] = speedups['microsoft'] / speedups['hashed_12_files']\n",
    "if {'hashed_128_files', 'hashed_12_files'}.issubset(speedups.columns):\n",
    "    speedups['hashed_128_vs_hashed_12_speedup'] = speedups['hashed_128_files'] / speedups['hashed_12_files']\n",
    "\n",
    "display(speedups.reset_index())\n",
    "\n",
    "if 'remote_summary' in globals() and not remote_summary.empty:\n",
    "    remote_pivot = remote_summary.pivot(index='query', columns='dataset', values='median_seconds')\n",
    "    remote_speedups = remote_pivot.copy()\n",
    "    if {'remote_microsoft', 'remote_hashed_12_files'}.issubset(remote_speedups.columns):\n",
    "        remote_speedups['remote_microsoft_vs_hashed_12_speedup'] = remote_speedups['remote_microsoft'] / remote_speedups['remote_hashed_12_files']\n",
    "    if {'remote_hashed_128_files', 'remote_hashed_12_files'}.issubset(remote_speedups.columns):\n",
    "        remote_speedups['remote_hashed_128_vs_hashed_12_speedup'] = remote_speedups['remote_hashed_128_files'] / remote_speedups['remote_hashed_12_files']\n",
    "    display(remote_speedups.reset_index())\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "id": "e3bf3ff6",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>dataset</th>\n",
       "      <th>files</th>\n",
       "      <th>row_groups</th>\n",
       "      <th>compressed_gb</th>\n",
       "      <th>median_row_groups_per_file</th>\n",
       "      <th>median_max_row_group_rows</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>hashed_128_files</td>\n",
       "      <td>128</td>\n",
       "      <td>128</td>\n",
       "      <td>701.764738</td>\n",
       "      <td>1.0</td>\n",
       "      <td>39307.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>hashed_12_files</td>\n",
       "      <td>12</td>\n",
       "      <td>48</td>\n",
       "      <td>613.700940</td>\n",
       "      <td>4.0</td>\n",
       "      <td>122880.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>microsoft</td>\n",
       "      <td>12</td>\n",
       "      <td>2463</td>\n",
       "      <td>1598.982122</td>\n",
       "      <td>220.0</td>\n",
       "      <td>2048.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            dataset  files  row_groups  compressed_gb  \\\n",
       "0  hashed_128_files    128         128     701.764738   \n",
       "1   hashed_12_files     12          48     613.700940   \n",
       "2         microsoft     12        2463    1598.982122   \n",
       "\n",
       "   median_row_groups_per_file  median_max_row_group_rows  \n",
       "0                         1.0                    39307.0  \n",
       "1                         4.0                   122880.0  \n",
       "2                       220.0                     2048.0  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "metadata_rows = []\n",
    "for dataset, glob in DATASETS.items():\n",
    "    df = con.execute(render(metadata_sql, glob, PARAMS)).df()\n",
    "    metadata_rows.append({\n",
    "        'dataset': dataset,\n",
    "        'files': len(df),\n",
    "        'row_groups': int(df['row_groups'].sum()),\n",
    "        'compressed_gb': float(df['compressed_bytes'].sum() / 1_000_000_000),\n",
    "        'median_row_groups_per_file': float(df['row_groups'].median()),\n",
    "        'median_max_row_group_rows': float(df['max_row_group_rows'].median()),\n",
    "    })\n",
    "\n",
    "file_summary = pd.DataFrame(metadata_rows).sort_values('dataset').reset_index(drop=True)\n",
    "display(file_summary)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "73ed4497",
   "metadata": {},
   "source": [
    "### Initial Reading Checklist\n",
    "\n",
    "- If `microsoft_vs_hashed_12_speedup` is high for `q04_stac_search_count`, the generated hashed 12-file layout is materially better for the STAC-style query.\n",
    "- If `hashed_128_vs_hashed_12_speedup` is above 1, the 12-file rewrite is faster than the 128-file layout for that query. That is a file-count or rewrite effect, not a hash effect.\n",
    "- If `q01_full_dataset_count` is dramatically different, do not attribute all wins to hash sorting. It usually indicates major Parquet-level differences such as compressed size, metadata, row groups, or schema encoding.\n",
    "- Treat `q05_hash_range_search` as provisional until the hash bounds are computed from the actual query window instead of placeholder min/max values.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e49911ed",
   "metadata": {},
   "source": [
    "## How To Read The Summary\n",
    "\n",
    "For each query, compare rows with the same `query` value:\n",
    "\n",
    "- `microsoft` vs `hashed_12_files` isolates sort/layout effects while keeping file count at 12.\n",
    "- `hashed_128_files` vs `hashed_12_files` shows how much file count changes timing for the same hashed data.\n",
    "- `q01_full_dataset_count` is mostly a raw scan control. If this differs a lot, file count/compression/schema overhead may be dominating.\n",
    "- `q04_stac_search_count` is the main STAC-style query to watch. A hashed win here is the strongest signal.\n",
    "- `q05_hash_range_search` is only meaningful once `PARAMS['min_hash']` and `PARAMS['max_hash']` represent the AOI/time window. Until then, treat it as provisional.\n",
    "- `q11_specific_id_lookup` is a control. Hash sorting is not expected to help much for a bare id lookup.\n",
    "\n",
    "Use `median_seconds` for comparison.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f6b260eb",
   "metadata": {},
   "source": [
    "## Inspect a Plan\n",
    "\n",
    "Use this when a timing difference looks interesting. The key line to look for is `Total Files Read`; if that number drops, DuckDB is pruning files. Also inspect filters shown under `TABLE_SCAN`.\n",
    "\n",
    "Start with `q04_stac_search_count` on `microsoft` and `hashed_12_files`, then compare their plans side by side.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "id": "3aa37255",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "SELECT count(*)\n",
      "FROM read_parquet('../data/benchmarks/generated/mspc-sentinel-2-l2a-sorted-12-files/*.parquet', hive_partitioning = false)\n",
      "WHERE collection = 'sentinel-2-l2a'\n",
      "  AND datetime >= TIMESTAMPTZ '2025-06-01T00:00:00Z'\n",
      "  AND datetime < TIMESTAMPTZ '2025-07-01T00:00:00Z'\n",
      "  AND bbox.xmax >= -109.0\n",
      "  AND bbox.xmin <= -102.0\n",
      "  AND bbox.ymax >= 37.0\n",
      "  AND bbox.ymin <= 41.0\n",
      "\n",
      "┌─────────────────────────────────────┐\n",
      "│┌───────────────────────────────────┐│\n",
      "││    Query Profiling Information    ││\n",
      "│└───────────────────────────────────┘│\n",
      "└─────────────────────────────────────┘\n",
      "EXPLAIN ANALYZE  SELECT count(*) FROM read_parquet('../data/benchmarks/generated/mspc-sentinel-2-l2a-sorted-12-files/*.parquet', hive_partitioning = false) WHERE collection = 'sentinel-2-l2a'   AND datetime >= TIMESTAMPTZ '2025-06-01T00:00:00Z'   AND datetime < TIMESTAMPTZ '2025-07-01T00:00:00Z'   AND bbox.xmax >= -109.0   AND bbox.xmin <= -102.0   AND bbox.ymax >= 37.0   AND bbox.ymin <= 41.0 \n",
      "┌─────────────────────────────────────┐\n",
      "│┌───────────────────────────────────┐│\n",
      "││         HTTPFS HTTP Stats         ││\n",
      "││                                   ││\n",
      "││            in: 0 bytes            ││\n",
      "││            out: 0 bytes           ││\n",
      "││              #HEAD: 0             ││\n",
      "││              #GET: 0              ││\n",
      "││              #PUT: 0              ││\n",
      "││              #POST: 0             ││\n",
      "││             #DELETE: 0            ││\n",
      "│└───────────────────────────────────┘│\n",
      "└─────────────────────────────────────┘\n",
      "┌────────────────────────────────────────────────┐\n",
      "│┌──────────────────────────────────────────────┐│\n",
      "││              Total Time: 0.0093s             ││\n",
      "│└──────────────────────────────────────────────┘│\n",
      "└────────────────────────────────────────────────┘\n",
      "┌───────────────────────────┐\n",
      "│           QUERY           │\n",
      "└─────────────┬─────────────┘\n",
      "┌─────────────┴─────────────┐\n",
      "│      EXPLAIN_ANALYZE      │\n",
      "│    ────────────────────   │\n",
      "│                           │\n",
      "│           0 rows          │\n",
      "│           0.00s           │\n",
      "└─────────────┬─────────────┘\n",
      "┌─────────────┴─────────────┐\n",
      "│    UNGROUPED_AGGREGATE    │\n",
      "│    ────────────────────   │\n",
      "│        Aggregates:        │\n",
      "│        count_star()       │\n",
      "│                           │\n",
      "│                           │\n",
      "│                           │\n",
      "│           1 row           │\n",
      "│           0.00s           │\n",
      "└─────────────┬─────────────┘\n",
      "┌─────────────┴─────────────┐\n",
      "│         TABLE_SCAN        │\n",
      "│    ────────────────────   │\n",
      "│         Function:         │\n",
      "│        READ_PARQUET       │\n",
      "│                           │\n",
      "│          Filters:         │\n",
      "│collection='sentinel-2-l2a'│\n",
      "│  datetime>='2025-06-01 00 │\n",
      "│ :00:00+00'::TIMESTAMP WITH│\n",
      "│   TIME ZONE AND datetime< │\n",
      "│ '2025-07-01 00:00:00+00': │\n",
      "│ :TIMESTAMP WITH TIME ZONE │\n",
      "│ bbox.xmax>=-109.0 AND bbox│\n",
      "│   .xmin<=-102.0 AND bbox  │\n",
      "│ .ymax>=37.0 AND bbox.ymin<│\n",
      "│           =41.0           │\n",
      "│                           │\n",
      "│    Total Files Read: 12   │\n",
      "│                           │\n",
      "│        Filename(s):       │\n",
      "│     ../data/benchmarks    │\n",
      "│ /generated/mspc-sentinel-2│\n",
      "│   -l2a-sorted-12-files/*  │\n",
      "│       .parquet, ...       │\n",
      "│                           │\n",
      "│                           │\n",
      "│                           │\n",
      "│          783 rows         │\n",
      "│           0.04s           │\n",
      "└───────────────────────────┘\n",
      "\n"
     ]
    }
   ],
   "source": [
    "dataset_name = 'hashed_12_files'\n",
    "query_name = 'q04_stac_search_count'\n",
    "\n",
    "sql = render(QUERIES[query_name], DATASETS[dataset_name], PARAMS)\n",
    "print(sql)\n",
    "print(explain_analyze(sql))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "id": "b3143c35",
   "metadata": {},
   "outputs": [],
   "source": [
    "# JSON profile for downstream parsing. This is mostly useful once you know which query/dataset pair matters.\n",
    "# Uncomment when needed.\n",
    "# profile = explain_analyze_json(sql)\n",
    "# profile\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "79d41b73",
   "metadata": {},
   "source": [
    "## Export Results\n",
    "\n",
    "Run this after `results` exists and you want to save the timings for sharing or comparison.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "id": "ca8fec01",
   "metadata": {},
   "outputs": [],
   "source": [
    "def write_results(results: list[BenchmarkResult], path: str | Path):\n",
    "    path = Path(path)\n",
    "    path.parent.mkdir(parents=True, exist_ok=True)\n",
    "    rows = [\n",
    "        {\n",
    "            'dataset': result.dataset,\n",
    "            'query': result.query,\n",
    "            'rows': result.rows,\n",
    "            'best_seconds': result.best_seconds,\n",
    "            'median_seconds': result.median_seconds,\n",
    "            'runs': list(result.runs),\n",
    "            'duckdb_version': duckdb.__version__,\n",
    "        }\n",
    "        for result in results\n",
    "    ]\n",
    "    path.write_text(json.dumps(rows, indent=2), encoding='utf-8')\n",
    "    return path\n",
    "\n",
    "\n",
    "write_results(results, '../benchmark-results/duckdb-geoparquet-results.json')\n",
    "if 'remote_results' in globals():\n",
    "    write_results(remote_results, '../benchmark-results/duckdb-geoparquet-remote-results.json')\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}