explain.depesz.com

PostgreSQL's explain analyze made readable

Result: fj5S : eXcell_GetTotals

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

Limit (cost=5,382,718.21..5,382,718.21 rows=1 width=188) (actual rows= loops=)

2. 0.000 0.000 ↓ 0.0

Sort (cost=5,382,718.21..5,382,718.21 rows=1 width=188) (actual rows= loops=)

  • Sort Key: ((SubPlan 2)) COLLATE "en_US", ((SubPlan 1)) COLLATE "en_US", ((SubPlan 3)) COLLATE "en_US", payrollitem0.subquery36, payrollitem0.subquery38
3. 0.000 0.000 ↓ 0.0

Subquery Scan on payrollitem0 (cost=5,382,697.60..5,382,718.20 rows=1 width=188) (actual rows= loops=)

4. 0.000 0.000 ↓ 0.0

HashAggregate (cost=5,382,697.60..5,382,704.37 rows=1 width=28) (actual rows= loops=)

  • Group Key: CASE WHEN (sum(payrollitem10.duration) <> '00:00:00'::interval) THEN sum(payrollitem10.duration) ELSE NULL::interval END, payrollitem10.userid, CASE WHEN (sum(CASE WHEN (payrollitem10.paycodeid = 3) THEN payrollitem10.duration ELSE NULL::interval END) <> '00:00:00'::interval) THEN sum(CASE WHEN (payrollitem10.paycodeid = 3) THEN payrollitem10.duration ELSE NULL::interval END) ELSE NULL::interval END, CASE WHEN (sum(CASE WHEN (payrollitem10.paycodeid = 5) THEN payrollitem10.duration ELSE NULL::interval END) <> '00:00:00'::interval) THEN sum(CASE WHEN (payrollitem10.paycodeid = 5) THEN payrollitem10.duration ELSE NULL::interval END) ELSE NULL::interval END, CASE WHEN (sum(CASE WHEN (payrollitem10.paycodeid = 17) THEN payrollitem10.duration ELSE NULL::interval END) <> '00:00:00'::interval) THEN sum(CASE WHEN (payrollitem10.paycodeid = 17) THEN payrollitem10.duration ELSE NULL::interval END) ELSE NULL::interval END, CASE WHEN (sum(CASE WHEN (payrollitem10.paycodeid = 12) THEN payrollitem10.duration ELSE NULL::interval END) <> '00:00:00'::interval) THEN sum(CASE WHEN (payrollitem10.paycodeid = 12) THEN payrollitem10.duration ELSE NULL::interval END) ELSE NULL::interval END, CASE WHEN (sum(CASE WHEN (payrollitem10.paycodeid = 11) THEN payrollitem10.duration ELSE NULL::interval END) <> '00:00:00'::interval) THEN sum(CASE WHEN (payrollitem10.paycodeid = 11) THEN payrollitem10.duration ELSE NULL::interval END) ELSE NULL::interval END, CASE WHEN (sum(CASE WHEN (payrollitem10.paycodeid = 18) THEN payrollitem10.duration ELSE NULL::interval END) <> '00:00:00'::interval) THEN sum(CASE WHEN (payrollitem10.paycodeid = 18) THEN payrollitem10.duration ELSE NULL::interval END) ELSE NULL::interval END, CASE WHEN (sum(CASE WHEN (payrollitem10.paycodeid = 1) THEN payrollitem10.duration ELSE NULL::interval END) <> '00:00:00'::interval) THEN sum(CASE WHEN (payrollitem10.paycodeid = 1) THEN payrollitem10.duration ELSE NULL::interval END) ELSE NULL::interval END, CASE WHEN (sum(CASE WHEN (payrollitem10.paycodeid = 2) THEN payrollitem10.duration ELSE NULL::interval END) <> '00:00:00'::interval) THEN sum(CASE WHEN (payrollitem10.paycodeid = 2) THEN payrollitem10.duration ELSE NULL::interval END) ELSE NULL::interval END, CASE WHEN (sum(CASE WHEN (payrollitem10.paycodeid = 19) THEN payrollitem10.duration ELSE NULL::interval END) <> '00:00:00'::interval) THEN sum(CASE WHEN (payrollitem10.paycodeid = 19) THEN payrollitem10.duration ELSE NULL::interval END) ELSE NULL::interval END, CASE WHEN (sum(CASE WHEN (payrollitem10.paycodeid = 4) THEN payrollitem10.duration ELSE NULL::interval END) <> '00:00:00'::interval) THEN sum(CASE WHEN (payrollitem10.paycodeid = 4) THEN payrollitem10.duration ELSE NULL::interval END) ELSE NULL::interval END, ((SubPlan 7)), ((SubPlan 8))
5. 0.000 0.000 ↓ 0.0

HashAggregate (cost=5,382,690.77..5,382,697.57 rows=1 width=28) (actual rows= loops=)

  • Group Key: payrollitem10.userid, (SubPlan 7), (SubPlan 8)
  • Filter: ((CASE WHEN (sum(payrollitem10.duration) <> '00:00:00'::interval) THEN sum(payrollitem10.duration) ELSE NULL::interval END IS NOT NULL) OR (CASE WHEN (sum(CASE WHEN (payrollitem10.paycodeid = 3) THEN payrollitem10.duration ELSE NULL::interval END) <> '00:00:00'::interval) THEN sum(CASE WHEN (payrollitem10.paycodeid = 3) THEN payrollitem10.duration ELSE NULL::interval END) ELSE NULL::interval END IS NOT NULL) OR (CASE WHEN (sum(CASE WHEN (payrollitem10.paycodeid = 5) THEN payrollitem10.duration ELSE NULL::interval END) <> '00:00:00'::interval) THEN sum(CASE WHEN (payrollitem10.paycodeid = 5) THEN payrollitem10.duration ELSE NULL::interval END) ELSE NULL::interval END IS NOT NULL) OR (CASE WHEN (sum(CASE WHEN (payrollitem10.paycodeid = 17) THEN payrollitem10.duration ELSE NULL::interval END) <> '00:00:00'::interval) THEN sum(CASE WHEN (payrollitem10.paycodeid = 17) THEN payrollitem10.duration ELSE NULL::interval END) ELSE NULL::interval END IS NOT NULL) OR (CASE WHEN (sum(CASE WHEN (payrollitem10.paycodeid = 12) THEN payrollitem10.duration ELSE NULL::interval END) <> '00:00:00'::interval) THEN sum(CASE WHEN (payrollitem10.paycodeid = 12) THEN payrollitem10.duration ELSE NULL::interval END) ELSE NULL::interval END IS NOT NULL) OR (CASE WHEN (sum(CASE WHEN (payrollitem10.paycodeid = 11) THEN payrollitem10.duration ELSE NULL::interval END) <> '00:00:00'::interval) THEN sum(CASE WHEN (payrollitem10.paycodeid = 11) THEN payrollitem10.duration ELSE NULL::interval END) ELSE NULL::interval END IS NOT NULL) OR (CASE WHEN (sum(CASE WHEN (payrollitem10.paycodeid = 18) THEN payrollitem10.duration ELSE NULL::interval END) <> '00:00:00'::interval) THEN sum(CASE WHEN (payrollitem10.paycodeid = 18) THEN payrollitem10.duration ELSE NULL::interval END) ELSE NULL::interval END IS NOT NULL) OR (CASE WHEN (sum(CASE WHEN (payrollitem10.paycodeid = 1) THEN payrollitem10.duration ELSE NULL::interval END) <> '00:00:00'::interval) THEN sum(CASE WHEN (payrollitem10.paycodeid = 1) THEN payrollitem10.duration ELSE NULL::interval END) ELSE NULL::interval END IS NOT NULL) OR (CASE WHEN (sum(CASE WHEN (payrollitem10.paycodeid = 2) THEN payrollitem10.duration ELSE NULL::interval END) <> '00:00:00'::interval) THEN sum(CASE WHEN (payrollitem10.paycodeid = 2) THEN payrollitem10.duration ELSE NULL::interval END) ELSE NULL::interval END IS NOT NULL) OR (CASE WHEN (sum(CASE WHEN (payrollitem10.paycodeid = 19) THEN payrollitem10.duration ELSE NULL::interval END) <> '00:00:00'::interval) THEN sum(CASE WHEN (payrollitem10.paycodeid = 19) THEN payrollitem10.duration ELSE NULL::interval END) ELSE NULL::interval END IS NOT NULL) OR (CASE WHEN (sum(CASE WHEN (payrollitem10.paycodeid = 4) THEN payrollitem10.duration ELSE NULL::interval END) <> '00:00:00'::interval) THEN sum(CASE WHEN (payrollitem10.paycodeid = 4) THEN payrollitem10.duration ELSE NULL::interval END) ELSE NULL::interval END IS NOT NULL))
6. 0.000 0.000 ↓ 0.0

Nested Loop Anti Join (cost=0.43..5,382,690.55 rows=1 width=28) (actual rows= loops=)

  • Join Filter: (payrunpayrollitem11.payrollitemid = payrollitem10.id)
7. 0.000 0.000 ↓ 0.0

Index Scan using ixpri_entrydate2paycodeid on payrollitem payrollitem10 (cost=0.43..5,362,415.19 rows=1 width=44) (actual rows= loops=)

  • Index Cond: ((paycodeid IS NOT NULL) AND (paycodeid = ANY ('{3,5,17,12,11,18,1,2,19,4}'::integer[])) AND (entrydate >= '2019-01-01'::date) AND (entrydate <= '2019-09-15'::date))
  • Filter: ((userid <> 1) AND ((SubPlan 11) = 5) AND (upper(((SubPlan 12))::text) = 'NO'::text) AND (COALESCE((SubPlan 9), (SubPlan 10)) = ANY ('{2,1}'::integer[])))
8.          

SubPlan (for Index Scan)

9. 0.000 0.000 ↓ 0.0

Index Scan using userinfo_pkey on userinfo userinfo14 (cost=0.29..2.30 rows=1 width=4) (actual rows= loops=)

  • Index Cond: (id = payrollitem10.userid)
10. 0.000 0.000 ↓ 0.0

Index Scan using userinfo_pkey on userinfo userinfo15 (cost=0.29..2.30 rows=1 width=3) (actual rows= loops=)

  • Index Cond: (id = payrollitem10.userid)
11. 0.000 0.000 ↓ 0.0

Index Scan using ixtsuseridstartdateenddate on timesheet timesheet12 (cost=0.42..6.01 rows=6 width=4) (actual rows= loops=)

  • Index Cond: ((userid = payrollitem10.userid) AND (startdate <= payrollitem10.entrydate) AND (enddate >= payrollitem10.entrydate))
12. 0.000 0.000 ↓ 0.0

Limit (cost=2.48..2.48 rows=1 width=12) (actual rows= loops=)

13. 0.000 0.000 ↓ 0.0

Sort (cost=2.48..2.49 rows=2 width=12) (actual rows= loops=)

  • Sort Key: timeoffrevision13.fromtimestamputc DESC
14. 0.000 0.000 ↓ 0.0

Index Scan using ixtortimeoffsid on timeoffrevision timeoffrevision13 (cost=0.43..2.47 rows=2 width=12) (actual rows= loops=)

  • Index Cond: (CASE WHEN ("substring"(payrollitem10.originuri, '^urn:replicon-tenant:e-xcell-new:time-off:(.*)$'::text) ~ '^\s*[-]?[0-9]+\s*$'::text) THEN ("substring"(payrollitem10.originuri, '^urn:replicon-tenant:e-xcell-new:time-off:(.*)$'::text))::integer ELSE NULL::integer END = timeoffsid)
15. 0.000 0.000 ↓ 0.0

Materialize (cost=0.00..13,335.83 rows=462,186 width=16) (actual rows= loops=)

16. 0.000 0.000 ↓ 0.0

Seq Scan on payrunpayrollitem payrunpayrollitem11 (cost=0.00..11,024.90 rows=462,186 width=16) (actual rows= loops=)

  • Filter: (payrunstatus <> 2)
17.          

SubPlan (for Nested Loop Anti Join)

18. 0.000 0.000 ↓ 0.0

Index Only Scan using ixtsuseridstartdateenddate on timesheet timesheet8 (cost=0.42..3.37 rows=6 width=4) (actual rows= loops=)

  • Index Cond: ((userid = payrollitem10.userid) AND (startdate <= payrollitem10.entrydate) AND (enddate >= payrollitem10.entrydate))
19. 0.000 0.000 ↓ 0.0

Index Only Scan using ixtsuseridstartdateenddate on timesheet timesheet9 (cost=0.42..3.37 rows=6 width=4) (actual rows= loops=)

  • Index Cond: ((userid = payrollitem10.userid) AND (startdate <= payrollitem10.entrydate) AND (enddate >= payrollitem10.entrydate))
20.          

SubPlan (for Subquery Scan)

21. 0.000 0.000 ↓ 0.0

Index Scan using userinfo_pkey on userinfo userinfo1 (cost=0.29..2.30 rows=1 width=6) (actual rows= loops=)

  • Index Cond: (id = payrollitem0.userid2)
22. 0.000 0.000 ↓ 0.0

Index Scan using userinfo_pkey on userinfo userinfo2 (cost=0.29..2.30 rows=1 width=7) (actual rows= loops=)

  • Index Cond: (id = payrollitem0.userid2)
23. 0.000 0.000 ↓ 0.0

Index Scan using login_pkey on login login3 (cost=0.29..2.30 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (userid = payrollitem0.userid2)
24. 0.000 0.000 ↓ 0.0

Index Scan using userinfo_pkey on userinfo userinfo4 (cost=0.29..2.30 rows=1 width=1) (actual rows= loops=)

  • Index Cond: (id = payrollitem0.userid2)
25. 0.000 0.000 ↓ 0.0

Index Scan using userinfo_pkey on userinfo userinfo5 (cost=0.29..2.30 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (id = payrollitem0.userid2)
26. 0.000 0.000 ↓ 0.0

Index Scan using userinfo_pkey on userinfo userinfo6 (cost=0.29..2.30 rows=1 width=1) (actual rows= loops=)

  • Index Cond: (id = payrollitem0.userid2)