explain.depesz.com

PostgreSQL's explain analyze made readable

Result: cGwE : OxfordFinancial

Settings
# exclusive inclusive rows x rows loops node
1. 0.009 3,265.353 ↓ 23.0 23 1

Limit (cost=2,839,016.58..2,839,016.59 rows=1 width=100) (actual time=3,265.338..3,265.353 rows=23 loops=1)

  • Output: payrollitem0.userid0, ((SubPlan 1)), ((SubPlan 2)), ((SubPlan 3)), ((SubPlan 4)), ((SubPlan 5)), ((SubPlan 6)), payrollitem0.duration14, payrollitem0.duration16, payrollitem0.duration18, payrollitem0.duration20, payrollitem0.duration22, payrollitem0.subquery24, payrollitem0.literal26, payrollitem0.subquery28, payrollitem0.subquery30
  • Buffers: shared hit=1865409 read=9427
2. 0.174 3,265.344 ↓ 23.0 23 1

Sort (cost=2,839,016.58..2,839,016.59 rows=1 width=100) (actual time=3,265.337..3,265.344 rows=23 loops=1)

  • Output: payrollitem0.userid0, ((SubPlan 1)), ((SubPlan 2)), ((SubPlan 3)), ((SubPlan 4)), ((SubPlan 5)), ((SubPlan 6)), payrollitem0.duration14, payrollitem0.duration16, payrollitem0.duration18, payrollitem0.duration20, payrollitem0.duration22, payrollitem0.subquery24, payrollitem0.literal26, payrollitem0.subquery28, payrollitem0.subquery30
  • Sort Key: ((SubPlan 2)) COLLATE "en_US", ((SubPlan 1)) COLLATE "en_US", ((SubPlan 3)) COLLATE "en_US", payrollitem0.literal26 NULLS FIRST, payrollitem0.subquery28, payrollitem0.subquery30, payrollitem0.subquery24 NULLS FIRST
  • Sort Method: quicksort Memory: 28kB
  • Buffers: shared hit=1865409 read=9427
3. 0.120 3,265.170 ↓ 23.0 23 1

Subquery Scan on payrollitem0 (cost=2,838,979.60..2,839,016.57 rows=1 width=100) (actual time=3,264.300..3,265.170 rows=23 loops=1)

  • Output: payrollitem0.userid0, (SubPlan 1), (SubPlan 2), (SubPlan 3), (SubPlan 4), (SubPlan 5), (SubPlan 6), payrollitem0.duration14, payrollitem0.duration16, payrollitem0.duration18, payrollitem0.duration20, payrollitem0.duration22, payrollitem0.subquery24, payrollitem0.literal26, payrollitem0.subquery28, payrollitem0.subquery30
  • Buffers: shared hit=1865396 read=9427
4. 0.030 3,263.854 ↓ 23.0 23 1

HashAggregate (cost=2,838,979.60..2,839,003.59 rows=1 width=143) (actual time=3,263.845..3,263.854 rows=23 loops=1)

  • Output: payrollitem14.userid, (CASE WHEN (sum(CASE WHEN (payrollitem14.paycodeid = 4) THEN payrollitem14.duration ELSE NULL::interval END) <> '00:00:00'::interval) THEN sum(CASE WHEN (payrollitem14.paycodeid = 4) THEN payrollitem14.duration ELSE NULL::interval END) ELSE NULL::interval END), (CASE WHEN (sum(CASE WHEN (payrollitem14.paycodeid = 3) THEN payrollitem14.duration ELSE NULL::interval END) <> '00:00:00'::interval) THEN sum(CASE WHEN (payrollitem14.paycodeid = 3) THEN payrollitem14.duration ELSE NULL::interval END) ELSE NULL::interval END), (CASE WHEN (sum(CASE WHEN (payrollitem14.paycodeid = 1) THEN payrollitem14.duration ELSE NULL::interval END) <> '00:00:00'::interval) THEN sum(CASE WHEN (payrollitem14.paycodeid = 1) THEN payrollitem14.duration ELSE NULL::interval END) ELSE NULL::interval END), (CASE WHEN (sum(CASE WHEN (payrollitem14.paycodeid = 6) THEN payrollitem14.duration ELSE NULL::interval END) <> '00:00:00'::interval) THEN sum(CASE WHEN (payrollitem14.paycodeid = 6) THEN payrollitem14.duration ELSE NULL::interval END) ELSE NULL::interval END), (CASE WHEN (sum(payrollitem14.duration) <> '00:00:00'::interval) THEN sum(payrollitem14.duration) ELSE NULL::interval END), (COALESCE((SubPlan 7), (SubPlan 8))), (CASE WHEN (alternatives: SubPlan 9 or hashed SubPlan 10) THEN 1 ELSE CASE WHEN (alternatives: SubPlan 11 or hashed SubPlan 12) THEN 2 ELSE 0 END END), ((SubPlan 13)), ((SubPlan 14))
  • Group Key: payrollitem14.userid, CASE WHEN (sum(CASE WHEN (payrollitem14.paycodeid = 4) THEN payrollitem14.duration ELSE NULL::interval END) <> '00:00:00'::interval) THEN sum(CASE WHEN (payrollitem14.paycodeid = 4) THEN payrollitem14.duration ELSE NULL::interval END) ELSE NULL::interval END, CASE WHEN (sum(CASE WHEN (payrollitem14.paycodeid = 3) THEN payrollitem14.duration ELSE NULL::interval END) <> '00:00:00'::interval) THEN sum(CASE WHEN (payrollitem14.paycodeid = 3) THEN payrollitem14.duration ELSE NULL::interval END) ELSE NULL::interval END, CASE WHEN (sum(CASE WHEN (payrollitem14.paycodeid = 1) THEN payrollitem14.duration ELSE NULL::interval END) <> '00:00:00'::interval) THEN sum(CASE WHEN (payrollitem14.paycodeid = 1) THEN payrollitem14.duration ELSE NULL::interval END) ELSE NULL::interval END, CASE WHEN (sum(CASE WHEN (payrollitem14.paycodeid = 6) THEN payrollitem14.duration ELSE NULL::interval END) <> '00:00:00'::interval) THEN sum(CASE WHEN (payrollitem14.paycodeid = 6) THEN payrollitem14.duration ELSE NULL::interval END) ELSE NULL::interval END, CASE WHEN (sum(payrollitem14.duration) <> '00:00:00'::interval) THEN sum(payrollitem14.duration) ELSE NULL::interval END, (COALESCE((SubPlan 7), (SubPlan 8))), (CASE WHEN (alternatives: SubPlan 9 or hashed SubPlan 10) THEN 1 ELSE CASE WHEN (alternatives: SubPlan 11 or hashed SubPlan 12) THEN 2 ELSE 0 END END), ((SubPlan 13)), ((SubPlan 14))
  • Buffers: shared hit=1865127 read=9420
5. 0.815 3,263.824 ↓ 23.0 23 1

HashAggregate (cost=2,838,955.57..2,838,979.57 rows=1 width=143) (actual time=3,263.801..3,263.824 rows=23 loops=1)

  • Output: payrollitem14.userid, CASE WHEN (sum(CASE WHEN (payrollitem14.paycodeid = 4) THEN payrollitem14.duration ELSE NULL::interval END) <> '00:00:00'::interval) THEN sum(CASE WHEN (payrollitem14.paycodeid = 4) THEN payrollitem14.duration ELSE NULL::interval END) ELSE NULL::interval END, CASE WHEN (sum(CASE WHEN (payrollitem14.paycodeid = 3) THEN payrollitem14.duration ELSE NULL::interval END) <> '00:00:00'::interval) THEN sum(CASE WHEN (payrollitem14.paycodeid = 3) THEN payrollitem14.duration ELSE NULL::interval END) ELSE NULL::interval END, CASE WHEN (sum(CASE WHEN (payrollitem14.paycodeid = 1) THEN payrollitem14.duration ELSE NULL::interval END) <> '00:00:00'::interval) THEN sum(CASE WHEN (payrollitem14.paycodeid = 1) THEN payrollitem14.duration ELSE NULL::interval END) ELSE NULL::interval END, CASE WHEN (sum(CASE WHEN (payrollitem14.paycodeid = 6) THEN payrollitem14.duration ELSE NULL::interval END) <> '00:00:00'::interval) THEN sum(CASE WHEN (payrollitem14.paycodeid = 6) THEN payrollitem14.duration ELSE NULL::interval END) ELSE NULL::interval END, CASE WHEN (sum(payrollitem14.duration) <> '00:00:00'::interval) THEN sum(payrollitem14.duration) ELSE NULL::interval END, (COALESCE((SubPlan 7), (SubPlan 8))), (CASE WHEN (alternatives: SubPlan 9 or hashed SubPlan 10) THEN 1 ELSE CASE WHEN (alternatives: SubPlan 11 or hashed SubPlan 12) THEN 2 ELSE 0 END END), ((SubPlan 13)), ((SubPlan 14))
  • Group Key: payrollitem14.userid, COALESCE((SubPlan 7), (SubPlan 8)), CASE WHEN (alternatives: SubPlan 9 or hashed SubPlan 10) THEN 1 ELSE CASE WHEN (alternatives: SubPlan 11 or hashed SubPlan 12) THEN 2 ELSE 0 END END, (SubPlan 13), (SubPlan 14)
  • Filter: ((CASE WHEN (sum(CASE WHEN (payrollitem14.paycodeid = 4) THEN payrollitem14.duration ELSE NULL::interval END) <> '00:00:00'::interval) THEN sum(CASE WHEN (payrollitem14.paycodeid = 4) THEN payrollitem14.duration ELSE NULL::interval END) ELSE NULL::interval END IS NOT NULL) OR (CASE WHEN (sum(CASE WHEN (payrollitem14.paycodeid = 3) THEN payrollitem14.duration ELSE NULL::interval END) <> '00:00:00'::interval) THEN sum(CASE WHEN (payrollitem14.paycodeid = 3) THEN payrollitem14.duration ELSE NULL::interval END) ELSE NULL::interval END IS NOT NULL) OR (CASE WHEN (sum(CASE WHEN (payrollitem14.paycodeid = 1) THEN payrollitem14.duration ELSE NULL::interval END) <> '00:00:00'::interval) THEN sum(CASE WHEN (payrollitem14.paycodeid = 1) THEN payrollitem14.duration ELSE NULL::interval END) ELSE NULL::interval END IS NOT NULL) OR (CASE WHEN (sum(CASE WHEN (payrollitem14.paycodeid = 6) THEN payrollitem14.duration ELSE NULL::interval END) <> '00:00:00'::interval) THEN sum(CASE WHEN (payrollitem14.paycodeid = 6) THEN payrollitem14.duration ELSE NULL::interval END) ELSE NULL::interval END IS NOT NULL) OR (CASE WHEN (sum(payrollitem14.duration) <> '00:00:00'::interval) THEN sum(payrollitem14.duration) ELSE NULL::interval END IS NOT NULL))
  • Buffers: shared hit=1865127 read=9420
6. 194.342 3,263.009 ↓ 494.0 494 1

Nested Loop Anti Join (cost=0.42..2,838,955.47 rows=1 width=143) (actual time=2,416.159..3,263.009 rows=494 loops=1)

  • Output: payrollitem14.userid, COALESCE((SubPlan 7), (SubPlan 8)), CASE WHEN (alternatives: SubPlan 9 or hashed SubPlan 10) THEN 1 ELSE CASE WHEN (alternatives: SubPlan 11 or hashed SubPlan 12) THEN 2 ELSE 0 END END, (SubPlan 13), (SubPlan 14), payrollitem14.paycodeid, payrollitem14.duration
  • Buffers: shared hit=1865127 read=9420
7. 575.874 1,750.381 ↓ 36,358.3 218,150 1

Seq Scan on cdece861d41e46a882b2ed30cad75315.payrollitem payrollitem14 (cost=0.00..2,838,922.80 rows=6 width=143) (actual time=2.631..1,750.381 rows=218,150 loops=1)

  • Output: payrollitem14.id, payrollitem14.userid, payrollitem14.paycodeid, payrollitem14.entrydate, payrollitem14.duration, payrollitem14.payamount, payrollitem14.paycurrencyid, payrollitem14.originuri, payrollitem14.payableitemid
  • Filter: ((payrollitem14.paycodeid IS NOT NULL) AND (payrollitem14.userid <> 1) AND (payrollitem14.entrydate >= '2018-07-01'::date) AND (payrollitem14.entrydate <= '2019-09-07'::date) AND (payrollitem14.paycodeid = ANY ('{4,3,1,6}'::integer[])) AND ((SubPlan 17) = 8) AND (COALESCE((SubPlan 15), (SubPlan 16)) = 2))
  • Rows Removed by Filter: 33232
  • Buffers: shared hit=1203050 read=7831
8.          

SubPlan (forSeq Scan)

9. 500.846 500.846 ↑ 1.0 1 250,423

Index Scan using userinfo_pkey on cdece861d41e46a882b2ed30cad75315.userinfo userinfo18 (cost=0.14..2.16 rows=1 width=4) (actual time=0.001..0.002 rows=1 loops=250,423)

  • Output: userinfo18.employeetypeid
  • Index Cond: (userinfo18.id = payrollitem14.userid)
  • Buffers: shared hit=500822 read=24
10. 656.889 656.889 ↑ 6.0 1 218,963

Index Scan using ixtsuseridstartdateenddate on cdece861d41e46a882b2ed30cad75315.timesheet timesheet16 (cost=0.29..7.54 rows=6 width=4) (actual time=0.003..0.003 rows=1 loops=218,963)

  • Output: timesheet16.approvalstatus
  • Index Cond: ((timesheet16.userid = payrollitem14.userid) AND (timesheet16.startdate <= payrollitem14.entrydate) AND (timesheet16.enddate >= payrollitem14.entrydate))
  • Buffers: shared hit=702194 read=157
11. 0.042 16.772 ↑ 1.0 1 14

Limit (cost=2.42..2.42 rows=1 width=12) (actual time=1.197..1.198 rows=1 loops=14)

  • Output: timeoffrevision17.approvalstatus, timeoffrevision17.fromtimestamputc
  • Buffers: shared hit=32 read=25
12. 2.198 16.730 ↑ 3.0 1 14

Sort (cost=2.42..2.43 rows=3 width=12) (actual time=1.195..1.195 rows=1 loops=14)

  • Output: timeoffrevision17.approvalstatus, timeoffrevision17.fromtimestamputc
  • Sort Key: timeoffrevision17.fromtimestamputc DESC
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=32 read=25
13. 14.532 14.532 ↑ 1.5 2 14

Index Scan using ixtortimeoffsid on cdece861d41e46a882b2ed30cad75315.timeoffrevision timeoffrevision17 (cost=0.30..2.41 rows=3 width=12) (actual time=0.581..1.038 rows=2 loops=14)

  • Output: timeoffrevision17.approvalstatus, timeoffrevision17.fromtimestamputc
  • Index Cond: (CASE WHEN ("substring"(payrollitem14.originuri, '^urn:replicon-tenant:cdece861d41e46a882b2ed30cad75315:time-off:(.*)$'::text) ~ '^\s*[-]?[0-9]+\s*$'::text) THEN ("substring"(payrollitem14.originuri, '^urn:replicon-tenant:cdece861d41e46a882b2ed30cad75315:time-off:(.*)$'::text))::integer ELSE NULL::integer END = timeoffrevision17.timeoffsid)
  • Buffers: shared hit=29 read=25
14. 1,308.900 1,308.900 ↑ 1.0 1 218,150

Index Only Scan using ixprpri_payrollitemid_payrunstatus on cdece861d41e46a882b2ed30cad75315.payrunpayrollitem payrunpayrollitem15 (cost=0.42..1.44 rows=1 width=16) (actual time=0.006..0.006 rows=1 loops=218,150)

  • Output: payrunpayrollitem15.payrollitemid, payrunpayrollitem15.payrunstatus
  • Index Cond: (payrunpayrollitem15.payrollitemid = payrollitem14.id)
  • Filter: (payrunpayrollitem15.payrunstatus <> 2)
  • Heap Fetches: 61
  • Buffers: shared hit=654208 read=1588
15.          

SubPlan (forNested Loop Anti Join)

16. 1.482 1.482 ↑ 6.0 1 494

Index Scan using ixtsuseridstartdateenddate on cdece861d41e46a882b2ed30cad75315.timesheet timesheet8 (cost=0.29..7.54 rows=6 width=4) (actual time=0.003..0.003 rows=1 loops=494)

  • Output: timesheet8.approvalstatus
  • Index Cond: ((timesheet8.userid = payrollitem14.userid) AND (timesheet8.startdate <= payrollitem14.entrydate) AND (timesheet8.enddate >= payrollitem14.entrydate))
  • Buffers: shared hit=1632
17. 0.000 0.000 ↓ 0.0 0

Limit (cost=2.42..2.42 rows=1 width=12) (never executed)

  • Output: timeoffrevision9.approvalstatus, timeoffrevision9.fromtimestamputc
18. 0.000 0.000 ↓ 0.0 0

Sort (cost=2.42..2.43 rows=3 width=12) (never executed)

  • Output: timeoffrevision9.approvalstatus, timeoffrevision9.fromtimestamputc
  • Sort Key: timeoffrevision9.fromtimestamputc DESC
19. 0.000 0.000 ↓ 0.0 0

Index Scan using ixtortimeoffsid on cdece861d41e46a882b2ed30cad75315.timeoffrevision timeoffrevision9 (cost=0.30..2.41 rows=3 width=12) (never executed)

  • Output: timeoffrevision9.approvalstatus, timeoffrevision9.fromtimestamputc
  • Index Cond: (CASE WHEN ("substring"((payrollitem14.originuri)::text, '^urn:replicon-tenant:cdece861d41e46a882b2ed30cad75315:time-off:(.*)$'::text) ~ '^\s*[-]?[0-9]+\s*$'::text) THEN ("substring"((payrollitem14.originuri)::text, '^urn:replicon-tenant:cdece861d41e46a882b2ed30cad75315:time-off:(.*)$'::text))::integer ELSE NULL::integer END = timeoffrevision9.timeoffsid)
20. 0.494 0.494 ↓ 0.0 0 494

Index Only Scan using ixprpri_payrollitemid_payrunstatus on cdece861d41e46a882b2ed30cad75315.payrunpayrollitem payrunpayrollitem10 (cost=0.42..1.44 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=494)

  • Index Cond: ((payrunpayrollitem10.payrollitemid = payrollitem14.id) AND (payrunpayrollitem10.payrunstatus = 0))
  • Heap Fetches: 0
  • Buffers: shared hit=1486
21. 0.000 0.000 ↓ 0.0 0

Index Only Scan using ixprpri_payrollitemid_payrunstatus on cdece861d41e46a882b2ed30cad75315.payrunpayrollitem payrunpayrollitem10_1 (cost=0.42..3,449.45 rows=1 width=16) (never executed)

  • Output: payrunpayrollitem10_1.payrollitemid
  • Index Cond: (payrunpayrollitem10_1.payrunstatus = 0)
  • Heap Fetches: 0
22. 0.494 0.494 ↓ 0.0 0 494

Index Only Scan using ixprpri_payrollitemid_payrunstatus on cdece861d41e46a882b2ed30cad75315.payrunpayrollitem payrunpayrollitem11 (cost=0.42..1.44 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=494)

  • Index Cond: ((payrunpayrollitem11.payrollitemid = payrollitem14.id) AND (payrunpayrollitem11.payrunstatus = 1))
  • Heap Fetches: 0
  • Buffers: shared hit=1486
23. 0.000 0.000 ↓ 0.0 0

Seq Scan on cdece861d41e46a882b2ed30cad75315.payrunpayrollitem payrunpayrollitem11_1 (cost=0.00..5,801.36 rows=248,269 width=16) (never executed)

  • Output: payrunpayrollitem11_1.payrollitemid
  • Filter: (payrunpayrollitem11_1.payrunstatus = 1)
24. 5.434 5.434 ↑ 6.0 1 494

Index Only Scan using ixtsuseridstartdateenddate on cdece861d41e46a882b2ed30cad75315.timesheet timesheet12 (cost=0.29..5.56 rows=6 width=4) (actual time=0.011..0.011 rows=1 loops=494)

  • Output: timesheet12.startdate
  • Index Cond: ((timesheet12.userid = payrollitem14.userid) AND (timesheet12.startdate <= payrollitem14.entrydate) AND (timesheet12.enddate >= payrollitem14.entrydate))
  • Heap Fetches: 511
  • Buffers: shared hit=1632 read=1
25. 1.482 1.482 ↑ 6.0 1 494

Index Only Scan using ixtsuseridstartdateenddate on cdece861d41e46a882b2ed30cad75315.timesheet timesheet13 (cost=0.29..5.56 rows=6 width=4) (actual time=0.003..0.003 rows=1 loops=494)

  • Output: timesheet13.enddate
  • Index Cond: ((timesheet13.userid = payrollitem14.userid) AND (timesheet13.startdate <= payrollitem14.entrydate) AND (timesheet13.enddate >= payrollitem14.entrydate))
  • Heap Fetches: 511
  • Buffers: shared hit=1633
26.          

SubPlan (forSubquery Scan)

27. 0.046 0.046 ↑ 1.0 1 23

Index Scan using userinfo_pkey on cdece861d41e46a882b2ed30cad75315.userinfo userinfo1 (cost=0.14..2.16 rows=1 width=6) (actual time=0.002..0.002 rows=1 loops=23)

  • Output: userinfo1.firstname
  • Index Cond: (userinfo1.id = payrollitem0.userid0)
  • Buffers: shared hit=46
28. 0.023 0.023 ↑ 1.0 1 23

Index Scan using userinfo_pkey on cdece861d41e46a882b2ed30cad75315.userinfo userinfo2 (cost=0.14..2.16 rows=1 width=7) (actual time=0.001..0.001 rows=1 loops=23)

  • Output: userinfo2.lastname
  • Index Cond: (userinfo2.id = payrollitem0.userid0)
  • Buffers: shared hit=46
29. 1.035 1.035 ↑ 1.0 1 23

Index Scan using login_pkey on cdece861d41e46a882b2ed30cad75315.login login3 (cost=0.14..2.16 rows=1 width=8) (actual time=0.044..0.045 rows=1 loops=23)

  • Output: login3.loginname
  • Index Cond: (login3.userid = payrollitem0.userid0)
  • Buffers: shared hit=39 read=7
30. 0.046 0.046 ↑ 1.0 1 23

Index Scan using userinfo_pkey on cdece861d41e46a882b2ed30cad75315.userinfo userinfo4 (cost=0.14..2.16 rows=1 width=1) (actual time=0.001..0.002 rows=1 loops=23)

  • Output: userinfo4.duplicatename
  • Index Cond: (userinfo4.id = payrollitem0.userid0)
  • Buffers: shared hit=46
31. 0.023 0.023 ↑ 1.0 1 23

Index Scan using userinfo_pkey on cdece861d41e46a882b2ed30cad75315.userinfo userinfo5 (cost=0.14..2.16 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=23)

  • Output: userinfo5.slug
  • Index Cond: (userinfo5.id = payrollitem0.userid0)
  • Buffers: shared hit=46
32. 0.023 0.023 ↑ 1.0 1 23

Index Scan using userinfo_pkey on cdece861d41e46a882b2ed30cad75315.userinfo userinfo6 (cost=0.14..2.16 rows=1 width=1) (actual time=0.001..0.001 rows=1 loops=23)

  • Output: userinfo6.disabled
  • Index Cond: (userinfo6.id = payrollitem0.userid0)
  • Buffers: shared hit=46
Planning time : 57.726 ms
Execution time : 3,265.902 ms