explain.depesz.com

PostgreSQL's explain analyze made readable

Result: r2GY : PS_test1

Settings
# exclusive inclusive rows x rows loops node
1. 10.592 1,300.568 ↑ 1.0 18,558 1

Sort (cost=41,326.10..41,381.78 rows=18,559 width=193) (actual time=1,298.027..1,300.568 rows=18,558 loops=1)

  • Sort Key: (count(*)) DESC
  • Sort Method: quicksort Memory: 5,472kB
  • JIT:
  • Functions: 247
  • Options: Inlining false, Optimization false, Expressions true, Deforming true
  • Timing: Generation 18.412 ms, Inlining 0.000 ms, Optimization 5.362 ms, Emission 125.466 ms, Total 149.239 ms
2. 0.000 1,289.976 ↑ 1.0 18,558 1

Hash Join (cost=27,852.86..39,747.12 rows=18,559 width=193) (actual time=1,206.793..1,289.976 rows=18,558 loops=1)

  • Hash Cond: (w.workeventgroupid_id = c.workeventgroupid_id)
3. 105.894 1,174.129 ↓ 1.0 18,657 1

Gather (cost=15,784.04..27,492.66 rows=18,564 width=153) (actual time=1,028.068..1,174.129 rows=18,657 loops=1)

  • Workers Planned: 3
  • Workers Launched: 3
4. 9.925 1,068.235 ↑ 1.3 4,664 4 / 4

Parallel Hash Left Join (cost=15,684.04..25,536.26 rows=5,988 width=153) (actual time=1,011.178..1,068.235 rows=4,664 loops=4)

  • Hash Cond: (w."WorkEventId" = ta.id)
5. 5.674 859.859 ↑ 1.3 4,664 4 / 4

Parallel Hash Left Join (cost=12,445.50..22,278.86 rows=5,988 width=121) (actual time=809.864..859.859 rows=4,664 loops=4)

  • Hash Cond: (w."WorkEventId" = t.id)
6. 7.867 767.484 ↑ 1.3 4,664 4 / 4

Parallel Hash Left Join (cost=10,356.75..20,171.25 rows=5,988 width=105) (actual time=722.112..767.484 rows=4,664 loops=4)

  • Hash Cond: (w."WorkEventId" = prc.id)
7. 5.951 611.811 ↑ 1.3 4,664 4 / 4

Parallel Hash Left Join (cost=7,927.84..17,723.48 rows=5,988 width=80) (actual time=572.213..611.811 rows=4,664 loops=4)

  • Hash Cond: (w."WorkEventId" = pr.id)
8. 20.246 516.412 ↑ 1.3 4,664 4 / 4

Parallel Hash Left Join (cost=5,838.14..15,614.91 rows=5,988 width=63) (actual time=481.709..516.412 rows=4,664 loops=4)

  • Hash Cond: (w."WorkEventId" = co.id)
9. 30.511 31.456 ↑ 1.3 4,664 4 / 4

Parallel Bitmap Heap Scan on erd_link_workevent w (cost=184.48..9,942.39 rows=5,988 width=44) (actual time=6.700..31.456 rows=4,664 loops=4)

  • Recheck Cond: ((author_date >= '2020-06-07 00:00:00+00'::timestamp with time zone) AND (author_date <= '2020-06-21 00:00:00+00'::timestamp with time zone))
  • Heap Blocks: exact=3,239
10. 0.945 0.945 ↓ 1.0 18,657 1 / 4

Bitmap Index Scan on erd_link_workevent_author_date_fdd43eb4 (cost=0.00..178.91 rows=18,564 width=0) (actual time=3.780..3.780 rows=18,657 loops=1)

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

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

  • Buckets: 8,388,608 Batches: 1 Memory Usage: 312,160kB
12. 118.470 118.470 ↑ 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.012..118.470 rows=1,125,000 loops=4)

13. 65.068 89.448 ↑ 1.7 250,000 4 / 4

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

  • Buckets: 1,048,576 Batches: 1 Memory Usage: 63,040kB
14. 24.380 24.380 ↑ 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.380 rows=250,000 loops=4)

15. 111.161 147.806 ↑ 1.3 375,000 4 / 4

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

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

17. 63.449 86.701 ↑ 1.7 250,000 4 / 4

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

  • Buckets: 1,048,576 Batches: 1 Memory Usage: 55,168kB
18. 23.252 23.252 ↑ 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.009..23.252 rows=250,000 loops=4)

19. 145.490 198.451 ↑ 1.3 500,000 4 / 4

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

  • Buckets: 2,097,152 Batches: 1 Memory Usage: 126,080kB
20. 52.961 52.961 ↑ 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.017..52.961 rows=500,000 loops=4)

21. 10.598 178.452 ↑ 1.0 49,962 1

Hash (cost=11,868.82..11,868.82 rows=50,000 width=40) (actual time=178.451..178.452 rows=49,962 loops=1)

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

Hash Join (cost=11,037.08..11,868.82 rows=50,000 width=40) (actual time=127.812..167.854 rows=49,962 loops=1)

  • Hash Cond: (c."ApexUserId" = c_1."ApexUserId")
23. 13.119 13.119 ↑ 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.119 rows=200,000 loops=1)

24. 0.038 127.780 ↑ 1.0 250 1

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

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

Limit (cost=10,940.33..11,035.83 rows=250 width=16) (actual time=127.453..127.742 rows=250 loops=1)

26. 0.000 89.509 ↑ 4.0 250 1

Finalize GroupAggregate (cost=10,940.33..11,322.33 rows=1,000 width=16) (actual time=89.237..89.509 rows=250 loops=1)

  • Group Key: c_1."ApexUserId
27. 17.659 91.205 ↑ 3.0 1,001 1

Gather Merge (cost=10,940.33..11,303.33 rows=3,000 width=16) (actual time=89.228..91.205 rows=1,001 loops=1)

  • Workers Planned: 3
  • Workers Launched: 3
28. 0.253 73.546 ↑ 1.2 813 4 / 4

Sort (cost=10,840.28..10,843.28 rows=1,000 width=16) (actual time=73.505..73.546 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. 3.170 73.293 ↑ 1.0 1,000 4 / 4

Partial HashAggregate (cost=10,779.49..10,780.49 rows=1,000 width=16) (actual time=73.179..73.293 rows=1,000 loops=4)

  • Group Key: c_1."ApexUserId
30. 5.808 70.123 ↑ 1.3 18,657 4 / 4

Parallel Hash Join (cost=773.99..10,635.80 rows=23,947 width=8) (actual time=32.060..70.123 rows=18,657 loops=4)

  • Hash Cond: (w_sub.workeventgroupid_id = c_1.workeventgroupid_id)
31. 39.278 40.494 ↑ 1.3 4,664 4 / 4

Parallel Bitmap Heap Scan on erd_link_workevent w_sub (cost=184.48..9,942.39 rows=5,988 width=8) (actual time=7.846..40.494 rows=4,664 loops=4)

  • Recheck Cond: ((author_date >= '2020-06-07 00:00:00+00'::timestamp with time zone) AND (author_date <= '2020-06-21 00:00:00+00'::timestamp with time zone))
  • Heap Blocks: exact=5,392
32. 1.216 1.216 ↓ 1.0 18,657 1 / 4

Bitmap Index Scan on erd_link_workevent_author_date_fdd43eb4 (cost=0.00..178.91 rows=18,564 width=0) (actual time=4.866..4.866 rows=18,657 loops=1)

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

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

  • Buckets: 262,144 Batches: 1 Memory Usage: 11,488kB
34. 13.660 13.660 ↑ 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.750..13.660 rows=50,000 loops=4)

Planning time : 23.184 ms
Execution time : 1,410.195 ms