explain.depesz.com

PostgreSQL's explain analyze made readable

Result: eXY6 : avg using additional queries

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.012 473.719 ↑ 153.8 13 1

Hash Right Join (cost=65,718.29..65,810.79 rows=2,000 width=49) (actual time=473.705..473.719 rows=13 loops=1)

  • Hash Cond: (ac.date_key = c.date_key)
2.          

CTE customers

3. 0.009 473.662 ↑ 30.8 13 1

Sort (cost=65,666.73..65,667.73 rows=400 width=17) (actual time=473.661..473.662 rows=13 loops=1)

  • Sort Key: (generate_series((CURRENT_DATE - '7 days'::interval), (CURRENT_DATE)::timestamp without time zone, '1 day'::interval))
  • Sort Method: quicksort Memory: 26kB
4. 0.027 473.653 ↑ 30.8 13 1

HashAggregate (cost=65,643.45..65,649.45 rows=400 width=17) (actual time=473.649..473.653 rows=13 loops=1)

  • Group Key: (generate_series((CURRENT_DATE - '7 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. 3.347 473.626 ↑ 16,144.4 52 1

Merge Left Join (cost=36,242.47..55,558.70 rows=839,510 width=13) (actual time=473.532..473.626 rows=52 loops=1)

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

Sort (cost=64.86..67.36 rows=1,000 width=8) (actual time=0.024..0.026 rows=8 loops=1)

  • Sort Key: (generate_series((CURRENT_DATE - '7 days'::interval), (CURRENT_DATE)::timestamp without time zone, '1 day'::interval))
  • Sort Method: quicksort Memory: 25kB
7. 0.008 0.009 ↑ 125.0 8 1

ProjectSet (cost=0.00..5.03 rows=1,000 width=8) (actual time=0.007..0.009 rows=8 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.569 275.513 ↑ 48.3 3,473 1

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

10. 87.945 274.944 ↑ 48.3 3,473 1

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

  • Sort Key: ((c_1."becameClientAt")::date)
  • Sort Method: external merge Disk: 2,984kB
11. 186.999 186.999 ↓ 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.009..186.999 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. 1.982 194.740 ↑ 3.4 1,009 1

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

  • Group Key: ct."companyId
14. 7.603 192.758 ↓ 1.0 5,394 1

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

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

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

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

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

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

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

18.          

CTE avg_customers

19. 0.000 0.033 ↑ 125.0 8 1

Nested Loop Left Join (cost=10.00..37.56 rows=1,000 width=40) (actual time=0.028..0.033 rows=8 loops=1)

20. 0.008 0.009 ↑ 125.0 8 1

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

21. 0.001 0.001 ↑ 1.0 1 1

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

22. 0.006 0.024 ↑ 1.0 1 8

Materialize (cost=10.00..10.03 rows=1 width=32) (actual time=0.002..0.003 rows=1 loops=8)

23. 0.016 0.018 ↑ 1.0 1 1

Aggregate (cost=10.00..10.02 rows=1 width=32) (actual time=0.018..0.018 rows=1 loops=1)

24. 0.002 0.002 ↑ 30.8 13 1

CTE Scan on customers (cost=0.00..8.00 rows=400 width=16) (actual time=0.000..0.002 rows=13 loops=1)

25. 0.036 0.036 ↑ 125.0 8 1

CTE Scan on avg_customers ac (cost=0.00..20.00 rows=1,000 width=40) (actual time=0.028..0.036 rows=8 loops=1)

26. 0.004 473.671 ↑ 30.8 13 1

Hash (cost=8.00..8.00 rows=400 width=17) (actual time=473.671..473.671 rows=13 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
27. 473.667 473.667 ↑ 30.8 13 1

CTE Scan on customers c (cost=0.00..8.00 rows=400 width=17) (actual time=473.663..473.667 rows=13 loops=1)

Planning time : 1.978 ms
Execution time : 474.529 ms