Compare Elasticsearch vs Loki vs ClickHouse for log management. When would you choose each?

# Loki LogQL query (equivalent to ES full-text search)
{namespace="production", app="api-gateway"}
  |= "error"
  | json
  | status_code >= 500
  | line_format "{{.timestamp}} {{.message}}"

# ClickHouse log table
CREATE TABLE logs (
  timestamp DateTime64(3),
  service LowCardinality(String),
  level LowCardinality(String),
  message String,
  trace_id String
) ENGINE = MergeTree()
PARTITION BY toYYYYMMDD(timestamp)
ORDER BY (service, timestamp)
TTL timestamp + INTERVAL 30 DAY;

# Elasticsearch equivalent query
GET logs-*/_search
{
  "query": {
    "bool": {
      "must": [
        { "match": { "message": "error" }},
        { "range": { "status_code": { "gte": 500 }}}
      ]
    }
  }
}