ft_vs_obj.md

FT vs OBJ

Query 1: STRING column with fulltext

SELECT COUNT(*), SUM(EnergyUsageDelta) AS EnergyUsage
FROM ...
WHERE
  DateTime >= '2018-01-01T00:00:00'
  AND DateTime < '2018-01-07T00:00:00'
  AND devicetypeid IS NOT NULL
  AND NOT (
    match(devicetypeid_idx, '3')
    OR match(devicetypeid_idx, '10')
    OR match(devicetypeid_idx, '12')
  );

• Concurrency: 1
• Iterations: 500

Result

Runtime (in ms):
    mean:    314.224 ± 1.337
    min/max: 278.885 → 399.355
Percentile:
    50:   312.621 ± 15.253 (stdev)
    95:   339.826
    99.9: 399.355

Query 2: OBJECT column

SELECT COUNT(*), SUM(EnergyUsageDelta) AS EnergyUsage
FROM ...
WHERE
  DateTime >= '2018-01-01T00:00:00'
  AND DateTime < '2018-01-07T00:00:00'
  AND devicetypeid['3'] = FALSE
  AND devicetypeid['10'] = FALSE
  AND devicetypeid['12'] = FALSE;

• Concurrency: 1
• Iterations: 500

Result

Runtime (in ms):
    mean:    316.982 ± 2.254
    min/max: 271.717 → 709.675
Percentile:
    50:   316.764 ± 25.716 (stdev)
    95:   342.271
    99.9: 709.675