explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3U3o : hmm

Settings
# exclusive inclusive rows x rows loops node
1. 15.409 3,075.976 ↑ 218.7 6 1

EXPLAIN for: SELECT "other_request_views".* FROM "other_request_views" WHERE "other_request_views"."user_id" IN ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, $22, $23, $24, $25, $26, $27, $28, $29, $30, $31, $32, $33, $34, $35, $36, $37) AND (other_request_status in ('New','Escalated','Approved'))Subquery Scan on other_request_views (cost=51,609.35..66,971.48 rows=1,312 width=345) (actual time=3,074.023..3,075.976 rows=6 loops=1)

  • Output: other_request_views.id, other_request_views.other_request_id, other_request_views.other_request_updated_at, other_request_views.other_request_status, other_request_views.other_request_comment, other_request_views.user_id, other_request_views.user_first_name, other_request_views.user_last_name, other_request_views.user_known_as, other_request_views.user_email, other_request_views.user_search, other_request_views.manager_id, other_request_views.manager_first_name, other_request_views.manager_last_name, other_request_views.manager_known_as, other_request_views.manager_email, other_request_views.manager_search, other_request_views.date_from, other_request_views.date_to, other_request_views.days, other_request_views.hours, other_request_views.units, other_request_views.value, other_request_views.dates_requested
  • Filter: (((other_request_views.other_request_status)::text = ANY ('{New,Escalated,Approved}'::text[])) AND (other_request_views.user_id = ANY ('{11782,14779,11783,11784,11785,12650,14034,12648,13273,15741,11790,11792,12288,11795,11796,12995,11797,14382,15465,11800,11801,11802,11803,11806,15760,13272,11941,11811,11813,11814,11816,15645,11817,14794,11819,12315,15271}'::integer[])))
  • Rows Removed by Filter: 99,516
  • Buffers: shared hit=9,015, temp read=12,558 written=12,558
2. 98.639 3,060.567 ↑ 4.7 99,522 1

WindowAgg (cost=51,609.35..60,826.63 rows=472,681 width=345) (actual time=2,320.031..3,060.567 rows=99,522 loops=1)

  • Output: row_number() OVER (?), other_requests.id, other_requests.updated_at, other_requests.status, other_requests.comment, users.id, users.first_name, users.last_name, users.known_as, users.email, (concat(users.first_name, users.last_name, users.known_as, users.email)), managers.id, managers.first_name, managers.last_name, managers.known_as, managers.email, (concat(managers.first_name, managers.last_name, managers.known_as, managers.email)), (min(other_request_details.date_requested)), (max(other_request_details.date_requested)), (sum(CASE WHEN ((leave_types.units)::text = 'Days'::text) THEN other_request_details.units ELSE '0'::numeric END)), (sum(CASE WHEN ((leave_types.units)::text = 'Hours'::text) THEN other_request_details.units ELSE '0'::numeric END)), (sum(CASE WHEN ((leave_types.units)::text = 'Units'::text) THEN other_request_details.units ELSE '0'::numeric END)), (sum(CASE WHEN ((leave_types.units)::text = 'Value'::text) THEN other_request_details.units ELSE '0'::numeric END)), (array_agg(other_request_details.date_requested))
  • Buffers: shared hit=9,015, temp read=12,558 written=12,558
3. 535.220 2,961.928 ↑ 4.7 99,522 1

GroupAggregate (cost=51,609.35..58,699.57 rows=472,681 width=337) (actual time=2,320.023..2,961.928 rows=99,522 loops=1)

  • Output: other_requests.id, users.id, (concat(users.first_name, users.last_name, users.known_as, users.email)), managers.id, (concat(managers.first_name, managers.last_name, managers.known_as, managers.email)), other_requests.updated_at, other_requests.status, other_requests.comment, users.first_name, users.last_name, users.known_as, users.email, managers.first_name, managers.last_name, managers.known_as, managers.email, min(other_request_details.date_requested), max(other_request_details.date_requested), sum(CASE WHEN ((leave_types.units)::text = 'Days'::text) THEN other_request_details.units ELSE '0'::numeric END), sum(CASE WHEN ((leave_types.units)::text = 'Hours'::text) THEN other_request_details.units ELSE '0'::numeric END), sum(CASE WHEN ((leave_types.units)::text = 'Units'::text) THEN other_request_details.units ELSE '0'::numeric END), sum(CASE WHEN ((leave_types.units)::text = 'Value'::text) THEN other_request_details.units ELSE '0'::numeric END), array_agg(other_request_details.date_requested)
  • Group Key: other_requests.id, users.id, (concat(users.first_name, users.last_name, users.known_as, users.email)), managers.id, (concat(managers.first_name, managers.last_name, managers.known_as, managers.email))
  • Buffers: shared hit=9,015, temp read=12,558 written=12,558
4. 1,018.618 2,426.708 ↑ 1.0 466,389 1

Sort (cost=51,609.35..51,845.69 rows=472,681 width=183) (actual time=2,319.991..2,426.708 rows=466,389 loops=1)

  • Output: other_requests.id, users.id, (concat(users.first_name, users.last_name, users.known_as, users.email)), managers.id, (concat(managers.first_name, managers.last_name, managers.known_as, managers.email)), other_requests.updated_at, other_requests.status, other_requests.comment, users.first_name, users.last_name, users.known_as, users.email, managers.first_name, managers.last_name, managers.known_as, managers.email, other_request_details.date_requested, leave_types.units, other_request_details.units
  • Sort Key: other_requests.id, users.id, (concat(users.first_name, users.last_name, users.known_as, users.email)), managers.id, (concat(managers.first_name, managers.last_name, managers.known_as, managers.email))
  • Sort Method: external merge Disk: 100,464kB
  • Buffers: shared hit=9,015, temp read=12,558 written=12,558
5. 785.656 1,408.090 ↑ 1.0 466,389 1

Hash Join (cost=3,561.58..12,694.07 rows=472,681 width=183) (actual time=63.863..1,408.090 rows=466,389 loops=1)

  • Output: other_requests.id, users.id, concat(users.first_name, users.last_name, users.known_as, users.email), managers.id, concat(managers.first_name, managers.last_name, managers.known_as, managers.email), other_requests.updated_at, other_requests.status, other_requests.comment, users.first_name, users.last_name, users.known_as, users.email, managers.first_name, managers.last_name, managers.known_as, managers.email, other_request_details.date_requested, leave_types.units, other_request_details.units
  • Inner Unique: true
  • Hash Cond: (other_requests.user_id = users.id)
  • Buffers: shared hit=9,014
6. 146.378 613.094 ↑ 1.0 466,389 1

Hash Join (cost=2,822.17..11,233.65 rows=472,714 width=83) (actual time=54.477..613.094 rows=466,389 loops=1)

  • Output: other_requests.id, other_requests.updated_at, other_requests.status, other_requests.comment, other_requests.user_id, managers.id, managers.first_name, managers.last_name, managers.known_as, managers.email, other_request_details.date_requested, other_request_details.units, leave_types.units
  • Inner Unique: true
  • Hash Cond: (other_requests.manager_id = managers.id)
  • Buffers: shared hit=8,336
7. 177.655 458.010 ↑ 1.0 467,240 1

Hash Join (cost=2,082.77..10,245.77 rows=472,983 width=47) (actual time=45.733..458.010 rows=467,240 loops=1)

  • Output: other_requests.id, other_requests.updated_at, other_requests.status, other_requests.comment, other_requests.user_id, other_requests.manager_id, other_request_details.date_requested, other_request_details.units, leave_types.units
  • Inner Unique: true
  • Hash Cond: (other_request_details.other_request_id = other_requests.id)
  • Buffers: shared hit=7,658
8. 172.744 235.964 ↑ 1.0 467,240 1

Hash Join (cost=84.26..7,998.93 rows=472,983 width=18) (actual time=1.133..235.964 rows=467,240 loops=1)

  • Output: other_request_details.date_requested, other_request_details.units, other_request_details.other_request_id, leave_types.units
  • Inner Unique: true
  • Hash Cond: (other_request_details.leave_type_id = leave_types.id)
  • Buffers: shared hit=6,311
9. 62.114 62.114 ↑ 1.0 467,462 1

Seq Scan on public.other_request_details (cost=0.00..7,665.70 rows=473,235 width=17) (actual time=0.009..62.114 rows=467,462 loops=1)

  • Output: other_request_details.id, other_request_details.account_id, other_request_details.leave_type_id, other_request_details.status, other_request_details.date_requested, other_request_details.created_at, other_request_details.updated_at, other_request_details.other_request_id, other_request_details.units, other_request_details.comment, other_request_details.paid_date, other_request_details.work_area_code, other_request_details.job_code, other_request_details.roster_date, other_request_details.roster_units, other_request_details.set_by_goroster
  • Buffers: shared hit=6,246
10. 0.479 1.106 ↑ 1.0 2,955 1

Hash (cost=73.89..73.89 rows=2,963 width=9) (actual time=1.106..1.106 rows=2,955 loops=1)

  • Output: leave_types.units, leave_types.id
  • Buckets: 4,096 Batches: 1 Memory Usage: 159kB
  • Buffers: shared hit=65
11. 0.627 0.627 ↑ 1.0 2,955 1

Seq Scan on public.leave_types (cost=0.00..73.89 rows=2,963 width=9) (actual time=0.006..0.627 rows=2,955 loops=1)

  • Output: leave_types.units, leave_types.id
  • Buffers: shared hit=65
12. 22.456 44.391 ↑ 1.0 100,169 1

Hash (cost=1,647.70..1,647.70 rows=100,232 width=33) (actual time=44.391..44.391 rows=100,169 loops=1)

  • Output: other_requests.id, other_requests.updated_at, other_requests.status, other_requests.comment, other_requests.user_id, other_requests.manager_id
  • Buckets: 131,072 Batches: 1 Memory Usage: 8,050kB
  • Buffers: shared hit=1,347
13. 21.935 21.935 ↑ 1.0 100,169 1

Seq Scan on public.other_requests (cost=0.00..1,647.70 rows=100,232 width=33) (actual time=0.007..21.935 rows=100,169 loops=1)

  • Output: other_requests.id, other_requests.updated_at, other_requests.status, other_requests.comment, other_requests.user_id, other_requests.manager_id
  • Buffers: shared hit=1,347
14. 2.334 8.706 ↑ 1.0 9,447 1

Hash (cost=706.34..706.34 rows=9,447 width=40) (actual time=8.706..8.706 rows=9,447 loops=1)

  • Output: managers.id, managers.first_name, managers.last_name, managers.known_as, managers.email
  • Buckets: 16,384 Batches: 1 Memory Usage: 800kB
  • Buffers: shared hit=678
15. 6.372 6.372 ↑ 1.0 9,447 1

Seq Scan on public.users managers (cost=0.00..706.34 rows=9,447 width=40) (actual time=0.007..6.372 rows=9,447 loops=1)

  • Output: managers.id, managers.first_name, managers.last_name, managers.known_as, managers.email
  • Buffers: shared hit=678
16. 2.248 9.340 ↑ 1.0 9,447 1

Hash (cost=706.34..706.34 rows=9,447 width=40) (actual time=9.340..9.340 rows=9,447 loops=1)

  • Output: users.id, users.first_name, users.last_name, users.known_as, users.email
  • Buckets: 16,384 Batches: 1 Memory Usage: 800kB
  • Buffers: shared hit=678
17. 7.092 7.092 ↑ 1.0 9,447 1

Seq Scan on public.users (cost=0.00..706.34 rows=9,447 width=40) (actual time=0.010..7.092 rows=9,447 loops=1)

  • Output: users.id, users.first_name, users.last_name, users.known_as, users.email
  • Buffers: shared hit=678