explain.depesz.com

PostgreSQL's explain analyze made readable

Result: HmpP

Settings
# exclusive inclusive rows x rows loops node
1. 71.088 401,466.512 ↓ 22,593.0 22,593 1

Sort (cost=50,849.71..50,849.72 rows=1 width=2,016) (actual time=401,441.595..401,466.512 rows=22,593 loops=1)

  • Sort Key: final.sname
  • Sort Method: external merge Disk: 6024kB
2.          

CTE t

3. 57.283 401,227.175 ↓ 22,593.0 22,593 1

GroupAggregate (cost=50,848.13..50,849.57 rows=1 width=541) (actual time=401,010.104..401,227.175 rows=22,593 loops=1)

  • Group Key: io.title, (date(io.created_date)), ((eu.user_profile ->> 'field_pay_pref_payment_addr_street'::text)), ((c.invoice_level_question ->> 'field_psit_kr_tic_cpny_nm'::text)), ((c.invoice_level_question ->> 'field_psit_kr_tic_rep_nm'::text)), eu.client_type, ((o.data ->> 'order_owner'::text)), o.client_invoice_frequency, ((eu.user_profile ->> 'field_client_profile_pri_email'::text)), ((eu.user_profile ->> 'field_pay_pref_payment_mail'::text)), (date_part('day'::text, io.created_date)), ((c.invoice_level_question ->> 'field_psit_kr_tic_reg_brn'::text)), ss.name, s.name, po.actual_amount, pia.matched_amount, po1.actual_amount, po2.actual_amount, m1.name, m2.name, note.title, m3.name
4. 410.408 401,105.493 ↓ 34,875.0 34,875 1

Sort (cost=50,848.13..50,848.13 rows=1 width=1,308) (actual time=401,010.049..401,105.493 rows=34,875 loops=1)

  • Sort Key: io.title, (date(io.created_date)), ((eu.user_profile ->> 'field_pay_pref_payment_addr_street'::text)), ((c.invoice_level_question ->> 'field_psit_kr_tic_cpny_nm'::text)), ((c.invoice_level_question ->> 'field_psit_kr_tic_rep_nm'::text)), eu.client_type, ((o.data ->> 'order_owner'::text)), o.client_invoice_frequency, ((eu.user_profile ->> 'field_client_profile_pri_email'::text)), ((eu.user_profile ->> 'field_pay_pref_payment_mail'::text)), (date_part('day'::text, io.created_date)), ((c.invoice_level_question ->> 'field_psit_kr_tic_reg_brn'::text)), ss.name, s.name, po.actual_amount, pia.matched_amount, po1.actual_amount, po2.actual_amount, m1.name, m2.name, note.title, m3.name
  • Sort Method: external merge Disk: 40520kB
5. 221,158.679 400,695.085 ↓ 34,875.0 34,875 1

Nested Loop Anti Join (cost=1,030.34..50,848.12 rows=1 width=1,308) (actual time=25.813..400,695.085 rows=34,875 loops=1)

  • Join Filter: (io.title = (vi.unique_identifier)::text)
  • Rows Removed by Join Filter: 611655302
6. 44.807 6,931.478 ↓ 34,912.0 34,912 1

Nested Loop Left Join (cost=1,030.34..49,139.89 rows=1 width=1,376) (actual time=3.470..6,931.478 rows=34,912 loops=1)

7. 60.732 6,851.759 ↓ 34,912.0 34,912 1

Nested Loop Left Join (cost=1,029.92..49,139.08 rows=1 width=1,361) (actual time=3.463..6,851.759 rows=34,912 loops=1)

  • Join Filter: (pia.payment_credit_note_type = 'credit_note'::payment_credit_note_type)
  • Rows Removed by Join Filter: 5
8. 52.625 6,721.203 ↓ 34,912.0 34,912 1

Nested Loop Left Join (cost=1,029.63..49,138.74 rows=1 width=1,353) (actual time=3.452..6,721.203 rows=34,912 loops=1)

9. 79.476 6,563.842 ↓ 34,912.0 34,912 1

Nested Loop Left Join (cost=1,029.21..49,138.29 rows=1 width=1,336) (actual time=3.437..6,563.842 rows=34,912 loops=1)

  • Join Filter: (pia.payment_credit_note_type = 'payment'::payment_credit_note_type)
  • Rows Removed by Join Filter: 3425
10. 51.334 6,309.806 ↓ 34,912.0 34,912 1

Nested Loop Left Join (cost=1,028.78..49,137.55 rows=1 width=1,332) (actual time=3.415..6,309.806 rows=34,912 loops=1)

11. 55.376 6,188.648 ↓ 34,912.0 34,912 1

Nested Loop Left Join (cost=1,028.36..49,136.98 rows=1 width=1,324) (actual time=3.404..6,188.648 rows=34,912 loops=1)

12. 51.263 5,923.800 ↓ 34,912.0 34,912 1

Nested Loop Left Join (cost=1,027.93..49,136.40 rows=1 width=1,316) (actual time=3.379..5,923.800 rows=34,912 loops=1)

13. 66.666 5,690.995 ↓ 30,257.0 30,257 1

Nested Loop (cost=1,027.50..49,135.54 rows=1 width=1,304) (actual time=3.351..5,690.995 rows=30,257 loops=1)

14. 27.495 5,442.787 ↓ 30,257.0 30,257 1

Nested Loop (cost=1,027.08..49,135.02 rows=1 width=1,300) (actual time=3.327..5,442.787 rows=30,257 loops=1)

15. 42.947 5,324.521 ↓ 30,257.0 30,257 1

Nested Loop (cost=1,026.94..49,126.86 rows=1 width=1,304) (actual time=3.314..5,324.521 rows=30,257 loops=1)

16. 59.544 5,221.060 ↓ 30,257.0 30,257 1

Nested Loop (cost=1,026.80..49,126.60 rows=1 width=1,288) (actual time=3.302..5,221.060 rows=30,257 loops=1)

17. 46.467 5,101.002 ↓ 30,257.0 30,257 1

Nested Loop Left Join (cost=1,026.66..49,126.43 rows=1 width=1,256) (actual time=3.290..5,101.002 rows=30,257 loops=1)

18. 54.111 4,661.194 ↓ 30,257.0 30,257 1

Nested Loop (cost=1,026.24..49,122.62 rows=1 width=372) (actual time=3.262..4,661.194 rows=30,257 loops=1)

19. 21.196 3,796.035 ↓ 28,966.0 28,966 1

Gather (cost=1,025.81..49,120.68 rows=1 width=224) (actual time=3.246..3,796.035 rows=28,966 loops=1)

  • Workers Planned: 4
  • Workers Launched: 0
20. 187.198 3,774.839 ↓ 28,966.0 28,966 1

Nested Loop (cost=25.81..48,120.58 rows=1 width=224) (actual time=0.573..3,774.839 rows=28,966 loops=1)

21. 185.829 3,587.636 ↓ 29,084.0 29,084 1

Nested Loop (cost=25.39..48,119.80 rows=1 width=218) (actual time=0.555..3,587.636 rows=29,084 loops=1)

22. 1,138.304 3,401.802 ↓ 29,084.0 29,084 1

Nested Loop (cost=24.96..48,119.24 rows=1 width=210) (actual time=0.537..3,401.802 rows=29,084 loops=1)

23. 880.663 2,263.493 ↓ 13,885.1 194,392 1

Hash Join (cost=24.53..48,111.32 rows=14 width=186) (actual time=0.112..2,263.493 rows=194,392 loops=1)

  • Hash Cond: ((o.invoice_type)::integer = m2.id)
24. 1,382.771 1,382.771 ↓ 4.0 1,862,498 1

Parallel Seq Scan on orders o (cost=0.00..46,764.38 rows=459,947 width=170) (actual time=0.008..1,382.771 rows=1,862,498 loops=1)

  • Filter: active
  • Rows Removed by Filter: 49019
25. 0.007 0.059 ↑ 1.0 3 1

Hash (cost=24.50..24.50 rows=3 width=25) (actual time=0.059..0.059 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
26. 0.052 0.052 ↑ 1.0 3 1

Index Scan using master_pkey on master m2 (cost=0.42..24.50 rows=3 width=25) (actual time=0.039..0.052 rows=3 loops=1)

  • Index Cond: (id = ANY ('{1277,1274,1289}'::integer[]))
  • Filter: active
27. 0.005 0.005 ↓ 0.0 0 194,392

Index Scan using invoice_pkey on invoice io (cost=0.42..0.57 rows=1 width=24) (actual time=0.005..0.005 rows=0 loops=194,392)

  • Index Cond: (id = o.invoice_id)
  • Filter: (active AND (created_date >= (date_trunc('month'::text, (LOCALTIMESTAMP + '05:30:00'::interval)) - '1 year'::interval)))
  • Rows Removed by Filter: 1
28. 0.005 0.005 ↑ 1.0 1 29,084

Index Scan using enquiry_pkey on enquiry e (cost=0.43..0.56 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=29,084)

  • Index Cond: (id = o.enquiry_id)
  • Filter: active
29. 0.005 0.005 ↑ 1.0 1 29,084

Index Scan using eos_user_pkey on eos_user eos (cost=0.42..0.78 rows=1 width=14) (actual time=0.005..0.005 rows=1 loops=29,084)

  • Index Cond: (id = e.eos_user_id)
  • Filter: (active AND (type <> 'test'::user_type) AND (partner_id = 5))
  • Rows Removed by Filter: 0
30. 811.048 811.048 ↑ 1.0 1 28,966

Index Scan using component_enquiry_id_idx on component c (cost=0.43..1.93 rows=1 width=160) (actual time=0.026..0.028 rows=1 loops=28,966)

  • Index Cond: (enquiry_id = o.enquiry_id)
  • Filter: active
31. 393.341 393.341 ↑ 1.0 1 30,257

Index Scan using eos_user_client_code_idx on eos_user eu (cost=0.42..3.80 rows=1 width=896) (actual time=0.010..0.013 rows=1 loops=30,257)

  • Index Cond: (eos.client_code = client_code)
  • Filter: jsonb_exists((roles -> 'roles'::text), '23'::text)
  • Rows Removed by Filter: 1
32. 60.514 60.514 ↑ 1.0 1 30,257

Index Scan using service_pkey on service s (cost=0.14..0.16 rows=1 width=40) (actual time=0.002..0.002 rows=1 loops=30,257)

  • Index Cond: (id = c.service_id)
  • Filter: active
33. 60.514 60.514 ↑ 1.0 1 30,257

Index Scan using service_segment_pkey on service_segment ss (cost=0.14..0.24 rows=1 width=24) (actual time=0.002..0.002 rows=1 loops=30,257)

  • Index Cond: (id = s.service_segment)
  • Filter: active
34. 90.771 90.771 ↑ 1.0 1 30,257

Index Scan using partner_pkey on partner p (cost=0.14..8.15 rows=1 width=4) (actual time=0.002..0.003 rows=1 loops=30,257)

  • Index Cond: (id = 5)
  • Filter: active
35. 181.542 181.542 ↑ 1.0 1 30,257

Index Scan using payment_outstanding_entity on payment_outstanding po (cost=0.43..0.51 rows=1 width=12) (actual time=0.006..0.006 rows=1 loops=30,257)

  • Index Cond: ((invoice_payment_note_id = o.invoice_id) AND (entity_type = 'invoice'::payment_outstanding_entity_type))
  • Filter: active
36. 181.542 181.542 ↑ 1.0 1 30,257

Index Scan using payment_invoice_association_invoice_debit_note_id_idx on payment_invoice_association pia (cost=0.42..0.85 rows=1 width=20) (actual time=0.005..0.006 rows=1 loops=30,257)

  • Index Cond: (invoice_debit_note_id = io.id)
  • Filter: (active AND (payment_credit_note_type = ANY ('{payment,credit_note}'::payment_credit_note_type[])) AND (invoice_debit_note_type = 'invoice'::invoice_debit_note_type))
  • Rows Removed by Filter: 0
37. 209.472 209.472 ↑ 1.0 1 34,912

Index Scan using payment_outstanding_entity on payment_outstanding po1 (cost=0.43..0.57 rows=1 width=12) (actual time=0.005..0.006 rows=1 loops=34,912)

  • Index Cond: ((invoice_payment_note_id = pia.payment_credit_note_id) AND (entity_type = 'payment'::payment_outstanding_entity_type))
  • Filter: active
38. 69.824 69.824 ↓ 0.0 0 34,912

Index Scan using payment_outstanding_entity on payment_outstanding po2 (cost=0.43..0.57 rows=1 width=12) (actual time=0.002..0.002 rows=0 loops=34,912)

  • Index Cond: ((invoice_payment_note_id = pia.payment_credit_note_id) AND (entity_type = 'credit_note'::payment_outstanding_entity_type))
  • Filter: active
39. 174.560 174.560 ↑ 1.0 1 34,912

Index Scan using payment_pkey on payment pay (cost=0.42..0.72 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=34,912)

  • Index Cond: (id = pia.payment_credit_note_id)
  • Filter: active
40. 104.736 104.736 ↑ 1.0 1 34,912

Index Scan using master_pkey on master m1 (cost=0.42..0.46 rows=1 width=25) (actual time=0.003..0.003 rows=1 loops=34,912)

  • Index Cond: (id = pay.payment_mode_id)
  • Filter: active
41. 69.824 69.824 ↓ 0.0 0 34,912

Index Scan using notes_pkey on note (cost=0.29..0.32 rows=1 width=20) (actual time=0.002..0.002 rows=0 loops=34,912)

  • Index Cond: (id = pia.payment_credit_note_id)
  • Filter: active
42. 34.912 34.912 ↓ 0.0 0 34,912

Index Scan using master_pkey on master m3 (cost=0.42..0.81 rows=1 width=25) (actual time=0.001..0.001 rows=0 loops=34,912)

  • Index Cond: (id = (note.note_reason_id)::integer)
  • Filter: active
43. 172,604.928 172,604.928 ↓ 1.0 17,520 34,912

Seq Scan on vat_invoice vi (cost=0.00..1,489.76 rows=17,476 width=8) (actual time=0.001..4.944 rows=17,520 loops=34,912)

44.          

SubPlan (forGroupAggregate)

45. 1.690 1.690 ↑ 1.0 1 1,690

CTE Scan on service_level_parent (cost=0.15..0.29 rows=1 width=32) (actual time=0.000..0.001 rows=1 loops=1,690)

  • Filter: (eng_parent_name = (ss.name)::text)
  • Rows Removed by Filter: 5
46.          

CTE service_level_parent

47. 0.006 0.013 ↑ 1.0 6 1

Append (cost=0.00..0.15 rows=6 width=64) (actual time=0.003..0.013 rows=6 loops=1)

48. 0.002 0.002 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=64) (actual time=0.001..0.002 rows=1 loops=1)

49. 0.001 0.001 ↑ 1.0 1 1

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

50. 0.001 0.001 ↑ 1.0 1 1

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

51. 0.001 0.001 ↑ 1.0 1 1

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

52. 0.001 0.001 ↑ 1.0 1 1

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

53. 0.001 0.001 ↑ 1.0 1 1

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

54. 62.709 62.709 ↑ 1.0 1 20,903

CTE Scan on service_level_child (cost=0.55..1.05 rows=1 width=32) (actual time=0.001..0.003 rows=1 loops=20,903)

  • Filter: (eng_name = (s.name)::text)
  • Rows Removed by Filter: 21
55.          

CTE service_level_child

56. 0.015 0.038 ↑ 1.0 22 1

Append (cost=0.00..0.55 rows=22 width=64) (actual time=0.002..0.038 rows=22 loops=1)

57. 0.002 0.002 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=64) (actual time=0.001..0.002 rows=1 loops=1)

58. 0.001 0.001 ↑ 1.0 1 1

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

59. 0.001 0.001 ↑ 1.0 1 1

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

60. 0.001 0.001 ↑ 1.0 1 1

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

61. 0.001 0.001 ↑ 1.0 1 1

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

62. 0.001 0.001 ↑ 1.0 1 1

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

63. 0.001 0.001 ↑ 1.0 1 1

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

64. 0.001 0.001 ↑ 1.0 1 1

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

65. 0.001 0.001 ↑ 1.0 1 1

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

66. 0.001 0.001 ↑ 1.0 1 1

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

67. 0.001 0.001 ↑ 1.0 1 1

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

68. 0.001 0.001 ↑ 1.0 1 1

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

69. 0.001 0.001 ↑ 1.0 1 1

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

70. 0.001 0.001 ↑ 1.0 1 1

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

71. 0.001 0.001 ↑ 1.0 1 1

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

72. 0.001 0.001 ↑ 1.0 1 1

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

73. 0.001 0.001 ↑ 1.0 1 1

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

74. 0.001 0.001 ↑ 1.0 1 1

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

75. 0.001 0.001 ↑ 1.0 1 1

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

76. 0.001 0.001 ↑ 1.0 1 1

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

77. 0.001 0.001 ↑ 1.0 1 1

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

78. 0.001 0.001 ↑ 1.0 1 1

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

79.          

CTE final

80. 60.783 401,331.456 ↓ 22,593.0 22,593 1

HashAggregate (cost=0.08..0.09 rows=1 width=2,016) (actual time=401,309.421..401,331.456 rows=22,593 loops=1)

  • Group Key: t.invoice_title, t.invoice_date, t.business_address, t.company_name, t.buiness_representative_name, t.tax, t.client_type, t.order_owner, t.client_invoice_frequency, t.billing_unit, t.client_profile_email_id, t.billing_email_id, t.invoice_day, t.business_license_number, t.service_name, t.total_amount, t.matched_amount, t.payment_amount, t.credit_note_amount, t.payment_mode, t.invoice_type, t.note_title, t.note_reason
81. 401,270.673 401,270.673 ↓ 22,593.0 22,593 1

CTE Scan on t (cost=0.00..0.02 rows=1 width=1,984) (actual time=401,010.108..401,270.673 rows=22,593 loops=1)

82. 401,395.424 401,395.424 ↓ 22,593.0 22,593 1

CTE Scan on final (cost=0.00..0.04 rows=1 width=2,016) (actual time=401,309.437..401,395.424 rows=22,593 loops=1)

Planning time : 20.328 ms
Execution time : 401,486.100 ms