explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xN18 : ds

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 5.366 ↓ 5.0 5,131 1

CTE Scan on calls_details c_1 (cost=2.38..48.35 rows=1,022 width=64) (actual time=0.047..5.366 rows=5,131 loops=1)

  • Filter: (NOT (hashed SubPlan 5))
  • Rows Removed by Filter: 3313
2.          

CTE survey_call_count_x

3. 0.079 55.276 ↑ 5.1 40 1

Sort (cost=406.58..407.09 rows=204 width=120) (actual time=55.247..55.276 rows=40 loops=1)

  • Sort Key: (count(c_2.file_name)) DESC
  • Sort Method: quicksort Memory: 28kB
4. 0.082 55.197 ↑ 5.1 40 1

WindowAgg (cost=394.68..398.76 rows=204 width=120) (actual time=55.102..55.197 rows=40 loops=1)

5. 0.107 55.115 ↑ 5.1 40 1

Sort (cost=394.68..395.19 rows=204 width=80) (actual time=55.088..55.115 rows=40 loops=1)

  • Sort Key: c_2.survey_id, (count(c_2.*)) DESC
  • Sort Method: quicksort Memory: 28kB
6. 2.868 55.008 ↑ 5.1 40 1

HashAggregate (cost=384.81..386.85 rows=204 width=80) (actual time=54.975..55.008 rows=40 loops=1)

  • Group Key: c_2.survey_id, c_2.call_outcome
7. 8.097 52.140 ↑ 2.1 3,313 1

Merge Join (cost=204.46..314.06 rows=7,075 width=216) (actual time=37.331..52.140 rows=3,313 loops=1)

  • Merge Cond: ((calloutcome_1.survey_id)::text = (c_2.survey_id)::text)
8. 0.025 8.587 ↑ 232.0 3 1

Sort (cost=46.78..48.52 rows=696 width=32) (actual time=8.584..8.587 rows=3 loops=1)

  • Sort Key: calloutcome_1.survey_id
  • Sort Method: quicksort Memory: 25kB
9. 8.562 8.562 ↑ 232.0 3 1

CTE Scan on calloutcome calloutcome_1 (cost=0.00..13.92 rows=696 width=32) (actual time=0.002..8.562 rows=3 loops=1)

10. 27.321 35.456 ↓ 4.2 8,444 1

Sort (cost=157.67..162.76 rows=2,033 width=216) (actual time=28.649..35.456 rows=8,444 loops=1)

  • Sort Key: c_2.survey_id
  • Sort Method: external sort Disk: 2464kB
11. 8.135 8.135 ↓ 4.2 8,444 1

CTE Scan on calls_details c_2 (cost=0.00..45.97 rows=2,033 width=216) (actual time=0.036..8.135 rows=8,444 loops=1)

  • Filter: ((survey_id)::text <> 'Others'::text)
12.          

CTE survey_call_count_y

13. 0.062 9.585 ↑ 5.3 38 1

Sort (cost=78.33..78.83 rows=200 width=88) (actual time=9.557..9.585 rows=38 loops=1)

  • Sort Key: (count(c_3.file_name)) DESC
  • Sort Method: quicksort Memory: 27kB
14.          

Initplan (for Sort)

15. 0.004 0.004 ↑ 1.0 1 1

CTE Scan on surveys_group surveys_group_3 (cost=0.00..1.53 rows=1 width=32) (actual time=0.002..0.004 rows=1 loops=1)

  • Filter: ((survey_id)::text = 'Others'::text)
  • Rows Removed by Filter: 2
16. 0.069 9.519 ↑ 5.3 38 1

WindowAgg (cost=65.66..69.16 rows=200 width=88) (actual time=9.434..9.519 rows=38 loops=1)

17. 0.060 9.450 ↑ 5.3 38 1

Sort (cost=65.66..66.16 rows=200 width=48) (actual time=9.425..9.450 rows=38 loops=1)

  • Sort Key: (count($8)) DESC
  • Sort Method: quicksort Memory: 27kB
18. 4.152 9.390 ↑ 5.3 38 1

HashAggregate (cost=56.01..58.01 rows=200 width=48) (actual time=9.360..9.390 rows=38 loops=1)

  • Group Key: c_3.call_outcome
19. 5.223 5.238 ↓ 5.0 5,131 1

CTE Scan on calls_details c_3 (cost=2.38..48.35 rows=1,022 width=64) (actual time=0.039..5.238 rows=5,131 loops=1)

  • Filter: (NOT (hashed SubPlan 9))
  • Rows Removed by Filter: 3313
20.          

SubPlan (for CTE Scan)

21. 0.010 0.015 ↑ 22.7 3 1

HashAggregate (cost=1.53..2.21 rows=68 width=32) (actual time=0.011..0.015 rows=3 loops=1)

  • Group Key: surveys_group_4.survey_id
22. 0.005 0.005 ↑ 22.7 3 1

CTE Scan on surveys_group surveys_group_4 (cost=0.00..1.36 rows=68 width=32) (actual time=0.002..0.005 rows=3 loops=1)

23.          

CTE aggregation

24. 0.008 65.030 ↑ 23.0 3 1

Append (cost=5.10..11.83 rows=69 width=64) (actual time=55.374..65.030 rows=3 loops=1)

25. 0.053 55.374 ↑ 34.0 2 1

HashAggregate (cost=5.10..5.95 rows=68 width=64) (actual time=55.371..55.374 rows=2 loops=1)

  • Group Key: survey_call_count_x.survey_id
26. 55.321 55.321 ↑ 5.7 12 1

CTE Scan on survey_call_count_x (cost=0.00..4.59 rows=68 width=72) (actual time=55.252..55.321 rows=12 loops=1)

  • Filter: (r < 7)
  • Rows Removed by Filter: 28
27. 0.027 9.648 ↑ 1.0 1 1

Aggregate (cost=4.84..4.85 rows=1 width=64) (actual time=9.646..9.648 rows=1 loops=1)

28. 9.621 9.621 ↑ 11.2 6 1

CTE Scan on survey_call_count_y (cost=0.00..4.50 rows=67 width=40) (actual time=9.560..9.621 rows=6 loops=1)

  • Filter: (r < 7)
  • Rows Removed by Filter: 32
29. 434.244 434.244 ↑ 232.0 3 1

CTE Scan on calloutcome (cost=0.00..13.92 rows=696 width=64) (actual time=434.241..434.244 rows=3 loops=1)

30. 0.009 65.056 ↑ 23.0 3 1

Hash (cost=1.38..1.38 rows=69 width=64) (actual time=65.055..65.056 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
31. 65.047 65.047 ↑ 23.0 3 1

CTE Scan on aggregation (cost=0.00..1.38 rows=69 width=64) (actual time=55.382..65.047 rows=3 loops=1)

32.          

SubPlan (for CTE Scan)

33. 0.009 0.014 ↑ 22.7 3 1

HashAggregate (cost=1.53..2.21 rows=68 width=32) (actual time=0.011..0.014 rows=3 loops=1)

  • Group Key: surveys_group_1.survey_id
34. 0.005 0.005 ↑ 22.7 3 1

CTE Scan on surveys_group surveys_group_1 (cost=0.00..1.36 rows=68 width=32) (actual time=0.002..0.005 rows=3 loops=1)