explain.depesz.com

PostgreSQL's explain analyze made readable

Result: V6Ep

Settings
# exclusive inclusive rows x rows loops node
1. 49.122 12,229.487 ↓ 1.3 7,127 1

Sort (cost=404,766.81..404,780.86 rows=5,622 width=31) (actual time=12,227.014..12,229.487 rows=7,127 loops=1)

  • Output: ((pj.name)::character varying(255)), pj.id, ((pj.info1)::character varying(255))
  • Sort Key: ((pj.name)::character varying(255)) COLLATE "en_US", ((pj.info1)::character varying(255)) COLLATE "en_US
  • Sort Method: quicksort Memory: 757kB
2. 10.608 12,180.365 ↓ 1.3 7,127 1

Hash Right Join (cost=403,496.65..404,416.64 rows=5,622 width=31) (actual time=12,161.864..12,180.365 rows=7,127 loops=1)

  • Output: pj.name, pj.id, pj.info1
  • Hash Cond: (p.id = project1.id)
3. 18.151 12,077.245 ↑ 2.9 12,577 1

HashAggregate (cost=400,570.68..400,934.38 rows=36,370 width=17) (actual time=12,069.328..12,077.245 rows=12,577 loops=1)

  • Output: p.id, pc.clientid, pc.effectivedate, (CASE WHEN (p.clientbillingallocationmethod = 1) THEN NULL::numeric ELSE (COALESCE(pc.costallocationpercentage, '100'::numeric) / '100'::numeric) END), p.clientbillingallocationmethod
  • Group Key: p.id, pc.clientid, pc.effectivedate, (CASE WHEN (p.clientbillingallocationmethod = 1) THEN NULL::numeric ELSE (COALESCE(pc.costallocationpercentage, '100'::numeric) / '100'::numeric) END), p.clientbillingallocationmethod
4. 7.763 12,059.094 ↑ 2.9 12,625 1

Append (cost=903.97..400,116.05 rows=36,370 width=17) (actual time=15.906..12,059.094 rows=12,625 loops=1)

5. 19.005 39.615 ↓ 1.0 12,555 1

Hash Right Join (cost=903.97..1,836.87 rows=12,443 width=20) (actual time=15.904..39.615 rows=12,555 loops=1)

  • Output: p.id, pc.clientid, pc.effectivedate, CASE WHEN (p.clientbillingallocationmethod = 1) THEN NULL::numeric ELSE (COALESCE(pc.costallocationpercentage, '100'::numeric) / '100'::numeric) END, p.clientbillingallocationmethod
  • Hash Cond: (pc.projectid = p.id)
6. 4.801 4.801 ↑ 2.8 11,417 1

Seq Scan on nscglobalnewinstance.projectclient pc (cost=0.00..624.82 rows=32,382 width=16) (actual time=0.005..4.801 rows=11,417 loops=1)

  • Output: pc.id, pc.projectid, pc.clientid, pc.costallocationpercentage, pc.effectivedate, pc.enddate
7. 5.724 15.809 ↓ 1.0 12,555 1

Hash (cost=748.43..748.43 rows=12,443 width=8) (actual time=15.809..15.809 rows=12,555 loops=1)

  • Output: p.id, p.clientbillingallocationmethod
  • Buckets: 16384 Batches: 1 Memory Usage: 619kB
8. 10.085 10.085 ↓ 1.0 12,555 1

Seq Scan on nscglobalnewinstance.project p (cost=0.00..748.43 rows=12,443 width=8) (actual time=0.005..10.085 rows=12,555 loops=1)

  • Output: p.id, p.clientbillingallocationmethod
9. 3.661 12,011.716 ↑ 341.8 70 1

HashAggregate (cost=397,616.40..397,915.49 rows=23,927 width=16) (actual time=12,011.618..12,011.716 rows=70 loops=1)

  • Output: facts.projectid, facts.clientid, (CASE WHEN (facts.clientid IS NULL) THEN NULL::date ELSE facts.effectivedate END), (CASE WHEN (p_1.clientbillingallocationmethod = 1) THEN NULL::numeric ELSE '1'::numeric END), p_1.clientbi (...)
  • Group Key: facts.projectid, facts.clientid, CASE WHEN (facts.clientid IS NULL) THEN NULL::date ELSE facts.effectivedate END, CASE WHEN (p_1.clientbillingallocationmethod = 1) THEN NULL::numeric ELSE '1'::numeric END, p_1.clientbil (...)
10. 777.833 12,008.055 ↑ 5.5 4,334 1

Hash Join (cost=903.97..397,317.31 rows=23,927 width=16) (actual time=192.505..12,008.055 rows=4,334 loops=1)

  • Output: facts.projectid, facts.clientid, CASE WHEN (facts.clientid IS NULL) THEN NULL::date ELSE facts.effectivedate END, CASE WHEN (p_1.clientbillingallocationmethod = 1) THEN NULL::numeric ELSE '1'::numeric END, p_1.client (...)
  • Hash Cond: (facts.projectid = p_1.id)
  • Join Filter: ((facts.clientid IS NULL) OR (p_1.clientbillingallocationmethod = 1))
  • Rows Removed by Join Filter: 1044267
11. 11,209.494 11,209.494 ↑ 2.7 1,048,601 1

Seq Scan on nscglobalnewinstance.dm_projectclientcostactuals_facts facts (cost=0.00..350,073.82 rows=2,847,982 width=12) (actual time=0.786..11,209.494 rows=1,048,601 loops=1)

  • Output: facts.entrydate, facts.projectid, facts.taskid, facts.billingrateid, facts.userid, facts.clientid, facts.costallocationfactor, facts.actualhours, facts.currencyid, facts.exchangerate, facts.costcapex, facts.cos (...)
12. 9.502 20.728 ↓ 1.0 12,555 1

Hash (cost=748.43..748.43 rows=12,443 width=8) (actual time=20.728..20.728 rows=12,555 loops=1)

  • Output: p_1.clientbillingallocationmethod, p_1.id
  • Buckets: 16384 Batches: 1 Memory Usage: 619kB
13. 11.226 11.226 ↓ 1.0 12,555 1

Seq Scan on nscglobalnewinstance.project p_1 (cost=0.00..748.43 rows=12,443 width=8) (actual time=0.007..11.226 rows=12,555 loops=1)

  • Output: p_1.clientbillingallocationmethod, p_1.id
14. 4.813 92.512 ↓ 3.7 7,120 1

Hash (cost=2,901.93..2,901.93 rows=1,923 width=35) (actual time=92.512..92.512 rows=7,120 loops=1)

  • Output: project1.id, pj.name, pj.id, pj.info1
  • Buckets: 8192 (originally 2048) Batches: 1 (originally 1) Memory Usage: 558kB
15. 10.078 87.699 ↓ 3.7 7,120 1

Nested Loop (cost=1,368.99..2,901.93 rows=1,923 width=35) (actual time=42.140..87.699 rows=7,120 loops=1)

  • Output: project1.id, pj.name, pj.id, pj.info1
16. 7.614 56.261 ↓ 3.7 7,120 1

Hash Join (cost=1,368.71..2,193.74 rows=1,923 width=12) (actual time=42.127..56.261 rows=7,120 loops=1)

  • Output: project1.id, project3.id, pj_1.id
  • Hash Cond: (project3.id = project1.id)
17. 6.552 6.552 ↓ 1.0 7,120 1

Seq Scan on nscglobalnewinstance.project project3 (cost=0.00..779.54 rows=7,004 width=4) (actual time=0.008..6.552 rows=7,120 loops=1)

  • Output: project3.id
  • Filter: (project3.projectstatuslabelid = 'b3cf7716-48f4-4beb-9ed3-bcfc2e0f68a7'::uuid)
  • Rows Removed by Filter: 5435
18. 5.413 42.095 ↓ 3.7 12,555 1

Hash (cost=1,325.99..1,325.99 rows=3,417 width=8) (actual time=42.095..42.095 rows=12,555 loops=1)

  • Output: project1.id, pj_1.id
  • Buckets: 16384 (originally 4096) Batches: 1 (originally 1) Memory Usage: 619kB
19. 10.005 36.682 ↓ 3.7 12,555 1

Hash Join (cost=962.52..1,325.99 rows=3,417 width=8) (actual time=19.094..36.682 rows=12,555 loops=1)

  • Output: project1.id, pj_1.id
  • Hash Cond: (project1.id = pj_1.id)
20. 7.622 7.622 ↓ 1.0 12,555 1

Index Only Scan using project_pkey on nscglobalnewinstance.project project1 (cost=0.29..282.93 rows=12,443 width=4) (actual time=0.016..7.622 rows=12,555 loops=1)

  • Output: project1.id
  • Heap Fetches: 6361
21. 5.107 19.055 ↓ 3.7 12,555 1

Hash (cost=919.52..919.52 rows=3,417 width=4) (actual time=19.055..19.055 rows=12,555 loops=1)

  • Output: pj_1.id
  • Buckets: 16384 (originally 4096) Batches: 1 (originally 1) Memory Usage: 570kB
22. 13.948 13.948 ↓ 3.7 12,555 1

Seq Scan on nscglobalnewinstance.project pj_1 (cost=0.00..919.52 rows=3,417 width=4) (actual time=0.012..13.948 rows=12,555 loops=1)

  • Output: pj_1.id
  • Filter: ((upper((pj_1.info1)::text) = ANY ('{"FIXED MONTHLY","MULTIPLE MILESTONE","NOT APPLICABLE",OTHERS,"SINGLE MILESTONE",T&M,"TICKET BASED",UNCLASSIFIED,"SPECIAL BILLING"}'::text[])) OR (pj_1.info1 IS N (...)
23. 21.360 21.360 ↑ 1.0 1 7,120

Index Scan using project_pkey on nscglobalnewinstance.project pj (cost=0.29..0.36 rows=1 width=31) (actual time=0.002..0.003 rows=1 loops=7,120)

  • Output: pj.name, pj.id, pj.info1
  • Index Cond: (pj.id = project1.id)
Planning time : 15.086 ms
Execution time : 12,232.258 ms