posthog
posthog copied to clipboard
PostHog
Funnel Actors Query Fails When Funnel Has Many Steps
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:
- Create a funnel in PostHog with a significant number of steps (e.g., 10 or more steps).
- Attempt to query the funnel actors to analyze the users who completed or dropped off at each step.
- 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
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
The other issue might actual be separate - results are just odd when there's a breakdown. Keeping it separate.
Another in https://posthoghelp.zendesk.com/agent/tickets/14118 (Looks like they've since reduced the number of steps in the funnel.)
Another in https://posthoghelp.zendesk.com/agent/tickets/15371. There are 16 steps in the funnel.
Separate issue and moved to #23522
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 2n. 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
Another in https://posthoghelp.zendesk.com/agent/tickets/15291 for FunnelActorsQuery: the AST length exception. Log is here.
As discussed, the ability to go above 20 steps now when the rollout is complete would be great!
From: https://posthoghelp.zendesk.com/agent/tickets/19669
Hey, just for my visibility - is there any PR in the makings to fix this?
Want to understand, whether it's viable to just wait another week to get this fixed, or better to change my funnel altogether for less steps.
Thank you.
FYI - I stopped having problems on funnel with 13 steps. Perhaps fix has been rolled out.