Ejemplo práctico de optimización de consultas

SET enable_filesystem_cache = 0;
SET use_query_cache = 0;
SET use_query_condition_cache = 0;

WITH
    dateDiff('s', pickup_datetime, dropoff_datetime) AS trip_time,
    (trip_distance / trip_time) * 3600 AS speed_mph
SELECT quantiles(0.5, 0.75, 0.9, 0.99)(trip_distance)
FROM nyc_taxi.trips_small_inferred
WHERE speed_mph > 30
FORMAT JSON;

SELECT
    payment_type,
    count() AS trip_count,
    formatReadableQuantity(sum(trip_distance)) AS total_distance,
    avg(total_amount) AS total_amount_avg,
    avg(tip_amount) AS tip_amount_avg
FROM nyc_taxi.trips_small_inferred
WHERE pickup_datetime >= '2009-01-01'
  AND pickup_datetime < '2009-04-01'
GROUP BY payment_type
ORDER BY trip_count DESC;

SELECT avg(dateDiff('s', pickup_datetime, dropoff_datetime))
FROM nyc_taxi.trips_small_inferred
WHERE passenger_count = 1 OR passenger_count = 2
FORMAT JSON;

SELECT
    countIf(vendor_id IS NULL) AS vendor_id_nulls,
    countIf(pickup_datetime IS NULL) AS pickup_datetime_nulls,
    countIf(dropoff_datetime IS NULL) AS dropoff_datetime_nulls,
    countIf(passenger_count IS NULL) AS passenger_count_nulls,
    countIf(trip_distance IS NULL) AS trip_distance_nulls,
    countIf(ratecode_id IS NULL) AS ratecode_id_nulls,
    countIf(fare_amount IS NULL) AS fare_amount_nulls,
    countIf(extra IS NULL) AS extra_nulls,
    countIf(mta_tax IS NULL) AS mta_tax_nulls,
    countIf(tip_amount IS NULL) AS tip_amount_nulls,
    countIf(tolls_amount IS NULL) AS tolls_amount_nulls,
    countIf(total_amount IS NULL) AS total_amount_nulls,
    countIf(payment_type IS NULL) AS payment_type_nulls,
    countIf(pickup_location_id IS NULL) AS pickup_location_id_nulls,
    countIf(dropoff_location_id IS NULL) AS dropoff_location_id_nulls
FROM nyc_taxi.trips_small_inferred
FORMAT VERTICAL;

Row 1:
──────
vendor_id_nulls:           0
pickup_datetime_nulls:     0
dropoff_datetime_nulls:    0
passenger_count_nulls:     0
trip_distance_nulls:       0
ratecode_id_nulls:          167200929
fare_amount_nulls:         0
extra_nulls:               0
mta_tax_nulls:             137946731
tip_amount_nulls:          0
tolls_amount_nulls:        0
total_amount_nulls:        0
payment_type_nulls:        69305
pickup_location_id_nulls:  0
dropoff_location_id_nulls: 0

SELECT
    uniq(ratecode_id),
    uniq(pickup_location_id),
    uniq(dropoff_location_id),
    uniq(vendor_id)
FROM nyc_taxi.trips_small_inferred
FORMAT VERTICAL;

Row 1:
──────
uniq(ratecode_id):         6
uniq(pickup_location_id):  260
uniq(dropoff_location_id): 260
uniq(vendor_id):           3

SELECT
    min(payment_type),
    max(payment_type),
    min(passenger_count),
    max(passenger_count)
FROM nyc_taxi.trips_small_inferred;

   ┌─min(payment_type)─┬─max(payment_type)─┬─min(passenger_count)─┬─max(passenger_count)─┐
1. │                 1 │                 4 │                    0 │                  255 │
   └───────────────────┴───────────────────┴──────────────────────┴──────────────────────┘

CREATE TABLE nyc_taxi.trips_small_no_pk
(
    vendor_id LowCardinality(String),
    pickup_datetime DateTime('UTC'),
    dropoff_datetime DateTime('UTC'),
    passenger_count UInt8,
    trip_distance Float32,
    ratecode_id LowCardinality(Nullable(String)),
    pickup_location_id LowCardinality(String),
    dropoff_location_id LowCardinality(String),
    payment_type Nullable(UInt8),
    fare_amount Decimal32(2),
    extra Decimal32(2),
    mta_tax Nullable(Decimal32(2)),
    tip_amount Decimal32(2),
    tolls_amount Decimal32(2),
    total_amount Decimal32(2)
)
ENGINE = MergeTree
ORDER BY tuple();

INSERT INTO nyc_taxi.trips_small_no_pk
SELECT *
FROM nyc_taxi.trips_small_inferred;

SELECT
    table,
    formatReadableSize(sum(data_compressed_bytes)) AS compressed,
    formatReadableSize(sum(data_uncompressed_bytes)) AS uncompressed,
    sum(rows) AS rows
FROM system.parts
WHERE active = 1
  AND database = 'nyc_taxi'
  AND table IN ('trips_small_inferred', 'trips_small_no_pk')
GROUP BY database, table
ORDER BY sum(data_compressed_bytes) DESC;

   ┌─table────────────────┬─compressed─┬─uncompressed─┬──────rows─┐
1. │ trips_small_inferred │ 7.38 GiB   │ 37.41 GiB    │ 329044175 │
2. │ trips_small_no_pk    │ 4.89 GiB   │ 15.31 GiB    │ 329044175 │
   └──────────────────────┴────────────┴──────────────┴───────────┘

CREATE TABLE nyc_taxi.trips_small_pk
(
    vendor_id LowCardinality(String),
    pickup_datetime DateTime('UTC'),
    dropoff_datetime DateTime('UTC'),
    passenger_count UInt8,
    trip_distance Float32,
    ratecode_id LowCardinality(Nullable(String)),
    pickup_location_id LowCardinality(String),
    dropoff_location_id LowCardinality(String),
    payment_type Nullable(UInt8),
    fare_amount Decimal32(2),
    extra Decimal32(2),
    mta_tax Nullable(Decimal32(2)),
    tip_amount Decimal32(2),
    tolls_amount Decimal32(2),
    total_amount Decimal32(2)
)
ENGINE = MergeTree
ORDER BY (passenger_count, pickup_datetime, dropoff_datetime);

INSERT INTO nyc_taxi.trips_small_pk
SELECT *
FROM nyc_taxi.trips_small_no_pk;

EXPLAIN indexes = 1
SELECT
    payment_type,
    count() AS trip_count,
    formatReadableQuantity(sum(trip_distance)) AS total_distance,
    avg(total_amount) AS total_amount_avg,
    avg(tip_amount) AS tip_amount_avg
FROM nyc_taxi.trips_small_pk
WHERE pickup_datetime >= '2009-01-01'
  AND pickup_datetime < '2009-04-01'
GROUP BY payment_type
ORDER BY trip_count DESC
SETTINGS
    use_query_condition_cache = 0,
    use_skip_indexes_on_data_read = 0;

ReadFromMergeTree (nyc_taxi.trips_small_pk)
Indexes:
  PrimaryKey
    Keys:
      pickup_datetime
    Condition: and((pickup_datetime in (-Inf, 1238543999]), (pickup_datetime in [1230768000, +Inf)))
    Parts: 9/9
    Granules: 5061/40167