explain.depesz.com

PostgreSQL's explain analyze made readable

Result: zXm

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.022 0.301 ↑ 1.3 3 1

GroupAggregate (cost=111.24..111.42 rows=4 width=345) (actual time=0.295..0.301 rows=3 loops=1)

  • Group Key: e1.event_id, events_index_tables.event_date, (CASE WHEN ((e1.deleted IS TRUE) OR (events_index_tables.event_date = ANY (e1.ex_dates)) OR (events_index_tables.event_date >= e1.future_deleted_date)) THEN true ELSE false END)
2.          

CTE events_index_tables

3. 0.010 0.076 ↑ 10.5 19 1

HashAggregate (cost=96.11..98.11 rows=200 width=8) (actual time=0.072..0.076 rows=19 loops=1)

  • Group Key: (unnest(events_colleague_repeating.event_ids)), events_colleague_repeating.event_date
4. 0.014 0.066 ↑ 42.1 19 1

HashAggregate (cost=76.11..84.11 rows=800 width=8) (actual time=0.059..0.066 rows=19 loops=1)

  • Group Key: (unnest(events_colleague_repeating.event_ids)), events_colleague_repeating.event_date
5. 0.003 0.052 ↑ 42.1 19 1

Append (cost=4.37..72.11 rows=800 width=8) (actual time=0.042..0.052 rows=19 loops=1)

6. 0.007 0.047 ↑ 36.8 19 1

ProjectSet (cost=4.37..20.26 rows=700 width=8) (actual time=0.041..0.047 rows=19 loops=1)

7. 0.009 0.040 ↑ 1.0 7 1

Bitmap Heap Scan on events_colleague_repeating (cost=4.37..16.71 rows=7 width=33) (actual time=0.039..0.040 rows=7 loops=1)

  • Recheck Cond: ((client_alias_id = 25) AND (event_date >= '2020-09-07'::date) AND (event_date <= '2020-09-13'::date))
  • Heap Blocks: exact=4
8. 0.031 0.031 ↓ 1.7 12 1

Bitmap Index Scan on events_colleague_repeating_client_alias_id_date_idx (cost=0.00..4.37 rows=7 width=0) (actual time=0.031..0.031 rows=12 loops=1)

  • Index Cond: ((client_alias_id = 25) AND (event_date >= '2020-09-07'::date) AND (event_date <= '2020-09-13'::date))
9. 0.000 0.002 ↓ 0.0 0 1

ProjectSet (cost=4.29..39.85 rows=100 width=8) (actual time=0.002..0.002 rows=0 loops=1)

10. 0.000 0.002 ↓ 0.0 0 1

Nested Loop (cost=4.29..39.34 rows=1 width=36) (actual time=0.002..0.002 rows=0 loops=1)

11. 0.001 0.002 ↓ 0.0 0 1

Bitmap Heap Scan on events_team_repeating (cost=4.14..13.62 rows=6 width=40) (actual time=0.002..0.002 rows=0 loops=1)

  • Recheck Cond: ((event_date >= '2020-09-07'::date) AND (event_date <= '2020-09-13'::date) AND (team_id IS NOT NULL))
12. 0.001 0.001 ↓ 0.0 0 1

Bitmap Index Scan on events_team_repeating_team_id_date_idx (cost=0.00..4.13 rows=6 width=0) (actual time=0.001..0.001 rows=0 loops=1)

  • Index Cond: ((event_date >= '2020-09-07'::date) AND (event_date <= '2020-09-13'::date))
13. 0.000 0.000 ↓ 0.0 0

Index Only Scan using teams_members_pkey on teams_members (cost=0.15..4.17 rows=1 width=4) (never executed)

  • Index Cond: ((team_id = events_team_repeating.team_id) AND (client_alias_id = 25))
  • Heap Fetches: 0
14. 0.013 0.279 ↑ 1.3 3 1

Sort (cost=13.13..13.14 rows=4 width=402) (actual time=0.278..0.279 rows=3 loops=1)

  • Sort Key: e1.event_id, events_index_tables.event_date, (CASE WHEN ((e1.deleted IS TRUE) OR (events_index_tables.event_date = ANY (e1.ex_dates)) OR (events_index_tables.event_date >= e1.future_deleted_date)) THEN true ELSE false END)
  • Sort Method: quicksort Memory: 26kB
15. 0.008 0.266 ↑ 1.3 3 1

Nested Loop Left Join (cost=8.97..13.09 rows=4 width=402) (actual time=0.260..0.266 rows=3 loops=1)

16. 0.044 0.249 ↑ 1.3 3 1

Hash Right Join (cost=8.97..12.90 rows=4 width=439) (actual time=0.247..0.249 rows=3 loops=1)

  • Hash Cond: ((c.complaint_id = t.complaint_id) AND (c.patient_id = ep.patient_id))
  • Join Filter: (e1.is_repeating_event IS FALSE)
17. 0.012 0.012 ↑ 1.0 96 1

Seq Scan on complaints c (cost=0.00..2.96 rows=96 width=8) (actual time=0.004..0.012 rows=96 loops=1)

18. 0.008 0.193 ↑ 1.3 3 1

Hash (cost=8.91..8.91 rows=4 width=439) (actual time=0.193..0.193 rows=3 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
19. 0.012 0.185 ↑ 1.3 3 1

Hash Right Join (cost=7.88..8.91 rows=4 width=439) (actual time=0.181..0.185 rows=3 loops=1)

  • Hash Cond: (ep.event_id = e1.event_id)
20. 0.006 0.006 ↑ 2.0 1 1

Seq Scan on events_patients ep (cost=0.00..1.02 rows=2 width=122) (actual time=0.006..0.006 rows=1 loops=1)

21. 0.007 0.167 ↑ 1.3 3 1

Hash (cost=7.83..7.83 rows=4 width=317) (actual time=0.167..0.167 rows=3 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
22. 0.007 0.160 ↑ 1.3 3 1

Merge Left Join (cost=7.79..7.83 rows=4 width=317) (actual time=0.157..0.160 rows=3 loops=1)

  • Merge Cond: (e1.event_id = t.event_id)
  • Join Filter: (e1.is_repeating_event IS FALSE)
23. 0.011 0.139 ↑ 1.3 3 1

Sort (cost=6.75..6.75 rows=4 width=309) (actual time=0.139..0.139 rows=3 loops=1)

  • Sort Key: e1.event_id
  • Sort Method: quicksort Memory: 25kB
24. 0.025 0.128 ↑ 1.3 3 1

Hash Join (cost=2.18..6.71 rows=4 width=309) (actual time=0.117..0.128 rows=3 loops=1)

  • Hash Cond: (events_index_tables.event_id = e1.event_id)
  • Join Filter: (NOT CASE WHEN ((e1.deleted IS TRUE) OR (events_index_tables.event_date = ANY (e1.ex_dates)) OR (events_index_tables.event_date >= e1.future_deleted_date)) THEN true ELSE false END)
  • Rows Removed by Join Filter: 4
25. 0.082 0.082 ↑ 10.5 19 1

CTE Scan on events_index_tables (cost=0.00..4.00 rows=200 width=8) (actual time=0.074..0.082 rows=19 loops=1)

26. 0.012 0.021 ↑ 8.0 1 1

Hash (cost=2.08..2.08 rows=8 width=305) (actual time=0.021..0.021 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
27. 0.009 0.009 ↑ 8.0 1 1

Seq Scan on events e1 (cost=0.00..2.08 rows=8 width=305) (actual time=0.009..0.009 rows=1 loops=1)

28. 0.007 0.014 ↑ 2.0 1 1

Sort (cost=1.04..1.04 rows=2 width=12) (actual time=0.014..0.014 rows=1 loops=1)

  • Sort Key: t.event_id
  • Sort Method: quicksort Memory: 25kB
29. 0.007 0.007 ↓ 1.5 3 1

Seq Scan on treatments t (cost=0.00..1.03 rows=2 width=12) (actual time=0.005..0.007 rows=3 loops=1)

  • Filter: (deleted IS FALSE)
30. 0.006 0.009 ↑ 1.0 1 3

Subquery Scan on ept (cost=0.00..0.02 rows=1 width=24) (actual time=0.002..0.003 rows=1 loops=3)

31. 0.003 0.003 ↑ 1.0 1 3

Result (cost=0.00..0.01 rows=1 width=53) (actual time=0.001..0.001 rows=1 loops=3)

Planning time : 1.023 ms
Execution time : 0.658 ms