explain.depesz.com

PostgreSQL's explain analyze made readable

Result: argS

Settings
# exclusive inclusive rows x rows loops node
1. 3,662.637 33,094.771 ↓ 0.0 100 1

Custom Scan (Citus Adaptive) (cost=0.00..0.00 rows=0 width=0) (actual time=33,094.763..33,094.771 rows=100 loops=1)

  • -> Distributed Subplan 14_1
  • -> Distributed Subplan 14_2
  • -> Distributed Subplan 14_3
  • -> Distributed Subplan 14_4
  • Task Count: 1
  • Tasks Shown: All
  • -> Task
  • Node: host=localhost port=5,432 dbname=citus
2. 67.151 1,654.786 ↓ 0.0 115,080 1

HashAggregate (cost=0.00..0.00 rows=0 width=0) (actual time=1,632.648..1,654.786 rows=115,080 loops=1)

  • Group Key: remote_scan.gl_business_unit_and_journal_id_computed
3. 811.009 1,587.635 ↓ 0.0 115,080 1

Custom Scan (Citus Adaptive) (cost=0.00..0.00 rows=0 width=0) (actual time=1,580.704..1,587.635 rows=115,080 loops=1)

  • Task Count: 32
  • Tasks Shown: One of 32
  • -> Task
  • Node: host=10.0.0.34 port=5,432 dbname=citus
4. 0.963 776.626 ↓ 28.6 3,637 1

Unique (cost=8,106.24..8,106.88 rows=127 width=24) (actual time=775.291..776.626 rows=3,637 loops=1)

5. 22.265 775.663 ↓ 64.6 8,203 1

Sort (cost=8,106.24..8,106.56 rows=127 width=24) (actual time=775.290..775.663 rows=8,203 loops=1)

  • Sort Key: gl_business_unit_and_journal_id_computed
  • Sort Method: quicksort Memory: 1,025kB
6. 418.617 753.398 ↓ 64.6 8,203 1

Bitmap Heap Scan on mega_gl_106189 gl (cost=7,911.99..8,101.81 rows=127 width=24) (actual time=351.272..753.398 rows=8,203 loops=1)

  • Recheck Cond: (gl_journal_id ~~ '%01_1%'::text)
  • Rows Removed by Index Recheck: 1,266,021
  • Heap Blocks: exact=99,452
7. 334.781 334.781 ↓ 10,033.3 1,274,224 1

Bitmap Index Scan on trgm_idx_mega_gl_jrn_id_106189 (cost=0.00..7,911.96 rows=127 width=0) (actual time=334.781..334.781 rows=1,274,224 loops=1)

  • Index Cond: (gl_journal_id ~~ '%01_1%'::text)
8. 637.393 22,637.901 ↓ 0.0 257,264 1

HashAggregate (cost=0.00..0.00 rows=0 width=0) (actual time=22,372.880..22,637.901 rows=257,264 loops=1)

  • Group Key: remote_scan.gl_journal_id, remote_scan.gl_business_unit_code, remote_scan.gl_effective_date, remote_scan.gl_account_number, remote_scan.ca_account_name, remote_scan.gl_local_amount_currency, remote_scan.gl_reporting_amount_currency
9. 17,853.964 22,000.508 ↓ 0.0 257,264 1

Custom Scan (Citus Adaptive) (cost=0.00..0.00 rows=0 width=0) (actual time=21,985.273..22,000.508 rows=257,264 loops=1)

  • Task Count: 32
  • Tasks Shown: One of 32
  • -> Task
  • Node: host=10.0.0.34 port=5,432 dbname=citus
10. 0.000 4,146.544 ↓ 1.6 8,199 1

Finalize GroupAggregate (cost=170,084.75..170,949.80 rows=5,141 width=191) (actual time=4,114.453..4,146.544 rows=8,199 loops=1)

  • Group Key: gl.gl_journal_id, gl.gl_business_unit_code, gl.gl_effective_date, gl.gl_account_number, gl.ca_account_name, gl.gl_local_amount_currency, gl.gl_reporting_amount_currency
11. 490.850 4,177.261 ↓ 1.9 8,199 1

Gather Merge (cost=170,084.75..170,686.33 rows=4,284 width=191) (actual time=4,114.441..4,177.261 rows=8,199 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
12. 5.447 3,686.411 ↓ 1.3 2,733 3 / 3

Partial GroupAggregate (cost=169,084.72..169,191.82 rows=2,142 width=191) (actual time=3,680.771..3,686.411 rows=2,733 loops=3)

  • Group Key: gl.gl_journal_id, gl.gl_business_unit_code, gl.gl_effective_date, gl.gl_account_number, gl.ca_account_name, gl.gl_local_amount_currency, gl.gl_reporting_amount_currency
13. 5.144 3,680.964 ↓ 1.3 2,734 3 / 3

Sort (cost=169,084.72..169,090.08 rows=2,142 width=75) (actual time=3,680.742..3,680.964 rows=2,734 loops=3)

  • Sort Key: gl.gl_journal_id, gl.gl_business_unit_code, gl.gl_effective_date, gl.gl_account_number, gl.ca_account_name, gl.gl_local_amount_currency, gl.gl_reporting_amount_currency
  • Sort Method: quicksort Memory: 660kB
  • Worker 0: Sort Method: quicksort Memory: 388kB
  • Worker 1: Sort Method: quicksort Memory: 395kB
14. 223.260 3,675.820 ↓ 1.3 2,734 3 / 3

Merge Join (cost=163,392.34..168,966.22 rows=2,142 width=75) (actual time=3,397.188..3,675.820 rows=2,734 loops=3)

  • Merge Cond: (gl.gl_business_unit_and_journal_id_computed = intermediate_result.gl_business_unit_and_journal_id_computed)
15. 2,348.251 2,717.620 ↑ 1.3 424,721 3 / 3

Sort (cost=155,264.48..156,592.18 rows=531,078 width=99) (actual time=2,679.359..2,717.620 rows=424,721 loops=3)

  • Sort Key: gl.gl_business_unit_and_journal_id_computed
  • Sort Method: quicksort Memory: 82,685kB
  • Worker 0: Sort Method: quicksort Memory: 66,183kB
  • Worker 1: Sort Method: quicksort Memory: 68,295kB
16. 369.369 369.369 ↑ 1.3 424,741 3 / 3

Parallel Seq Scan on mega_gl_106189 gl (cost=0.00..104,762.78 rows=531,078 width=99) (actual time=0.023..369.369 rows=424,741 loops=3)

17. 692.702 734.940 ↓ 1.3 116,457 3 / 3

Sort (cost=8,127.85..8,354.91 rows=90,822 width=32) (actual time=717.712..734.940 rows=116,457 loops=3)

  • Sort Key: intermediate_result.gl_business_unit_and_journal_id_computed
  • Sort Method: quicksort Memory: 12,063kB
  • Worker 0: Sort Method: quicksort Memory: 12,063kB
  • Worker 1: Sort Method: quicksort Memory: 12,063kB
18. 42.238 42.238 ↓ 1.3 115,080 3 / 3

Function Scan on read_intermediate_result intermediate_result (cost=0.00..648.35 rows=90,822 width=32) (actual time=36.049..42.238 rows=115,080 loops=3)

19. 257.906 3,198.957 ↓ 0.0 115,080 1

HashAggregate (cost=0.00..0.00 rows=0 width=0) (actual time=3,088.016..3,198.957 rows=115,080 loops=1)

  • Group Key: remote_scan.gl_journal_id, remote_scan.gl_business_unit_code, remote_scan.gl_effective_date
20. 1,365.615 2,941.051 ↓ 0.0 115,080 1

Custom Scan (Citus Adaptive) (cost=0.00..0.00 rows=0 width=0) (actual time=2,933.938..2,941.051 rows=115,080 loops=1)

  • Task Count: 32
  • Tasks Shown: One of 32
  • -> Task
  • Node: host=10.0.0.34 port=5,432 dbname=citus
21. 5.843 1,575.436 ↑ 1.4 3,637 1

GroupAggregate (cost=163,185.72..163,391.36 rows=5,141 width=283) (actual time=1,569.148..1,575.436 rows=3,637 loops=1)

  • Group Key: gl.gl_journal_id, gl.gl_business_unit_code, gl.gl_effective_date
22. 13.798 1,569.593 ↓ 1.6 8,203 1

Sort (cost=163,185.72..163,198.57 rows=5,141 width=39) (actual time=1,569.133..1,569.593 rows=8,203 loops=1)

  • Sort Key: gl.gl_journal_id, gl.gl_business_unit_code, gl.gl_effective_date
  • Sort Method: quicksort Memory: 1,025kB
23. 153.665 1,555.795 ↓ 1.6 8,203 1

Hash Join (cost=141,822.21..162,868.83 rows=5,141 width=39) (actual time=1,397.860..1,555.795 rows=8,203 loops=1)

  • Hash Cond: (intermediate_result.gl_business_unit_and_journal_id_computed = gl.gl_business_unit_and_journal_id_computed)
24. 35.983 35.983 ↓ 1.3 115,080 1

Function Scan on read_intermediate_result intermediate_result (cost=0.00..648.35 rows=90,822 width=32) (actual time=28.620..35.983 rows=115,080 loops=1)

25. 512.206 1,366.147 ↑ 1.0 1,274,224 1

Hash (cost=112,197.87..112,197.87 rows=1,274,587 width=63) (actual time=1,366.147..1,366.147 rows=1,274,224 loops=1)

  • Buckets: 2,097,152 (originally 2097152) Batches: 2 (originally 1) Memory Usage: 120,833kB
26. 853.941 853.941 ↑ 1.0 1,274,224 1

Seq Scan on mega_gl_106189 gl (cost=0.00..112,197.87 rows=1,274,587 width=63) (actual time=0.018..853.941 rows=1,274,224 loops=1)

27. 550.212 1,156.111 ↓ 0.0 372,344 1

Custom Scan (Citus Adaptive) (cost=0.00..0.00 rows=0 width=0) (actual time=1,134.288..1,156.111 rows=372,344 loops=1)

  • Task Count: 1
  • Tasks Shown: All
  • -> Task
  • Node: host=localhost port=5,432 dbname=citus
28. 356.051 605.899 ↓ 186.2 372,344 1

Gather (cost=1,000.00..8,404.37 rows=2,000 width=324) (actual time=195.413..605.899 rows=372,344 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
29. 9.961 249.848 ↓ 2.3 124,115 3 / 3

Parallel Append (cost=0.00..7,204.37 rows=54,015 width=324) (actual time=231.692..249.848 rows=124,115 loops=3)

30. 172.448 172.448 ↓ 2.7 257,264 1 / 3

Function Scan on read_intermediate_result intermediate_result (cost=0.00..6,934.29 rows=95,427 width=324) (actual time=500.215..517.343 rows=257,264 loops=1)

31. 67.440 67.440 ↓ 3.4 115,080 1 / 3

Function Scan on read_intermediate_result intermediate_result_1 (cost=0.00..2,485.89 rows=34,210 width=324) (actual time=194.857..202.319 rows=115,080 loops=1)

32. 0.009 784.379 ↑ 1.0 100 1

Limit (cost=14,374.80..14,375.05 rows=100 width=324) (actual time=784.366..784.379 rows=100 loops=1)

33. 66.279 784.370 ↑ 1,296.4 100 1

Sort (cost=14,374.80..14,698.89 rows=129,637 width=324) (actual time=784.364..784.370 rows=100 loops=1)

  • Sort Key: intermediate_result.gl_journal_id, intermediate_result.gl_business_unit_code, intermediate_result.gl_account_number
  • Sort Method: top-N heapsort Memory: 49kB
34. 718.091 718.091 ↓ 2.9 372,344 1

Function Scan on read_intermediate_result intermediate_result (cost=0.00..9,420.18 rows=129,637 width=324) (actual time=696.168..718.091 rows=372,344 loops=1)

Planning time : 13.335 ms
Execution time : 33,094.810 ms