explain.depesz.com

PostgreSQL's explain analyze made readable

Result: AjH3 : Optimization for: plan #zXm

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.057 0.313 ↑ 1.1 7 1

HashAggregate (cost=111.80..112.04 rows=8 width=345) (actual time=0.303..0.313 rows=7 loops=1)

  • Group Key: e1.event_id, events_index_tables.event_date
2.          

CTE events_index_tables

3. 0.012 0.060 ↑ 10.5 19 1

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

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

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

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

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

6. 0.007 0.030 ↑ 36.8 19 1

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

7. 0.006 0.023 ↑ 1.0 7 1

Bitmap Heap Scan on events_colleague_repeating (cost=4.37..16.71 rows=7 width=33) (actual time=0.022..0.023 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.017 0.017 ↓ 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.017..0.017 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.000 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.002 0.002 ↓ 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.002..0.002 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.012 0.256 ↑ 1.1 7 1

Nested Loop Left Join (cost=8.68..13.62 rows=8 width=406) (actual time=0.213..0.256 rows=7 loops=1)

15. 0.016 0.230 ↑ 1.1 7 1

Hash Left Join (cost=8.68..13.36 rows=8 width=439) (actual time=0.199..0.230 rows=7 loops=1)

  • Hash Cond: ((t.complaint_id = c.complaint_id) AND (ep.patient_id = c.patient_id))
  • Join Filter: (e1.is_repeating_event IS FALSE)
16. 0.021 0.174 ↑ 1.1 7 1

Hash Left Join (cost=4.28..8.92 rows=8 width=439) (actual time=0.146..0.174 rows=7 loops=1)

  • Hash Cond: (e1.event_id = t.event_id)
  • Join Filter: (e1.is_repeating_event IS FALSE)
17. 0.015 0.144 ↑ 1.1 7 1

Hash Left Join (cost=3.23..7.81 rows=8 width=431) (actual time=0.120..0.144 rows=7 loops=1)

  • Hash Cond: (e1.event_id = ep.event_id)
18. 0.022 0.105 ↑ 1.1 7 1

Hash Join (cost=2.18..6.72 rows=8 width=309) (actual time=0.085..0.105 rows=7 loops=1)

  • Hash Cond: (events_index_tables.event_id = e1.event_id)
19. 0.068 0.068 ↑ 10.5 19 1

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

20. 0.004 0.015 ↑ 8.0 1 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
21. 0.011 0.011 ↑ 8.0 1 1

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

22. 0.006 0.024 ↑ 2.0 1 1

Hash (cost=1.02..1.02 rows=2 width=122) (actual time=0.024..0.024 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
23. 0.018 0.018 ↑ 2.0 1 1

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

24. 0.001 0.009 ↓ 0.0 0 1

Hash (cost=1.03..1.03 rows=2 width=12) (actual time=0.009..0.009 rows=0 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
25. 0.008 0.008 ↓ 1.5 3 1

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

  • Filter: (deleted IS FALSE)
26. 0.018 0.040 ↑ 1.0 96 1

Hash (cost=2.96..2.96 rows=96 width=8) (actual time=0.040..0.040 rows=96 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
27. 0.022 0.022 ↑ 1.0 96 1

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

28. 0.007 0.014 ↑ 1.0 1 7

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

29. 0.007 0.007 ↑ 1.0 1 7

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

Planning time : 0.692 ms
Execution time : 0.620 ms