explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ZZaU

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 1.986 276,214.884 ↑ 1.0 1 1

Aggregate (cost=3,912.32..3,912.33 rows=1 width=16) (actual time=276,214.884..276,214.884 rows=1 loops=1)

2.          

CTE step_0

3. 25.010 313.544 ↓ 15,664.0 15,664 1

Unique (cost=1,617.57..1,617.58 rows=1 width=49) (actual time=243.696..313.544 rows=15,664 loops=1)

4. 86.468 288.534 ↓ 15,778.0 15,778 1

Sort (cost=1,617.57..1,617.58 rows=1 width=49) (actual time=243.694..288.534 rows=15,778 loops=1)

  • Sort Key: events.user_id
  • Sort Method: quicksort Memory: 2603kB
5. 9.321 202.066 ↓ 15,778.0 15,778 1

Nested Loop Semi Join (cost=0.98..1,617.56 rows=1 width=49) (actual time=91.013..202.066 rows=15,778 loops=1)

6. 128.553 128.553 ↓ 32,096.0 32,096 1

Index Scan using project_id_event_name_id_timestamp_idx on events (cost=0.56..1,601.10 rows=1 width=53) (actual time=0.108..128.553 rows=32,096 loops=1)

  • Index Cond: ((project_id = '215'::bigint) AND ("timestamp" >= '1569559210'::bigint) AND ("timestamp" <= '1570164010'::bigint))
7. 64.192 64.192 ↓ 0.0 0 32,096

Index Scan using event_names_pkey on event_names (cost=0.42..8.44 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=32,096)

  • Index Cond: ((id = events.event_name_id) AND (project_id = '215'::bigint))
  • Filter: (name = '$session'::text)
  • Rows Removed by Filter: 1
8.          

CTE step_1

9. 0.002 275,753.005 ↓ 0.0 0 1

Unique (cost=2,286.06..2,286.07 rows=1 width=49) (actual time=275,753.005..275,753.005 rows=0 loops=1)

10. 0.017 275,753.003 ↓ 0.0 0 1

Sort (cost=2,286.06..2,286.07 rows=1 width=49) (actual time=275,753.003..275,753.003 rows=0 loops=1)

  • Sort Key: events_1.user_id
  • Sort Method: quicksort Memory: 25kB
11. 118.241 275,752.986 ↓ 0.0 0 1

Nested Loop Semi Join (cost=0.98..2,286.05 rows=1 width=49) (actual time=275,752.986..275,752.986 rows=0 loops=1)

12. 84.473 275,282.976 ↓ 31,979.0 31,979 1

Nested Loop (cost=0.56..2,272.85 rows=1 width=53) (actual time=15.536..275,282.976 rows=31,979 loops=1)

13. 122.999 122.999 ↓ 15,664.0 15,664 1

CTE Scan on step_0 (cost=0.00..0.02 rows=1 width=40) (actual time=0.000..122.999 rows=15,664 loops=1)

14. 275,075.504 275,075.504 ↓ 2.0 2 15,664

Index Scan using project_id_event_name_id_user_id_idx on events events_1 (cost=0.56..2,272.82 rows=1 width=53) (actual time=11.309..17.561 rows=2 loops=15,664)

  • Index Cond: ((project_id = '215'::bigint) AND (user_id = step_0.user_id))
  • Filter: (("timestamp" <= '1570164010'::bigint) AND ("timestamp" >= step_0.step_0_timestamp))
  • Rows Removed by Filter: 0
15. 351.769 351.769 ↓ 0.0 0 31,979

Index Scan using event_names_pkey on event_names event_names_1 (cost=0.42..6.81 rows=1 width=8) (actual time=0.011..0.011 rows=0 loops=31,979)

  • Index Cond: ((id = events_1.event_name_id) AND (project_id = '215'::bigint))
  • Filter: (name = 'blog.expertrec.com/wordpress-design-help/'::text)
  • Rows Removed by Filter: 1
16.          

CTE funnel

17. 2.615 276,210.433 ↓ 15,664.0 15,664 1

Unique (cost=8.64..8.65 rows=1 width=40) (actual time=276,204.022..276,210.433 rows=15,664 loops=1)

18. 12.205 276,207.818 ↓ 15,664.0 15,664 1

Sort (cost=8.64..8.65 rows=1 width=40) (actual time=276,204.021..276,207.818 rows=15,664 loops=1)

  • Sort Key: (COALESCE(users.customer_user_id, (step_0_1.user_id)::character varying)), step_0_1.step_0, step_1.step_1
  • Sort Method: quicksort Memory: 1608kB
19. 8.787 276,195.613 ↓ 15,664.0 15,664 1

Nested Loop Left Join (cost=0.55..8.63 rows=1 width=40) (actual time=275,996.814..276,195.613 rows=15,664 loops=1)

  • Join Filter: (step_0_1.user_id = step_1.user_id)
20. 2.598 437.770 ↓ 15,664.0 15,664 1

Nested Loop Left Join (cost=0.55..8.60 rows=1 width=71) (actual time=243.803..437.770 rows=15,664 loops=1)

21. 247.204 247.204 ↓ 15,664.0 15,664 1

CTE Scan on step_0 step_0_1 (cost=0.00..0.02 rows=1 width=36) (actual time=243.699..247.204 rows=15,664 loops=1)

22. 187.968 187.968 ↑ 1.0 1 15,664

Index Scan using users_pkey on users (cost=0.55..8.57 rows=1 width=72) (actual time=0.012..0.012 rows=1 loops=15,664)

  • Index Cond: (step_0_1.user_id = id)
23. 275,749.056 275,749.056 ↓ 0.0 0 15,664

CTE Scan on step_1 (cost=0.00..0.02 rows=1 width=36) (actual time=17.604..17.604 rows=0 loops=15,664)

24. 276,212.898 276,212.898 ↓ 15,664.0 15,664 1

CTE Scan on funnel (cost=0.00..0.02 rows=1 width=8) (actual time=276,204.026..276,212.898 rows=15,664 loops=1)