explain.depesz.com

PostgreSQL's explain analyze made readable

Result: HTM

Settings
# exclusive inclusive rows x rows loops node
1. 17.249 359.999 ↓ 120.2 3,004 1

HashAggregate (cost=759.51..760.01 rows=25 width=8) (actual time=354.971..359.999 rows=3,004 loops=1)

  • Group Key: visits.id
  • Filter: ((array_agg(skills.id) <@ '{22,23,24,25,26,27,28,29,30,31,32,39,40,41,42,43,44,45,46,55,56,57,63,64,65,66,67,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,94,95,96,97,33,34,35}'::integer[]) OR (array_agg(skills.id) = '{NULL}'::integer[]))
  • Rows Removed by Filter: 1023
2. 10.688 342.750 ↓ 1,179.7 29,492 1

Nested Loop Left Join (cost=74.32..759.32 rows=25 width=8) (actual time=3.708..342.750 rows=29,492 loops=1)

3. 6.393 302.570 ↓ 1,179.7 29,492 1

Nested Loop Left Join (cost=74.18..755.07 rows=25 width=8) (actual time=3.699..302.570 rows=29,492 loops=1)

4. 0.560 280.069 ↓ 1,342.3 4,027 1

Nested Loop (cost=73.75..733.85 rows=3 width=4) (actual time=3.689..280.069 rows=4,027 loops=1)

5. 0.009 0.009 ↑ 1.0 1 1

Seq Scan on branches (cost=0.00..1.06 rows=1 width=4) (actual time=0.007..0.009 rows=1 loops=1)

  • Filter: (id = 1)
  • Rows Removed by Filter: 4
6. 2.523 279.500 ↓ 1,342.3 4,027 1

Nested Loop (cost=73.75..732.76 rows=3 width=8) (actual time=3.679..279.500 rows=4,027 loops=1)

7. 0.000 168.041 ↓ 864.6 6,052 1

Nested Loop (cost=73.33..713.93 rows=7 width=12) (actual time=3.345..168.041 rows=6,052 loops=1)

  • Join Filter: (clients_visits_join.id = branch_associations.resource_id)
8. 0.212 129.258 ↓ 1,338.7 20,080 1

Nested Loop (cost=73.05..708.97 rows=15 width=16) (actual time=3.243..129.258 rows=20,080 loops=1)

9. 13.652 88.886 ↓ 542.7 20,080 1

Nested Loop (cost=72.76..695.73 rows=37 width=12) (actual time=3.237..88.886 rows=20,080 loops=1)

10. 5.114 35.074 ↓ 514.9 20,080 1

Nested Loop (cost=72.48..678.10 rows=39 width=4) (actual time=3.232..35.074 rows=20,080 loops=1)

11. 2.985 6.160 ↓ 198.3 2,975 1

Bitmap Heap Scan on people (cost=72.19..334.96 rows=15 width=4) (actual time=3.219..6.160 rows=2,975 loops=1)

  • Recheck Cond: ((type)::text = 'Client'::text)
  • Filter: ((deleted_at IS NULL) AND (deleted_at IS NULL) AND ((profile -> 'public_transport'::text) = 'true'::text))
  • Rows Removed by Filter: 212
  • Heap Blocks: exact=207
12. 3.175 3.175 ↑ 1.0 3,187 1

Bitmap Index Scan on index_people_on_type (cost=0.00..72.19 rows=3,187 width=0) (actual time=3.175..3.175 rows=3,187 loops=1)

  • Index Cond: ((type)::text = 'Client'::text)
13. 23.800 23.800 ↑ 1.0 7 2,975

Index Scan using index_vouchers_on_client_id on vouchers (cost=0.29..22.81 rows=7 width=8) (actual time=0.002..0.008 rows=7 loops=2,975)

  • Index Cond: (client_id = people.id)
  • Filter: (deleted_at IS NULL)
  • Rows Removed by Filter: 0
14. 40.160 40.160 ↑ 1.0 1 20,080

Index Scan using vouchers_pkey on vouchers vouchers_visits_join (cost=0.29..0.44 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=20,080)

  • Index Cond: (id = vouchers.id)
  • Filter: (deleted_at IS NULL)
15. 40.160 40.160 ↑ 1.0 1 20,080

Index Scan using people_pkey on people clients_visits_join (cost=0.28..0.35 rows=1 width=4) (actual time=0.001..0.002 rows=1 loops=20,080)

  • Index Cond: (id = vouchers_visits_join.client_id)
  • Filter: ((deleted_at IS NULL) AND ((type)::text = 'Client'::text))
16. 40.160 40.160 ↓ 0.0 0 20,080

Index Scan using index_branch_associations_on_resource_id on branch_associations (cost=0.28..0.32 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=20,080)

  • Index Cond: (resource_id = vouchers_visits_join.client_id)
  • Filter: (branch_id = 1)
  • Rows Removed by Filter: 1
17. 108.936 108.936 ↑ 1.0 1 6,052

Index Scan using index_visits_on_voucher_id on visits (cost=0.42..2.68 rows=1 width=8) (actual time=0.016..0.018 rows=1 loops=6,052)

  • Index Cond: (voucher_id = vouchers.id)
  • Filter: ((start_from <= '2019-01-20 22:59:59.999999'::timestamp without time zone) AND ((state)::text = 'released'::text))
  • Rows Removed by Filter: 30
18. 16.108 16.108 ↑ 1.6 7 4,027

Index Scan using index_visit_skills_on_visit_id on visit_skills (cost=0.43..6.96 rows=11 width=8) (actual time=0.003..0.004 rows=7 loops=4,027)

  • Index Cond: (visit_id = visits.id)
19. 29.492 29.492 ↑ 1.0 1 29,492

Index Only Scan using skills_pkey on skills (cost=0.14..0.16 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=29,492)

  • Index Cond: (id = visit_skills.skill_id)
  • Heap Fetches: 29306