explain.depesz.com

PostgreSQL's explain analyze made readable

Result: MoT

Settings
# exclusive inclusive rows x rows loops node
1. 38.311 2,012.286 ↓ 1.3 7,127 1

Sort (cost=404,768.12..404,782.17 rows=5,622 width=31) (actual time=2,010.749..2,012.286 rows=7,127 loops=1)

  • Sort Key: ((pj.name)::character varying(255)) COLLATE "en_US", ((pj.info1)::character varying(255)) COLLATE "en_US
  • Sort Method: quicksort Memory: 757kB
  • Buffers: shared hit=30867 read=321091
2. 6.216 1,973.975 ↓ 1.3 7,127 1

Hash Right Join (cost=403,497.93..404,417.95 rows=5,622 width=31) (actual time=1,963.015..1,973.975 rows=7,127 loops=1)

  • Hash Cond: (p.id = project1.id)
  • Buffers: shared hit=30867 read=321091
3. 11.409 1,907.921 ↑ 2.9 12,577 1

HashAggregate (cost=400,571.96..400,935.67 rows=36,371 width=17) (actual time=1,903.153..1,907.921 rows=12,577 loops=1)

  • 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
  • Buffers: shared hit=2053 read=321091
4. 4.694 1,896.512 ↑ 2.9 12,625 1

Append (cost=903.97..400,117.33 rows=36,371 width=17) (actual time=10.190..1,896.512 rows=12,625 loops=1)

  • Buffers: shared hit=2053 read=321091
5. 11.379 24.675 ↓ 1.0 12,555 1

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

  • Hash Cond: (pc.projectid = p.id)
  • Buffers: shared hit=925
6. 3.193 3.193 ↑ 2.8 11,417 1

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

  • Buffers: shared hit=301
7. 3.578 10.103 ↓ 1.0 12,555 1

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

  • Buckets: 16384 Batches: 1 Memory Usage: 619kB
  • Buffers: shared hit=624
8. 6.525 6.525 ↓ 1.0 12,555 1

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

  • Buffers: shared hit=624
9. 2.276 1,867.143 ↑ 341.8 70 1

HashAggregate (cost=397,617.65..397,916.75 rows=23,928 width=16) (actual time=1,867.079..1,867.143 rows=70 loops=1)

  • 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.clientbillingallocationmethod
  • Buffers: shared hit=1128 read=321091
10. 391.575 1,864.867 ↑ 5.5 4,334 1

Hash Join (cost=903.97..397,318.55 rows=23,928 width=16) (actual time=243.097..1,864.867 rows=4,334 loops=1)

  • Hash Cond: (facts.projectid = p_1.id)
  • Join Filter: ((facts.clientid IS NULL) OR (p_1.clientbillingallocationmethod = 1))
  • Rows Removed by Join Filter: 1044280
  • Buffers: shared hit=1128 read=321091
11. 1,462.959 1,462.959 ↑ 2.7 1,048,614 1

Seq Scan on dm_projectclientcostactuals_facts facts (cost=0.00..350,074.91 rows=2,847,991 width=12) (actual time=9.232..1,462.959 rows=1,048,614 loops=1)

  • Buffers: shared hit=504 read=321091
12. 3.612 10.333 ↓ 1.0 12,555 1

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

  • Buckets: 16384 Batches: 1 Memory Usage: 619kB
  • Buffers: shared hit=624
13. 6.721 6.721 ↓ 1.0 12,555 1

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

  • Buffers: shared hit=624
14. 2.842 59.838 ↓ 3.7 7,120 1

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

  • Buckets: 8192 (originally 2048) Batches: 1 (originally 1) Memory Usage: 558kB
  • Buffers: shared hit=28814
15. 4.151 56.996 ↓ 3.7 7,120 1

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

  • Buffers: shared hit=28814
16. 4.062 38.605 ↓ 3.7 7,120 1

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

  • Hash Cond: (project3.id = project1.id)
  • Buffers: shared hit=7368
17. 4.355 4.355 ↓ 1.0 7,120 1

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

  • Filter: (projectstatuslabelid = 'b3cf7716-48f4-4beb-9ed3-bcfc2e0f68a7'::uuid)
  • Rows Removed by Filter: 5435
  • Buffers: shared hit=624
18. 3.733 30.188 ↓ 3.7 12,555 1

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

  • Buckets: 16384 (originally 4096) Batches: 1 (originally 1) Memory Usage: 619kB
  • Buffers: shared hit=6744
19. 6.751 26.455 ↓ 3.7 12,555 1

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

  • Hash Cond: (project1.id = pj_1.id)
  • Buffers: shared hit=6744
20. 5.271 5.271 ↓ 1.0 12,555 1

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

  • Heap Fetches: 6416
  • Buffers: shared hit=6120
21. 3.559 14.433 ↓ 3.7 12,555 1

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

  • Buckets: 16384 (originally 4096) Batches: 1 (originally 1) Memory Usage: 570kB
  • Buffers: shared hit=624
22. 10.874 10.874 ↓ 3.7 12,555 1

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

  • Filter: ((upper((info1)::text) = ANY ('{"FIXED MONTHLY","MULTIPLE MILESTONE","NOT APPLICABLE",OTHERS,"SINGLE MILESTONE",T&M,"TICKET BASED",UNCLASSIFIED,"SPECIAL BILLING"}'::text[])) OR (info1 IS NULL))
  • Buffers: shared hit=624
23. 14.240 14.240 ↑ 1.0 1 7,120

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

  • Index Cond: (id = project1.id)
  • Buffers: shared hit=21446