Funnel Actors Query Fails When Funnel Has Many Steps

[webjunkie]

When creating a funnel with a large number of steps in PostHog, the funnel actors query fails to execute successfully. This issue prevents the retrieval of actor data, making it difficult to analyze user behavior through complex funnels.

Steps to Reproduce:

1. Create a funnel in PostHog with a significant number of steps (e.g., 10 or more steps).
2. Attempt to query the funnel actors to analyze the users who completed or dropped off at each step.
3. Observe the behavior and note any errors or failures in the query execution.

Expected Result: The funnel actors query should execute successfully, returning the actors' data for each step of the funnel, regardless of the number of steps.

Actual Result: The funnel actors query fails when the funnel includes a large number of steps, preventing the retrieval of actor data and hindering detailed analysis of the funnel.

https://posthoghelp.zendesk.com/agent/tickets/14256

[webjunkie]

Another one https://posthoghelp.zendesk.com/agent/tickets/14979

[aspicer]

Another example: this funnel based off of this experiment won't load actors even though it shows that people have reached the later stage.

More info: https://posthog.slack.com/archives/C0368RPHLQH/p1719540397842309 https://posthog.slack.com/archives/C0374DA782U/p1717801112677939

[aspicer]

The other issue might actual be separate - results are just odd when there's a breakdown. Keeping it separate.

[slshults]

Another in https://posthoghelp.zendesk.com/agent/tickets/14118 (Looks like they've since reduced the number of steps in the funnel.)

[skoob13]

Another in https://posthoghelp.zendesk.com/agent/tickets/15371. There are 16 steps in the funnel.

Separate issue and moved to #23522

[aspicer]

So this looks like it's timing out, and not in a good way. If you run the following on metabase: select * from clusterAllReplicas(posthog, system.query_log) where event_date = '2024-07-03' and query like '%countIf(ifNull(equals(steps, 17), 0)) AS step_17%' and query not like '%query_log%' order by event_time desc limit 10

there are two exceptions that crop up

• Code: 36. DB::Exception: Query tree is too big. Maximum: 2000000. (BAD_ARGUMENTS) (version 23.12.5.81 (official build))
• Code: 159. DB::Exception: Timeout exceeded: elapsed 2850.284703422 seconds, maximum: 600. (TIMEOUT_EXCEEDED) (version 23.12.5.81 (official build))

The first one we can fix, but there's really no point, because the second one is what happens to all these 17 funnel step queries.

It oddly lets them run for nearly an hour.

It seems like the time it takes blows up exponentially for steps. Almost exactly 2ⁿ. I ran some tests on the insight linked here 10 steps: 19 seconds 11 steps: 36 seconds 12 steps: 70 seconds 13 steps: 143 seconds 14 steps: 288 seconds 15 steps: blew up (600+)

an example of the failing query is here https://posthog.slack.com/files/U07040M78VA/F07AZ8RCD0T/scratch_20.sql

[skoob13]

Another in https://posthoghelp.zendesk.com/agent/tickets/15291 for FunnelActorsQuery: the AST length exception. Log is here.

[Twixes]

+1 from ZEN-11277 +1 from ZEN-15869

[slshults]

Another here: https://posthoghelp.zendesk.com/agent/tickets/16149

[RCVZ]

When can we expect this issue to be fixed?