explain.depesz.com

PostgreSQL's explain analyze made readable

Result: BUwDA : Optimization for: plan #ZZaU

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 1.323 8,393.054 ↑ 1.0 1 1

Aggregate (cost=8,057.75..8,057.76 rows=1 width=16) (actual time=8,393.054..8,393.054 rows=1 loops=1)

2.          

CTE step_0

3. 2.511 84.766 ↓ 11,382.0 11,382 1

Unique (cost=4,024.52..4,024.52 rows=1 width=49) (actual time=77.694..84.766 rows=11,382 loops=1)

4. 41.762 82.255 ↓ 14,085.0 14,085 1

Sort (cost=4,024.52..4,024.52 rows=1 width=49) (actual time=77.693..82.255 rows=14,085 loops=1)

  • Sort Key: events.user_id
  • Sort Method: quicksort Memory: 2365kB
5. 6.913 40.493 ↓ 14,085.0 14,085 1

Nested Loop (cost=362.81..4,024.51 rows=1 width=49) (actual time=6.294..40.493 rows=14,085 loops=1)

6. 5.361 6.913 ↑ 1.0 1 1

Bitmap Heap Scan on event_names (cost=362.25..4,015.91 rows=1 width=8) (actual time=6.203..6.913 rows=1 loops=1)

  • Recheck Cond: (project_id = '22'::bigint)
  • Filter: (name = '$session'::text)
  • Rows Removed by Filter: 11408
  • Heap Blocks: exact=1949
7. 1.552 1.552 ↑ 1.0 11,409 1

Bitmap Index Scan on project_type_fexpr_unique_idx (cost=0.00..362.25 rows=11,444 width=0) (actual time=1.552..1.552 rows=11,409 loops=1)

  • Index Cond: (project_id = '22'::bigint)
8. 26.667 26.667 ↓ 14,085.0 14,085 1

Index Scan using project_id_event_name_id_timestamp_idx on events (cost=0.56..8.59 rows=1 width=53) (actual time=0.083..26.667 rows=14,085 loops=1)

  • Index Cond: ((project_id = '22'::bigint) AND (event_name_id = event_names.id) AND ("timestamp" >= '1569559210'::bigint) AND ("timestamp" <= '1570164010'::bigint))
9.          

CTE step_1

10. 1.166 184.279 ↓ 3,840.0 3,840 1

Unique (cost=4,024.55..4,024.56 rows=1 width=49) (actual time=182.193..184.279 rows=3,840 loops=1)

11. 5.081 183.113 ↓ 9,189.0 9,189 1

Sort (cost=4,024.55..4,024.55 rows=1 width=49) (actual time=182.193..183.113 rows=9,189 loops=1)

  • Sort Key: events_1.user_id
  • Sort Method: quicksort Memory: 1677kB
12. 1.160 178.032 ↓ 9,189.0 9,189 1

Nested Loop (cost=362.81..4,024.54 rows=1 width=49) (actual time=1.408..178.032 rows=9,189 loops=1)

13. 6.061 7.108 ↑ 1.0 1 1

Bitmap Heap Scan on event_names event_names_1 (cost=362.25..4,015.91 rows=1 width=8) (actual time=1.353..7.108 rows=1 loops=1)

  • Recheck Cond: (project_id = '22'::bigint)
  • Filter: (name = 'CheckMyRate'::text)
  • Rows Removed by Filter: 11408
  • Heap Blocks: exact=1949
14. 1.047 1.047 ↑ 1.0 11,409 1

Bitmap Index Scan on project_type_fexpr_unique_idx (cost=0.00..362.25 rows=11,444 width=0) (actual time=1.047..1.047 rows=11,409 loops=1)

  • Index Cond: (project_id = '22'::bigint)
15. 0.000 169.764 ↓ 9,189.0 9,189 1

Nested Loop (cost=0.56..8.62 rows=1 width=53) (actual time=0.051..169.764 rows=9,189 loops=1)

16. 10.849 10.849 ↓ 11,382.0 11,382 1

CTE Scan on step_0 (cost=0.00..0.02 rows=1 width=40) (actual time=0.001..10.849 rows=11,382 loops=1)

17. 159.348 159.348 ↑ 1.0 1 11,382

Index Scan using project_id_event_name_id_user_id_idx on events events_1 (cost=0.56..8.59 rows=1 width=53) (actual time=0.009..0.014 rows=1 loops=11,382)

  • Index Cond: ((project_id = '22'::bigint) AND (event_name_id = event_names_1.id) AND (user_id = step_0.user_id))
  • Filter: (("timestamp" <= '1570164010'::bigint) AND ("timestamp" >= step_0.step_0_timestamp))
  • Rows Removed by Filter: 1
18.          

CTE funnel

19. 2.144 8,390.040 ↓ 11,382.0 11,382 1

Unique (cost=8.64..8.65 rows=1 width=40) (actual time=8,386.350..8,390.040 rows=11,382 loops=1)

20. 11.247 8,387.896 ↓ 11,382.0 11,382 1

Sort (cost=8.64..8.65 rows=1 width=40) (actual time=8,386.349..8,387.896 rows=11,382 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: 1274kB
21. 4,323.373 8,376.649 ↓ 11,382.0 11,382 1

Nested Loop Left Join (cost=0.55..8.63 rows=1 width=40) (actual time=263.515..8,376.649 rows=11,382 loops=1)

  • Join Filter: (step_0_1.user_id = step_1.user_id)
  • Rows Removed by Join Filter: 43703040
22. 11.647 251.688 ↓ 11,382.0 11,382 1

Nested Loop Left Join (cost=0.55..8.60 rows=1 width=71) (actual time=77.770..251.688 rows=11,382 loops=1)

23. 80.693 80.693 ↓ 11,382.0 11,382 1

CTE Scan on step_0 step_0_1 (cost=0.00..0.02 rows=1 width=36) (actual time=77.696..80.693 rows=11,382 loops=1)

24. 159.348 159.348 ↑ 1.0 1 11,382

Index Scan using users_pkey on users (cost=0.55..8.57 rows=1 width=72) (actual time=0.014..0.014 rows=1 loops=11,382)

  • Index Cond: (step_0_1.user_id = id)
25. 3,801.588 3,801.588 ↓ 3,840.0 3,840 11,382

CTE Scan on step_1 (cost=0.00..0.02 rows=1 width=36) (actual time=0.016..0.334 rows=3,840 loops=11,382)

26. 8,391.731 8,391.731 ↓ 11,382.0 11,382 1

CTE Scan on funnel (cost=0.00..0.02 rows=1 width=8) (actual time=8,386.353..8,391.731 rows=11,382 loops=1)