explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2qM2 : Optimization for: avg using additional queries; plan #eXY6

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.027 381.438 ↑ 36.4 11 1

WindowAgg (cost=65,678.73..65,690.73 rows=400 width=81) (actual time=381.431..381.438 rows=11 loops=1)

2.          

CTE customers

3. 0.009 381.393 ↑ 36.4 11 1

Sort (cost=65,666.73..65,667.73 rows=400 width=17) (actual time=381.392..381.393 rows=11 loops=1)

  • Sort Key: (generate_series((CURRENT_DATE - '6 days'::interval), (CURRENT_DATE)::timestamp without time zone, '1 day'::interval))
  • Sort Method: quicksort Memory: 25kB
4. 0.028 381.384 ↑ 36.4 11 1

HashAggregate (cost=65,643.45..65,649.45 rows=400 width=17) (actual time=381.379..381.384 rows=11 loops=1)

  • Group Key: (generate_series((CURRENT_DATE - '6 days'::interval), (CURRENT_DATE)::timestamp without time zone, '1 day'::interval)), CASE WHEN ((c_1.branch = 'SG'::""countryType"") AND (hashed SubPlan 1)) THEN true ELSE false END
5. 0.982 381.356 ↑ 17,489.8 48 1

Merge Left Join (cost=36,242.47..55,558.70 rows=839,510 width=13) (actual time=381.303..381.356 rows=48 loops=1)

  • Merge Cond: ((generate_series((CURRENT_DATE - '6 days'::interval), (CURRENT_DATE)::timestamp without time zone, '1 day'::interval)) = ((c_1.""becameClientAt"")::date))
6. 0.005 0.012 ↑ 142.9 7 1

Sort (cost=64.86..67.36 rows=1,000 width=8) (actual time=0.010..0.012 rows=7 loops=1)

  • Sort Key: (generate_series((CURRENT_DATE - '6 days'::interval), (CURRENT_DATE)::timestamp without time zone, '1 day'::interval))
  • Sort Method: quicksort Memory: 25kB
7. 0.006 0.007 ↑ 142.9 7 1

ProjectSet (cost=0.00..5.03 rows=1,000 width=8) (actual time=0.005..0.007 rows=7 loops=1)

8. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=1)

9. 0.494 225.680 ↑ 48.3 3,473 1

Materialize (cost=32,389.18..33,228.69 rows=167,902 width=16) (actual time=223.121..225.680 rows=3,473 loops=1)

10. 69.549 225.186 ↑ 48.3 3,473 1

Sort (cost=32,389.18..32,808.94 rows=167,902 width=16) (actual time=223.119..225.186 rows=3,473 loops=1)

  • Sort Key: ((c_1."becameClientAt")::date)
  • Sort Method: external merge Disk: 2,976kB
11. 155.637 155.637 ↓ 1.0 167,932 1

Seq Scan on companies c_1 (cost=0.00..14,947.59 rows=167,902 width=16) (actual time=0.007..155.637 rows=167,932 loops=1)

  • Filter: (("partnershipType" IS NULL) AND ("isPartner" IS NOT TRUE))
  • Rows Removed by Filter: 3,627
12.          

SubPlan (for Merge Left Join)

13. 2.179 154.682 ↑ 3.4 1,009 1

HashAggregate (cost=3,745.40..3,779.82 rows=3,442 width=4) (actual time=154.249..154.682 rows=1,009 loops=1)

  • Group Key: ct."companyId
14. 2.093 152.503 ↓ 1.0 5,394 1

Hash Join (cost=565.56..3,732.09 rows=5,325 width=4) (actual time=3.254..152.503 rows=5,394 loops=1)

  • Hash Cond: (ci."contractId" = ct.id)
15. 147.208 147.208 ↓ 1.0 5,394 1

Seq Scan on "contractItems" ci (cost=0.00..3,152.55 rows=5,325 width=4) (actual time=0.027..147.208 rows=5,394 loops=1)

  • Filter: ((name)::text ~~* '%nominee%'::text)
  • Rows Removed by Filter: 45,639
16. 1.489 3.202 ↓ 1.0 11,334 1

Hash (cost=426.36..426.36 rows=11,136 width=8) (actual time=3.202..3.202 rows=11,334 loops=1)

  • Buckets: 16,384 Batches: 1 Memory Usage: 571kB
17. 1.713 1.713 ↓ 1.0 11,334 1

Seq Scan on contracts ct (cost=0.00..426.36 rows=11,136 width=8) (actual time=0.005..1.713 rows=11,334 loops=1)

18. 0.013 381.411 ↑ 36.4 11 1

HashAggregate (cost=11.00..15.00 rows=400 width=49) (actual time=381.406..381.411 rows=11 loops=1)

  • Group Key: c.date_key, c.is_sg_foreigner, c.clients
19. 381.398 381.398 ↑ 36.4 11 1

CTE Scan on customers c (cost=0.00..8.00 rows=400 width=17) (actual time=381.394..381.398 rows=11 loops=1)

Planning time : 0.743 ms
Execution time : 382.398 ms