## In what situation are you experiencing subpar performance?

Selecting multiple properties with HogQL translates to less than optimally performant code:

<img width="1226" alt="image" src="https://github.com/PostHog/posthog/assets/53387/af464d83-660e-439e-9026-1799080e1d9c">

This gets more apparent if you have a lot of properties and look at the ClickHouse output.

The old lifecycle insight gave me the following code for the filter in this [insight](https://app.posthog.com/insights/GmcRM06S/edit):

```sql
  AND (
    has(
      [ 'standard', 
      'enterprise', 
      'scale', 
      'startup', 
      'flat-5', 
      'starter', 
      'growth', 
      'premium', 
      'Enterprise' ], 
      replaceRegexpAll(JSONExtractRaw(e.person_properties, 'billing_plan'), '^"|"$', '')
    )
  ) 
```

The new HogQL lifecycle insight gave the following code:

```sql
or(
    equals(replaceRegexpAll(nullIf(nullIf(JSONExtractRaw(events.person_properties, 'billing_plan'), ''), 'null'), '^"|"$', ''), 'standard'), 
    equals(replaceRegexpAll(nullIf(nullIf(JSONExtractRaw(events.person_properties, 'billing_plan'), ''), 'null'), '^"|"$', ''), 'enterprise'), 
    equals(replaceRegexpAll(nullIf(nullIf(JSONExtractRaw(events.person_properties, 'billing_plan'), ''), 'null'), '^"|"$', ''), 'scale'), 
    equals(replaceRegexpAll(nullIf(nullIf(JSONExtractRaw(events.person_properties, 'billing_plan'), ''), 'null'), '^"|"$', ''), 'startup'), 
    equals(replaceRegexpAll(nullIf(nullIf(JSONExtractRaw(events.person_properties, 'billing_plan'), ''), 'null'), '^"|"$', ''), 'flat-5'), 
    equals(replaceRegexpAll(nullIf(nullIf(JSONExtractRaw(events.person_properties, 'billing_plan'), ''), 'null'), '^"|"$', ''), 'starter'), 
    equals(replaceRegexpAll(nullIf(nullIf(JSONExtractRaw(events.person_properties, 'billing_plan'), ''), 'null'), '^"|"$', ''), 'growth'), 
    equals(replaceRegexpAll(nullIf(nullIf(JSONExtractRaw(events.person_properties, 'billing_plan'), ''), 'null'), '^"|"$', ''), 'premium'), 
    equals(replaceRegexpAll(nullIf(nullIf(JSONExtractRaw(events.person_properties, 'billing_plan'), ''), 'null'), '^"|"$', ''), 'Enterprise')
), 
```

ClickHouse isn't (or at least isn't guaranteed to be) clever in the way it reads data. The same `events.person_properties` field might be read 9 separate times to extract the same key, making this potentially really slow.

## How to reproduce

1. Go to /debug
2. Enter a filter for a property, e.g. $browser is Chrome, Firefox, Safari
3. Look at the generated code


## Additional context


The problem comes from these lazy lines in [hogql/property.py](https://github.com/PostHog/posthog/blob/5513be7731414cb1a4ffa5a7789610005a8538ae/posthog/hogql/property.py#L129-L148). We should handle comparisons with a list of constants in a much better way. We should also make sure all property comparisons work.


#### *Thank you* for your performance issue report – we want PostHog to go supersonic!

posthog/hogql/property.py
if step.url:
            if step.url_matching == ActionStep.EXACT:
                expr = parse_expr(
                    "properties.$current_url = {url}",
                    {"url": ast.Constant(value=step.url)},
                )
            elif step.url_matching == ActionStep.REGEX:
                expr = parse_expr(
                    "properties.$current_url =~ {regex}",
                    {"regex": ast.Constant(value=step.url)},
                )
            else:
                expr = parse_expr(
                    "properties.$current_url like {url}",
                    {"url": ast.Constant(value=f"%{step.url}%")},
                )
            exprs.append(expr)
        if step.properties:
            exprs.append(property_to_expr(step.properties, action.team))
        if len(exprs) == 1:
            or_queries.append(exprs[0])
        elif len(exprs) > 1:
            or_queries.append(ast.And(exprs=exprs))
        else:
            or_queries.append(ast.Constant(value=True))
    if len(or_queries) == 1:
        return or_queries[0]
    else:
        return ast.Or(exprs=or_queries)
def element_chain_key_filter(key: str, text: str, operator: PropertyOperator):