explain.depesz.com

PostgreSQL's explain analyze made readable

Result: i9vZ : Optimization for: Optimization for: plan #dPhA; plan #lML6

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.012 0.396 ↑ 2.3 7 1

Sort (cost=132.39..132.43 rows=16 width=1,258) (actual time=0.395..0.396 rows=7 loops=1)

  • Sort Key: (CASE WHEN ((details.benefit_status)::text = 'IN_PROGRESS'::text) THEN 1 WHEN ((details.benefit_status)::text = 'ACTIVE'::text) THEN 2 WHEN ((details.benefit_status)::text = 'INACTIVE'::text) THEN 3 ELSE NULL::integer END), details.benefit_priority, details.benefit_start_date, details.order_date
  • Sort Method: quicksort Memory: 26kB
2. 0.004 0.384 ↑ 2.3 7 1

Subquery Scan on details (cost=131.63..132.07 rows=16 width=1,258) (actual time=0.378..0.384 rows=7 loops=1)

3. 0.017 0.380 ↑ 2.3 7 1

HashAggregate (cost=131.63..131.79 rows=16 width=1,254) (actual time=0.376..0.380 rows=7 loops=1)

  • Group Key: benefit_order.id, benefit_order.employee_id, benefit.name, benefit_type.name, benefit_type.beneficiary_type, benefit_type.priority, ('IN_PROGRESS'::character varying), benefit_order.order_date, (CASE WHEN (bbo.benefit_order_id IS NOT NULL) THEN bbo.beneficiary_first_name WHEN (employee_benefit.id IS NOT NULL) THEN employee_benefit.beneficiary_first_name ELSE NULL::character varying END), (CASE WHEN (bbo.benefit_order_id IS NOT NULL) THEN bbo.beneficiary_last_name WHEN (employee_benefit.id IS NOT NULL) THEN employee_benefit.beneficiary_last_name ELSE NULL::character varying END), (CASE WHEN (bbo.benefit_order_id IS NOT NULL) THEN bbo.benefit_start_date WHEN (rebo.benefit_order_id IS NOT NULL) THEN rebo.benefit_start_date ELSE NULL::timestamp without time zone END), (CASE WHEN (bbo.benefit_order_id IS NOT NULL) THEN bbo.benefit_end_date WHEN (rebo.benefit_order_id IS NOT NULL) THEN rebo.benefit_end_date ELSE NULL::timestamp without time zone END), (max(benefit_order_consent_given_by_employee.given_at))
4. 0.001 0.363 ↑ 2.3 7 1

Append (cost=64.76..131.11 rows=16 width=1,254) (actual time=0.190..0.363 rows=7 loops=1)

5. 0.009 0.193 ↑ 5.0 3 1

Hash Join (cost=64.76..76.69 rows=15 width=1,254) (actual time=0.190..0.193 rows=3 loops=1)

  • Hash Cond: (benefit_type.id = benefit.benefit_type_id)
6. 0.006 0.006 ↑ 32.5 4 1

Seq Scan on benefit_type (cost=0.00..11.30 rows=130 width=582) (actual time=0.006..0.006 rows=4 loops=1)

7. 0.002 0.178 ↑ 5.0 3 1

Hash (cost=64.57..64.57 rows=15 width=2,712) (actual time=0.178..0.178 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
8. 0.006 0.176 ↑ 5.0 3 1

Hash Join (cost=53.87..64.57 rows=15 width=2,712) (actual time=0.173..0.176 rows=3 loops=1)

  • Hash Cond: (benefit.id = bbo.benefit_id)
9. 0.003 0.003 ↑ 10.0 4 1

Seq Scan on benefit (cost=0.00..10.40 rows=40 width=524) (actual time=0.002..0.003 rows=4 loops=1)

10. 0.002 0.167 ↑ 5.0 3 1

Hash (cost=53.68..53.68 rows=15 width=2,196) (actual time=0.167..0.167 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
11. 0.006 0.165 ↑ 5.0 3 1

Hash Join (cost=43.09..53.68 rows=15 width=2,196) (actual time=0.162..0.165 rows=3 loops=1)

  • Hash Cond: (bbo.benefit_order_id = benefit_order.id)
12. 0.006 0.006 ↑ 5.7 7 1

Seq Scan on buy_benefit_order bbo (cost=0.00..10.40 rows=40 width=1,068) (actual time=0.004..0.006 rows=7 loops=1)

13. 0.004 0.153 ↑ 1.3 3 1

Hash (cost=43.04..43.04 rows=4 width=1,128) (actual time=0.153..0.153 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
14. 0.004 0.149 ↑ 1.3 3 1

Merge Left Join (cost=42.99..43.04 rows=4 width=1,128) (actual time=0.147..0.149 rows=3 loops=1)

  • Merge Cond: (benefit_order.id = rebo.benefit_order_id)
15. 0.007 0.114 ↑ 1.3 3 1

Sort (cost=31.41..31.42 rows=4 width=48) (actual time=0.114..0.114 rows=3 loops=1)

  • Sort Key: benefit_order.id
  • Sort Method: quicksort Memory: 25kB
16. 0.014 0.107 ↑ 1.3 3 1

Hash Right Join (cost=30.68..31.37 rows=4 width=48) (actual time=0.103..0.107 rows=3 loops=1)

  • Hash Cond: (benefit_order_1.id = benefit_order.id)
17. 0.010 0.081 ↑ 3.3 9 1

HashAggregate (cost=29.46..29.76 rows=30 width=24) (actual time=0.079..0.081 rows=9 loops=1)

  • Group Key: benefit_order_1.id
18. 0.015 0.071 ↑ 1.6 19 1

HashAggregate (cost=28.71..29.01 rows=30 width=24) (actual time=0.067..0.071 rows=19 loops=1)

  • Group Key: benefit_order_1.id, benefit_order_consent_given_by_employee.given_at
19. 0.003 0.056 ↑ 1.2 25 1

Append (cost=1.26..28.56 rows=30 width=24) (actual time=0.020..0.056 rows=25 loops=1)

20. 0.018 0.031 ↓ 1.1 21 1

Hash Join (cost=1.26..16.33 rows=20 width=24) (actual time=0.019..0.031 rows=21 loops=1)

  • Hash Cond: (benefit_order_consent_given_by_employee.benefit_order_id = benefit_order_1.id)
21. 0.005 0.005 ↑ 16.7 24 1

Seq Scan on benefit_order_consent_given_by_employee (cost=0.00..14.00 rows=400 width=24) (actual time=0.003..0.005 rows=24 loops=1)

22. 0.004 0.008 ↑ 1.1 9 1

Hash (cost=1.14..1.14 rows=10 width=16) (actual time=0.008..0.008 rows=9 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
23. 0.004 0.004 ↑ 1.1 9 1

Seq Scan on benefit_order benefit_order_1 (cost=0.00..1.14 rows=10 width=16) (actual time=0.002..0.004 rows=9 loops=1)

  • Filter: ((status)::text <> 'WAITING_FOR_REQUIRED_ACTIONS'::text)
  • Rows Removed by Filter: 2
24. 0.010 0.022 ↑ 2.5 4 1

Hash Join (cost=1.26..11.78 rows=10 width=24) (actual time=0.020..0.022 rows=4 loops=1)

  • Hash Cond: (benefit_order_consent_given_by_family_member_or_friend.benefit_order_id = benefit_order_2.id)
25. 0.003 0.003 ↑ 10.0 4 1

Seq Scan on benefit_order_consent_given_by_family_member_or_friend (cost=0.00..10.40 rows=40 width=24) (actual time=0.002..0.003 rows=4 loops=1)

26. 0.005 0.009 ↑ 1.1 9 1

Hash (cost=1.14..1.14 rows=10 width=16) (actual time=0.009..0.009 rows=9 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
27. 0.004 0.004 ↑ 1.1 9 1

Seq Scan on benefit_order benefit_order_2 (cost=0.00..1.14 rows=10 width=16) (actual time=0.003..0.004 rows=9 loops=1)

  • Filter: ((status)::text <> 'WAITING_FOR_REQUIRED_ACTIONS'::text)
  • Rows Removed by Filter: 2
28. 0.004 0.012 ↑ 1.3 3 1

Hash (cost=1.17..1.17 rows=4 width=40) (actual time=0.012..0.012 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
29. 0.008 0.008 ↑ 1.3 3 1

Seq Scan on benefit_order (cost=0.00..1.17 rows=4 width=40) (actual time=0.006..0.008 rows=3 loops=1)

  • Filter: ((status)::text = ANY ('{WAITING_FOR_REQUIRED_ACTIONS,READY_TO_PROCESS,SENT_TO_BENEFIT_PROVIDER_PLATFORM,RECEIVED_ON_BENEFIT_PROVIDER_PLATFORM}'::text[]))
  • Rows Removed by Filter: 8
30. 0.007 0.031 ↑ 1.0 4 1

Sort (cost=11.58..11.59 rows=4 width=1,080) (actual time=0.031..0.031 rows=4 loops=1)

  • Sort Key: rebo.benefit_order_id
  • Sort Method: quicksort Memory: 25kB
31. 0.016 0.024 ↑ 1.0 4 1

Hash Right Join (cost=1.09..11.54 rows=4 width=1,080) (actual time=0.019..0.024 rows=4 loops=1)

  • Hash Cond: (employee_benefit.id = rebo.employee_benefit_id)
32. 0.002 0.002 ↑ 7.5 4 1

Seq Scan on employee_benefit (cost=0.00..10.30 rows=30 width=1,048) (actual time=0.002..0.002 rows=4 loops=1)

33. 0.002 0.006 ↑ 1.0 4 1

Hash (cost=1.04..1.04 rows=4 width=48) (actual time=0.006..0.006 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
34. 0.004 0.004 ↑ 1.0 4 1

Seq Scan on reactivate_employee_benefit_order rebo (cost=0.00..1.04 rows=4 width=48) (actual time=0.003..0.004 rows=4 loops=1)

35. 0.007 0.169 ↓ 4.0 4 1

Nested Loop Left Join (cost=41.57..54.18 rows=1 width=2,308) (actual time=0.131..0.169 rows=4 loops=1)

  • Join Filter: (benefit_order_5.id = result_with_row_number.benefit_order_id)
  • Rows Removed by Join Filter: 10
36. 0.005 0.098 ↓ 4.0 4 1

Nested Loop (cost=12.11..23.74 rows=1 width=2,316) (actual time=0.067..0.098 rows=4 loops=1)

37. 0.003 0.085 ↓ 4.0 4 1

Nested Loop (cost=11.97..22.36 rows=1 width=1,742) (actual time=0.061..0.085 rows=4 loops=1)

38. 0.003 0.074 ↓ 4.0 4 1

Nested Loop (cost=11.83..20.52 rows=1 width=1,226) (actual time=0.055..0.074 rows=4 loops=1)

39. 0.002 0.063 ↓ 4.0 4 1

Subquery Scan on result_with_row_number (cost=11.69..11.85 rows=1 width=40) (actual time=0.051..0.063 rows=4 loops=1)

  • Filter: (result_with_row_number.row_number = 1)
  • Rows Removed by Filter: 4
40. 0.014 0.061 ↓ 1.6 8 1

WindowAgg (cost=11.69..11.79 rows=5 width=48) (actual time=0.051..0.061 rows=8 loops=1)

41. 0.007 0.047 ↓ 1.6 8 1

Sort (cost=11.69..11.70 rows=5 width=40) (actual time=0.046..0.047 rows=8 loops=1)

  • Sort Key: buy_benefit_order.employee_benefit_id, benefit_order_3.order_date DESC
  • Sort Method: quicksort Memory: 25kB
42. 0.002 0.040 ↓ 1.6 8 1

Result (cost=0.14..11.63 rows=5 width=40) (actual time=0.010..0.040 rows=8 loops=1)

43. 0.001 0.038 ↓ 1.6 8 1

Append (cost=0.14..11.58 rows=5 width=40) (actual time=0.010..0.038 rows=8 loops=1)

44. 0.002 0.023 ↑ 1.0 4 1

Nested Loop (cost=0.14..9.30 rows=4 width=40) (actual time=0.010..0.023 rows=4 loops=1)

45. 0.005 0.005 ↓ 8.0 8 1

Seq Scan on benefit_order benefit_order_3 (cost=0.00..1.14 rows=1 width=24) (actual time=0.003..0.005 rows=8 loops=1)

  • Filter: ((status)::text = 'COMPLETED'::text)
  • Rows Removed by Filter: 3
46. 0.016 0.016 ↓ 0.0 0 8

Index Scan using idx__buy_benefit_order__benefit_order_id on buy_benefit_order (cost=0.14..8.16 rows=1 width=32) (actual time=0.001..0.002 rows=0 loops=8)

  • Index Cond: (benefit_order_id = benefit_order_3.id)
47. 0.004 0.014 ↓ 4.0 4 1

Hash Join (cost=1.15..2.20 rows=1 width=40) (actual time=0.012..0.014 rows=4 loops=1)

  • Hash Cond: (reactivate_employee_benefit_order.benefit_order_id = benefit_order_4.id)
48. 0.003 0.003 ↑ 1.0 4 1

Seq Scan on reactivate_employee_benefit_order (cost=0.00..1.04 rows=4 width=32) (actual time=0.002..0.003 rows=4 loops=1)

49. 0.002 0.007 ↓ 8.0 8 1

Hash (cost=1.14..1.14 rows=1 width=24) (actual time=0.007..0.007 rows=8 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
50. 0.005 0.005 ↓ 8.0 8 1

Seq Scan on benefit_order benefit_order_4 (cost=0.00..1.14 rows=1 width=24) (actual time=0.002..0.005 rows=8 loops=1)

  • Filter: ((status)::text = 'COMPLETED'::text)
  • Rows Removed by Filter: 3
51. 0.008 0.008 ↑ 1.0 1 4

Index Scan using employee_benefit_pkey on employee_benefit eb (cost=0.14..8.15 rows=1 width=1,202) (actual time=0.002..0.002 rows=1 loops=4)

  • Index Cond: (id = result_with_row_number.employee_benefit_id)
52. 0.008 0.008 ↑ 1.0 1 4

Index Scan using benefit_pkey on benefit benefit_1 (cost=0.14..1.76 rows=1 width=524) (actual time=0.002..0.002 rows=1 loops=4)

  • Index Cond: (id = eb.benefit_id)
53. 0.008 0.008 ↑ 1.0 1 4

Index Scan using benefit_type_pkey on benefit_type benefit_type_1 (cost=0.14..1.36 rows=1 width=582) (actual time=0.002..0.002 rows=1 loops=4)

  • Index Cond: (id = benefit_1.benefit_type_id)
54. 0.009 0.064 ↑ 7.5 4 4

HashAggregate (cost=29.46..29.76 rows=30 width=24) (actual time=0.016..0.016 rows=4 loops=4)

  • Group Key: benefit_order_5.id
55. 0.014 0.055 ↑ 1.6 19 1

HashAggregate (cost=28.71..29.01 rows=30 width=24) (actual time=0.050..0.055 rows=19 loops=1)

  • Group Key: benefit_order_5.id, benefit_order_consent_given_by_employee_1.given_at
56. 0.003 0.041 ↑ 1.2 25 1

Append (cost=1.26..28.56 rows=30 width=24) (actual time=0.015..0.041 rows=25 loops=1)

57. 0.015 0.026 ↓ 1.1 21 1

Hash Join (cost=1.26..16.33 rows=20 width=24) (actual time=0.015..0.026 rows=21 loops=1)

  • Hash Cond: (benefit_order_consent_given_by_employee_1.benefit_order_id = benefit_order_5.id)
58. 0.005 0.005 ↑ 16.7 24 1

Seq Scan on benefit_order_consent_given_by_employee benefit_order_consent_given_by_employee_1 (cost=0.00..14.00 rows=400 width=24) (actual time=0.002..0.005 rows=24 loops=1)

59. 0.002 0.006 ↑ 1.1 9 1

Hash (cost=1.14..1.14 rows=10 width=16) (actual time=0.006..0.006 rows=9 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
60. 0.004 0.004 ↑ 1.1 9 1

Seq Scan on benefit_order benefit_order_5 (cost=0.00..1.14 rows=10 width=16) (actual time=0.002..0.004 rows=9 loops=1)

  • Filter: ((status)::text <> 'WAITING_FOR_REQUIRED_ACTIONS'::text)
  • Rows Removed by Filter: 2
61. 0.003 0.012 ↑ 2.5 4 1

Hash Join (cost=1.26..11.78 rows=10 width=24) (actual time=0.011..0.012 rows=4 loops=1)

  • Hash Cond: (benefit_order_consent_given_by_family_member_or_friend_1.benefit_order_id = benefit_order_6.id)
62. 0.003 0.003 ↑ 10.0 4 1

Seq Scan on benefit_order_consent_given_by_family_member_or_friend benefit_order_consent_given_by_family_member_or_friend_1 (cost=0.00..10.40 rows=40 width=24) (actual time=0.002..0.003 rows=4 loops=1)

63. 0.002 0.006 ↑ 1.1 9 1

Hash (cost=1.14..1.14 rows=10 width=16) (actual time=0.006..0.006 rows=9 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
64. 0.004 0.004 ↑ 1.1 9 1

Seq Scan on benefit_order benefit_order_6 (cost=0.00..1.14 rows=10 width=16) (actual time=0.002..0.004 rows=9 loops=1)

  • Filter: ((status)::text <> 'WAITING_FOR_REQUIRED_ACTIONS'::text)
  • Rows Removed by Filter: 2
Planning time : 2.157 ms
Execution time : 0.657 ms