explain.depesz.com

PostgreSQL's explain analyze made readable

Result: MiZt : PS_test3

Settings
# exclusive inclusive rows x rows loops node
1. 10.515 1,357.986 ↑ 1.0 18,425 1

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

  • Sort Key: (count(*)) DESC
  • Sort Method: quicksort Memory: 5,434kB
  • Buffers: shared hit=86,458
  • JIT:
  • Functions: 247
  • Options: Inlining false, Optimization false, Expressions true, Deforming true
  • Timing: Generation 18.766 ms, Inlining 0.000 ms, Optimization 5.209 ms, Emission 123.610 ms, Total 147.585 ms
2. 0.000 1,347.471 ↑ 1.0 18,425 1

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

  • Hash Cond: (w.workeventgroupid_id = c.workeventgroupid_id)
  • Buffers: shared hit=86,458
3. 102.838 1,217.543 ↑ 1.0 18,521 1

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

  • Workers Planned: 3
  • Workers Launched: 3
  • Buffers: shared hit=79,651
4. 9.916 1,114.705 ↑ 1.3 4,630 4 / 4

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

  • Hash Cond: (w."WorkEventId" = ta.id)
  • Buffers: shared hit=79,651
5. 5.637 899.380 ↑ 1.3 4,630 4 / 4

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

  • Hash Cond: (w."WorkEventId" = t.id)
  • Buffers: shared hit=66,831
6. 7.938 803.652 ↑ 1.3 4,630 4 / 4

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

  • Hash Cond: (w."WorkEventId" = prc.id)
  • Buffers: shared hit=61,425
7. 5.875 635.703 ↑ 1.3 4,630 4 / 4

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

  • Hash Cond: (w."WorkEventId" = pr.id)
  • Buffers: shared hit=51,870
8. 19.658 535.685 ↑ 1.3 4,630 4 / 4

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

  • Hash Cond: (w."WorkEventId" = co.id)
  • Buffers: shared hit=45,500
9. 30.515 31.408 ↑ 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.425..31.408 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,061
  • Buffers: shared hit=16,837
10. 0.893 0.893 ↑ 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.571..3.571 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))
  • Buffers: shared hit=65
11. 359.018 484.619 ↑ 1.0 1,125,000 4 / 4

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

  • Buckets: 8,388,608 Batches: 1 Memory Usage: 312,192kB
  • Buffers: shared hit=28,663
12. 125.601 125.601 ↑ 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.010..125.601 rows=1,125,000 loops=4)

  • Buffers: shared hit=28,663
13. 68.065 94.143 ↑ 1.7 250,000 4 / 4

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

  • Buckets: 1,048,576 Batches: 1 Memory Usage: 63,072kB
  • Buffers: shared hit=6,370
14. 26.078 26.078 ↑ 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.010..26.078 rows=250,000 loops=4)

  • Buffers: shared hit=6,370
15. 120.134 160.011 ↑ 1.3 375,000 4 / 4

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

  • Buckets: 2,097,152 Batches: 1 Memory Usage: 98,656kB
  • Buffers: shared hit=9,555
16. 39.877 39.877 ↑ 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.012..39.877 rows=375,000 loops=4)

  • Buffers: shared hit=9,555
17. 65.965 90.091 ↑ 1.7 250,000 4 / 4

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

  • Buckets: 1,048,576 Batches: 1 Memory Usage: 55,168kB
  • Buffers: shared hit=5,406
18. 24.126 24.126 ↑ 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.008..24.126 rows=250,000 loops=4)

  • Buffers: shared hit=5,406
19. 149.975 205.409 ↑ 1.3 500,000 4 / 4

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

  • Buckets: 2,097,152 Batches: 1 Memory Usage: 126,048kB
  • Buffers: shared hit=12,739
20. 55.434 55.434 ↑ 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.018..55.434 rows=500,000 loops=4)

  • Buffers: shared hit=12,739
21. 11.400 188.067 ↑ 1.0 49,962 1

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

  • Buckets: 65,536 Batches: 1 Memory Usage: 4,025kB
  • Buffers: shared hit=6,807
22. 29.141 176.667 ↑ 1.0 49,962 1

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

  • Hash Cond: (c."ApexUserId" = c_1."ApexUserId")
  • Buffers: shared hit=6,807
23. 15.242 15.242 ↑ 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..15.242 rows=200,000 loops=1)

  • Buffers: shared hit=1,274
24. 0.042 132.284 ↑ 1.0 250 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 20kB
  • Buffers: shared hit=5,533
25. 37.968 132.242 ↑ 1.0 250 1

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

  • Buffers: shared hit=5,533
26. 0.000 94.274 ↑ 4.0 250 1

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

  • Group Key: c_1."ApexUserId
  • Buffers: shared hit=5,533
27. 18.517 95.962 ↑ 3.0 1,001 1

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

  • Workers Planned: 3
  • Workers Launched: 3
  • Buffers: shared hit=18,263
28. 0.263 77.445 ↑ 1.2 813 4 / 4

Sort (cost=10,985.67..10,988.67 rows=1,000 width=16) (actual time=77.401..77.445 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
  • Buffers: shared hit=18,263
29. 3.354 77.182 ↑ 1.0 1,000 4 / 4

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

  • Group Key: c_1."ApexUserId
  • Buffers: shared hit=18,242
30. 6.101 73.828 ↑ 1.3 18,595 4 / 4

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

  • Hash Cond: (w_sub.workeventgroupid_id = c_1.workeventgroupid_id)
  • Buffers: shared hit=18,242
31. 40.503 41.578 ↑ 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.243..41.578 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=4,685
  • Buffers: shared hit=16,968
32. 1.075 1.075 ↑ 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.301..4.301 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))
  • Buffers: shared hit=74
33. 11.174 26.149 ↑ 2.4 50,000 4 / 4

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

  • Buckets: 262,144 Batches: 1 Memory Usage: 11,488kB
  • Buffers: shared hit=1,274
34. 14.975 14.975 ↑ 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=7.653..14.975 rows=50,000 loops=4)

  • Buffers: shared hit=1,274
Planning time : 16.970 ms
Execution time : 1,449.291 ms