explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 174M

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Sort (cost=10,214.15..10,214.65 rows=200 width=156) (actual rows= loops=)

  • Sort Key: months.date_month
2. 0.000 0.000 ↓ 0.0

HashAggregate (cost=10,199.01..10,206.51 rows=200 width=156) (actual rows= loops=)

  • Group Key: months.date_month
3. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=191.20..6,174.01 rows=70,000 width=684) (actual rows= loops=)

  • Merge Cond: (employee.id = employee_refunded_advance_payments.employee_id)
  • Join Filter: (employee_refunded_advance_payments.date_month = months.date_month)
4.          

CTE months

5. 0.000 0.000 ↓ 0.0

Function Scan on generate_series date_month (cost=0.01..10.01 rows=1,000 width=8) (actual rows= loops=)

6.          

CTE employee_expenses

7. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=10.39..10.45 rows=2 width=40) (actual rows= loops=)

  • Group Key: expense.employee_id, (date_trunc('month'::text, (expense.date)::timestamp with time zone))
8. 0.000 0.000 ↓ 0.0

Sort (cost=10.39..10.40 rows=2 width=32) (actual rows= loops=)

  • Sort Key: expense.employee_id, (date_trunc('month'::text, (expense.date)::timestamp with time zone))
9. 0.000 0.000 ↓ 0.0

Seq Scan on expenses expense (cost=0.00..10.38 rows=2 width=32) (actual rows= loops=)

  • Filter: ((status)::text = ANY ('{validated,refunded}'::text[]))
10.          

CTE employee_services

11. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=10.52..10.57 rows=2 width=40) (actual rows= loops=)

  • Group Key: service.employee_id, (date_trunc('month'::text, (service.date)::timestamp with time zone))
12. 0.000 0.000 ↓ 0.0

Sort (cost=10.52..10.53 rows=2 width=32) (actual rows= loops=)

  • Sort Key: service.employee_id, (date_trunc('month'::text, (service.date)::timestamp with time zone))
13. 0.000 0.000 ↓ 0.0

Seq Scan on services service (cost=0.00..10.51 rows=2 width=32) (actual rows= loops=)

  • Filter: ((status)::text = ANY ('{validated,payed}'::text[]))
14.          

CTE employee_invoices

15. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=10.27..10.32 rows=2 width=40) (actual rows= loops=)

  • Group Key: invoice.employee_id, (date_trunc('month'::text, (invoice.date)::timestamp with time zone))
16. 0.000 0.000 ↓ 0.0

Sort (cost=10.27..10.28 rows=2 width=48) (actual rows= loops=)

  • Sort Key: invoice.employee_id, (date_trunc('month'::text, (invoice.date)::timestamp with time zone))
17. 0.000 0.000 ↓ 0.0

Seq Scan on invoices invoice (cost=0.00..10.26 rows=2 width=48) (actual rows= loops=)

  • Filter: ((status)::text = ANY ('{validated,cashed}'::text[]))
18.          

CTE employee_payslips

19. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=19.07..19.13 rows=2 width=40) (actual rows= loops=)

  • Group Key: payslip.employee_id, (date_trunc('month'::text, (make_date(distribution.year, distribution.month_number, 1))::timestamp with time zone))
20. 0.000 0.000 ↓ 0.0

Sort (cost=19.07..19.07 rows=2 width=40) (actual rows= loops=)

  • Sort Key: payslip.employee_id, (date_trunc('month'::text, (make_date(distribution.year, distribution.month_number, 1))::timestamp with time zone))
21. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.14..19.06 rows=2 width=40) (actual rows= loops=)

22. 0.000 0.000 ↓ 0.0

Seq Scan on distributions distribution (cost=0.00..10.88 rows=1 width=24) (actual rows= loops=)

  • Filter: ((status)::text = 'validated'::text)
23. 0.000 0.000 ↓ 0.0

Index Scan using payslips_distribution_id_index on payslips payslip (cost=0.14..8.16 rows=1 width=48) (actual rows= loops=)

  • Index Cond: (distribution_id = distribution.id)
24.          

CTE employee_cashings

25. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=52.41..74.08 rows=510 width=88) (actual rows= loops=)

  • Group Key: invoice_1.employee_id, (date_trunc('month'::text, (cashing.date)::timestamp with time zone))
26. 0.000 0.000 ↓ 0.0

Sort (cost=52.41..53.68 rows=510 width=104) (actual rows= loops=)

  • Sort Key: invoice_1.employee_id, (date_trunc('month'::text, (cashing.date)::timestamp with time zone))
27. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=10.45..29.47 rows=510 width=104) (actual rows= loops=)

  • Hash Cond: (cashing.invoice_id = invoice_1.id)
28. 0.000 0.000 ↓ 0.0

Seq Scan on cashings cashing (cost=0.00..15.10 rows=510 width=84) (actual rows= loops=)

29. 0.000 0.000 ↓ 0.0

Hash (cost=10.20..10.20 rows=20 width=32) (actual rows= loops=)

30. 0.000 0.000 ↓ 0.0

Seq Scan on invoices invoice_1 (cost=0.00..10.20 rows=20 width=32) (actual rows= loops=)

31.          

CTE employee_advance_payments

32. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=11.52..11.54 rows=1 width=32) (actual rows= loops=)

  • Group Key: advance_payment.employee_id, (date_trunc('month'::text, (advance_payment.date)::timestamp with time zone))
33. 0.000 0.000 ↓ 0.0

Sort (cost=11.52..11.52 rows=1 width=32) (actual rows= loops=)

  • Sort Key: advance_payment.employee_id, (date_trunc('month'::text, (advance_payment.date)::timestamp with time zone))
34. 0.000 0.000 ↓ 0.0

Seq Scan on advance_payments advance_payment (cost=0.00..11.51 rows=1 width=32) (actual rows= loops=)

  • Filter: ((status)::text = 'pending'::text)
35.          

CTE employee_refunded_advance_payments

36. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=11.52..11.54 rows=1 width=32) (actual rows= loops=)

  • Group Key: advance_payment_1.employee_id, (date_trunc('month'::text, (advance_payment_1.refunded_date)::timestamp with time zone))
37. 0.000 0.000 ↓ 0.0

Sort (cost=11.52..11.52 rows=1 width=32) (actual rows= loops=)

  • Sort Key: advance_payment_1.employee_id, (date_trunc('month'::text, (advance_payment_1.refunded_date)::timestamp with time zone))
38. 0.000 0.000 ↓ 0.0

Seq Scan on advance_payments advance_payment_1 (cost=0.00..11.51 rows=1 width=32) (actual rows= loops=)

  • Filter: ((status)::text = 'refunded'::text)
39. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=33.51..5,823.81 rows=70,000 width=160) (actual rows= loops=)

  • Merge Cond: (employee.id = employee_advance_payments.employee_id)
  • Join Filter: (employee_advance_payments.date_month = months.date_month)
40. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=33.48..5,631.28 rows=70,000 width=152) (actual rows= loops=)

  • Merge Cond: (employee.id = employee_cashings.employee_id)
  • Join Filter: (employee_cashings.date_month = months.date_month)
41. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=0.34..2,299.39 rows=70,000 width=88) (actual rows= loops=)

  • Merge Cond: (employee.id = employee_payslips.employee_id)
  • Join Filter: (employee_payslips.date_month = months.date_month)
42. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=0.29..2,089.34 rows=70,000 width=72) (actual rows= loops=)

  • Merge Cond: (employee.id = employee_invoices.employee_id)
  • Join Filter: (employee_invoices.date_month = months.date_month)
43. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=0.24..1,879.29 rows=70,000 width=56) (actual rows= loops=)

  • Merge Cond: (employee.id = employee_services.employee_id)
  • Join Filter: (employee_services.date_month = months.date_month)
44. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=0.19..1,669.24 rows=70,000 width=40) (actual rows= loops=)

  • Merge Cond: (employee.id = employee_expenses.employee_id)
  • Join Filter: (employee_expenses.date_month = months.date_month)
45. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.14..1,459.19 rows=70,000 width=24) (actual rows= loops=)

46. 0.000 0.000 ↓ 0.0

Index Only Scan using employees_pkey on employees employee (cost=0.14..49.19 rows=70 width=16) (actual rows= loops=)

47. 0.000 0.000 ↓ 0.0

CTE Scan on months (cost=0.00..20.00 rows=1,000 width=8) (actual rows= loops=)

48. 0.000 0.000 ↓ 0.0

Sort (cost=0.05..0.06 rows=2 width=40) (actual rows= loops=)

  • Sort Key: employee_expenses.employee_id
49. 0.000 0.000 ↓ 0.0

CTE Scan on employee_expenses (cost=0.00..0.04 rows=2 width=40) (actual rows= loops=)

50. 0.000 0.000 ↓ 0.0

Sort (cost=0.05..0.06 rows=2 width=40) (actual rows= loops=)

  • Sort Key: employee_services.employee_id
51. 0.000 0.000 ↓ 0.0

CTE Scan on employee_services (cost=0.00..0.04 rows=2 width=40) (actual rows= loops=)

52. 0.000 0.000 ↓ 0.0

Sort (cost=0.05..0.06 rows=2 width=40) (actual rows= loops=)

  • Sort Key: employee_invoices.employee_id
53. 0.000 0.000 ↓ 0.0

CTE Scan on employee_invoices (cost=0.00..0.04 rows=2 width=40) (actual rows= loops=)

54. 0.000 0.000 ↓ 0.0

Sort (cost=0.05..0.06 rows=2 width=40) (actual rows= loops=)

  • Sort Key: employee_payslips.employee_id
55. 0.000 0.000 ↓ 0.0

CTE Scan on employee_payslips (cost=0.00..0.04 rows=2 width=40) (actual rows= loops=)

56. 0.000 0.000 ↓ 0.0

Sort (cost=33.14..34.41 rows=510 width=88) (actual rows= loops=)

  • Sort Key: employee_cashings.employee_id
57. 0.000 0.000 ↓ 0.0

CTE Scan on employee_cashings (cost=0.00..10.20 rows=510 width=88) (actual rows= loops=)

58. 0.000 0.000 ↓ 0.0

Materialize (cost=0.03..0.04 rows=1 width=32) (actual rows= loops=)

59. 0.000 0.000 ↓ 0.0

Sort (cost=0.03..0.04 rows=1 width=32) (actual rows= loops=)

  • Sort Key: employee_advance_payments.employee_id
60. 0.000 0.000 ↓ 0.0

CTE Scan on employee_advance_payments (cost=0.00..0.02 rows=1 width=32) (actual rows= loops=)

61. 0.000 0.000 ↓ 0.0

Materialize (cost=0.03..0.04 rows=1 width=32) (actual rows= loops=)

62. 0.000 0.000 ↓ 0.0

Sort (cost=0.03..0.04 rows=1 width=32) (actual rows= loops=)

  • Sort Key: employee_refunded_advance_payments.employee_id
63. 0.000 0.000 ↓ 0.0

CTE Scan on employee_refunded_advance_payments (cost=0.00..0.02 rows=1 width=32) (actual rows= loops=)