explain.depesz.com

PostgreSQL's explain analyze made readable

Result: pqMT : ps_test2

Settings
# exclusive inclusive rows x rows loops node
1. 10.321 1,285.427 ↑ 1.0 18,425 1

Sort (cost=41,788.68..41,846.20 rows=19,173 width=193) (actual time=1,282.979..1,285.427 rows=18,425 loops=1)

  • Sort Key: (count(*)) DESC
  • Sort Method: quicksort Memory: 5,434kB
  • JIT:
  • Functions: 247
  • Options: Inlining false, Optimization false, Expressions true, Deforming true
  • Timing: Generation 20.895 ms, Inlining 0.000 ms, Optimization 5.645 ms, Emission 134.327 ms, Total 160.867 ms
2. 0.000 1,275.106 ↑ 1.0 18,425 1

Hash Join (cost=28,004.12..40,152.06 rows=19,173 width=193) (actual time=1,193.093..1,275.106 rows=18,425 loops=1)

  • Hash Cond: (w.workeventgroupid_id = c.workeventgroupid_id)
3. 102.025 1,138.894 ↑ 1.0 18,521 1

Gather (cost=15,789.91..27,746.08 rows=19,178 width=153) (actual time=997.848..1,138.894 rows=18,521 loops=1)

  • Workers Planned: 3
  • Workers Launched: 3
4. 9.630 1,036.869 ↑ 1.3 4,630 4 / 4

Parallel Hash Left Join (cost=15,689.91..25,728.28 rows=6,186 width=153) (actual time=980.848..1,036.869 rows=4,630 loops=4)

  • Hash Cond: (w."WorkEventId" = ta.id)
5. 5.505 833.030 ↑ 1.3 4,630 4 / 4

Parallel Hash Left Join (cost=12,451.36..22,470.25 rows=6,186 width=121) (actual time=783.874..833.030 rows=4,630 loops=4)

  • Hash Cond: (w."WorkEventId" = t.id)
6. 7.621 743.759 ↑ 1.3 4,630 4 / 4

Parallel Hash Left Join (cost=10,362.62..20,362.02 rows=6,186 width=105) (actual time=699.057..743.759 rows=4,630 loops=4)

  • Hash Cond: (w."WorkEventId" = prc.id)
7. 5.803 592.338 ↑ 1.3 4,630 4 / 4

Parallel Hash Left Join (cost=7,933.71..17,913.62 rows=6,186 width=80) (actual time=553.198..592.338 rows=4,630 loops=4)

  • Hash Cond: (w."WorkEventId" = pr.id)
8. 19.381 500.037 ↑ 1.3 4,630 4 / 4

Parallel Hash Left Join (cost=5,844.01..15,804.43 rows=6,186 width=63) (actual time=465.631..500.037 rows=4,630 loops=4)

  • Hash Cond: (w."WorkEventId" = co.id)
9. 30.568 31.466 ↑ 1.3 4,630 4 / 4

Parallel Bitmap Heap Scan on erd_link_workevent w (cost=190.35..10,131.29 rows=6,186 width=44) (actual time=6.505..31.466 rows=4,630 loops=4)

  • Recheck Cond: ((author_date >= '2018-06-07 00:00:00+00'::timestamp with time zone) AND (author_date <= '2018-06-21 00:00:00+00'::timestamp with time zone))
  • Heap Blocks: exact=3,054
10. 0.898 0.898 ↑ 1.0 18,521 1 / 4

Bitmap Index Scan on erd_link_workevent_author_date_fdd43eb4 (cost=0.00..184.59 rows=19,178 width=0) (actual time=3.594..3.594 rows=18,521 loops=1)

  • Index Cond: ((author_date >= '2018-06-07 00:00:00+00'::timestamp with time zone) AND (author_date <= '2018-06-21 00:00:00+00'::timestamp with time zone))
11. 332.393 449.190 ↑ 1.0 1,125,000 4 / 4

Parallel Hash (cost=1,153.66..1,153.66 rows=1,125,000 width=19) (actual time=449.190..449.190 rows=1,125,000 loops=4)

  • Buckets: 8,388,608 Batches: 1 Memory Usage: 312,160kB
12. 116.797 116.797 ↑ 1.0 1,125,000 4 / 4

Parallel Seq Scan on erd_link_workeventcommit co (cost=0.00..1,153.66 rows=1,125,000 width=19) (actual time=0.011..116.797 rows=1,125,000 loops=4)

13. 62.360 86.498 ↑ 1.7 250,000 4 / 4

Parallel Hash (cost=423.04..423.04 rows=416,667 width=17) (actual time=86.498..86.498 rows=250,000 loops=4)

  • Buckets: 1,048,576 Batches: 1 Memory Usage: 63,040kB
14. 24.138 24.138 ↑ 1.7 250,000 4 / 4

Parallel Seq Scan on erd_link_workeventpr pr (cost=0.00..423.04 rows=416,667 width=17) (actual time=0.008..24.138 rows=250,000 loops=4)

15. 107.775 143.800 ↑ 1.3 375,000 4 / 4

Parallel Hash (cost=493.43..493.43 rows=483,871 width=25) (actual time=143.800..143.800 rows=375,000 loops=4)

  • Buckets: 2,097,152 Batches: 1 Memory Usage: 98,656kB
16. 36.025 36.025 ↑ 1.3 375,000 4 / 4

Parallel Seq Scan on erd_link_workeventprcomment prc (cost=0.00..493.43 rows=483,871 width=25) (actual time=0.011..36.025 rows=375,000 loops=4)

17. 60.338 83.766 ↑ 1.7 250,000 4 / 4

Parallel Hash (cost=422.07..422.07 rows=416,667 width=16) (actual time=83.766..83.766 rows=250,000 loops=4)

  • Buckets: 1,048,576 Batches: 1 Memory Usage: 55,168kB
18. 23.428 23.428 ↑ 1.7 250,000 4 / 4

Parallel Seq Scan on erd_link_workeventticket t (cost=0.00..422.07 rows=416,667 width=16) (actual time=0.012..23.428 rows=250,000 loops=4)

19. 142.013 194.209 ↑ 1.3 500,000 4 / 4

Parallel Hash (cost=657.90..657.90 rows=645,161 width=32) (actual time=194.209..194.209 rows=500,000 loops=4)

  • Buckets: 2,097,152 Batches: 1 Memory Usage: 126,016kB
20. 52.196 52.196 ↑ 1.3 500,000 4 / 4

Parallel Seq Scan on erd_link_workeventticketactivity ta (cost=0.00..657.90 rows=645,161 width=32) (actual time=0.016..52.196 rows=500,000 loops=4)

21. 10.855 194.882 ↑ 1.0 49,962 1

Hash (cost=12,014.21..12,014.21 rows=50,000 width=40) (actual time=194.881..194.882 rows=49,962 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 4,025kB
22. 27.344 184.027 ↑ 1.0 49,962 1

Hash Join (cost=11,182.47..12,014.21 rows=50,000 width=40) (actual time=143.536..184.027 rows=49,962 loops=1)

  • Hash Cond: (c."ApexUserId" = c_1."ApexUserId")
23. 13.185 13.185 ↑ 1.0 200,000 1

Seq Scan on erd_link_workeventcontributor c (cost=0.00..201.27 rows=200,000 width=24) (actual time=0.013..13.185 rows=200,000 loops=1)

24. 0.037 143.498 ↑ 1.0 250 1

Hash (cost=11,181.47..11,181.47 rows=250 width=16) (actual time=143.498..143.498 rows=250 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 20kB
25. 38.180 143.461 ↑ 1.0 250 1

Limit (cost=11,085.72..11,181.22 rows=250 width=16) (actual time=143.163..143.461 rows=250 loops=1)

26. 0.000 105.281 ↑ 4.0 250 1

Finalize GroupAggregate (cost=11,085.72..11,467.72 rows=1,000 width=16) (actual time=105.001..105.281 rows=250 loops=1)

  • Group Key: c_1."ApexUserId
27. 20.351 106.931 ↑ 3.0 1,001 1

Gather Merge (cost=11,085.72..11,448.72 rows=3,000 width=16) (actual time=104.992..106.931 rows=1,001 loops=1)

  • Workers Planned: 3
  • Workers Launched: 3
28. 0.360 86.580 ↑ 1.2 813 4 / 4

Sort (cost=10,985.67..10,988.67 rows=1,000 width=16) (actual time=86.516..86.580 rows=813 loops=4)

  • Sort Key: c_1."ApexUserId
  • Sort Method: quicksort Memory: 71kB
  • Worker 0: Sort Method: quicksort Memory: 71kB
  • Worker 1: Sort Method: quicksort Memory: 71kB
  • Worker 2: Sort Method: quicksort Memory: 71kB
29. 4.012 86.220 ↑ 1.0 1,000 4 / 4

Partial HashAggregate (cost=10,924.87..10,925.87 rows=1,000 width=16) (actual time=86.051..86.220 rows=1,000 loops=4)

  • Group Key: c_1."ApexUserId
30. 6.753 82.208 ↑ 1.3 18,595 4 / 4

Parallel Hash Join (cost=777.84..10,777.69 rows=24,531 width=8) (actual time=38.069..82.208 rows=18,595 loops=4)

  • Hash Cond: (w_sub.workeventgroupid_id = c_1.workeventgroupid_id)
31. 44.152 45.282 ↑ 1.3 4,646 4 / 4

Parallel Bitmap Heap Scan on erd_link_workevent w_sub (cost=188.33..10,081.72 rows=6,135 width=8) (actual time=7.541..45.282 rows=4,646 loops=4)

  • Recheck Cond: ((author_date >= '2019-06-07 00:00:00+00'::timestamp with time zone) AND (author_date <= '2019-06-21 00:00:00+00'::timestamp with time zone))
  • Heap Blocks: exact=5,174
32. 1.130 1.130 ↑ 1.0 18,585 1 / 4

Bitmap Index Scan on erd_link_workevent_author_date_fdd43eb4 (cost=0.00..182.63 rows=19,017 width=0) (actual time=4.521..4.521 rows=18,585 loops=1)

  • Index Cond: ((author_date >= '2019-06-07 00:00:00+00'::timestamp with time zone) AND (author_date <= '2019-06-21 00:00:00+00'::timestamp with time zone))
33. 11.701 30.173 ↑ 2.4 50,000 4 / 4

Parallel Hash (cost=118.92..118.92 rows=117,647 width=16) (actual time=30.172..30.173 rows=50,000 loops=4)

  • Buckets: 262,144 Batches: 1 Memory Usage: 11,520kB
34. 18.472 18.472 ↑ 2.4 50,000 4 / 4

Parallel Seq Scan on erd_link_workeventcontributor c_1 (cost=0.00..118.92 rows=117,647 width=16) (actual time=10.324..18.472 rows=50,000 loops=4)

Planning time : 26.181 ms
Execution time : 1,377.729 ms