explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3bTJ : eXcell_GetTotals

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

Limit (cost=5,382,683.97..5,382,683.97 rows=1 width=20) (actual rows= loops=)

2. 0.000 0.000 ↓ 0.0

Sort (cost=5,382,683.97..5,382,683.97 rows=1 width=20) (actual rows= loops=)

  • Sort Key: (CASE WHEN (sum(payrollitem1.duration) <> '00:00:00'::interval) THEN sum(payrollitem1.duration) ELSE NULL::interval END)
3. 0.000 0.000 ↓ 0.0

Aggregate (cost=5,382,683.92..5,382,683.96 rows=1 width=20) (actual rows= loops=)

4. 0.000 0.000 ↓ 0.0

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

  • Join Filter: (payrunpayrollitem2.payrollitemid = payrollitem1.id)
5. 0.000 0.000 ↓ 0.0

Index Scan using ixpri_entrydate2paycodeid on payrollitem payrollitem1 (cost=0.43..5,362,415.19 rows=1 width=36) (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 3) = 5) AND (upper(((SubPlan 4))::text) = 'NO'::text) AND (COALESCE((SubPlan 1), (SubPlan 2)) = ANY ('{2,1}'::integer[])))
6.          

SubPlan (for Index Scan)

7. 0.000 0.000 ↓ 0.0

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

  • Index Cond: (id = payrollitem1.userid)
8. 0.000 0.000 ↓ 0.0

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

  • Index Cond: (id = payrollitem1.userid)
9. 0.000 0.000 ↓ 0.0

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

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

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

11. 0.000 0.000 ↓ 0.0

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

  • Sort Key: timeoffrevision4.fromtimestamputc DESC
12. 0.000 0.000 ↓ 0.0

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

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

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

14. 0.000 0.000 ↓ 0.0

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

  • Filter: (payrunstatus <> 2)